Setting variable within stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Setting variable within stored procedure

Author

Topic

jdenning
Starting Member

3 Posts

Posted - 2011-11-10 : 16:06:03

I have a trigger that opens a cursor, fetches data into @Row and @Result, and needs to query a table to get the name of the user that did the transaction. It takes @Result and @SAPUserName, and sends it to USP_sendTransaction,but it's not working. When @SAPUserName is set to a string, it works so I'm pretty sure it's the query that sets @SAPUserName is the problem. Is there a problem with the code below that would keep @SAPUserName from being set?


Declare @Row int
Declare @Result nvarchar (50)
Declare @SAPUserName nvarchar (15)
 

SELECT @SAPUserName = SAPUserName from TransactionHistoryData WHERE TransactionHistoryKey = @Row

exec USP_sendTransaction @SAPUserName, @Result

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-10 : 16:50:17

Maybe a NULL value?

It's really hard to answer with what you have provided.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 03:44:20

are you sure you will have a row for every @row value sent to TransactionHistoryKey field?If not @SAPUserName will not always have value

Also why are you using trigger for all this? you want all these processing to be triggered based on DML operation?
Probably you could explain us a bigger picture of what you're trying to achieve here so that we can suggest any better alternatives.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jdenning
Starting Member

3 Posts

Posted - 2011-11-11 : 09:33:11

We are trying to synchronize stock activity in a SQL DB to an online non-SQL DB in real time. The thought was to use a trigger to check for the type of transaction, get the user name that performed the transaction from another table, and call a stored procedure that calls a web service to insert the transaction into the other DB. There is not a lot of activity on the table (average 400 records a day inserted into table and 1500 max). I am open to suggestions as to how to get it accomplished.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 09:43:20

>> and call a stored procedure that calls a web service to insert the transaction into the other DB.
From inside the trigger - a very bad idea even if it works.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 09:46:21

You could use a trigger to insert a row into a table - then have a scheduled job to get rows from that table and send then mark the row as processed.
No need to use a cursor inside the trigger.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 10:00:29

quote:
Originally posted by jdenning

We are trying to synchronize stock activity in a SQL DB to an online non-SQL DB in real time. The thought was to use a trigger to check for the type of transaction, get the user name that performed the transaction from another table, and call a stored procedure that calls a web service to insert the transaction into the other DB. There is not a lot of activity on the table (average 400 records a day inserted into table and 1500 max). I am open to suggestions as to how to get it accomplished.

just being curious here,whats the other DB you need synch up with?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jdenning
Starting Member

3 Posts

Posted - 2011-11-11 : 10:40:24

Salesforce.com

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-11 : 10:53:27

Use DBAmp to upsert a table - You can create the file from the trigger table I suggested above.
You can run this from sql server and it gives a lot more visibility of what is happening.

Check if you can create the linked server from v2000.

Also means you can run queries against salesforce tables from sql server which is handy.
Not sure if the exec ('xxx') at linked server was available in v2000 so you might have to us ev2005 for it.

Can also keep the salesforce tables up to date in sql server using sp_sfrefresh and just query them there.

(Guess what I'm working on - but it's 100k's rows a day).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources