Activation not working

Hi There

Everything works 100% until i activate my sp.

I specify to execute as 'dbo' in the alter queue statement. I also define the activated sp to execute as 'dbo'.

But i keep getting permission errors from my activated sp. i have tried to excute as 'sa' , i have even tried to write a job that excutes to stored procedure but it also get weird errors. Bottom line if i exceute the sp in management studio logged in as sa it works , but thorugh activation or a job nothing works, as 'dbo' or 'sa'.

?

Thanx

Hi there.

It seems the statement in my sp that has a permission problem is the get conversation group sattement.

So my activation on the queue does not work when i stipulate execute as 'dbo' or OWNER. I cannot assign execute as 'sa' i get the following error "The server principal "sa" is not able to access the database "OLTP1PRO" under the current security context". I have tried not to stipulate the execute as so taht it defaults to a sysadmin.

But i always get this error:

The user does not have permission to perform this action.

So i tried the following.

CREATE LOGIN QueueReader with password = 'T#ST123'

CREATE USER QueueReader for login QueueReader

grant receive on [ewx.co.za/Queue/ewx_sb_central_queue] to QueueReader

grant execute on dbo.CENTRAL_Queue_Processor to QueueReader

And chanegd the execute as to QueueReader, but is till get the same error.

I dunno what else to try, no matter hwat i do i cannot execute the get conversation group statement ?

Please help ?

|||anyone?|||

What error do you get if you manually invoke the procedure, from Management Studio, under the QueueReader user context?

executeasuser'QueueReader';

go

exec dbo.CENTRAL_Queue_Processor;

go

revert;

go

|||

Hi Remus

I get the same error as before. The user does not have permission to perform this action. Error number 297.

I have done more investigation, judging by the erro line number line it seems that the QueueReader user cannot read from

sys.dm_broker_activated_tasks.

However i am expecting these errors, the activated stored procedure has many inserts etc. SO basically i want the activated sp to run as dbo, i do not want to have to setup alot of permission for the user.

My biggest question is why does EXECUTE as 'dbo' or OWNER not work ? How can i get permission errors when it executes as dbo ?

So i make QueueReader dbo of the database and i still get the user doesnot have permission error, so it seems only sysadmin can select from the sys. views, however i am still stumped , since the dbo of the database is a sysadmin. If i login as the user who is the dbo i can execute it. But if i say execute as dbo i get the permission error, i am totally stumped ?

Thanx

|||

Ok so it seems that if i reference sis.dm views in my activated sp i have to make the execute as user a sysadmin, or grant that user select on the sys.dm views , is this correct?

However i am still interested to know why execute as dbo does nto work when the dbo is also a sysadmin?

Thanx Remus you rfeedabck will be greatly appreciated.

|||

Hi Remus

Lastly, it also mentions in Roger Wolters book that if you do not stipulate execute as for your activated sp it will execute in the backgrouond as a sysadmin. However if i do not stipulate execute as i still get the permission error ? So not sure what is happening there?

Thanx

|||

Execute as user is restricted to database context. sysadmin is a server context concept. An untrusted database cannot extend the context to the server. This topic is covered in my blog in these posts: http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx, http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx and http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

The simplest solution is to mark the database as trustworthy.

HTH,
~ Remus

|||

Thanx Remus

The links were worth reading and the trustworthy worked.

Just as a matter of interrest , what is the best practice in a prouction environment when the activation proc requires sys.dm views. Do you make the queue reader user a sysadmin (must be a bad idea), or make the dbo of the database a sysadmin, or simply grant the user permission to the sys.dm views (probably best) ? In gerneral when the actvation proc must use these views what would you reccomend ?

|||

The safest option is code signing (as in the exaple in my link). Getting code siging righ is really hard, so if you can get away with trustworthy bit, is much more easier. sysadmin is not a requirement, the appropiate priviledges needed to be granted are AUTHENTICATE SERVER to the database dbo and VIEW SERVER STATE to the execute as user.

HTH,
~ Remus

|||

