Preventing Deadlock: stored

Showing posts with label stored. Show all posts

Friday, March 30, 2012

print list of queries, tables, views and sp

I just started a new job and 1st time on sql server, how can i print list of queries, tables, views, stored procedures and functions?What do you mean by print?
What version of SQL Server are you running? This will have an effect on the query you need to run. The below example was written for 2000

SELECT name
, id
, type
FROM sysobjects
WHERE type IN ('V', 'U', 'SP', 'FN')
-- v = view, u = table, sp = sproc, fn = user-defined function|||2005 Users Note:
BOL Says
Important: This Microsoft SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use catalog views instead.

Any general comments as to whether we should still be coding with sysobjects ?

:angel:

GW|||We are kind of caught on the edge of the sword on this issue. Because users rarely give us enough information to know what version of SQL they are using, we tend to give them the answers that work under the largest possible set of conditions.

You are correct, using the catalog views is preferable if you are running a version of SQL Server that supports the catalog views. On a "going forward" basis, you probably ought to only use the catalog views, but on a "forum answer" I tend to stick with what will work for the largest number of people.

-PatP|||Anybody have the catalog solution to hand?

I don't get to play on much 2K5, but I am going to be taking my MCTS in it in a couple of months, so I should really get brushed up on it :p|||Play around with the view sys.objects. You should have it in no time. I think id changed to object_id, but most of the rest is the same.|||SELECT ROUTINE_TYPE, ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES

SELECT TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES-PatP

Wednesday, March 28, 2012

Print Attributes in Stored Procedure

I've upgraded MS-Access 2002 to a MS-Access Project (adp), so now I have to
deal with more sophisticated queries (may I call them so?) like stored
procedures. I have a form with a combobox for selections and a textbox to
enter a certain value. Let us say I call the combobox @.select and the
textbox @.find. The combobox always shows the first of the items to select.
Now I want to return a message if nothing is found, or if nothing has been
entered at all. The stored procedure reads for instance:

ALTER PROCEDURE OPC
@.select nvarchar(20), @.find nvarchar (100)

SET NOCOUNT ON

IF
@.find IS NULL
BEGIN
PRINT 'You didn't enter any value'
RETURN
END

IF
@.select = 'Author'
BEGIN
SELECT *
FROM Books
WHERE Author LIKE '%' + @.find + '%'
ORDER BY Author, Title, Publisher, Year
END

[And so on]

RETURN

This works correct when I enter something, but when I don't enter any value,
a message box pops up saying that the stored procedure has been executed,
but no records were found. I want to see an error message like above. It's
as simple as

ALTER PROCEDURE Hello
AS
BEGIN
PRINT 'Hello'
END
and nothing is seen. Does anybody know? Thank you.

Wim"Wim van Rosmalen" <wvanrosm@.tiscali.nl> wrote in message
news:410789c7$0$62355$5fc3050@.dreader2.news.tiscal i.nl...
> I've upgraded MS-Access 2002 to a MS-Access Project (adp), so now I have
to
> deal with more sophisticated queries (may I call them so?) like stored
> procedures. I have a form with a combobox for selections and a textbox to
> enter a certain value. Let us say I call the combobox @.select and the
> textbox @.find. The combobox always shows the first of the items to select.
> Now I want to return a message if nothing is found, or if nothing has been
> entered at all. The stored procedure reads for instance:
> ALTER PROCEDURE OPC
> @.select nvarchar(20), @.find nvarchar (100)
> AS
> SET NOCOUNT ON
> IF
> @.find IS NULL
> BEGIN
> PRINT 'You didn't enter any value'
> RETURN
> END
> IF
> @.select = 'Author'
> BEGIN
> SELECT *
> FROM Books
> WHERE Author LIKE '%' + @.find + '%'
> ORDER BY Author, Title, Publisher, Year
> END
> [And so on]
> RETURN
>
> This works correct when I enter something, but when I don't enter any
value,
> a message box pops up saying that the stored procedure has been executed,
> but no records were found. I want to see an error message like above. It's
> as simple as
> ALTER PROCEDURE Hello
> AS
> BEGIN
> PRINT 'Hello'
> END
> and nothing is seen. Does anybody know? Thank you.
> Wim

I'm not at all sure, but if your code works as expected in Query Analyzer,
but you're running from an ADP, then perhaps Access is not handling the
PRINT output correctly - it's returned as an informational error (see "Using
PRINT" in Books Online). You might want to investigate how Access handles
this specific output - posting in an Access newsgroup might be useful.

