Bulk Load from Stored Procedure

All of the examples of Bulk Load that I have found use VBScript to Envoke
the bulk load. Is it possible to write and SQL Stored procedure to
accomplish the task? An example of such a procedure would be greatly
appreciated.
Thanks
you could use the OA series of extended sps to do that .
"Scott McKillop" <scott_mckillop_NOSPAM_MAN@.adlt.com_REMOVE_CAPS> wrote in
message news:OCn2ObGNEHA.624@.TK2MSFTNGP11.phx.gbl...
> All of the examples of Bulk Load that I have found use VBScript to Envoke
> the bulk load. Is it possible to write and SQL Stored procedure to
> accomplish the task? An example of such a procedure would be greatly
> appreciated.
> Thanks
>

Bulk Insert: Unexpected end-of-file (EOF) encountered...

Hi to all,
I have a problem about a importation of a file *.csv with SQL Server,
through a bulk insert, called in a store procedure that a c# sw calls.
This is the description of the error:
--
System.Data.SqlClient.SqlException stata individuata
Message="Bulk Insert: Unexpected end-of-file (EOF) encountered in
data file.\r\nOLE DB provider 'STREAM' reported an error. The provider
did not give any information about the error.\r\nOLE DB error trace
[OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
The provider did not give any information about the error.].\r\nThe
statement has been terminated."
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=16
LineNumber=1
Number=4832
Procedure=""
Server="ets3971"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption
exception, Boolean breakConnection)
at
System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at
System.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString)
at
System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async)
at
System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result)
at
System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at sarbox.Default.LoadFlux_Click(Object sender, EventArgs e) in
c:\Inetpub\wwwroot\Zarbox2.2\SoxAdmin\Default.aspx .cs:line 1509
--

Th@.nks to all

AB@.AB@. (b.aharon44@.gmail.com) writes:

Quote:

Originally Posted by

I have a problem about a importation of a file *.csv with SQL Server,
through a bulk insert, called in a store procedure that a c# sw calls.
This is the description of the error:
--
System.Data.SqlClient.SqlException stata individuata
Message="Bulk Insert: Unexpected end-of-file (EOF) encountered in
data file.\r\nOLE DB provider 'STREAM' reported an error. The provider
did not give any information about the error.\r\nOLE DB error trace
[OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
The provider did not give any information about the error.].\r\nThe
statement has been terminated."

Unfortunately, the information you posted is not sufficient to help
you. Could you please post:

1) The BULK INSERT statement.
2) Any format file you are using.
3) A short sample of the data file.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 17 Apr, 23:40, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

AB@. (b.aharo...@.gmail.com) writes:

Quote:

Originally Posted by

I have a problem about a importation of a file *.csv with SQL Server,
through a bulk insert, called in a store procedure that a c# sw calls.
This is the description of the error:
--
System.Data.SqlClient.SqlException stata individuata
Message="Bulk Insert: Unexpected end-of-file (EOF) encountered in
data file.\r\nOLE DB provider 'STREAM' reported an error. The provider
did not give any information about the error.\r\nOLE DB error trace
[OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
The provider did not give any information about the error.].\r\nThe
statement has been terminated."

>
Unfortunately, the information you posted is not sufficient to help
you. Could you please post:
>
1) The BULK INSERT statement.
2) Any format file you are using.
3) A short sample of the data file.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I have risolve it - thanks|||This looks resolved, but I've experienced this problem before.
Resolution occured in one of three ways:

1) We sometimes get files that are cut off prematurely and a line
will only be a fraction completed. This will fail a bulk-insert.
2) Sometimes an extra carriage return is at the end of the file.
I've seen this fail the bulk-insert with an Unexecpected EOF message.
3) Sometimes I just couldn't figure out the answer and using DTS
instead of bulk insert resolved the problem.

I hope that helps somebody. :D

-Utah

Bulk Insert statement

Hi,

I was using a BULK INSERT statement in a stored procedure.Could any one help me out on one prob.I wanted to let the user select the file he wants to update and then i want to pass this address as a parameter in the stored procedure.

Suppose there is a parameter @.loc,so i want to use this parameter as