HI Remus

Great thanx , i thnk i will stick to trustworthy and the permission you specified, thanx very much

Activation not working

Hi There

Everything works 100% until i activate my sp.

I specify to execute as 'dbo' in the alter queue statement. I also define the activated sp to execute as 'dbo'.

But i keep getting permission errors from my activated sp. i have tried to excute as 'sa' , i have even tried to write a job that excutes to stored procedure but it also get weird errors. Bottom line if i exceute the sp in management studio logged in as sa it works , but thorugh activation or a job nothing works, as 'dbo' or 'sa'.

?

Thanx

Hi there.

It seems the statement in my sp that has a permission problem is the get conversation group sattement.

So my activation on the queue does not work when i stipulate execute as 'dbo' or OWNER. I cannot assign execute as 'sa' i get the following error "The server principal "sa" is not able to access the database "OLTP1PRO" under the current security context". I have tried not to stipulate the execute as so taht it defaults to a sysadmin.

But i always get this error:

The user does not have permission to perform this action.

So i tried the following.

CREATE LOGIN QueueReader with password = 'T#ST123'

CREATE USER QueueReader for login QueueReader

grant receive on [ewx.co.za/Queue/ewx_sb_central_queue] to QueueReader

grant execute on dbo.CENTRAL_Queue_Processor to QueueReader

And chanegd the execute as to QueueReader, but is till get the same error.

I dunno what else to try, no matter hwat i do i cannot execute the get conversation group statement ?

Please help ?

|||anyone?|||

What error do you get if you manually invoke the procedure, from Management Studio, under the QueueReader user context?

execute as user 'QueueReader';

go

exec dbo.CENTRAL_Queue_Processor;

go

revert;

go

|||

Hi Remus

I get the same error as before. The user does not have permission to perform this action. Error number 297.

I have done more investigation, judging by the erro line number line it seems that the QueueReader user cannot read from

sys.dm_broker_activated_tasks.

However i am expecting these errors, the activated stored procedure has many inserts etc. SO basically i want the activated sp to run as dbo, i do not want to have to setup alot of permission for the user.

My biggest question is why does EXECUTE as 'dbo' or OWNER not work ? How can i get permission errors when it executes as dbo ?

So i make QueueReader dbo of the database and i still get the user doesnot have permission error, so it seems only sysadmin can select from the sys. views, however i am still stumped , since the dbo of the database is a sysadmin. If i login as the user who is the dbo i can execute it. But if i say execute as dbo i get the permission error, i am totally stumped ?

Thanx

|||

Ok so it seems that if i reference sis.dm views in my activated sp i have to make the execute as user a sysadmin, or grant that user select on the sys.dm views , is this correct?

However i am still interested to know why execute as dbo does nto work when the dbo is also a sysadmin?

Thanx Remus you rfeedabck will be greatly appreciated.

|||

Hi Remus

Lastly, it also mentions in Roger Wolters book that if you do not stipulate execute as for your activated sp it will execute in the backgrouond as a sysadmin. However if i do not stipulate execute as i still get the permission error ? So not sure what is happening there?

Thanx

|||

Execute as user is restricted to database context. sysadmin is a server context concept. An untrusted database cannot extend the context to the server. This topic is covered in my blog in these posts: http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx, http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx and http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

The simplest solution is to mark the database as trustworthy.

HTH,
~ Remus

|||

Thanx Remus

The links were worth reading and the trustworthy worked.

Just as a matter of interrest , what is the best practice in a prouction environment when the activation proc requires sys.dm views. Do you make the queue reader user a sysadmin (must be a bad idea), or make the dbo of the database a sysadmin, or simply grant the user permission to the sys.dm views (probably best) ? In gerneral when the actvation proc must use these views what would you reccomend ?

|||

The safest option is code signing (as in the exaple in my link). Getting code siging righ is really hard, so if you can get away with trustworthy bit, is much more easier. sysadmin is not a requirement, the appropiate priviledges needed to be granted are AUTHENTICATE SERVER to the database dbo and VIEW SERVER STATE to the execute as user.