Simon|||Wim van Rosmalen (wvanrosm@.tiscali.nl) writes:
> I've upgraded MS-Access 2002 to a MS-Access Project (adp), so now I have
> to deal with more sophisticated queries (may I call them so?) like
> stored procedures. I have a form with a combobox for selections and a
> textbox to enter a certain value. Let us say I call the combobox @.select
> and the textbox @.find. The combobox always shows the first of the items
> to select. Now I want to return a message if nothing is found, or if
> nothing has been entered at all. The stored procedure reads for
> instance:
>...
> IF @.find IS NULL
> BEGIN
> PRINT 'You didn't enter any value'
> RETURN
> END
>...
> This works correct when I enter something, but when I don't enter any
> value, a message box pops up saying that the stored procedure has been
> executed, but no records were found. I want to see an error message like
> above. It's as simple as

SQL Server is not Access, so a PRINT statement does not by itself outupt
something anywhere. It is up to the client to pick it up. And then you
will have to code for it in the client. From Access, you usually access
SQL Server from ADO, and ADO is not very good making messages avilable
anyway.

You may want to try to use RAISERROR instead:

RAISERROR('You didn''t enter any value', 11, 1)

this causes ADO to raise an error as well.

Disclaimer: I don't know Access or ADP, but I'm speaking from general
experience of ADO programming.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland! I knew about RAISERROR, but in a S.P. like this, PRINT is
returned by the same configuration (MSDE, which doesn't have a
query-analyser):

ALTER PROCEDURE StoredProcedure1
AS
SET NOCOUNT ON

DECLARE @.msg varchar(255)
-- If 53000 is changed into 3000, titles beginning with TE are displayed.
-- If 53000 (or any high number) is not changed, titles beginning with TA
are displayed.
IF (SELECT COUNT(Nr)
FROM RecordTable
WHERE Title LIKE 'Ta%' AND Nr BETWEEN 2000 AND 53000) > 0
BEGIN
SET NOCOUNT ON
SET @.msg = 'Error Message Nr. One'
PRINT @.msg
SELECT title
--If the name "RecordTable" is changed, Error Message Nr. One is displayed
(related to a high number;.
--related to a low number an empty table is displayed).
FROM RecordTable
WHERE title LIKE 'Ta%'
END
ELSE
BEGIN
SET NOCOUNT ON
SET @.msg = 'Error Message Nr. Two'
PRINT @.msg
SELECT title
--If the name "RecordTable" is changed, Error Message Nr. Two is displayed
(related to a low number;.
--related to a high number titles beginning with TA are displayed).
FROM RecordTables
WHERE title LIKE 'Te%'
END

Best regards,

Wim van Rosmalen

"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns9534F3AE283C2Yazorman@.127.0.0.1...
> Wim van Rosmalen (wvanrosm@.tiscali.nl) writes:
> > I've upgraded MS-Access 2002 to a MS-Access Project (adp), so now I have
> > to deal with more sophisticated queries (may I call them so?) like
> > stored procedures. I have a form with a combobox for selections and a
> > textbox to enter a certain value. Let us say I call the combobox @.select
> > and the textbox @.find. The combobox always shows the first of the items
> > to select. Now I want to return a message if nothing is found, or if
> > nothing has been entered at all. The stored procedure reads for
> > instance:
> >...
> > IF @.find IS NULL
> > BEGIN
> > PRINT 'You didn't enter any value'
> > RETURN
> > END
> >...
> > This works correct when I enter something, but when I don't enter any
> > value, a message box pops up saying that the stored procedure has been
> > executed, but no records were found. I want to see an error message like
> > above. It's as simple as
> SQL Server is not Access, so a PRINT statement does not by itself outupt
> something anywhere. It is up to the client to pick it up. And then you
> will have to code for it in the client. From Access, you usually access
> SQL Server from ADO, and ADO is not very good making messages avilable
> anyway.
> You may want to try to use RAISERROR instead:
> RAISERROR('You didn''t enter any value', 11, 1)
> this causes ADO to raise an error as well.
> Disclaimer: I don't know Access or ADP, but I'm speaking from general
> experience of ADO programming.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Wim van Rosmalen (wvanrosm@.tiscali.nl) writes:
> Thanks Erland! I knew about RAISERROR, but in a S.P. like this, PRINT is
> returned by the same configuration

Yes, ADO is inconsistent in how it handles PRINT statements. It does
slightly better on errors.

