| Author |
Topic |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
 Posted - 2011-03-16 : 14:52:42
|
| hi there, Hope you'll be having a good time. I am working over a system where using which i am creating excel format reports using BCP Utility via SQL Server (Management Studio). I have a stored procedure (XYZ) which actually construct the query through Dynamic SQL and then executes it using sp_ExecuteSQL e.g. exec sp_ExecuteSQL @SQLString ....parameters. Executing XYZ stand alone works perfect. But when I try to generate Excel file for that dataset via BCP, I am facing the above stated error message. I tried to Google it in order to find the details but couldn't find a satisfactory answer. As it was working very well in SQL 2000, however after moving it to SQL 2008 R2 I have started facing this problem. Why is it happening and how to fix this issue?CheersMIK |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-16 : 16:13:13
|
| Well the requirements are so that I need to go that way... Any how I have printed the text passing to the xp_CmdShell command and it looks like below. Exec Master..xp_cmdShell 'bcp "Exec ChannelsDB.dbo.XYZ 52,0" queryout "F:\CDSReports\test_20110317.xls" -c -T -SHomePC'Even if try to execute this, i get the above error message. Seems to be problem in the XYZ stored procedure. Any help?CheersMIK |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-17 : 01:33:43
|
| Well Yes, the results are displaying quite well. As i said in my original post, that these procs were working fine in SQL 2000 but I have moved the system onto SQL 2008 where it has started giving me the error "BCP host-files must contain at least one column". when i check it in detail i found the issue is happening when i execute the above snipped BCP command. The SP i am trying to pass to this BCP works fine individually, however it gets fail with BCP :-sCheersMIK |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-17 : 08:03:07
|
| sounds like the transaction is getting rolled back.service account has permissions on everything touched here? |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-17 : 08:43:54
|
Russel thanks for your reply... Every thing is ok, It seems to be parser specific issue of the BCP usage via SQL 2000 onward. because the same code works fine if I execute it under SQL 2000 environment ... Any how I think i should change my strategy to make it workable in SQL 2008. Once again thanks to both of you  CheersMIK |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-17 : 08:47:01
|
| I doubt it's parsing issues. I think it's either permissions issue, or an error is encountered on the 2k8 environment. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-17 : 08:49:34
|
| what is the final value of @SQLString? |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-17 : 09:00:43
|
| @sqlstring stores a SQL query which i executes through the sp_ExecuteSQL e.g. Execute Sp_executeSQL @SQLString,N'@C_ID int,@PB_ID int',@ChannelID,@ProdBatch_IDreplacing the paramters with fixed values. I get a data as per sQL string I have constructed in @SQLStringCheersMIK |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-17 : 09:52:30
|
| I know. What is the final value of it before it gets executed?If you haven't output that yet, there is now way whatsoever to troubleshoot it. This is the first step in the troubleshooting process. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-17 : 10:20:23
|
| by value you mean the query it is constructing?CheersMIK |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-17 : 10:33:16
|
| yes. you need to add a print @sqlstring to your code so you can see it.can comment out the sp_executesql if you don't want it to run while debugging. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-17 : 13:02:23
|
| Pardon me Russel, I am unable to understand the point of debugging as what to debug? since the query I've printed if executed separately, is working fine. Any how the query looks like; Select 'Col1','Col2','Col3','Col4',...,'ColN'Union AllSelect tab1.col1,tab1.Col2,...TabN.ColNFrom Tab1Inner Join tab2 on Tab1.ColName=Tab2.ColNameInner Join tab3 on tab3.colName=tab2.colnameCrossApply dbo.FunctionName(tab1.columnName)Left Join tab4 on tab4.colName=tab1.ColNameWhere tab1.ColName=@Param1 and tab1.ColName=@Param2this is set into the @SQLString which is then executed by sp_ExecuteSQL. While constructing the query i am fetching the columnNames through variables from a table for both queries linked via UNION ALL.CheersMIK |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-03-17 : 13:11:34
|
| There shouldn't be any variables left in the print of @sqlString. @param1 and @param2 should have values in them.JimEveryday I learn something that somebody else already knew |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-17 : 13:20:07
|
| Jim i am using sp_ExecuteSQL to pass parameters to that SQL query .. e.g. Execute Sp_executeSQL @SQLStringHavingTheQuery,N'@C_ID int,@PB_ID int',@ChannelID,@ProdBatch_ID@C_ID and @PB_ID are those two Parameters (Param1 and Param2). All this is done in a stored procedure, which is then passed to the BCP command as a Query to produce a result set and query out it to file.CheersMIK |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-17 : 14:13:31
|
| I think i Need to re-clarify what i am doing... I am working on a project to create excel format reports of Different Formats for a set of data for different Channels/parties. There were two approaches to solve the format variations problem of the reports. 1) to create different stored procedures for all of the reports 2) to create one procedure which is made in a way that displays the data in the required format. I adopted the second approach, because in the first one i would need to write queries every time if any new parties are added. As per approach 2, I have defined formats for each and every party/channel and store that format/headings of the report in a column of a table. Whereas i have stored the column names in a second column. then i wrote a procedure which construct the query by fetching the heading and corresponding column names for the required party. and pass on the constructed query to the BCP command. I was executing this approach on a machine having SQL 2000 and was working fine. but now i moved it onto SQL 2008 and the same code is not working any more. As far as the debugging is concerned i have checked that every thing is working fine, untill the master..xp_cmdShell 'BCP.... command is executed. Which throws the subjected error messageCheersMIK |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-17 : 14:41:13
|
| Which is why I said to check the permissions.If you tell us the query executes if you run it, then we know it's not a parsing issue.But the reason to print out the sql that gets executed, is so that you can see if the service account can execute it without error.xp_cmdshell always executes under the context of the service account, so it isn't helpful to know that you can execute it.Again, when a dynamic query fails, for any reason, the first step is pretty much always to print out the value of the sql string, so you can figure out what's wrong. In this case, you need to have the service account execute that.If it can, then the problem is in the job (or calling program) itself, or the permissions associated with it. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-03-17 : 14:48:20
|
| I have already enabled it Brett....CheersMIK |
|
|
|
Next Page
|
BCP host-files must contain at least one column