HTH,
~ Remus

|||

HI Remus

Great thanx , i thnk i will stick to trustworthy and the permission you specified, thanx very much

Accessing which data caused a trigger to fire

Hi.

I need to make a trigger which fires when after an UPDATE is done on
table A in database X. The trigger must perform a statement which
copies some of the data from the update to table B in database Y on
the same server.
My question is: Can I access the data which caused the trigger to fire
or just get an indication of which entry in table A had been updated?

Best Regards
Jens Christian Andersen.There are 2 pseudo-tables available in trigger code: "deleted" and
"inserted". When a trigger is fired by an UPDATE statement, the "deleted"
table contains images of the rows before the update and the "inserted" table
contain the row images after the update. In your update trigger code, you
can do something like:

INSERT INTO Y.dbo.B (Column1, Column2)
SELECT Column1, Column2
FROM inserted

In a trigger fired by a DELETE statement, the "deleted" table contains the
rows just deleted and the "inserted" table is empty. Similarly, when a
trigger is fired by an INSERT statement, the "inserted" table contains the
rows just inserted and the "deleted" table in empty. See the Books Online
for details.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"jcandersen" <jca@.dse.dkwrote in message
news:1172660503.230464.291380@.8g2000cwh.googlegrou ps.com...

Quote:

Originally Posted by

Hi.
>
I need to make a trigger which fires when after an UPDATE is done on
table A in database X. The trigger must perform a statement which
copies some of the data from the update to table B in database Y on
the same server.
My question is: Can I access the data which caused the trigger to fire
or just get an indication of which entry in table A had been updated?
>
Best Regards
Jens Christian Andersen.
>

|||On 28 Feb., 14:07, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

There are 2 pseudo-tables available in trigger code: "deleted" and
"inserted". When a trigger is fired by an UPDATE statement, the "deleted"
table contains images of the rows before the update and the "inserted" table
contain the row images after the update. In your update trigger code, you
can do something like:
>
INSERT INTO Y.dbo.B (Column1, Column2)
SELECT Column1, Column2
FROM inserted
>
In a trigger fired by a DELETE statement, the "deleted" table contains the
rows just deleted and the "inserted" table is empty. Similarly, when a
trigger is fired by an INSERT statement, the "inserted" table contains the
rows just inserted and the "deleted" table in empty. See the Books Online
for details.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"jcandersen" <j...@.dse.dkwrote in message
>
news:1172660503.230464.291380@.8g2000cwh.googlegrou ps.com...
>

Quote:

Originally Posted by

Hi.

>

Quote:

Originally Posted by

I need to make a trigger which fires when after an UPDATE is done on
table A in database X. The trigger must perform a statement which
copies some of the data from the update to table B in database Y on
the same server.
My question is: Can I access the data which caused the trigger to fire
or just get an indication of which entry in table A had been updated?

>

Quote:

Originally Posted by

Best Regards
Jens Christian Andersen.

Thanks, this is just what I needed.

Accessing the variable in Execute SQL Task

I have a variable SYear with the data type Integer and the value 2005.

Connection type is OLE DB, and my sql statement is:

DECLARE @.MyYear Int

SET @.MyYear = ?

I have set the variable User::Syear as Input with the parameter name=0, there is no result set. I set BypassPrepare both True and False. However, this simple statement does not work. I keep having an error. If I put integer value directly into @.MyYear, it is fine. What is wrong in here? Somebody help me!

The OLE-DB parameter support is more orientated to queries, so this should work-

SELECT * FROM Table WHERE YearCol = ?

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

EXEC MyProc ?, ?

Why and how I have never really got to the bottom off, but that is what I have found to work.

|||This is VERY annoying. It is a type cast problem between the .Net type and the SQL type. When it can't cast the .NET type to the SQL type you selected, it just fails SILENTLY and gives you NOTHING in your task and NO ERROR.

What EXACTLY is the type of SYear, Int32 or Int16. And what EXACTLY did you set the parameter type too.