>(MSDE, which doesn't have a query-analyser):

You can use the command-line tool OSQL to run the queries. There you
will always see the PRINT statements.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||O.K. Erland, thanks again!

Wim

"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns9535EFF62F45CYazorman@.127.0.0.1...
> Wim van Rosmalen (wvanrosm@.tiscali.nl) writes:
> > Thanks Erland! I knew about RAISERROR, but in a S.P. like this, PRINT is
> > returned by the same configuration
> Yes, ADO is inconsistent in how it handles PRINT statements. It does
> slightly better on errors.
> >(MSDE, which doesn't have a query-analyser):
> You can use the command-line tool OSQL to run the queries. There you
> will always see the PRINT statements.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||For MS verbiage on the topic, take a look at

http://support.microsoft.com/defaul...&NoWebContent=1

"Wim van Rosmalen" <wvanrosm@.tiscali.nl> wrote in message
news:410a5f89$0$62373$5fc3050@.dreader2.news.tiscal i.nl...
> O.K. Erland, thanks again!
> Wim
> "Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
> news:Xns9535EFF62F45CYazorman@.127.0.0.1...
> > Wim van Rosmalen (wvanrosm@.tiscali.nl) writes:
> > > Thanks Erland! I knew about RAISERROR, but in a S.P. like this, PRINT
is
> > > returned by the same configuration
> > Yes, ADO is inconsistent in how it handles PRINT statements. It does
> > slightly better on errors.
> > >(MSDE, which doesn't have a query-analyser):
> > You can use the command-line tool OSQL to run the queries. There you
> > will always see the PRINT statements.
> > --
> > Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> > Books Online for SQL Server SP3 at
> > http://www.microsoft.com/sql/techin.../2000/books.asp

Print and Raiserror

I wanted to give user message from SQL Stored Procedure.
I tried to use PRINT, but it does not go to my MS Access front end
application
I tried to use 'RAISERROR' to send out message, but the message has SQL
server message to my MS Access.
Are there any way to sent message to my front end application user form
stored procedure?
Any information is great appreciated.
Souris,souris wrote:
> I wanted to give user message from SQL Stored Procedure.
> I tried to use PRINT, but it does not go to my MS Access front end
> application
> I tried to use 'RAISERROR' to send out message, but the message has
> SQL server message to my MS Access.
> Are there any way to sent message to my front end application user
> form stored procedure?
> Any information is great appreciated.
> Souris,
You can use RAISERROR. Please post you code to generate the error and
exactly the problem you are seeing with using it. I assume you want to
notify the client app about an error condition and not just a normal
message. Is that correct?
For example, you could use:
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
or
RAISERROR ('The level for job_id:%d should be between %d and %d.', 16,
1, @.@.JOB_ID, @.@.MIN_LVL, @.@.MAX_LVL)
David Gugick
Imceda Software
www.imceda.com|||Thanks for the message.
Here is my SQL
BEGIN
RAISERROR('YOU HAVE UPDATE DATA TODAY, IF YOU THINK YOU NEED UPLOAD ONE
MORE TIME PLEASE SEE YOUR ADMINISTRATOR, THANKS', 16, -1)
RETURN
END
The message comes following form my Access front end
Microsoft Access
ODBC - call failer
[Microsoft][ODBC SQL server Driver][SQL Driver]YOU AHVE UPDATE DATA TODAY,
OF YOU THINK YOU NEED UPLOAD ONE MORE TIME PLEASE SEE YOUR ADMINISTARTOR,
THANKS(#50000) [Microsoft][ODBC driver SQL Server][SQL server] Violation of
PRIMARY KEY constaint 'PK_TSC_CMPLD_CALLOUTS', Cannot insert duplicate key
in object 'TSC_CMPLD_CALLOUTS'. (#2627)Microsoft][ODBC SQL server
Driver][SQL Driver]The statement has been terminated.(#3621)[ODBC driver SQL
Server][SQL server] Violation of PRIMARY KEY constaint
'PK_TSC_CMPLD_CALLOUTS', Cannot insert duplicate key in object
'TSC_CMPLD_CALLOUTS'. (#2627)Microsoft][ODBC SQL server Driver][SQL
Driver]The statement has been terminated.(#3621)
How can I give the user message from my stored procedure?
Why my Access front give so much message to user?
Any information is great appreciated.
Thanks again,
Souris,
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23OSTKNfJFHA.1308@.TK2MSFTNGP15.phx.gbl...
> souris wrote:
> You can use RAISERROR. Please post you code to generate the error and
> exactly the problem you are seeing with using it. I assume you want to
> notify the client app about an error condition and not just a normal
> message. Is that correct?
>
> For example, you could use:
> RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
> or
> RAISERROR ('The level for job_id:%d should be between %d and %d.', 16, 1,
> @.@.JOB_ID, @.@.MIN_LVL, @.@.MAX_LVL)
>
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||souris wrote:
> Thanks for the message.
> Here is my SQL
> BEGIN
> RAISERROR('YOU HAVE UPDATE DATA TODAY, IF YOU THINK YOU NEED UPLOAD
> ONE MORE TIME PLEASE SEE YOUR ADMINISTRATOR, THANKS', 16, -1)
> RETURN
> END
Sounds like you are generating a lot of errors in whatever SQL you are
running. Try running the SQL with the same parameter values from Query
Analyzer or some other query tool (probably can use Access as well) and
make sure it doesn't generate any errors.
It looks like you are using RAISERROR to return a message. RAISERROR is
for raising errors only, not for messages. I would have the application
display the message on it's own, and not have it generated from the
database.
David Gugick
Imceda Software
www.imceda.com|||Thanks for the message.
My App needs download once and only once per day
The date filed is a part of my primary key.
I wanted to use RAISERROR to raise error and also let user know what
happens.
1. If I use RAISERROR the SQL statement should not continue executing the
rest of SQL, but it seems continues running to violate primary key rule.
2. If I need to write message on front end application then there must be a
way to get rid the [Microsoft ODBC][SQL Server] message from RAISERROR.
Your information is great appreciated
Souris,
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OTuFBvyJFHA.3552@.TK2MSFTNGP12.phx.gbl...
> souris wrote:
> Sounds like you are generating a lot of errors in whatever SQL you are
> running. Try running the SQL with the same parameter values from Query
> Analyzer or some other query tool (probably can use Access as well) and
> make sure it doesn't generate any errors.
> It looks like you are using RAISERROR to return a message. RAISERROR is
> for raising errors only, not for messages. I would have the application
> display the message on it's own, and not have it generated from the
> database.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||souris wrote:
> Thanks for the message.
> My App needs download once and only once per day
> The date filed is a part of my primary key.
> I wanted to use RAISERROR to raise error and also let user know what
> happens.
> 1. If I use RAISERROR the SQL statement should not continue executing
> the rest of SQL, but it seems continues running to violate primary
> key rule.
> 2. If I need to write message on front end application then there
> must be a way to get rid the [Microsoft ODBC][SQL Server] message
> from RAISERROR.
It looks like the snippet of code you posted should work. You are using
raiserror in a block with a return statement (you should add a return
code). I don't see how the rest of the SQL is being executed unless it
occurred before the raiserror. Can you post the entire procedure?
David Gugick
Imceda Software
www.imceda.com

Print

Hello Guys,

Is it Possible to receive the PRINT Message from a Stored Procedure in the applications?

If no, how SQL Entrerpris Manager receives the print generated from the RESTORE DATABASE WIHT STAT ?

RESTORE DATABASE [dbProdApp]
FROM DISK = N'C:\S_HOMSQL_Db_20030513_dbProdSys_Temp_Manual.ba k'
WITH
FILE = 1,
NOUNLOAD ,
STATS = 10,
RECOVERY ,
REPLACE ,
MOVE N'dbSolomonProdSys_Log' TO N'c:\dbProdSys_Temp_log.ldf',
MOVE N'dbSolomonProdSys_Data' TO N'c:\dbProdSys_Temp.mdf'

Thankxs Everybody!You can mention PRINT statement in the SP to get the information. Refer to books online for PRINT topic.|||BUt I'd like to receive the print messages in a ASP application for example.

Or save the messages in a temporary table.sql

Print

When executing a print statment in a stored procedure that is called from an
odbc application. The procedure stops without any furhter information.
SQLServer 2000 sp3
Jones
"Ricky Jones" wrote:

> When executing a print statment in a stored procedure that is called from an
> odbc application. The procedure stops without any furhter information.
> SQLServer 2000 sp3
> --
> Jones
Just to let people know that in certain cases when you apply patch
SQL2000-KB810185-8.00.0859 executing a sqlserver stored procedure print
statments will fail and cause the procedure to stop execting imedialty with
no error messages.

Print

Hi all,
Can anyone tell me if by having a print of a variable i.e. (print @.strsql)
in a stored procedure slows down the overall performace, even by millisecond
s
as I have run some test with STATISTICS TIME ON and most of the time they ar
e
quicker with the print in, is this right?
Thanks PhilAny time you send more data, it's going to slow down a little bit. Whether
it is measurable really depends on your hardware and other factors, so
you're just going to have to test it.
For more interesting results, test a stored procedure that returns multiple
resultsets, and then put SET NOCOUNT ON at the beginning and test it again.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:4EBF1ED7-775C-490D-A074-AC1D1EB247AB@.microsoft.com...
> Hi all,
> Can anyone tell me if by having a print of a variable i.e. (print @.strsql)
> in a stored procedure slows down the overall performace, even by
> milliseconds
> as I have run some test with STATISTICS TIME ON and most of the time they
> are
> quicker with the print in, is this right?
> Thanks Phil

Monday, March 26, 2012

Print

When executing a print statment in a stored procedure that is called from an
odbc application. The procedure stops without any furhter information.
SQLServer 2000 sp3
--
Jones"Ricky Jones" wrote:

> When executing a print statment in a stored procedure that is called from
an
> odbc application. The procedure stops without any furhter information.
> SQLServer 2000 sp3
> --
> Jones
Just to let people know that in certain cases when you apply patch
SQL2000-KB810185-8.00.0859 executing a sqlserver stored procedure print
statments will fail and cause the procedure to stop execting imedialty with
no error messages.

Primary Keys When importing data

I am creating a stored procedure for our SQL server which does the following:
1. Drops a table called 'Teachers'

drop table [users_dd].[dbo].[Teachers]

2. Recreates that table ready for new data like so...

CREATE TABLE [users_dd].[dbo].[Teachers] (
[username] char (30) NOT NULL,
[SL_Teachcode] nvarchar (12) NULL,
[DueDate] smalldatetime DEFAULT GetDate(),
[ID] INT IDENTITY (1,1) PRIMARY KEY NOT NULL)

3. The last step attempts to import new data. I have used the import/export data wizard to create this query to import new data.

select [users].[username], [users].[SL_Teachcode]
from [users]
where [users].[Staff_teaching]=1
order by [users].[SL_Teachcode]

The last step returns an error because the ID field will not accept Nulls. Of course I don't want it to have nulls as this is to be the primary key for this table. What I want is an automatically generated number there but how do I make that happen? I thought it was all in the definition of the field, ie [ID] INT IDENTITY (1,1) PRIMARY KEY NOT NULL). It seems there is something I missing here however.

Can anybody tell me what I doing wrong (or not doing)?

Murrayuse some thing like:

insert into [users_dd].[dbo].[Teachers] (
[username],].[SL_Teachcode])
select [users].[username], [users].[SL_Teachcode]
from [users]
where [users].[Staff_teaching]=1
order by [users].[SL_Teachcode]

tel us if it works
i try it on my side , it works.
Ridwan,,,|||If you do not need transactional recovery during this load you might do one or a combination of the folowing.

Instead of delete the table with corresponding rows, you might truncate it first and then delete it, less logged and faster. If you do drop the table, you can use select into instead of insert as that is minimally logged and faster as well.

HTH|||SELECT INTO is not faster and is not recommended because of concurrency implications. Truncating and then deleting just doesn't make sence. Why not just TRUNCATE?|||select into is not faster than insert into? I beg to differ, as inserts are fully logged they will take longer. They will also bloat your transaction log and if you have to reallocate space on a drive that could really hurt performance and slow things down. With select into there is no question of drive space allocation and it will run faster. Just for kicks I just ran a select into statement for 1.4 million rows, it took 26 seconds. For that same structure and table an insert into took 2 minutes and 54 seconds.

Now, obviously the argument would be the log and possibly the database file was growing in this time span so to make it an even playing field I truncated the table and did it again with no hard drive growth. The insert into only took 15 seconds without the hard drive growth but after a truncate and a drop table the select into only took 9 seconds.

So, I do not see any situation where the insert into is as fast as the select into and you get the bonus of knowing the hard drive will not have to reallocate space for the log file.

Yes, I meant truncate then DROP the table, not delete. Truncate with delete does not make sense, agreed.|||yup, blurred it out too fast :o

rhigdon, you're absolutely right, - did a 1M-row select into in 20 seconds and insert has been running for the past 6 minutes (Personal edition).

Now you got me experimenting with increased preallocated trx. log.

But I also noticed that we're assuming that recovery mode is Simple. Will it be the same difference in speed if I have it set to Full? Well, I'm still waiting on my insert :)|||No problem :)

I would assume so about the full and simple, you can watch that t-log grow by playing with the dbcc sqlperf('logspace') before and after inserts or select into's.|||BTW, do you have your auto-grow settings enabled? Most of our apps here got converted from 6.5 to either directly 2K or to 7.0 then 2K. Since in 6.5 world we didn't have this luxury of auto-growing, once discovered, - we first got relieved, but then started noticing that for 150+ servers we will have to implement space utilization monitors based on activity within each database. And then - react really quickly once the size reaches the disk limitation. So instead we performed a 2-month space growth analysis and pre-estimated potential annual growth for each database. Then we pre-allocated space on both data and trx. log devices and turned off auto-growth feature. Now we can tell with the level of accuracy to +/- 10MB how much disk space will be needed for any given database by the beginning of the next year.|||Thanks Ridwan,
I tried that code and worked fine. I think I went wrong by using the import/exprot data wizard to construct my query. As soon as I did it 'by hand' it worked fine.
Interesting conversation about the difference between select into and insert into. In this example there will never be more than about 90 -100 records so speed is not a big issue to this task as it takes less than a second to run anyhow.
Thank you all for your input... I hope I can return the favour someday.
Murray|||rdjabarov - that sounds pretty interesting, yes, we use auto-grow for everything. I do have a script that uses some stored procs that runs daily to check for free space and alert me if it gets low though.

Friday, March 23, 2012

Primary Key Violation Constraint, how to debug.....

Hi all,
I have a stored procedure (from a vendor) that attempts to insert some
records.. Unfortunately, its a very buggy early version, and tech support is
sketchy at best, so I'm trying to figure out the problem myself..
This is the error I'm getting:
Server: Msg 2627, Level 14, State 1, Procedure
usp_SAIncShipToDeliveryLocation, Line 87
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
object 'SA_ShipToDeliveryLocation'.
This is the code fragment that is causing the problem. I'd like to identify
the specific record that is causing primary key violation.. Is this
possible?
insert into SA_ShipToDeliveryLocation
(ShipToDeliveryLocationID
,ShipToDeliveryLocationName
,ShipToDeliveryLocationState
,ShipToDeliveryLocationZip
,ShipToDeliveryLocationCountry
,RegionKey
,CustomerKey
,ShipToDeliveryLocationKey
)
select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
when 0
then r.RegionKey
else null
end RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
from #SASTemp_ShipToDeliveryLocation sd
left join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A'
order by c.CustomerKey
,sd.ShipToDeliveryLocationID
set @.nError = @.@.error
if (@.nError <> 0)
begin
rollback tran;
return @.nError;
endTake your select statment, remove all columns but those in the PK column (or
columns), group by these columns and add a having clause (having count(*) >
1). Off hand, I think the use of a cross join is suspicious (unless there
is only one row in the table). Another thing to check is a poorly written
insert trigger (but that should generate an error with the trigger name in
it).|||There are 2 possible causes for this error. Either a row with the PK value
already exists in the target table or the select statement is returning more
that one row with the same key.
Assuming ShipToDeliveryLocationKey is the primary key of
SA_ShipToDeliveryLocation, you can include your source query as a derived
table to easily identify problem data. See untested examples below.
The CROSS JOIN looks suspect here since this will effectively multiply the
number or rows returned. You'll get the PK error if the
SA_ControlParameters table contains more than one row.
--keys that already exist
SELECT source.*
FROM (
SELECT sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
WHEN 0
THEN r.RegionKey
ELSE NULL
END RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A') source
WHERE EXISTS
(
SELECT *
FROM SA_ShipToDeliveryLocation target
WHERE target.ShipToDeliveryLocationKey =
source.ShipToDeliveryLocationKey
)
--keys that duplicated in source query
SELECT source.*
FROM (
SELECT sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
,case cp.IsSOP
WHEN 0
THEN r.RegionKey
ELSE NULL
END RegionKey
,c.CustomerKey CustomerKey
,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r on sd.RegionID = r.RegionID
and r.RegionType = 'R'
join SA_Customer c on c.CustomerKey = sd.CustomerKey
cross join SA_ControlParameters cp
where sd.AddChangeDelete = 'A') source
JOIN
(SELECT sd.ShipToDeliveryLocationKey
FROM #SASTemp_ShipToDeliveryLocation sd
LEFT join SA_Region r ON sd.RegionID = r.RegionID
ANDr.RegionType = 'R'
JOIN SA_Customer c ON c.CustomerKey = sd.CustomerKey
CROSS JOIN SA_ControlParameters cp
WHERE sd.AddChangeDelete = 'A'
GROUP BY sd.ShipToDeliveryLocationKey
HAVING COUNT(*) > 1) dups ON
dups.ShipToDeliveryLocationKey = source.ShipToDeliveryLocationKey
Hope this helps.
Dan Guzman
SQL Server MVP
"certolnut" <whitney_neal@.hotmail.com> wrote in message
news:OGHQOFVEGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I have a stored procedure (from a vendor) that attempts to insert some
> records.. Unfortunately, its a very buggy early version, and tech support
> is sketchy at best, so I'm trying to figure out the problem myself..
> This is the error I'm getting:
> Server: Msg 2627, Level 14, State 1, Procedure
> usp_SAIncShipToDeliveryLocation, Line 87
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
> constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
> object 'SA_ShipToDeliveryLocation'.
> This is the code fragment that is causing the problem. I'd like to
> identify the specific record that is causing primary key violation.. Is
> this possible?
>
> insert into SA_ShipToDeliveryLocation
> (ShipToDeliveryLocationID
> ,ShipToDeliveryLocationName
> ,ShipToDeliveryLocationState
> ,ShipToDeliveryLocationZip
> ,ShipToDeliveryLocationCountry
> ,RegionKey
> ,CustomerKey
> ,ShipToDeliveryLocationKey
> )
> select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
> ,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
> ,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
> ,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
> ,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
> ,case cp.IsSOP
> when 0
> then r.RegionKey
> else null
> end RegionKey
> ,c.CustomerKey CustomerKey
> ,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
> from #SASTemp_ShipToDeliveryLocation sd
> left join SA_Region r on sd.RegionID = r.RegionID
> and r.RegionType = 'R'
> join SA_Customer c on c.CustomerKey = sd.CustomerKey
> cross join SA_ControlParameters cp
> where sd.AddChangeDelete = 'A'
> order by c.CustomerKey
> ,sd.ShipToDeliveryLocationID
> set @.nError = @.@.error
> if (@.nError <> 0)
> begin
> rollback tran;
> return @.nError;
> end
>|||Thanks for the advice guys. Dan I'll give the derived table a shot and get
back to
Thanks very much
"certolnut" <whitney_neal@.hotmail.com> wrote in message
news:OGHQOFVEGHA.516@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I have a stored procedure (from a vendor) that attempts to insert some
> records.. Unfortunately, its a very buggy early version, and tech support
> is sketchy at best, so I'm trying to figure out the problem myself..
> This is the error I'm getting:
> Server: Msg 2627, Level 14, State 1, Procedure
> usp_SAIncShipToDeliveryLocation, Line 87
> [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
> constraint 'PK_ShipToDeliveryLocation'. Cannot insert duplicate key in
> object 'SA_ShipToDeliveryLocation'.
> This is the code fragment that is causing the problem. I'd like to
> identify the specific record that is causing primary key violation.. Is
> this possible?
>
> insert into SA_ShipToDeliveryLocation
> (ShipToDeliveryLocationID
> ,ShipToDeliveryLocationName
> ,ShipToDeliveryLocationState
> ,ShipToDeliveryLocationZip
> ,ShipToDeliveryLocationCountry
> ,RegionKey
> ,CustomerKey
> ,ShipToDeliveryLocationKey
> )
> select sd.ShipToDeliveryLocationID ShipToDeliveryLocationID
> ,sd.ShipToDeliveryLocationName ShipToDeliveryLocationName
> ,sd.ShipToDeliveryLocationState ShipToDeliveryLocationState
> ,sd.ShipToDeliveryLocationZip ShipToDeliveryLocationZip
> ,sd.ShipToDeliveryLocationCountry ShipToDeliveryLocationCountry
> ,case cp.IsSOP
> when 0
> then r.RegionKey
> else null
> end RegionKey
> ,c.CustomerKey CustomerKey
> ,sd.ShipToDeliveryLocationKey ShipToDeliveryLocationKey
> from #SASTemp_ShipToDeliveryLocation sd
> left join SA_Region r on sd.RegionID = r.RegionID
> and r.RegionType = 'R'
> join SA_Customer c on c.CustomerKey = sd.CustomerKey
> cross join SA_ControlParameters cp
> where sd.AddChangeDelete = 'A'
> order by c.CustomerKey
> ,sd.ShipToDeliveryLocationID
> set @.nError = @.@.error
> if (@.nError <> 0)
> begin
> rollback tran;
> return @.nError;
> end
>

Saturday, February 25, 2012

Previous and Next functions

Hi,

I am tasked with duplicating many Oracle report in SRS. One uses a stored proc to get a list of people and sorts them by name. It then prints in red those names that are duplicates. It uses this bit of code to do it:

If {PlayerName} =
Previous ({PlayerName})
or
{PlayerName} =
Next ({PlayerName})
then Red
else Black

I don't find any quite so simple functions to do this.

I'm at the moment using VS2003 against SQL Server 2005.

Thanks,

Fairfield

We do have a Previous function http://msdn2.microsoft.com/en-us/library/ms156372.aspx

Next is not currently supported. An alternative way of doing this is to use custom code. You'd pass in all the names into the custom code and detect duplicates there (by using a hashtable for example). And then in the color style property call a custom function that looks in the hashtable and returns the desired color based on whether the specific name is a dup.

|||Thanks, I'll give it a go.

preview tab does not display bound data

I have a table in my report that is bound to a dataset which in turn is bound
to a stored procedure. When I preview the report, the table remains blank,
despite the fact that I have bound the columns from my dataset to the table
on my report.
I get the following warning in my debug summary:
The data set â'DatasetNameâ' contains a definition for the field â'FieldName.
This field is missing from the returned result set from the data source.
However, I'm sure the result set is returning this field.
Any ideas on what could cause this?
Thanks.i figured out what the problem was. i did not properly bind the dataset.
"momo" wrote:
> I have a table in my report that is bound to a dataset which in turn is bound
> to a stored procedure. When I preview the report, the table remains blank,
> despite the fact that I have bound the columns from my dataset to the table
> on my report.
> I get the following warning in my debug summary:
> The data set â'DatasetNameâ' contains a definition for the field â'FieldName.
> This field is missing from the returned result set from the data source.
> However, I'm sure the result set is returning this field.
> Any ideas on what could cause this?
> Thanks.

Preview has data but no columns returned

In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

I just ran a test on my side and it actually worked. I create a simple sp with a query (select * from adventureworks.production.productcategory) and the use it in the OLE DB. The preview worked fine and when connecting to a copy column transform I was able to see all the columns. However there is another thread going on now about the same issue and they say there are some work arounds:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=786591&SiteID=1

In case you need it this is the xml code of my package

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">MARINER\rsalas</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">MARINERLAPTOP14</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">10/4/2006 8:05:36 AM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">3</DTS:Property><DTS:Property DTS:Name="VersionGUID">{28EC0411-DE09-4FBE-AA35-FA11901A0511}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>

<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ETLRafLab</DTS:Property><DTS:Property DTS:Name="DTSID">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=MARINERLAPTOP14;Initial Catalog=ETLRafLabDev;Provider=SQLNCLI.1;Integrated Security=SSPI;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:BoundingTop>2593</dwd:BoundingTop><dwd:Layout><dds>

<font>

</font>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="DTSID">{D803BA1C-C91E-408F-9D6F-1226D9333CD0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><ConnectionManager xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DataSourceID>ETLRafLab</dwd:DataSourceID></ConnectionManager></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="DTSID">{CDC092B4-325E-4283-8E03-88E168C82372}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsDataFlowDiagram><dwd:BoundingTop>1667</dwd:BoundingTop><dwd:Layout><dds>

<font>

</font>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</ddsxmlobj>

</control>

</ddsxmlobj>

</layoutobject>

</connector>

</ddscontrol>

</dds></dwd:Layout></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="DTSID">{A8831B5B-654F-43D4-BAA9-605512FCCDB0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DestinationName>Copy Column Input</dwd:DestinationName><dwd:SourceName>OLE DB Source Output</dwd:SourceName></PipelinePath></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}-86</DTS:Property><DTS:Property DTS:Name="DTSID">{2C57A2EE-F8A6-4FD6-8BAF-54C95E9E30B7}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:Executable DTS:ExecutableType="DTS.Pipeline.1"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">DTS.Pipeline.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">

<property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None">Exec sp_SelectFromProductCategory</property>

<property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>

</outputs>

</component>

</inputs>

</outputs>

</component>

</components>

<paths>

</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">OLEDB source with SP</DTS:Property><DTS:Property DTS:Name="DTSID">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

|||yeah I have a similar package connecting to another database (and executing a stored procedure) and that works just fine. But the package that I described in my last email just refuses to get going...btw..thanks for the other link - let me check if tweaking the procedure helps - appreciate your help!!|||

Manmeet Panigrahi wrote:
In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

I think the root cause of the problem here is that sprocs don't own their own metadata like tables and views do. It is theoretically possible to return completely different result sets from the same sproc depending on what parameters you pass it so there is no real metadata to bind to. Adam Machanic has a great discussion about this here:

Stored procedures are not parameterized views
(http://www.sqljunkies.com/WebLog/amachanic/archive/2006/05/29/21482.aspx)

Try the workaround that I suggested on the other thread.

-Jamie

|||

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!

So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

|||

Manmeet Panigrahi wrote:

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!
So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

There's similar. You could set the sql dynamically at runtime using an expression. That might be a very clever workaround actually. I'd be interested to see if it works.

-Jamie

|||Unfortunately that didnt work too...this is what I did...I now have this variable that stores the command to execute the proc that returns the data (EXEC pr_....). (This variable is populated at run time). Also this variable becomes the Data Access Mode of my OLEDB data source. Now at design time, I have this variable populated with a select statement that returns data in the correct schema format..so that I am able to setup my source destination mappings. So when the package runs, the variable gets populated (at runtime) with the EXEC statement...but unfortunately SSIS revalidates the schema (and detects that the EXEC does not return any columns) and gives me the "VS_NEEDSNEWMETADATA" errror....sigh...Unfortunately the proc is owned by a different set of ppl and it will take a lot of time for me to convince them to change it..

Preview has data but no columns returned

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=786591&SiteID=1

In case you need it this is the xml code of my package

<font>

</font>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

<font>

</font>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</ddsxmlobj>

</control>

</ddsxmlobj>

</layoutobject>

</connector>

</ddscontrol>

</outputs>

</component>

</inputs>

</outputs>

</component>

</components>

<paths>

Manmeet Panigrahi wrote:
In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

Stored procedures are not parameterized views
(http://www.sqljunkies.com/WebLog/amachanic/archive/2006/05/29/21482.aspx)

Try the workaround that I suggested on the other thread.

-Jamie

|||

So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

|||

Manmeet Panigrahi wrote:

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!
So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

There's similar. You could set the sql dynamically at runtime using an expression. That might be a very clever workaround actually. I'd be interested to see if it works.

-Jamie

Friday, March 30, 2012

print list of queries, tables, views and sp

Wednesday, March 28, 2012

Print Attributes in Stored Procedure

Print and Raiserror

Print

Print

Print

Monday, March 26, 2012

Print

Primary Keys When importing data

Friday, March 23, 2012

Primary Key Violation Constraint, how to debug.....

Saturday, February 25, 2012

Previous and Next functions

preview tab does not display bound data

Preview has data but no columns returned

Preview has data but no columns returned

Preventing Deadlock

Blog Archive

About Me