Bulk Insert with FileName Variable

I am trying to run a bulk insert in a SP using the filename as a variable:
BULK INSERT #tblTemp
From '+@.FilePath+'
WITH
(ROWTERMINATOR = '\n')
My procedure is not recognizing @.Filepath as a variable file name. Is there
a way to do this without resorting to full dynamic SQL?
Larry Menzin
American Techsystems Corp.> My procedure is not recognizing @.Filepath as a variable file name. Is
> there
> a way to do this without resorting to full dynamic SQL?
No, BULK INSERT command is not parameterized that way.|||Example below:BULK INSERT pubs..publishers2 FROM 'c:\newpubs.dat'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eeH4hL1oFHA.3936@.TK2MSFTNGP10.phx.gbl...
> No, BULK INSERT command is not parameterized that way.
>|||I think Larry knows how BULK INSERT works when you hard-code everything, the
problem is that @.FilePath is variable/unknown (in your example, it is
hard-coded as c:\newpubs.dat) .

> Example below:BULK INSERT pubs..publishers2 FROM 'c:\newpubs.dat'
> WITH (
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = ',',
> ROWTERMINATOR = '\n'
> )

Bulk insert problem, any ideas?

Hello, i am trying to get this to work, i made a SP that send internalmessages to x number of users, the users is located in a variable called @.To, they are seperated by commas.

INSERTINTO [dbo].[post](touser, fromuser,subject, body, recived, w, a)(SELECT s.nstr, @.From, @.Subject, @.Message,getdate(), 0, 1FROM iter_charlist_to_table(@.To,DEFAULT) s)

the function iter_charlist_to_table takes the usernames inside of @.To and returns a table of usernames, i then want to insert a record for each of these users.

When i try to run this:

EXEC SendInternalMessageToUsers
@.From= N'nouser',
@.To= N'Dirk,piffo,Steve',
@.Subject= N'Test',
@.Message= N'This is to test message'

I get the following result:

Msg 512, Level 16, State 1, Procedure LaberMail_SendInternalMessageToUsers, Line 36

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

The statement has been terminated.

any ideas?

|||

Well, the error was returned from a select statement (a subquery in one to be precise), but you did not show us the code for the query.

My idea is to show us the select statement... :)

|||

The SP contains, one insert, one update and one select that returns the results back to my program, the insert statement is solved, that one works and inserts the correct values when i remove the update and select. The same message appears for both the update statement and the select statement.

-- Works
INSERTINTO [dbo].[post](touser, fromuser,subject, body, recived, weight, adminmessage)(SELECT s.nstr, @.From, @.Subject, @.Message,getdate(), 0, 1FROM iter_charlist_to_table(@.To,DEFAULT) s)

-- Not working
UPDATE profile_statisticsSET post_new= post_new+ 1, post_recived= post_recived+ 1WHERE(username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))

-- Not working
SELECT profile_publicinfo.username, profile_publicinfo.emailFROM profile_publicinfoINNERJOIN settings_settingsON(settings_settings.username= profile_publicinfo.username)WHERE(profile_publicinfo.username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))AND(settings_settings.post_newmailemail= 1)

these statements calls this function:

SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
ALTERFUNCTION [dbo].[iter_charlist_to_table]
(@.listntext,
@.delimiternchar(1)= N',')
RETURNS @.tblTABLE(listposintIDENTITY(1, 1)NOTNULL,
strvarchar(4000),
nstrnvarchar(2000))AS
BEGIN
DECLARE @.posint,
@.textposint,
@.chunklensmallint,
@.tmpstrnvarchar(4000),
@.leftovernvarchar(4000),
@.tmpvalnvarchar(4000)
SET @.textpos= 1
SET @.leftover=''
WHILE @.textpos<=datalength(@.list)/ 2
BEGIN
SET @.chunklen= 4000-datalength(@.leftover)/ 2
SET @.tmpstr= @.leftover+substring(@.list, @.textpos, @.chunklen)
SET @.textpos= @.textpos+ @.chunklen
SET @.pos=charindex(@.delimiter, @.tmpstr)
WHILE @.pos> 0
BEGIN
SET @.tmpval=ltrim(rtrim(left(@.tmpstr, @.pos- 1)))
INSERT @.tbl(str, nstr)VALUES(@.tmpval, @.tmpval)
SET @.tmpstr=substring(@.tmpstr, @.pos+ 1,len(@.tmpstr))
SET @.pos=charindex(@.delimiter, @.tmpstr)
END
SET @.leftover= @.tmpstr
END
INSERT @.tbl(str, nstr)VALUES(ltrim(rtrim(@.leftover)),ltrim(rtrim(@.leftover)))
RETURN