See this doc: http://msdn2.microsoft.com/en-us/library/4e5xt97a.aspx

I have had much better luck always setting the parameter type to varchar. At least then it gets passed as something.

Also, BypassPrepare must be True or it will fail with an error, that again does not tell you anything about setting BypassPrepare to True.|||

DarrenSQLIS wrote:

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

Why not build the SQL statement in a variable expression first and then use that variable?|||

If you are asking me, then yep, it is valid enough, just didn't pop into my head when writing that reply. I prefer parameters myself, but use either. Couple of pros and cons here-

Execute SQL
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExecuteSQL.html)

|||

Thank you all for your replies.

First of all, I would like to explain why I need to use the variable @.Year1. In my SQL codes in Execute SQL Task, I need to use the value of SYear over and over again. If I use ? instead of @.Year, I need to define SYear in the Parameter Mapping as many times as I use '?'. Is that correct? SYear is a data-type 'Int32'. In the parameter mapping, the data-type used is 'LONG' (only choices here are LONG or SHORT for integer value). Any comment is appreciated. Thank you.

|||You are doing it correctly. Having a var of "Int32" and mapping it to a parameter type "LONG" is correct. Is SYear in the correct "scope"?

You must have "BypassPrepare" set to True otherwise you will always get an error using ?

When I do the same thing it works fine for me. I setup a global var called SYear Int32 and set it as a Parameter, Direction=Input, Type=LONG, ParameterName = 0

SQLStatement:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

What is the error message you get?

Also, make sure you have the SP1 installed on your local machine as well as the server.|||

Thank you, Tom.

I have created the test package and the scope of SYear is the package which contains only this Execute SQL Task.

The one thing I do not understand is that the code 1 works but not code 2. Why? It is not logical.

**Code 1

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = ?

**Code 2

DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

The error message I got is (I set the result set NONE.):

SSIS package "Package1.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package1.dtsx" finished: Failure.

|||

I have seen this behaviour. It has to do with OLE DB limitations regarding variables. You can't use variables in OLE DB if your script contains multiple SQL statements. Your Code 1 contains one statement, so it works fine. Your Code 2 contains three, which doesn't work. I've got two workarounds for you:

1. use a DataReader source

2. Use multiple consecutive Execute SQL Tasks.

Attention: The DataReader Source syntax for using variables is different.

Configure your task to use the right variable. For instance, if you map package variable SYear to Parameter name @.SYear, then your code should look something like this:

DECLARE @.MyYear Int

SET @.MyYear = @.SYear

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

you don't need to declare @.SYear.

Regards,

Pipo

|||

To be clear, there are two more workarounds further up this thread, stored procedures and property expressions.

It is also advisable to issue SET NOCOUNT ON when working with parameters and result sets.

|||

Thank you for your reply, Pipo. But, Tom used 3 SQL statements with the input variable and he said it worked (please look at the previous reply). I do understand that there are some other ways to get around this problem, but I still would like to find out what is causing this. I have about 500-fields syntax check to clean the data for every year. I would not like to create the separate program for each year. That is why I would like to accept the user input (year) and use it for 500 syntax checks. The program should be flexible, shouldn′t it?

I really appreciate replies from you all.

Thank you.

|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure. Should I use the Execute SQL Task and set the SQL Source Type = File Connection?

|||What do you mean by code 2 doesn't work?

You need to have result set set to something other than "None" if it returns a record set. That is what is generating the error you are seeing.

My guess from the code you posted is, SQL is implicitly converting DB030 into the correct type on code 1 and code 2 you are not comparing an INT to an INT. What is the data type of DB030?

Do you have SP1 installed on the server and the client? What version does it show. The current version (pre SP2) is 9.0.2153.|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure.

Instead of using a SELECT statement or other T-SQL in your Execute SQL Task, you call the stored procedure with placeholders for parameters, so it may look like this-

EXEC myproc ?, ?

? is still the placeholder for the parameter in the same way it is when using a SELECT such as SELECT * FROM table WHERE id = ? AND id2 = ?