Bulk insert TableName from @.loc with(fieldterminator=',')
plz help me out thxI haven't test this but you may take help of Dynamic SQL, http://www.sommarskog.se/dynamic_sql.html fyi.|||Hi its been days now is there no one who could help me out?Is it because i am asking something that is impossible or something wrong plz let me know so that i would not waste my time n go ahead with something that would fulfill my task|||Did you even try dynamic sql as suggested to you?
declare @.fn varchar(255)
set @.fn='\\mypc\tmp\tmp1.txt'
exec ('bulk insert mytable from '''+@.fn+''' with(fieldterminator='','')')|||Hi its been days now is there no one who could help me out?Is it because i am asking something that is impossible or something wrong plz let me know so that i would not waste my time n go ahead with something that would fulfill my task
u haven't replied to Satya's solution.Then how we know that u got the answer or not?|||Even here u are giving the static location of the file from which u want the bulk insert the data from.What i wanted was to allow the user to define the path of the file and i wanted to pass this path as a parameter .In the example u have given its seems to me the same thing.May be if you could explain me more as i am a newbie.Sorry for the inconvienience.|||also i did try this code-:
@.loc nvarchar (50)
Bulk Insert Tablename From '''@.loc'' with(fieldterminator='','')
but i get an error while saving this stored procedure which says
"Cant find @.loc"|||post ur stored procedure|||Even here u are giving the static location
No, I am using a variable. Here are some code for you to play with
Note: UNC path used for the load as the server is not running on my local PC.
C:\tmp>echo aaa,bbb >tmp.txt

C:\tmp>more tmp.txt
aaa,bbb

C:\tmp>"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\isql" -U sa -S devdb
Password:
1> use tempdb
2> go
1> create proc pdreyer_load
2> @.filename varchar(255)
3> as
4> exec ('bulk insert #t1 from '''+@.filename+''' with(fieldterminator='','')')
5> go
1> create table #t1 (f1 varchar(10),f2 varchar(10))
2> exec pdreyer_load '\\pdreyer\tmp\tmp.txt'
3> select * from #t1
4> go
f1 f2
---- ----
aaa bbb

(1 row affected)
1> drop table #t1
2> drop procedure pdreyer_load
3> go
1> exit

C:\tmp>del tmp.txt|||Is the source file changed all the time?

Bulk Insert Runs Twice!

I have a simple Bulk Insert statement I want to run in a stored procedure.
Here it is:
Set @.bulk_cmd = 'BULK INSERT MyTable
FROM ''C:\mydump.txt''
WITH (FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')'
EXEC(@.bulk_cmd)
It runs fine but does the insert twice. There are 35 records in the file,
you can watch it load 35 records twice. The records are field terminated wit
h
a tab, and row terminated with CR\LF. I have tried numerous other terminatio
n
characters and all does the same. The actual txt file was created using DTS
from a table.
I am using SQL Server 2000.
Any ideas would be appreciated.
MGAre you checking the target table to confirm that it really is inserting the
rows twice, or do you think that's what is happening because you see this in
the messages:
(35 rows affected)
(35 rows affected)
You see it twice because the top message is from the BULK INSERT inside the
sp, and the bottom is the sp reported how many rows where affected. If you
want to only see the message once, put SET NOCOUNT ON at the top of your sp.
"mgcap" wrote:

> I have a simple Bulk Insert statement I want to run in a stored procedure.
> Here it is:
> Set @.bulk_cmd = 'BULK INSERT MyTable
> FROM ''C:\mydump.txt''
> WITH (FIELDTERMINATOR = ''\t'',
> ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')'
> EXEC(@.bulk_cmd)
> It runs fine but does the insert twice. There are 35 records in the file,
> you can watch it load 35 records twice. The records are field terminated w
ith
> a tab, and row terminated with CR\LF. I have tried numerous other terminat
ion
> characters and all does the same. The actual txt file was created using DT
S
> from a table.
> I am using SQL Server 2000.
> Any ideas would be appreciated.
> MG|||Mark,
Many thanks. I feel like a goof. That was it. The real issue was that I was
running the routine numerous times, not truncing the table every time, then
seeing more record in the table that 35. That along with the double message
threw me. I should have known better.
Thanks,
Mark
"Mark Williams" wrote:
> Are you checking the target table to confirm that it really is inserting t
he
> rows twice, or do you think that's what is happening because you see this
in
> the messages:
> (35 rows affected)
> (35 rows affected)
> You see it twice because the top message is from the BULK INSERT inside th
e
> sp, and the bottom is the sp reported how many rows where affected. If you
> want to only see the message once, put SET NOCOUNT ON at the top of your s
p.
>
> --
>
> "mgcap" wrote:
>

bulk insert not working from asp.netpage

Hi,

I am tryin to run the store procedure which has bulk insert command.when i run the code,it says you don't have permission to use bulk insert command. This query/storeprocedure is running succesfully in the backend(sqlserver2000).

solution is only user in Bulkadmin role has permission for bulk insert .But i don't know how to create one and use it in asp.net.

Can anyone help me in this. ..Here is the code.

string conn_string1 =@."Data Source=SENTHIL\TEST;Initial Catalog=EMPLOYEE;Integrated Security=SSPI";

SqlConnection objconn1=

new SqlConnection(conn_string1);

objconn1.Open();

SqlCommand command1=

new SqlCommand("bulkinsert",objconn1);

command1.CommandType=CommandType.StoredProcedure;

command1.ExecuteNonQuery();

Response.Write("Stored procedure excecuted");

objconn1.Close();

thanks,

kar

In http://msdn2.microsoft.com/en-us/library/ms188365.aspx

Requires INSERT and ADMINISTER BULKOPERATIONS permissions. Additionally, ALTER TABLE permission isrequired if one or more of the following is true:

Constraints exist and the CHECK_CONSTRAINTS option is not specified.

Note: Disabling constraints is the default behavior. To check constraints explicitly, use the CHECK_CONSTRAINTS option.

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?
Hi Nick
"Nick" wrote:

> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicD
ata.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?Hi Nick
"Nick" wrote:

> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicD
ata.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

BULK INSERT not loading all rows from a text file.

I have written a stored procedure that is used to load data into a SQL
Server 2000 and 2005 databases from some 40ish different text files.
One of the text files is loading all of the rows apart from the last
one. No error message is issued when this happens.
The command I am using to load the data is as follows:
BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
\Basicdata.bad')
(I understand that the ERRORFILE option only works in SQL Server 2005,
but 2000 seems to be able to ignore it without a problem.)
To fix the problem, I have to insert a 'tab' after the last section of
data in the last row.
49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
02
49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
02 02<tab>
Unless the last tab is manually inserted, the last row does not get
loaded to the database, and no error message is issued. If the tab is
inserted all of the data is loaded correctly. However, the text files
come from a third party so I cannot ensure that this problem will not
occur again.
The .fmt file used to load the data is as follows:
8.0
11
1 SQLCHAR 0 12 "\t" 1
BasicDataID ""
2 SQLCHAR 0 12 "\t" 2
BasicTaxOverrideID ""
3 SQLCHAR 0 24 "\t" 3
LegalEffectiveDate ""
4 SQLCHAR 0 12 "\t" 4
ReleaseNo ""
5 SQLCHAR 0 50 "\t" 5
BasicDataDesc SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "\t" 6
PassFlag SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 2 "\t" 7
PassType SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 2 "\t" 8
BaseType SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 2 "\t" 9
DateFlag SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 2 "\t" 10
RoundingLevel SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 8 "\r\n" 11
TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
My question is, is there any way to trigger an error message to denote
that (in my example) the last row did not get loaded successfully? Is
there a parameter associated with the BULK INSERT command that I am
not currently using that could help with this problem?Hi Nick
"Nick" wrote:
> I have written a stored procedure that is used to load data into a SQL
> Server 2000 and 2005 databases from some 40ish different text files.
> One of the text files is loading all of the rows apart from the last
> one. No error message is issued when this happens.
> The command I am using to load the data is as follows:
> BULK INSERT BasicDataT FROM 'E:\Temp\Food GNRLv3\BasicData.txt' WITH
> ( FORMATFILE = 'C:\Taxcalc\Client\InitialInstall\BasicData.fmt',
> MAXERRORS=0, BATCHSIZE=500000,CHECK_CONSTRAINTS, ERRORFILE='C:\Taxcalc
> \Basicdata.bad')
> (I understand that the ERRORFILE option only works in SQL Server 2005,
> but 2000 seems to be able to ignore it without a problem.)
> To fix the problem, I have to insert a 'tab' after the last section of
> data in the last row.
>
> 49708 60646 2006-11-15 1 CITY SALES TAX 1 00 00 02
> 02
> 49709 60647 1990-01-01 1 GROSS RECEIPTS TAX 2 03 01
> 02 02<tab>
>
> Unless the last tab is manually inserted, the last row does not get
> loaded to the database, and no error message is issued. If the tab is
> inserted all of the data is loaded correctly. However, the text files
> come from a third party so I cannot ensure that this problem will not
> occur again.
> The .fmt file used to load the data is as follows:
> 8.0
> 11
> 1 SQLCHAR 0 12 "\t" 1
> BasicDataID ""
> 2 SQLCHAR 0 12 "\t" 2
> BasicTaxOverrideID ""
> 3 SQLCHAR 0 24 "\t" 3
> LegalEffectiveDate ""
> 4 SQLCHAR 0 12 "\t" 4
> ReleaseNo ""
> 5 SQLCHAR 0 50 "\t" 5
> BasicDataDesc SQL_Latin1_General_CP1_CI_AS
> 6 SQLCHAR 0 1 "\t" 6
> PassFlag SQL_Latin1_General_CP1_CI_AS
> 7 SQLCHAR 0 2 "\t" 7
> PassType SQL_Latin1_General_CP1_CI_AS
> 8 SQLCHAR 0 2 "\t" 8
> BaseType SQL_Latin1_General_CP1_CI_AS
> 9 SQLCHAR 0 2 "\t" 9
> DateFlag SQL_Latin1_General_CP1_CI_AS
> 10 SQLCHAR 0 2 "\t" 10
> RoundingLevel SQL_Latin1_General_CP1_CI_AS
> 11 SQLCHAR 0 8 "\r\n" 11
> TaxGroupingCode SQL_Latin1_General_CP1_CI_AS
> My question is, is there any way to trigger an error message to denote
> that (in my example) the last row did not get loaded successfully? Is
> there a parameter associated with the BULK INSERT command that I am
> not currently using that could help with this problem?
>
It looks like the last field is not present if you removed this from the
format file it should work!
John

Bulk Insert into SQL Server Table With XML

Hey There,

Here, is the example of Bulk Insert into SQL Server Table.

From Application you have to pass a XML string to a Stored Procedure and it will insert all data into table using that XML.

Example SP.

CREATE PROCEDURE StoredProcName

(

@.strXML varchar(8000)

)

AS

Declare @.intPointer int

exec sp_xml_preparedocument @.intPointer output, @.strXML

INSERT into tbl_plnd_insertion

SELECT Column1, Column2, Column3, Column4, Column5

FROM OpenXml(@.intPointer,'/root/tbl_plnd_insertion',2)

WITH (Column1 varchar(20) '@.Column1' , Column2 varchar(20) '@.Column2', Column3 varchar(20) '@.Column3' , Column4 varchar(50) '@.Column4', Column5 varchar(50) '@.Column5')

exec sp_xml_removedocument @.intPointer

Thanks !!!!!

In SQL Server 2000, Text data type is reasonable to use for huge data,

@.strXML Text--varchar(8000)

This is not a bulk insert, it is reading the data from the XML string into a table. sp_xml_preparedocument is expensive, it will use lot of memory & resource when the HUGE data parsed.

In SQL Server 2005, the best option is using XML datatype. You don’t need expensive sp_xml_preparedocuemnt to parse your XML string into selectable values, no need OpenXML also.

Sample,

Code Snippet

Declare @.xml as XML

set @.xml = '<root><student name="Stud1" sex="m"/><student name="Stud2" sex="f"/></root>'

Select

[Table].[Column].value('./@.name', 'varchar(100)') as [Name],

[Table].[Column].value('./@.sex', 'char') as [Sex]

from

@.xml.nodes('/root/student') as [Table]([Column])

|||

Hey

I am really appreciating your input… J

I did not describe all details in my code.

Thanks

Bulk insert from netware problems after applying SP4

I'm running MSSQL 2K on Win 2k and just upgraded SQL Server from SP3
to SP4 (that's when my problem started). I have a procedure that bulk
inserts from a text file that is located on a Netware 4.11 file server.
The procedure actually references the file using the UNC path to a
share on a NT 4 server that is connected to the novel server using
gateway services for NetWare. Now when it tries to reference the file I
get:

Server: Msg 4861, Level 16, State 1, Line 1
Could not bulk insert because file '\\NtServer\Netware Share\file.txt'
could not be opened. Operating system error code 5(Access is denied.).

I've tried running the process as a domain administrator and get the
same results.

I can run these commands and they work as expected

master..xp_cmdshell 'dir \\NtServer\Netware Share\file.txt'
master..xp_cmdshell 'type \\ NtServer\Netware Share\file.txt'

Also if I Bulk insert the file from the local drive of the same NT
server it works fine i.e.:
BULK INSERT #imp FROM \\NtServer\d$\file.txt'

Any thoughts would be appreciated.(gchavez@.aspenres.com) writes:
> I'm running MSSQL 2K on Win 2k and just upgraded SQL Server from SP3
> to SP4 (that's when my problem started). I have a procedure that bulk
> inserts from a text file that is located on a Netware 4.11 file server.
> The procedure actually references the file using the UNC path to a
> share on a NT 4 server that is connected to the novel server using
> gateway services for NetWare. Now when it tries to reference the file I
> get:
> Server: Msg 4861, Level 16, State 1, Line 1
> Could not bulk insert because file '\\NtServer\Netware Share\file.txt'
> could not be opened. Operating system error code 5(Access is denied.).
> I've tried running the process as a domain administrator and get the
> same results.

Make sure that the account under which the SQL Server service runs has
access to the file on the share. You can check which account this is
by right-clicking My Computer, Manage, find Services, then find the
MS SQL Server server, right-click, Properties and then the Log On
tab. Note that users such as Local System do not have network access
at all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

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

BULK INSERT crashes Stored Procedure ?

I'm trying to BULK INSERT from a series of files, but the files may or may
not exist.
Problem is that if a BULK INSERT command fails (because the file doesn't
exist), the next line of code in the SP is never reached!
>> Server: Msg 4860, Level 16, State 1, Line 1
>> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
>> CODE:
>> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
>> + @.path + @.fn + ''''
>> + ' WITH
>> (
>> FIELDTERMINATOR = ''|''
>> ,ROWTERMINATOR = ''\n''
>> ,TABLOCK
>> )
>> '
>> EXEC( @.cmd ) ; -- BOMBS
>> -- EXEC sp_executesql @.cmd ; -- BOMBS
Naturally I've tried setting XACT_ABORT OFF (among other various
superstitious rituals). Nothing works.
TIA,
--joe
PS,
I don't have control of this server & the current admin & users aren't very
technical... As such I want to avoid using xp_cmdshell... similarly, I don't
want to use a linked text server, and DTS is a problem b/c the file names
change along with the aforementioned administrative issues. I would like to
have something that works stand alone that can run w/out intervention and a
minimum of admin.I googled around for this problem, and it appears that "It's supposed to
work that way" is the answer. Is this the only answer, that this class of
error throws a "fatal" & immediately short-circuits further execution of the
remainder of the SP?
This has become a show stopper at the moment.
I'm trying to run through a directory of ASCII files, so having a fatal on
just one file is a poor option when there are 20 files to import. I'd use
xp_cmdshell, but its permissions seem to get turned off whenever a patch is
applied, and that doesn't guard me against a bad 0-byte ASCII file (no
CR/LF).
Is there perhaps a way to set up DTS so it handles dynamic file names when
importing from ASCII? Any suggestions?
Thanx,
-joe
PS, Just to reiterate from my previous post, I thought of using a linked
OLEDB text server, but that requires the right OLEDB driver and permissions,
along with a capable DBA watching over things (plus if the file doesn't
exist, will the OLEDB driver throw the same kind of fatal?). Using bcp in
DOS from a scheduler doesn't solve the dynamic file name problem (oh yeh,
try programming DOS batch files... it can be done, BUT!!!), and implementing
client software (perl, WSE, etc.) on the server or workstation creates
complexity & other headaches. Please, oh dear God, Why ME!!!!' Tell them
to go back to the @.#$ mainframe, hell, they were better off with 3x5
cards!!!!!!
Previous n.g. thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&frame=right&th=28fd4c29145420ba&seekm=OQmAG3VcBHA.1848%40tkmsftngp05#link12
thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3BF9B2DC.65712B51%40drew.edu&rnum=1&prev=/groups%3Fq%3Dhandling%2Berrors%2BCould%2Bnot%2Bbulk%2Binsert.%2BFile%2Bgroup:microsoft.public.sqlserver.programming%2Bgroup:microsoft.public.sqlserver.programming%26hl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dmicrosoft.public.sqlserver.programming%26selm%3D3BF9B2DC.65712B51%2540drew.edu%26rnum%3D1
--
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:W4xsb.34667$Mc.21141@.twister.austin.rr.com...
> I'm trying to BULK INSERT from a series of files, but the files may or may
> not exist.
> Problem is that if a BULK INSERT command fails (because the file doesn't
> exist), the next line of code in the SP is never reached!
> >> Server: Msg 4860, Level 16, State 1, Line 1
> >> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
> >> CODE:
> >> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
> >> + @.path + @.fn + ''''
> >> + ' WITH
> >> (
> >> FIELDTERMINATOR = ''|''
> >> ,ROWTERMINATOR = ''\n''
> >> ,TABLOCK
> >> )
> >> '
> >> EXEC( @.cmd ) ; -- BOMBS
> >> -- EXEC sp_executesql @.cmd ; -- BOMBS
>
> Naturally I've tried setting XACT_ABORT OFF (among other various
> superstitious rituals). Nothing works.
>
> TIA,
> --joe
> PS,
> I don't have control of this server & the current admin & users aren't
very
> technical... As such I want to avoid using xp_cmdshell... similarly, I
don't
> want to use a linked text server, and DTS is a problem b/c the file names
> change along with the aforementioned administrative issues. I would like
to
> have something that works stand alone that can run w/out intervention and
a
> minimum of admin.
>
>|||I just came up with a ghastly hack for a solution... a tad dangerous
perhaps...
In the SP, I log the file in a log table before I run the BULK INSERT. The
SP checks in the log table see if an attempt has been made on that file name
before, and will loop if an attempt has already been made.
I then set up a 2-step job on the server. Both steps call the same SP, and
step 1 calls step 2 on failure, and step 2 calls step 1 on failure. Until
the SP runs out of possible file names (filename is based on date in a
xxxxmmddyy.txt format), it'll keep BULK INSERTing files it finds and failing
on ones it doesn't (the SP succeeds when it can exit without error, when it
runs out of names, the maximum of which is based upon today's date).
Finally, both job steps (whichever one happens to be running at the time)
exit the job on success (which is again, running out of file names to look
for).
As I said, a ghastly hack.
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:W4xsb.34667$Mc.21141@.twister.austin.rr.com...
> I'm trying to BULK INSERT from a series of files, but the files may or may
> not exist.
> Problem is that if a BULK INSERT command fails (because the file doesn't
> exist), the next line of code in the SP is never reached!
> >> Server: Msg 4860, Level 16, State 1, Line 1
> >> Could not bulk insert. File 'C:\2003_05_02.txt' does not exist.
> >> CODE:
> >> set @.cmd = 'BULK INSERT Z_BATCH_IMP_USPS_RAW FROM '''
> >> + @.path + @.fn + ''''
> >> + ' WITH
> >> (
> >> FIELDTERMINATOR = ''|''
> >> ,ROWTERMINATOR = ''\n''
> >> ,TABLOCK
> >> )
> >> '
> >> EXEC( @.cmd ) ; -- BOMBS
> >> -- EXEC sp_executesql @.cmd ; -- BOMBS
>
> Naturally I've tried setting XACT_ABORT OFF (among other various
> superstitious rituals). Nothing works.
>
> TIA,
> --joe
> PS,
> I don't have control of this server & the current admin & users aren't
very
> technical... As such I want to avoid using xp_cmdshell... similarly, I
don't
> want to use a linked text server, and DTS is a problem b/c the file names
> change along with the aforementioned administrative issues. I would like
to
> have something that works stand alone that can run w/out intervention and
a
> minimum of admin.
>
>|||Before you start using Bulk Insert, can you confirm if the file really exists , you might want to use the command "xp_fileexist"|||"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> Before you start using Bulk Insert, can you confirm if the file really
exists , you might want to use the command "xp_fileexist"
No such xp_ on their machine, although xp_cmdshell is there, and worked at
one point in time. Plus, the admin situation is problematic such that xp_*
priveleges are revoked when service packs get applied.
This has to work w/out any special priveleges or assignments, just a job
that works reliably (nightly) w/out user intervention.
Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so even
if I knew the file existed, it doesn't protect the batch process from
interruption.
I *did* find a workaround to this problem. See my last reply to myself in
the thread. It's too bad that MS never got around to fixing these issues
(poor T-SQL error handling) until Longhorn.
Thanks,|||just a fyi, enhanced error handling has nothing to do with longhorn. it's to
do with yukon.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
news:HlFsb.36901$Mc.28999@.twister.austin.rr.com...
> "Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
> news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> > Before you start using Bulk Insert, can you confirm if the file really
> exists , you might want to use the command "xp_fileexist"
> No such xp_ on their machine, although xp_cmdshell is there, and worked at
> one point in time. Plus, the admin situation is problematic such that xp_*
> priveleges are revoked when service packs get applied.
> This has to work w/out any special priveleges or assignments, just a job
> that works reliably (nightly) w/out user intervention.
> Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
> BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so
even
> if I knew the file existed, it doesn't protect the batch process from
> interruption.
> I *did* find a workaround to this problem. See my last reply to myself in
> the thread. It's too bad that MS never got around to fixing these issues
> (poor T-SQL error handling) until Longhorn.
> Thanks,
>
>|||ahhhh, you are my oj in the mourning... and yukon correct me any time you
want when i get the code names for MS products mixed up
--
+--
To e-mail me,
replace "REPLACETHIS" with buddhashortfatguy
"oj" <nospam_ojngo@.home.com> wrote in message
news:e3vmQWbqDHA.2496@.TK2MSFTNGP09.phx.gbl...
> just a fyi, enhanced error handling has nothing to do with longhorn. it's
to
> do with yukon.
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "buddhashortfatguy" <REPLACETHIS@.yahoo.com> wrote in message
> news:HlFsb.36901$Mc.28999@.twister.austin.rr.com...
> > "Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
> > news:D1E87B91-DD63-4B62-BC27-1DC437CAB343@.microsoft.com...
> > > Before you start using Bulk Insert, can you confirm if the file really
> > exists , you might want to use the command "xp_fileexist"
> >
> > No such xp_ on their machine, although xp_cmdshell is there, and worked
at
> > one point in time. Plus, the admin situation is problematic such that
xp_*
> > priveleges are revoked when service packs get applied.
> >
> > This has to work w/out any special priveleges or assignments, just a job
> > that works reliably (nightly) w/out user intervention.
> >
> > Even so, if the ASCII files are 0-Byte or formatted badly in any regard,
> > BULK INSERT will also make a T-SQL stored proc bomb out w/ a fatal, so
> even
> > if I knew the file existed, it doesn't protect the batch process from
> > interruption.
> >
> > I *did* find a workaround to this problem. See my last reply to myself
in
> > the thread. It's too bad that MS never got around to fixing these issues
> > (poor T-SQL error handling) until Longhorn.
> >
> > Thanks,
> >
> >
> >
>|||The SP "xp_fileexist" exists in Master db, not in the User DB.|||Thanks, I gather that it's new in SS2K?
It's not listed in the SS2K BOL in the T-SQL ref. Doesn't require additional
rights, great!
It still doesn't solve the problem with any other kind of fatal error in
BULK INSERT (a 0-byte file, for instance).
The bottom line is that MS SQL 7's & 8's error handling isn't very capable
and I'll still have to either go w/ a hacked workaround in the job manager
or a workaround in client software (Perl, WSE...).
What really makes me wonder is, why in the first place was BULK INSERT
implemented to throw fatal errors *at all?* It's an import utility, that's
all it is - it's not a table reference that is the subject of
latent/deferred binding. Makes me wonder about the underpinning API, the
(familiar) scent of old Sybase code lurking under the hood.
"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:8AD1A570-60F4-48CF-B368-73369419CC27@.microsoft.com...
> The SP "xp_fileexist" exists in Master db, not in the User DB.|||Just to let you know you're not alone,
I posted this a few hours before you.
http://communities.microsoft.com/newsgroups/previewFrame.asp?ICP=msdn&sLCID=
us&sgroupURL=microsoft.public.sqlserver.programming&sMessageID=%253CuaBAWQSq
DHA.1884@.TK2MSFTNGP09.phx.gbl%253E.
At the moment I'm using this technic: in the sp that needs to bulk insert I
assign the text of the format file to a variable and create the file on the
fly (using a wrapper to the file system object), something like this:
/*********/
declare @.txt varchar(8000)
declare @.FormatFilePath varchar(255)
declare @.path varchar(255)
set @.txt='8.0
1
1 SQLCHAR 0 0 "\r\n" 2 Code ""
'
select @.path='Target.dat', @.FormatFilePath='c\winnt\temp\Target.fmt'
exec @.Res=dbo.st_WriteToFile @.FormatFilePath, @.txt --This is a FSO wrapper
that creates or overwrites a file with the specified text
if @.Res<>0
goto ErrorHandler
create table Target(
PKCheck int Identity(1,1),
Code char(15)
)
exec(N'bulk insert Target from '''
+ @.path
+ N''' with (FORMATFILE='''
+ @.TempDirPath
+ N''', TABLOCK)')
/**********/
It works fine, but of course if the data file or the table change their
structures you have to modify the sp, and you can't use third parties format
file without checking for their correctness.
I' was trying to generalize this approch by adding error controls when I
stumbled into your same problem.
Now I'm thinking of using BCP's format option via xp_cmdshell to produce a
valid format file, but of course I have to take into account the cases when
the number of table fields and file fields and their mutual positions
differ, but the problem of using format files not created by you remains.
Still working on.
Salvor
--
++++++++++++++
To e-mail me,
remove ".NO_SPAM" from my e-mail address

Bulk import stored procedure

I am trying to devise the best way to perform the following:
Import/export word documents into and out of a table in SQL 2005 for a VB
program to access. The user of the program would be able to choose what doc
they want to import into the table thus the location of the file and name of
the file can change. I have made a simple inserst statement that works when I
explicitly state the file path, but I need a variable file path, but I can't
seem to find how to do this or if there is a better way. The following is
what I have come up with so far even thought I can't execute it to create the
stored procedure because it won't accept the @.doclink in the Bulk statement.
Anyone have ideas on how to do this or a better way?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DocInsert]
(
@.PermitID int,
@.DocLink char(255),
@.Title nchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
Insert into documents(doclink,title,created,createdby,doc)
Select @.doclink,@.title,getdate(),'John Doe',* from openrowset(bulk
@.doclink,single_blob,codepage='raw') as doc
END
GO"Joe S." <joecrew@.news.postalias> wrote in message
news:4E3401D2-24BA-41B6-ACFE-144D7D15772B@.microsoft.com...
>I am trying to devise the best way to perform the following:
> Import/export word documents into and out of a table in SQL 2005 for a VB
> program to access. The user of the program would be able to choose what
> doc
> they want to import into the table thus the location of the file and name
> of
> the file can change. I have made a simple inserst statement that works
> when I
> explicitly state the file path, but I need a variable file path, but I
> can't
> seem to find how to do this or if there is a better way. The following is
> what I have come up with so far even thought I can't execute it to create
> the
> stored procedure because it won't accept the @.doclink in the Bulk
> statement.
> Anyone have ideas on how to do this or a better way?
>
This isn't going to work in the general case. For this to work the SQL
Server must be able to access the file location. If the file is on the
client location, the pathing would be different and the server's account
wouldn't be able to open the file.
Instead of passing the file path to the procedure, pass the file bits. Just
declare the procedure to take a parameter of type varbinary(max) and pass
the file bits from VB. From VB load the file into a byte array and pass it
to through a SqlParameter.
David

Bulk import stored procedure

I am trying to devise the best way to perform the following:
Import/export word documents into and out of a table in SQL 2005 for a VB
program to access. The user of the program would be able to choose what doc
they want to import into the table thus the location of the file and name o
f
the file can change. I have made a simple inserst statement that works when
I
explicitly state the file path, but I need a variable file path, but I can't
seem to find how to do this or if there is a better way. The following is
what I have come up with so far even thought I can't execute it to create th
e
stored procedure because it won't accept the @.doclink in the Bulk statement.
Anyone have ideas on how to do this or a better way?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DocInsert]
(
@.PermitID int,
@.DocLink char(255),
@.Title nchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
Insert into documents(doclink,title,created,createdb
y,doc)
Select @.doclink,@.title,getdate(),'John Doe',* from openrowset(bulk
@.doclink,single_blob,codepage='raw') as doc
END
GO"Joe S." <joecrew@.news.postalias> wrote in message
news:4E3401D2-24BA-41B6-ACFE-144D7D15772B@.microsoft.com...
>I am trying to devise the best way to perform the following:
> Import/export word documents into and out of a table in SQL 2005 for a VB
> program to access. The user of the program would be able to choose what
> doc
> they want to import into the table thus the location of the file and name
> of
> the file can change. I have made a simple inserst statement that works
> when I
> explicitly state the file path, but I need a variable file path, but I
> can't
> seem to find how to do this or if there is a better way. The following is
> what I have come up with so far even thought I can't execute it to create
> the
> stored procedure because it won't accept the @.doclink in the Bulk
> statement.
> Anyone have ideas on how to do this or a better way?
>
This isn't going to work in the general case. For this to work the SQL
Server must be able to access the file location. If the file is on the
client location, the pathing would be different and the server's account
wouldn't be able to open the file.
Instead of passing the file path to the procedure, pass the file bits. Just
declare the procedure to take a parameter of type varbinary(max) and pass
the file bits from VB. From VB load the file into a byte array and pass it
to through a SqlParameter.
David

Building Dynamic Sql in Stored Proc issue

Hi all,

I'm gonna need some help with this one.

I have this stored procedure written up that basically builds a dataset by querying a bunch of tables using outer joins. Our problem now is that it seems it takes a while for the dataset to pull back across the network. We would hence like to filter that dataset by adding on to the query in the procedure dynamically. Heres the query from the proc below:

SELECT N_Client.Prefix,
IsNull(dbo.N_CLIENT.SURNAME, '') + ', ' + IsNull(dbo.N_CLIENT.FIRST_NAME, '') AS Client_FullName,
dbo.N_CLIENT.TITLE,
dbo.N_COMPANY.COMPANY_NAME,
dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_NAME,
dbo.N_DIVISION.DIVISION_NAME,
dbo.N_REF_INDUSTRY.INDUSTRY_NAME,
dbo.N_CLIENT.DIRECT_PHONE,
dbo.N_CLIENT.EMAIL,
dbo.N_CLIENT.TIER_ID,
(SELECT COUNT(Client_ID)
FROM N_Alumni
WHERE N_Alumni.Client_ID = N_Client.Client_ID) AS Alumni,
(SELECT COUNT(Client_ID)
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandB,
(SELECT BAH_EMP_NID
FROM N_XREF_Client_Activity
WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandBMailer,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'MM' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS MMEMPNID,
dbo.N_CLIENT.SURNAME AS Client_Surname,
dbo.N_CLIENT.FIRST_NAME,
dbo.N_CLIENT.FIRST_NAME AS Client_FirstName,
dbo.N_CLIENT.COMPANY_ID,
dbo.N_CLIENT.DIVISION_ID,
dbo.N_CLIENT.BUSINESS_UNIT_ID,
dbo.N_COMPANY.GROUP_ID,
dbo.N_CLIENT.COUNTRY,
dbo.N_GROUP.GROUP_NAME,
dbo.N_CLIENT.CLIENT_ID,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCEMPNID,
(SELECT NMT_Practice_Code
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Code,
(SELECT NMT_Practice_Name
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Name,
#returnTable.AddlFullName,
#returnTable.AddlEMPNID,
#returnTable.FunctionID as Function_ID,
#returnTable.FunctionName as Function_Name,
(SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '')
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOFullName,
(SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID
FROM N_Vw_Client_BAH_Contact
WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOEMPNID,
ISNULL(dbo.N_CLIENT.ARCHIVE_FLAG, 'N') AS Archive_Flag,
dbo.N_COMPANY.TARGET_COMPANY_FLAG,
dbo.N_COMPANY.INDUSTRY_ID,
N_Client.Address1,
N_Client.Address2,
N_Client.Address3,
N_Client.Address4,
N_Client.Address5,
N_Client.City,
N_Client.State,
N_Client.Postal_Code,
N_Client.Country,
N_Client.Region,
N_Client.Office_Code,
N_Client.Broderick_Target_Flag
FROM dbo.N_CLIENT
INNER JOIN
dbo.N_COMPANY ON dbo.N_CLIENT.COMPANY_ID = dbo.N_COMPANY.COMPANY_ID
LEFT OUTER JOIN
dbo.N_GROUP ON dbo.N_COMPANY.GROUP_ID = dbo.N_GROUP.GROUP_ID
LEFT OUTER JOIN
dbo.N_REF_INDUSTRY ON dbo.N_COMPANY.INDUSTRY_ID = dbo.N_REF_INDUSTRY.INDUSTRY_ID
LEFT OUTER JOIN
dbo.N_DIVISION ON dbo.N_DIVISION.DIVISION_ID = dbo.N_CLIENT.DIVISION_ID
LEFT OUTER JOIN
#returnTable ON #returnTable.CLIENT_ID = dbo.N_CLIENT.CLIENT_ID
LEFT OUTER JOIN
dbo.N_BUSINESS_UNIT ON dbo.N_CLIENT.BUSINESS_UNIT_ID = dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_ID
ORDER BY N_Client.client_id
Where upper(title) like '%parameter_value%'
and company_id = 'parameter_value'
and Nmt_practice_code = 'parameter_value' ............and so on

What we would like to do is to add 15 (where some may be null) input parameters to the definition of the query and then somehow (where the parameter is not null), dynamically add that parameter to the WHERE clause of the query illustrated in italics above. The bold print are examples of 3 of the 15 parameters to be passed into the query by the proc, so basically
title, company_id,Nmt_practice_code would be the 3 parameters being passed into this proc.

So in other words if 9 parameters out of the 15 are passed into the proc, we would like those 9 parameters to be added/built dynamically onto the SQL Query as 9 predicates. I hope I have been clear. Does anyone have any experience with this? Help!!

ThanksYou can store your parameters into a 15-column temporary table and then join the main query with it.|||Alternativley...use and exec statemnt ( not for the weak of stomach )

declare @.var_1 varch(10), @.var_2 varchar (10)
set @.var_1 = 'value_1'
set @.var_2 = 'value_2'

exec( ' select * from table where x = ' +@.var_1+ ' and y = ' +@.var_2+ '')

so :

exec('..............
Where upper(title) like %'+@.parameter_value'%
and company_id = '@.parameter_value'
and Nmt_practice_code = '@.parameter_value'')

You effectively have to enclose the whole statement in an exec statement.
Its messy this way, and you will take a while to debig to get it right.

rdjabarov has a good idea.....its more elegant than my approach....

Building an SQL statement in a Stored Procedure

Hi everybody!!
The title says it all!!!
Joking aside, I need to build an SQL statement by concatenating fields and
parameters and if conditions and then execute that sql statement within the
same SP.
Is this possible, and if yes, how exactly?
Thanks,
IvanA lot of text, but I strongly encourage you to do the reading so you can mak
e an informed decision:
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
Home page: http://www.sommarskog.se/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message news:eGYyPVBlFHA.4012@.TK2MSFTNGP10.ph
x.gbl...
> Hi everybody!!
> The title says it all!!!
> Joking aside, I need to build an SQL statement by concatenating fields and
> parameters and if conditions and then execute that sql statement within th
e
> same SP.
> Is this possible, and if yes, how exactly?
> Thanks,
> Ivan
>|||I managed to build a string but I need to use it in a sub select. It doesn't
work.
I think the problem is because it apends a ' at the beginning and at the
end.
Is there a way to solve it?
Thanks,
Ivan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eIsEccBlFHA.1968@.TK2MSFTNGP14.phx.gbl...
> A lot of text, but I strongly encourage you to do the reading so you can
make an informed decision:
> http://www.sommarskog.se/dyn-search.html
> http://www.sommarskog.se/dynamic_sql.html
> Home page: http://www.sommarskog.se/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message
news:eGYyPVBlFHA.4012@.TK2MSFTNGP10.phx.gbl...
and
the|||You need to execute the whole query, like:
SET @.sql = 'SELECT ... FROM ... WHERE ... IN (SELECT... ...) ... '
And then do a PRINT of the @.sql variable before executing it so you can see
what the problem is. And
do read those articles if you haven't already.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ivan Debono" <ivanmdeb@.hotmail.com> wrote in message news:OmJbYtBlFHA.1148@.TK2MSFTNGP12.ph
x.gbl...
>I managed to build a string but I need to use it in a sub select. It doesn'
t
> work.
> I think the problem is because it apends a ' at the beginning and at the
> end.
> Is there a way to solve it?
> Thanks,
> Ivan
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
> im Newsbeitrag news:eIsEccBlFHA.1968@.TK2MSFTNGP14.phx.gbl...
> make an informed decision:
> news:eGYyPVBlFHA.4012@.TK2MSFTNGP10.phx.gbl...
> and
> the
>|||Print your query and test it in QA before attempting to execute it. That muc
h
I can say without seeing your script.
ML|||Look up coupling and cohesion in a book on basic software engineering.
That will tell you in detail why this is a very, very poor programming
pratice that good programmers would never use.|||Could you post an example of a document explaining this? Telling other peopl
e
to go find things that are basic to you is good for your ego but useless for
anything else. You won't convince a development team to do things right by
telling them how stupid they are.
Jerry
"--CELKO--" wrote:

> Look up coupling and cohesion in a book on basic software engineering.
> That will tell you in detail why this is a very, very poor programming
> pratice that good programmers would never use.
>

Building a Select Statement and SPs

Hey folks,
I've got a select statement that gets built dynamically in code.
If what I pass to a stored procedure is nothing but a string that is a
select statement, and then have the stored procedure execute that string, is
there still an advantage to using stored procedures (over just executing it
directly from code).
The other possibility is to pass other parameters to the SP and let the SP
build the Sql string. Either way though, the Select statement will be a
variable string that gets executed.
Is there still a case for using stored procedures in this type of case? The
result set size will range from 1 record to 50,000 records.
Thanks!John,
I generally advocate building the SELECT dynamically within the stored
procedure rather than within application code. This has two primary
benefits:
A) It keeps data access logic encapsulated
B) It can help keep SQL injection attacks at bay (by using sp_executesql
with parameters instead of just using EXEC)
You should carefully consider whether you actually need dynamic SQL at all,
and either way you should read this article for more information:
http://www.sommarskog.se/dynamic_sql.html
"John Smith" <js@.no.com> wrote in message
news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that string,
is
> there still an advantage to using stored procedures (over just executing
it
> directly from code).
> The other possibility is to pass other parameters to the SP and let the SP
> build the Sql string. Either way though, the Select statement will be a
> variable string that gets executed.
> Is there still a case for using stored procedures in this type of case?
The
> result set size will range from 1 record to 50,000 records.
> Thanks!
>|||John Smith wrote:
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that
> string, is there still an advantage to using stored procedures (over
> just executing it directly from code).
> The other possibility is to pass other parameters to the SP and let
> the SP build the Sql string. Either way though, the Select statement
> will be a variable string that gets executed.
> Is there still a case for using stored procedures in this type of
> case? The result set size will range from 1 record to 50,000 records.
> Thanks!
Having the SP build the SQL is fine. Just use sp_executesql to execute
the call, and if the query has parameters, then use the parameter
feature of sp_executesql to define them and pass them to the SQL
statement. That way, if similar statements get executed, the plan will
already be in cache.
Performance will be fine... assuming your queries are tuned properly.
David G.|||Thanks a lot
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBeLKXUgEHA.1764@.TK2MSFTNGP10.phx.gbl...
> John,
> I generally advocate building the SELECT dynamically within the stored
> procedure rather than within application code. This has two primary
> benefits:
> A) It keeps data access logic encapsulated
> B) It can help keep SQL injection attacks at bay (by using
sp_executesql
> with parameters instead of just using EXEC)
> You should carefully consider whether you actually need dynamic SQL at
all,
> and either way you should read this article for more information:
> http://www.sommarskog.se/dynamic_sql.html
>
> "John Smith" <js@.no.com> wrote in message
> news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
string,[vbcol=seagreen]
> is
> it
SP[vbcol=seagreen]
> The
>|||Thanks a lot
"David G." <david_nospam@.nospam.com> wrote in message
news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
> John Smith wrote:
> Having the SP build the SQL is fine. Just use sp_executesql to execute
> the call, and if the query has parameters, then use the parameter
> feature of sp_executesql to define them and pass them to the SQL
> statement. That way, if similar statements get executed, the plan will
> already be in cache.
> Performance will be fine... assuming your queries are tuned properly.
>
> --
> David G.
>|||John Smith wrote:[vbcol=seagreen]
> Thanks a lot
> "David G." <david_nospam@.nospam.com> wrote in message
> news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
And I should point out that if this procedure can be accessed by the
public or you are security conscious, you should validate the procedure
parameters to prevent any SQL injection problems.
David G.

Building a Select Statement and SPs

Hey folks,
I've got a select statement that gets built dynamically in code.
If what I pass to a stored procedure is nothing but a string that is a
select statement, and then have the stored procedure execute that string, is
there still an advantage to using stored procedures (over just executing it
directly from code).
The other possibility is to pass other parameters to the SP and let the SP
build the Sql string. Either way though, the Select statement will be a
variable string that gets executed.
Is there still a case for using stored procedures in this type of case? The
result set size will range from 1 record to 50,000 records.
Thanks!John,
I generally advocate building the SELECT dynamically within the stored
procedure rather than within application code. This has two primary
benefits:
A) It keeps data access logic encapsulated
B) It can help keep SQL injection attacks at bay (by using sp_executesql
with parameters instead of just using EXEC)
You should carefully consider whether you actually need dynamic SQL at all,
and either way you should read this article for more information:
http://www.sommarskog.se/dynamic_sql.html
"John Smith" <js@.no.com> wrote in message
news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that string,
is
> there still an advantage to using stored procedures (over just executing
it
> directly from code).
> The other possibility is to pass other parameters to the SP and let the SP
> build the Sql string. Either way though, the Select statement will be a
> variable string that gets executed.
> Is there still a case for using stored procedures in this type of case?
The
> result set size will range from 1 record to 50,000 records.
> Thanks!
>|||John Smith wrote:
> Hey folks,
> I've got a select statement that gets built dynamically in code.
> If what I pass to a stored procedure is nothing but a string that is a
> select statement, and then have the stored procedure execute that
> string, is there still an advantage to using stored procedures (over
> just executing it directly from code).
> The other possibility is to pass other parameters to the SP and let
> the SP build the Sql string. Either way though, the Select statement
> will be a variable string that gets executed.
> Is there still a case for using stored procedures in this type of
> case? The result set size will range from 1 record to 50,000 records.
> Thanks!
Having the SP build the SQL is fine. Just use sp_executesql to execute
the call, and if the query has parameters, then use the parameter
feature of sp_executesql to define them and pass them to the SQL
statement. That way, if similar statements get executed, the plan will
already be in cache.
Performance will be fine... assuming your queries are tuned properly.
David G.|||Thanks a lot
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBeLKXUgEHA.1764@.TK2MSFTNGP10.phx.gbl...
> John,
> I generally advocate building the SELECT dynamically within the stored
> procedure rather than within application code. This has two primary
> benefits:
> A) It keeps data access logic encapsulated
> B) It can help keep SQL injection attacks at bay (by using
sp_executesql
> with parameters instead of just using EXEC)
> You should carefully consider whether you actually need dynamic SQL at
all,
> and either way you should read this article for more information:
> http://www.sommarskog.se/dynamic_sql.html
>
> "John Smith" <js@.no.com> wrote in message
> news:eZmbhPUgEHA.596@.TK2MSFTNGP11.phx.gbl...
> > Hey folks,
> >
> > I've got a select statement that gets built dynamically in code.
> >
> > If what I pass to a stored procedure is nothing but a string that is a
> > select statement, and then have the stored procedure execute that
string,
> is
> > there still an advantage to using stored procedures (over just executing
> it
> > directly from code).
> >
> > The other possibility is to pass other parameters to the SP and let the
SP
> > build the Sql string. Either way though, the Select statement will be a
> > variable string that gets executed.
> >
> > Is there still a case for using stored procedures in this type of case?
> The
> > result set size will range from 1 record to 50,000 records.
> >
> > Thanks!
> >
> >
>|||Thanks a lot
"David G." <david_nospam@.nospam.com> wrote in message
news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
> John Smith wrote:
> > Hey folks,
> >
> > I've got a select statement that gets built dynamically in code.
> >
> > If what I pass to a stored procedure is nothing but a string that is a
> > select statement, and then have the stored procedure execute that
> > string, is there still an advantage to using stored procedures (over
> > just executing it directly from code).
> >
> > The other possibility is to pass other parameters to the SP and let
> > the SP build the Sql string. Either way though, the Select statement
> > will be a variable string that gets executed.
> >
> > Is there still a case for using stored procedures in this type of
> > case? The result set size will range from 1 record to 50,000 records.
> >
> > Thanks!
> Having the SP build the SQL is fine. Just use sp_executesql to execute
> the call, and if the query has parameters, then use the parameter
> feature of sp_executesql to define them and pass them to the SQL
> statement. That way, if similar statements get executed, the plan will
> already be in cache.
> Performance will be fine... assuming your queries are tuned properly.
>
> --
> David G.
>|||John Smith wrote:
> Thanks a lot
> "David G." <david_nospam@.nospam.com> wrote in message
> news:#ZYiMyUgEHA.2544@.TK2MSFTNGP10.phx.gbl...
>> John Smith wrote:
>> Hey folks,
>> I've got a select statement that gets built dynamically in code.
>> If what I pass to a stored procedure is nothing but a string that
>> is a select statement, and then have the stored procedure execute
>> that string, is there still an advantage to using stored procedures
>> (over just executing it directly from code).
>> The other possibility is to pass other parameters to the SP and let
>> the SP build the Sql string. Either way though, the Select
>> statement will be a variable string that gets executed.
>> Is there still a case for using stored procedures in this type of
>> case? The result set size will range from 1 record to 50,000
>> records.
>> Thanks!
>> Having the SP build the SQL is fine. Just use sp_executesql to
>> execute the call, and if the query has parameters, then use the
>> parameter feature of sp_executesql to define them and pass them to
>> the SQL statement. That way, if similar statements get executed, the
>> plan will already be in cache.
>> Performance will be fine... assuming your queries are tuned properly.
>>
>> --
>> David G.
And I should point out that if this procedure can be accessed by the
public or you are security conscious, you should validate the procedure
parameters to prevent any SQL injection problems.
--
David G.