@.From, @.Subject, @.Message, @.To are sent as parameters to the program, the @.To contains the usernames seperated by commas, ex. "John,Steve,Andrew,Patrick,"
(It is always a extra comma after the last username in the @.To parameter)

Patrick

|||

You are doing a basic no-no in this statement:

UPDATE profile_statisticsSET post_new= post_new+ 1, post_recived= post_recived+ 1WHERE(username=(SELECT s.nstrFROM iter_charlist_to_table(@.To,DEFAULT) s))

When you state that username must = the result of a subquery (that's the select s.nstr etc. is), the subquery can only return 1 row. If it returns more than one row, how would sql server know which one you meant?

I think you may want to change to

...WHERE (username IN (SELECT ...

The IN operator works of a list of items, which can be hard-coded or supplied via query. (I work in several flavors of sql databases and my test database is down at the moment, so I can't double check the syntax.)

|||

You have the same problem in the select statement. I don't have time to work thru that one, but I'm wondering why you just don't join the table function results instead of doing a subquery. It will run faster and be easier to understand.

|||

How do join that function table into the select statement?

I solved the problem, with the IN instead of =, like you said, it worked for both the select and the update

Bulk Insert into a Table Variable

I am attempting to use a table variable as a destination for a bulk insert.

DECLARE @.TEXTFILE_1 TABLE

(CHAR_FIELD1 varchar(1) ,

CHAR_FIELD2 varchar(1) ,

CHAR_FIELD3 varchar(1) ,

CHAR_FIELD4 varchar(1) )

BULK INSERT @.TEXTFILE_1 FROM 'C:\TRASH.TXT'

WITH (FIELDTERMINATOR =' | ',ROWTERMINATOR =' \n')

The input file looks like:

A | B | C | D
E | F | G | H

But the errors indicate that the input is not the issue (or at least not yet...).

The errors look like:

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near '@.TEXTFILE_1'.

Msg 319, Level 15, State 1, Line 8

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

I couldn't find any documentation that stated a table varable is an invalid destination for a bulk insert, but it is looking like that is the case. Any suggestions would be appreciated.

Thanks

Yeah, bulk insert is designed for actual tables, not table variables. Silly really, as you would think the system shouldn't really differentiate between the two... but unfortunately it does. Try creating a table in your database and seeing if it works with only that changed - if it does, then there's your problem for sure.

Rob|||You can't BULK INSERT into a table variable. If you are on SQL Server 2005 you can use INSERT...SELECT OPENROWSET(BULK) instead. See BOL for more help on how to use OPENROWSET(BULK).|||Unfortunately this client is on an older version of SQL and will not be upgrading until late this year at the earliest.

Is there an alternative to bulk insert for SQL 2000? We were trying to use a temporary table. That worked until the stored procedure was run under a login different than the user who ran the script that created the SP. That produced an error: "The current user is not the database or object owner of table 'name of temp table here'. Cannot perform SET operation." In understand this is a known issue. http://laneys.info/node/487

We didn't want to create a permanent scratch table since we would have to deal with the multi-user aspect and cleanup.

Because of the format of the data stored in the text file we cannot import directly to the destination table. We are trying to contain all of the activity in the base application which means we cannot write a parser in .NET. These are the hazards providing third party support.

Thanks so much for your help,
Richard|||

Does the workaround in the KB article referenced in the link above help? The KB article is:

http://support.microsoft.com/default.aspx?scid=kb;en-us;302621

Is it possible for you to give a detailed description of your setup? Any code will also help.

1. Who owns the SP?

2. How is the temporary table created? Is it inside the SP?

3. What privileges does the user calling the SP have in the user database? Is he part of some roles?

4. Does the user belong to any roles in the tempdb?

BULK INSERT into a table variable

Bulk Insert to an existing table works fine, but substituting a table variable causes parsing error:

Msg 137, Must declare the scalar variable "@.tblInput". or

Msg 207, Invalid column name '@.tblInput'.

To repro:

CREATE PROC X AS

BEGIN

DECLARE @.strSQL char(99)

DECLARE @.BulkFile varchar(60)

SET @.BulkFile = 'c:\LookingGlass\BulkCopy1.tmp'

DECLARE @.tblInput TABLE (

[Word] [varchar] (50),

[UseCount] [int] )

--works ok with temp table:

SET @.strSQL = 'BULK INSERT temp_tbl From ''' + @.BulkFile + ''''

--but not with table variable

SET @.strSQL = 'BULK INSERT ''' + @.tblInput + ''' From ''' + @.BulkFile + ''''

EXEC(@.strSQL)

END

These don't work either:

SET @.strSQL = 'BULK INSERT @.tblInput From ''' + @.BulkFile + ''''

SET @.strSQL = 'BULK INSERT ' + @.tblInput + ' From ''' + @.BulkFile + ''''

version: Microsoft SQL Server Management Studio Express 9.00.2047.00

You can't do that what you could do is use OPENROWSET with a txt or csv file

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Ignore!!!

|||

The op has tried that already, look at his last line of code

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks, Denis, for the very quick reply. I had searched extensively, and the documentation neglects to mention that Table Variables can't be used with Bulk Insert.

Ironically, the name of the Table Variable is fixed, but because it's a "variable", it can't be used to import.

Importing with a variable filename is a requirement.

But according to BOL, OPENROWSET, down in the Remarks,

"OPENROWSET does not accept variables for its arguments. "

Doesn't that mean hardcoded input filenames? ( OPENROWSET can use a Linked Server, but that's overkill for a text file.)

I wanted the performance benefits that a Table Variable provides, but it appears I'll have to make do with a temp table.

|||

Denis, stupidity on my part ... Read the post and thought he was trying to pass a table name in the variable. He is trying to pass a table variable... You're post is correct, you can't use table variables as part of a dynamic SQL string (would be nice if you could cuz it would same me from using #temp tables...

|||

you will have to use dynamic SQL

example

declare @.v varchar(500)
declare @.s varchar(500)
select @.v ='select top 6 * from ' + 'TestTextFileImport.txt'

select @.s ='
select * from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\;'', ''' +@.v +' '')'

print @.s
exec (@.s)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Denis, that works great, just open the text file as a rowset! No need for a table at all.

Unfortunately, this raises a security issue. Running the sproc produces this error:

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries'

because this component is turned off as part of the security configuration for this server.

A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.

For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Here are excerpts from searching BOL (underlines are mine):

If a nonzero value is set, SQL Server does not allow for ad hoc access

through the OPENROWSET and OPENDATASOURCE functions against the OLE DB provider.

When this option is not set, SQL Server also does not allow for ad hoc access.

This option controls the ability of non-administrators to run ad hoc queries. Administrators are not affected by this option.

By default, SQL Server does not allow ad hoc distributed queries

using OPENROWSET and OPENDATASOURCE against providers other than the SQL Native Client OLE DB Provider.

When this option is set to 0, SQL Server allows ad hoc access against other providers.

When this option is not set or is set to 1, SQL Server does not allow ad hoc access.

Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions

to connect to remote data sources that use OLE DB.

OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently.

For any data sources that will be accessed more than several times, define a linked server.

'Ad Hoc Distributed Queries is an Advanced option, which should be changed only by an experienced database administrator or a certified SQL Server technician,

and which require setting show advanced options to 1.

And here is how to do it:

sp_configure 'show advanced options',1;

GO

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries',1;

GO

RECONFIGURE;

GO

or with version 2.0 of the Microsoft .NET Framework, set the OleDbProviderSettings.DisallowAdHocAccess Property

All that just to read local text files? The server can't differentiate between local and remote files?

Why is a linked server recommended for files that will be read frequently? (thousands per hour, hopefully)

And I thought this was a pretty basic task. Thanks for answering my question.

Bulk Insert into a Table Variable

I am attempting to use a table variable as a destination for a bulk insert.

DECLARE @.TEXTFILE_1 TABLE

(CHAR_FIELD1 varchar(1) ,

CHAR_FIELD2 varchar(1) ,

CHAR_FIELD3 varchar(1) ,

CHAR_FIELD4 varchar(1) )

BULK INSERT @.TEXTFILE_1 FROM 'C:\TRASH.TXT'

WITH (FIELDTERMINATOR =' | ',ROWTERMINATOR =' \n')

The input file looks like:

A | B | C | D
E | F | G | H

But the errors indicate that the input is not the issue (or at least not yet...).

The errors look like:

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near '@.TEXTFILE_1'.

Msg 319, Level 15, State 1, Line 8

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

I couldn't find any documentation that stated a table varable is an invalid destination for a bulk insert, but it is looking like that is the case. Any suggestions would be appreciated.

Thanks

Yeah, bulk insert is designed for actual tables, not table variables. Silly really, as you would think the system shouldn't really differentiate between the two... but unfortunately it does. Try creating a table in your database and seeing if it works with only that changed - if it does, then there's your problem for sure.

Rob|||You can't BULK INSERT into a table variable. If you are on SQL Server 2005 you can use INSERT...SELECT OPENROWSET(BULK) instead. See BOL for more help on how to use OPENROWSET(BULK).|||Unfortunately this client is on an older version of SQL and will not be upgrading until late this year at the earliest.

Is there an alternative to bulk insert for SQL 2000? We were trying to use a temporary table. That worked until the stored procedure was run under a login different than the user who ran the script that created the SP. That produced an error: "The current user is not the database or object owner of table 'name of temp table here'. Cannot perform SET operation." In understand this is a known issue. http://laneys.info/node/487

We didn't want to create a permanent scratch table since we would have to deal with the multi-user aspect and cleanup.

Because of the format of the data stored in the text file we cannot import directly to the destination table. We are trying to contain all of the activity in the base application which means we cannot write a parser in .NET. These are the hazards providing third party support.

Thanks so much for your help,
Richard|||

Does the workaround in the KB article referenced in the link above help? The KB article is:

http://support.microsoft.com/default.aspx?scid=kb;en-us;302621

Is it possible for you to give a detailed description of your setup? Any code will also help.

1. Who owns the SP?

2. How is the temporary table created? Is it inside the SP?

3. What privileges does the user calling the SP have in the user database? Is he part of some roles?

4. Does the user belong to any roles in the tempdb?

BULK INSERT Help

I could use some help with BULK INSERT. It works fine unless I try to pass a variable. Check out the code below.

This works fine:

Code Snippet

BULK INSERT EPCJ_Input

FROM 'c:\Census_20070901.csv'

WITH

(

FIRSTROW = 2,

FIELDTERMINATOR = ','

)

But this does not:

Code Snippet

DECLARE @.filename VARCHAR(255)

SET @.filename = 'c:\Census_20070901.csv'

BULK INSERT EPCJ_Input

FROM @.filename

WITH

(

FIRSTROW = 2,

FIELDTERMINATOR = ','

)

This is the error I get:

Msg 102, Level 15, State 1, Line 13

Incorrect syntax near '@.filename'.

Msg 319, Level 15, State 1, Line 14

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Any ideas? I've tried all kinds of quote options around the @.filename.

Thanks,

Chuck

Give this a shot

Code Snippet

DECLARE @.bulk_cmd NVARCHAR(1000),

@.filename VARCHAR(255)

SET @.filename = 'c:\Census_20070901.csv'

SET @.bulk_cmd = 'BULK INSERT EPCJ_Input FROM ''' + @.filename + ''' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '','' )'

EXEC sp_executesql @.statement=@.bulk_cmd

|||Beautiful, thank you.

BULK insert from a text file name stored into a variable

Hello I need to write a proc to load data from txt files I receive into a table. It works fine when I specify
bulk insert... from 'myfilename.txt'
BUT my filename will always change and I store it into a variable @.filename

When I try to run the bulk insert instruction ... from @.filename it doesn't work..
do you know why?

Thank you in advanceAccoding to the destructions for BULK INSERT (http://msdn.microsoft.com/library/en-us/tsqlref/ts_ba-bz_4fec.asp), it takes a literal string for the file name. To sidestep this requirement, you can resort to dynamic SQL using the EXECUTE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_4vxn.asp) character string syntax. It isn't pretty, but it should get the job done for you.

-PatP

BULK INSERT datafile parameter

Hi everyone,
how can I pass a variable to the command "BULK INSERT", to the datafile
parameter? The following script:
create procedure some_procedure (@.filename varchar(256)) as
begin
bulk insert some_table from @.filename with(codepage='raw');
end;
fails with error "Incorrect syntax near '@.filename'". Any idea?
Thanks,
Tamas Beri
Create a string with the bulk insert command and exec it ie
exec('bulk insert sometable from ' + @.filename + yad yada)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gfoyle" <gfoyle@.discussions.microsoft.com> wrote in message
news:546E659E-ABC3-4B34-B6D5-2EB647E3A6A9@.microsoft.com...
> Hi everyone,
> how can I pass a variable to the command "BULK INSERT", to the datafile
> parameter? The following script:
> create procedure some_procedure (@.filename varchar(256)) as
> begin
> bulk insert some_table from @.filename with(codepage='raw');
> end;
> fails with error "Incorrect syntax near '@.filename'". Any idea?
> Thanks,
> Tamas Beri
>
|||Thanks,
it's finally working with the exec...
"Wayne Snyder" wrote:

> Create a string with the bulk insert command and exec it ie
> exec('bulk insert sometable from ' + @.filename + yad yada)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com

BULK INSERT datafile parameter

Hi everyone,
how can I pass a variable to the command "BULK INSERT", to the datafile
parameter? The following script:
create procedure some_procedure (@.filename varchar(256)) as
begin
bulk insert some_table from @.filename with(codepage='raw');
end;
fails with error "Incorrect syntax near '@.filename'". Any idea?
Thanks,
Tamas BeriCreate a string with the bulk insert command and exec it ie
exec('bulk insert sometable from ' + @.filename + yad yada)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gfoyle" <gfoyle@.discussions.microsoft.com> wrote in message
news:546E659E-ABC3-4B34-B6D5-2EB647E3A6A9@.microsoft.com...
> Hi everyone,
> how can I pass a variable to the command "BULK INSERT", to the datafile
> parameter? The following script:
> create procedure some_procedure (@.filename varchar(256)) as
> begin
> bulk insert some_table from @.filename with(codepage='raw');
> end;
> fails with error "Incorrect syntax near '@.filename'". Any idea?
> Thanks,
> Tamas Beri
>|||Thanks,
it's finally working with the exec...
"Wayne Snyder" wrote:
> Create a string with the bulk insert command and exec it ie
> exec('bulk insert sometable from ' + @.filename + yad yada)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com

BULK INSERT datafile parameter

Hi everyone,
how can I pass a variable to the command "BULK INSERT", to the datafile
parameter? The following script:
create procedure some_procedure (@.filename varchar(256)) as
begin
bulk insert some_table from @.filename with(codepage='raw');
end;
fails with error "Incorrect syntax near '@.filename'". Any idea?
Thanks,
Tamas BeriCreate a string with the bulk insert command and exec it ie
exec('bulk insert sometable from ' + @.filename + yad yada)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gfoyle" <gfoyle@.discussions.microsoft.com> wrote in message
news:546E659E-ABC3-4B34-B6D5-2EB647E3A6A9@.microsoft.com...
> Hi everyone,
> how can I pass a variable to the command "BULK INSERT", to the datafile
> parameter? The following script:
> create procedure some_procedure (@.filename varchar(256)) as
> begin
> bulk insert some_table from @.filename with(codepage='raw');
> end;
> fails with error "Incorrect syntax near '@.filename'". Any idea?
> Thanks,
> Tamas Beri
>|||Thanks,
it's finally working with the exec...
"Wayne Snyder" wrote:

> Create a string with the bulk insert command and exec it ie
> exec('bulk insert sometable from ' + @.filename + yad yada)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com

Build SQL Statement for execution with variables

How do I build a sql statement using variable that will be run within SP.
I have the following:
**Temp folder is designed above
declare @.CurrentDatabase nvarchar(128)
set @.CurrentDatabase = 'abc'
insert #BackupSet_Header
exec ('restore headeronly from On_Demand_' & @.CurrentDatabase)
I have written the restore sql, bur want the backup device to be dynamic
depending on variable passed to SP.
Thanks.might want to do something like this ps. I did not compile this so I hope it
compiles
create procedure my_sp @.dbname nvarchar(128) as
begin
declare @.sql nvarchar(1000)
select @.sql = 'restore headeronly from ' + @.dbname + '_backup'
insert into #BackupSet
exec master..sp_executesql @.sql
end
"Ed Gregory" <eg@.hotmail.com> wrote in message
news:O7P150o8EHA.936@.TK2MSFTNGP12.phx.gbl...
> How do I build a sql statement using variable that will be run within SP.
> I have the following:
> **Temp folder is designed above
> declare @.CurrentDatabase nvarchar(128)
> set @.CurrentDatabase = 'abc'
> insert #BackupSet_Header
> exec ('restore headeronly from On_Demand_' & @.CurrentDatabase)
> I have written the restore sql, bur want the backup device to be dynamic
> depending on variable passed to SP.
> Thanks.
>

Build SQL Statement for execution with variables

How do I build a sql statement using variable that will be run within SP.
I have the following:
**Temp folder is designed above
declare @.CurrentDatabase nvarchar(128)
set @.CurrentDatabase = 'abc'
insert #BackupSet_Header
exec ('restore headeronly from On_Demand_' & @.CurrentDatabase)
I have written the restore sql, bur want the backup device to be dynamic
depending on variable passed to SP.
Thanks.might want to do something like this ps. I did not compile this so I hope it
compiles
create procedure my_sp @.dbname nvarchar(128) as
begin
declare @.sql nvarchar(1000)
select @.sql = 'restore headeronly from ' + @.dbname + '_backup'
insert into #BackupSet
exec master..sp_executesql @.sql
end
"Ed Gregory" <eg@.hotmail.com> wrote in message
news:O7P150o8EHA.936@.TK2MSFTNGP12.phx.gbl...
> How do I build a sql statement using variable that will be run within SP.
> I have the following:
> **Temp folder is designed above
> declare @.CurrentDatabase nvarchar(128)
> set @.CurrentDatabase = 'abc'
> insert #BackupSet_Header
> exec ('restore headeronly from On_Demand_' & @.CurrentDatabase)
> I have written the restore sql, bur want the backup device to be dynamic
> depending on variable passed to SP.
> Thanks.
>

Build SQL Statement for execution with variables

How do I build a sql statement using variable that will be run within SP.
I have the following:
**Temp folder is designed above
declare @.CurrentDatabase nvarchar(128)
set @.CurrentDatabase = 'abc'
insert #BackupSet_Header
exec ('restore headeronly from On_Demand_' & @.CurrentDatabase)
I have written the restore sql, bur want the backup device to be dynamic
depending on variable passed to SP.
Thanks.
might want to do something like this ps. I did not compile this so I hope it
compiles
create procedure my_sp @.dbname nvarchar(128) as
begin
declare @.sql nvarchar(1000)
select @.sql = 'restore headeronly from ' + @.dbname + '_backup'
insert into #BackupSet
exec master..sp_executesql @.sql
end
"Ed Gregory" <eg@.hotmail.com> wrote in message
news:O7P150o8EHA.936@.TK2MSFTNGP12.phx.gbl...
> How do I build a sql statement using variable that will be run within SP.
> I have the following:
> **Temp folder is designed above
> declare @.CurrentDatabase nvarchar(128)
> set @.CurrentDatabase = 'abc'
> insert #BackupSet_Header
> exec ('restore headeronly from On_Demand_' & @.CurrentDatabase)
> I have written the restore sql, bur want the backup device to be dynamic
> depending on variable passed to SP.
> Thanks.
>