|||

yhalldorsson,

To be honest, I overlooked Tom's little remark that it works fine for him.

I've been over this some time ago, and couldn't get it to work. To make sure I didn't miss anything, I tried again just now, using the exact same code showen above:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

I used the same parameter mapping as described by Tom, but I can't get it working. I get the exact same error message as you do.

So, to be honest, I'm getting curious as to what's the (hidden) difference between Tom's attempt and mine (and yours, presumably).

(To be complete: I used Tom's Parameter mapping and code, Single row result set, and put the Result Name Year into another variable of type int32.

And I set BypassPrepare to True)

Regards,

Pipo

Accessing the variable in Execute SQL Task

I have a variable SYear with the data type Integer and the value 2005.

Connection type is OLE DB, and my sql statement is:

DECLARE @.MyYear Int

SET @.MyYear = ?

I have set the variable User::Syear as Input with the parameter name=0, there is no result set. I set BypassPrepare both True and False. However, this simple statement does not work. I keep having an error. If I put integer value directly into @.MyYear, it is fine. What is wrong in here? Somebody help me!

The OLE-DB parameter support is more orientated to queries, so this should work-

SELECT * FROM Table WHERE YearCol = ?

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

EXEC MyProc ?, ?

Why and how I have never really got to the bottom off, but that is what I have found to work.

|||This is VERY annoying. It is a type cast problem between the .Net type and the SQL type. When it can't cast the .NET type to the SQL type you selected, it just fails SILENTLY and gives you NOTHING in your task and NO ERROR.

What EXACTLY is the type of SYear, Int32 or Int16. And what EXACTLY did you set the parameter type too.

See this doc: http://msdn2.microsoft.com/en-us/library/4e5xt97a.aspx

I have had much better luck always setting the parameter type to varchar. At least then it gets passed as something.

Also, BypassPrepare must be True or it will fail with an error, that again does not tell you anything about setting BypassPrepare to True.

|||

DarrenSQLIS wrote:

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

Why not build the SQL statement in a variable expression first and then use that variable?|||

If you are asking me, then yep, it is valid enough, just didn't pop into my head when writing that reply. I prefer parameters myself, but use either. Couple of pros and cons here-

Execute SQL
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExecuteSQL.html)

|||

Thank you all for your replies.

First of all, I would like to explain why I need to use the variable @.Year1. In my SQL codes in Execute SQL Task, I need to use the value of SYear over and over again. If I use ? instead of @.Year, I need to define SYear in the Parameter Mapping as many times as I use '?'. Is that correct? SYear is a data-type 'Int32'. In the parameter mapping, the data-type used is 'LONG' (only choices here are LONG or SHORT for integer value). Any comment is appreciated. Thank you.

|||You are doing it correctly. Having a var of "Int32" and mapping it to a parameter type "LONG" is correct. Is SYear in the correct "scope"?

You must have "BypassPrepare" set to True otherwise you will always get an error using ?
When I do the same thing it works fine for me. I setup a global var called SYear Int32 and set it as a Parameter, Direction=Input, Type=LONG, ParameterName = 0

SQLStatement:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

What is the error message you get?

Also, make sure you have the SP1 installed on your local machine as well as the server.

|||

Thank you, Tom.

I have created the test package and the scope of SYear is the package which contains only this Execute SQL Task.

The one thing I do not understand is that the code 1 works but not code 2. Why? It is not logical.

**Code 1

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = ?

**Code 2

DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

The error message I got is (I set the result set NONE.):

SSIS package "Package1.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package1.dtsx" finished: Failure.

|||

I have seen this behaviour. It has to do with OLE DB limitations regarding variables. You can't use variables in OLE DB if your script contains multiple SQL statements. Your Code 1 contains one statement, so it works fine. Your Code 2 contains three, which doesn't work. I've got two workarounds for you:

1. use a DataReader source

2. Use multiple consecutive Execute SQL Tasks.

Attention: The DataReader Source syntax for using variables is different.

