February 6, 2006 at 4:53 pm
Hi guys,
I'm using SQL Server 2000. Till now I still unable to figure a way to do it. The whole flow of my code goes something like this:
1. create and insert index into table variable @tblVarList
2. create and insert data into table variable @tblVarData
3 loop through each row of @tblVarList
3.1 set @index = index of each row
3.2 create a query text @qrytxt that looks like: select * from @tblVarData where index = @index
3.3 create a bcp statement @bcpcmd: bcp "@qrytxt " queryout "C:\@index" -U -P -c
3.4 replace @qrytxt and @index in @bcpcmd with the actual @index and @qrytxt values
3.5 execute the bcp statement using EXEC master..xp_cmdshell @bcpcmd
and I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@tblVarData '.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
From my limited understanding, I think the bcp command doesn't support any variable. Is this correct? Do I have any alternative to perform this task? Do I need to change to use a real table instead of a table variable? Is there other way that I can output the data of a table variable to a file?
thanks in advance,
Kiat
February 6, 2006 at 5:19 pm
If you execute a
EXEC master..xp_cmdshell @bcpcmd
It is actually starting up an other connection. the data is not available to that new connection only the connection that created the temp variable.
The same thing holds true if you create a table variable. in a parent stored procedure, and try to access that variable in a child or nested stored procedure.
February 6, 2006 at 7:54 pm
does it mean i have no alternative but to create a real table to store those data?
February 7, 2006 at 9:27 am
Just a quick update, I used a get around to solve this problem. Instead of table variable, I use global temp table. So now the bcp command is able to see the data transformed in the stored procedure I created!
February 7, 2006 at 11:39 am
that works, but are you control how/when this executes?
what if this task/procedure runs twice, at the same time?
Say it is called twice the first instance inserts data into the global temp table, and the second instance inserts rows also before the first instance finishes getting its data out. Does that make sense.
If this occurs then your going to have to somehow create a method to manage what data each thread pulls out.
February 7, 2006 at 12:11 pm
geez... I didn't think of that scenario! my problem is only a bit simplier though. The procedure is created to run once a week by a job. So the odds of this procedure being executed twice at the same should only occurs when the job runs twice simultaneously.
but no harm to be a bit cautious. So, in order to solve this issue, does it mean i need to also create a global mutex variable? so everytime when the procedure runs, it first check if the mutex flag is set or cleared. if it's cleared, no other procedure is accessing the global table. otherwise it simply terminates the procedure.
is this the way to solve this issue? or is there any other way? thanks Ray for pointing this out!
February 7, 2006 at 12:14 pm
oops, when I say global variable I actually mean to create a table to store value of the mutex variable.
February 8, 2006 at 8:40 am
Yeah, I would put some additional field in that table that even can have like a Guid, and then only export values with that guid. But if you can control when the process executes, I wouldn't spend the extra time. You should be okay.
As long as the job doesn't take days to execute. (Hopefully not)
February 8, 2006 at 8:52 am
Hi Ray,
thanks for the reply. I think for my case, to control when to execute the procedure will be the easiest to prevent the scenario you mentioned. The job only runs for 1~2 minutes, 3 the most. I try to keep it simple and clean, in case someone else need to dig in to look at it later on they have a clue of what I did.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply