BCP in stored procedure

Hi,
I am using the following statements in a atored procedure to be executed in SQL Server 2000.

SET @.QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'

SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''

EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE

I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.L_CURRENTDATE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Can someone help me out?

Regards,
Bharathram GHi,
I am using the following statements in a atored procedure to be executed in SQL Server 2000.

SET @.QUERY = 'bcp "SELECT * FROM FDWSTGD.DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'

SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''

EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE

I get the following error:
SQLState = 37000, NativeError = 137
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@.L_CURRENTDATE'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Can someone help me out?

Regards,
Bharathram G

It seems many things are missing,plz provide full code of the stored pro.
Joydeep|||Hi,
Following is the full code:
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
DECLARE @.L_OUT_FILENAME VARCHAR(100)
DECLARE @.QUERY NVARCHAR(4000)

SET @.L_CURRENTDATE = GETDATE()
SET @.L_OUT_PATH = '"C:\'
SET @.L_OUT_FILENAME = 'out.txt'

SET @.QUERY = '"SELECT * FROM DBO.PACK_DELETION_LOG WHERE DELETE_DT = @.L_CURRENTDATE"'
SET @.QUERY = 'bcp "'+@.QUERY+'" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+'" -c -q'
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''
EXECUTE SP_EXECUTESQL @.QUERY,N'@.L_CURRENTDATE DATETIME',@.L_CURRENTDATE

END|||Hi,
Following is the full code:
create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)

Hi ,
Try this ...

create procedure dbo.test as
begin
DECLARE @.L_CURRENTDATE DATETIME
DECLARE @.L_OUT_PATH VARCHAR(100)
DECLARE @.L_OUT_FILENAME VARCHAR(100)
DECLARE @.QUERY NVARCHAR(4000)

SET @.L_CURRENTDATE = GETDATE()
SET @.L_OUT_PATH = 'C:\'
SET @.L_OUT_FILENAME = 'out.txt'

SET @.QUERY = 'SELECT * FROM test.dbo.PACK_DELETION_LOG WHERE convert(varchar(12),delete_dt,101) =convert(varchar(12),getdate(),101)'
print @.query

SELECT * FROM PACK_DELETION_LOG WHERE day(DELETE_DT) = day(@.L_CURRENTDATE)

SET @.QUERY = 'bcp "'+@.QUERY+'" queryout '+@.L_OUT_PATH+@.L_OUT_FILENAME+' -c -P '
print @.query
SET @.QUERY = 'execute master.dbo.xp_cmdshell '+''''+@.QUERY+''''

EXECUTE SP_EXECUTESQL @.QUERY

end

Joydeep

BCP call to stored procedure - broke during upgrade from SQL 7.0 to 2000

I have this stored procedure that takes a few parameters like date and
merchant ID, and basically goes through a set of if-then statements to build
a SQL SELECT string.

When we upgraded from SQL Server 7.0 to 2000, the stored procedure still
worked from Query Analyzer, but not in BCP. It used to work in BCP just
fine with 7.0. The error I get now is:

SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain
at least one column

What's really strange is, if I instruct the stored procedure to simply print
the SELECT string, then cut-and-paste it into the end of stored procedure
code (assigning it to the variable that already contains the SELECT string),
then it works from BCP.

Any help would be greatly appreciated.

AstonAston (alau@.selera.com) writes:
> I have this stored procedure that takes a few parameters like date and
> merchant ID, and basically goes through a set of if-then statements to
> build a SQL SELECT string.
> When we upgraded from SQL Server 7.0 to 2000, the stored procedure still
> worked from Query Analyzer, but not in BCP. It used to work in BCP just
> fine with 7.0. The error I get now is:
> SQLState = S1000, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]BCP host-files must contain
> at least one column
> What's really strange is, if I instruct the stored procedure to simply
> print the SELECT string, then cut-and-paste it into the end of stored
> procedure code (assigning it to the variable that already contains the
> SELECT string), then it works from BCP.

If I understand this right you are doing something like:

bcp "exec some_db..some_sp" queryout datafile.bcp -c -T

To find out what columns there are in the query, bcp first submits the
query with SET FMTONLY ON. This command is causes SQL Server to not execute
the statements in the procedure, but return data about any result sets
it finds. However, if you produces a dynamic SQL string and executes it,
there not be anyting executed with FMTONLY ON, and BCP will not find any
result set.