Configure your task to use the right variable. For instance, if you map package variable SYear to Parameter name @.SYear, then your code should look something like this:

DECLARE @.MyYear Int

SET @.MyYear = @.SYear

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

you don't need to declare @.SYear.

Regards,

Pipo

|||

To be clear, there are two more workarounds further up this thread, stored procedures and property expressions.

It is also advisable to issue SET NOCOUNT ON when working with parameters and result sets.

|||

Thank you for your reply, Pipo. But, Tom used 3 SQL statements with the input variable and he said it worked (please look at the previous reply). I do understand that there are some other ways to get around this problem, but I still would like to find out what is causing this. I have about 500-fields syntax check to clean the data for every year. I would not like to create the separate program for each year. That is why I would like to accept the user input (year) and use it for 500 syntax checks. The program should be flexible, shouldn′t it?

I really appreciate replies from you all.

Thank you.

|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure. Should I use the Execute SQL Task and set the SQL Source Type = File Connection?

|||What do you mean by code 2 doesn't work?

You need to have result set set to something other than "None" if it returns a record set. That is what is generating the error you are seeing.

My guess from the code you posted is, SQL is implicitly converting DB030 into the correct type on code 1 and code 2 you are not comparing an INT to an INT. What is the data type of DB030?

Do you have SP1 installed on the server and the client? What version does it show. The current version (pre SP2) is 9.0.2153.

|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure.

Instead of using a SELECT statement or other T-SQL in your Execute SQL Task, you call the stored procedure with placeholders for parameters, so it may look like this-

EXEC myproc ?, ?

? is still the placeholder for the parameter in the same way it is when using a SELECT such as SELECT * FROM table WHERE id = ? AND id2 = ?

|||

yhalldorsson,

To be honest, I overlooked Tom's little remark that it works fine for him.

I've been over this some time ago, and couldn't get it to work. To make sure I didn't miss anything, I tried again just now, using the exact same code showen above:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

I used the same parameter mapping as described by Tom, but I can't get it working. I get the exact same error message as you do.

So, to be honest, I'm getting curious as to what's the (hidden) difference between Tom's attempt and mine (and yours, presumably).

(To be complete: I used Tom's Parameter mapping and code, Single row result set, and put the Result Name Year into another variable of type int32.

And I set BypassPrepare to True)

Regards,

Pipo

Accessing SELECT results within a stored procedure

Hi,
Can anyone tell me how to access the results from a select statement within the stored procedure?

This is to implement auditting functionality, before updating a record i want select the original record and compare the contents of each field with the varibles that are passed in, if the fields contents has changed i want to write an audit entry.

So i'd like to store the results of the select statement in a variable and access it like a dataset. e.g

declare selectResult

set selectResult = Select field1,field2,field3 from table1

if selectResult.field1 <> @.field1
begin
exec writeAudit @.var1,@.var2,var3
end

Many thanks.Cursors are the answer.|||Cursors are NEVER the answer...|||You can use the dreaded temp tables or move your inner proc to a function.

insert into #temp
exec mylovelyProc

I'd love to suggest tables vars but they don't work in this situation...grrr.|||Well if you are updating one record you can do something like so:

CREATE PROC DoSomething
 @.UniqueID INT,
 @.Value1 VARCHAR(10),
 @.Value2 INT,
 @.Value3 VARCHAR(100)
AS
 DECLARE @.OldValue1 VARCHAR(10)
 DECLARE @.OldValue2 INT
 DECLARE @.OldValue3 VARCHAR(100)
 SELECT @.OldValue1 = Table1.Value1, @.OldValue2 = Table1.Value2, @.OldValue3 = Table1.Value3 FROM Table1 WHERE Table1.IDField = @.UniqueID
 IF @.OldValue1 <> @.Value1 OR @.OldValue2 <> @.Value2 OR @.OldValue3 <> @.Value3
 EXEC writeAudit @.OldValue1, @.OldValue2, @.OldValue3

Tha's *one* way to do it. :)