Why this worked in SQL 7, I don't know. (I never worked much with SQL 7,
jumped direct to SQL 2000 from 6.5.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP

1. When using BCP to copy data into a table, which of the following
statements apply? Choose 2.
A) Database users will not be able to access the table because BCP
will lock it.
B) Database users will see the rows inserted by BCP after each batch
is complete.
C) You must have INSERT permissions on the table.
D) Existing rows are replaced by BCP.> 1. When using BCP to copy data into a table, which of the following
> statements apply? Choose 2.
> A) Database users will not be able to access the table because BCP
> will lock it.
>
The default behavior is row locking so other users can use the table.
However, a TABLOCK hint can also be specified.
> B) Database users will see the rows inserted by BCP after each batch
> is complete.
>
A BCP batch is a transaction so users can see data once committed.
> C) You must have INSERT permissions on the table.
>
Yes.
> D) Existing rows are replaced by BCP.
>
Nope.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"docsql" <docsql@.noemail.nospam> wrote in message
news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...|||Not sure if this question related to SQL2000 or SQL2005? Some more
information on permissions for BCP in SQL2005. You will note there is now a
requirement to get ALTER table permission if you are doing DDL operations
transparently. This was not the case in SQL2000. However, you will also
require SELECT permission on the traget table both in SQL2000 and SQL2005
A bcp out operation requires SELECT permission on the source table.
A bcp in operation minimally requires SELECT/INSERT permissions on the
target table. In addition, ALTER TABLE permission is required if any of the
following is true:
(1)Constraints are disabled, which is the default behavior. To keep
constraints enabled, use the -h option with the CHECK_CONSTRAINTS hint.
(2) Triggers are disabled, which is the default behavior. To fire triggers,
use the -h option with the FIRE_TRIGGERS hint.
(3) You use the -E option to import identity values from a data file.
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:elgg6Cp2FHA.3592@.TK2MSFTNGP12.phx.gbl...
>> 1. When using BCP to copy data into a table, which of the following
>> statements apply? Choose 2.
>> A) Database users will not be able to access the table because BCP
>> will lock it.
> The default behavior is row locking so other users can use the table.
> However, a TABLOCK hint can also be specified.
>> B) Database users will see the rows inserted by BCP after each
>> batch is complete.
> A BCP batch is a transaction so users can see data once committed.
>
>> C) You must have INSERT permissions on the table.
> Yes.
>> D) Existing rows are replaced by BCP.
>>
> Nope.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eP8VbIl2FHA.3272@.TK2MSFTNGP09.phx.gbl...
>

BATCH Update ?

Hi all,

we need to update many single cells with individual MDX Update Statements.
We're doing this with ADOMD now (C# Project) in a loop.
In order to save roundtrips and put things in one transaction
we considered using the <Batch> Element of XMLA.
I could'nt find a example how to use this with MDX Commands.
this doesnt work:
<Batch>
<Command>
<Statement>
UPDATE ..
</Statement>
</Command>
<Command>
<Statement>
UPDATE..
</Statement>
</Command>
</Batch>

The Batch element at line 7, column 22 (namespace urn:schemas-microsoft-com:xml-analysis) cannot appear under Envelope/Body/Execute/Command.

Do you have any hints ?
BTW: what happend to www.xmla.org, its down?
Wher can I find a complete schema file for XMLA?
a lot of questions...
Thanks a lot,
mik

You can update multiple cell values in a single Update statement seperated by commas.

For example:

UPDATE CUBE [Cube1] SET

(USA, Sales) = 100 USE_EQUAL_ALLOCATION,

(Canada, Sales) = 50 USE_EQUAL_ALLOCATION

You can also find some information on XML/A at

http://msdn2.microsoft.com/de-de/library/ms186604.aspx

Batch Printing

Hi,

I have to print bank statements for several thousand accounts. I'll have to design a statement template and connect it to my SQL Server database. What tool(s) can I use for this? I can use Reporting Services but I'm not sure if I can use it to print several reports based on an account range in batch mode. Any pointers and suggestions will be highly appreciated.

Thanks.

Umar.

Reporting Services, custom Visual Studio application, Access -even Microsoft Word or Excel 'could' work.

|||See this http://www.sql-server-performance.com/sm_sql_2005_reporting.asp for direct client printing.