Bulk Insert SQL Server in C#

I am searching for an efficient way to do
bulk inserts via C#. It seems there are three
ways of doing that:

1.) Copy values to file and call BCP or BULK INSERT in TSQL
2.) Call the IRowFastLoad Interface of the SQL OLEDB Provider
3.) bcp interface of the ODBC driver

I think 2.) and 3.) are the only viable solutions and I wanted
to ask if anybody out there has already experiences
with these interfaces and the way of invocating them in C#.

Thank you for your help.

Bernhard
------------------
Solution Architect
Management Factory
Vienna/AUSTRIA/EUROPE
http://www.mf.ag
Bernhard.Walter@.mf.agYou may want to look at using SQL-DMO with C#. I've not coded in C# however I have used SQL-DMO with VB, VBScript (in an ASP page) and Perl.|||Thanks for the help.

Using SQL-DMO seems to be quite the same than inserting into a
file and then calling BCP, right ?
What I am looking for is a more efficient approach (memory to
SQL Server), because my routine has to work for 1 record to
millions of records in a fast way.

Bernhard

Bulk Insert Error

All,
I'm getting the following error when running a BULK INSERT via T-SQL:
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 3. Make sure the field terminator and row terminator are
specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
My table has the following structure:
CREATE TABLE [dbo].[TABLE1] (
[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [bigint] NULL ,
[TimeStamp] [datetime] NULL
) ON [PRIMARY]
GO
This is my T-SQL statement:
BULK INSERT [Database].[dbo].[TABLE1]
FROM 'C:\File.txt'
WITH
(
FORMATFILE='C:\format.fmt'
)
And this is my File Format:
8.0
3
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
Finally, my file is a fixed width format:
11 for the ID,
128 for the Email
33 for the TimeStamp
However, it keeps failing. Can anybody offer any insight to my issue?
Thanks,
Neal> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
The field length specification in the format file describes the field length
in the file, not the table column width. If you intention is to import only
the Email field and truncate, you can either add a dummy field to account
for the entire Email field length or increase the defined Timestamp field
length to 86:
8.0
4
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 53 "" 0 Email_Unused SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 33 "" 0 Timestamp SQL_Latin1_General_CP1_CI_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141158732.676526.308630@.t39g2000cwt.googlegroups.com...
> All,
> I'm getting the following error when running a BULK INSERT via T-SQL:
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 3. Make sure the field terminator and row terminator are
> specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
> returned 0x80004005: The provider did not give any information about
> the error.].
> The statement has been terminated.
> My table has the following structure:
> CREATE TABLE [dbo].[TABLE1] (
> [Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [bigint] NULL ,
> [TimeStamp] [datetime] NULL
> ) ON [PRIMARY]
> GO
> This is my T-SQL statement:
> BULK INSERT [Database].[dbo].[TABLE1]
> FROM 'C:\File.txt'
> WITH
> (
> FORMATFILE='C:\format.fmt'
> )
> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
> However, it keeps failing. Can anybody offer any insight to my issue?
> Thanks,
> Neal
>|||Dan,
I appreciate your help. You suggestion worked for me. Just curious,
why do I not need a record terminator "\r\n" on my last column?
Thanks again for your help.
Neal|||With a format file, the row terminator is specified after the last field of
the file. This is normally a carriage return/line feed ('\r\n') for text
files created via Windows applications.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141230410.344952.47950@.t39g2000cwt.googlegroups.com...
> Dan,
> I appreciate your help. You suggestion worked for me. Just curious,
> why do I not need a record terminator "\r\n" on my last column?
> Thanks again for your help.
> Neal
>

Bulk Insert Error

All,
I'm getting the following error when running a BULK INSERT via T-SQL:
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 3. Make sure the field terminator and row terminator are
specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
My table has the following structure:
CREATE TABLE [dbo].[TABLE1] (
[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [bigint] NULL ,
[TimeStamp] [datetime] NULL
) ON [PRIMARY]
GO
This is my T-SQL statement:
BULK INSERT [Database].[dbo].[TABLE1]
FROM 'C:\File.txt'
WITH
(
FORMATFILE='C:\format.fmt'
)
And this is my File Format:
8.0
3
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_A
S
Finally, my file is a fixed width format:
11 for the ID,
128 for the Email
33 for the TimeStamp
However, it keeps failing. Can anybody offer any insight to my issue?
Thanks,
Neal> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
The field length specification in the format file describes the field length
in the file, not the table column width. If you intention is to import only
the Email field and truncate, you can either add a dummy field to account
for the entire Email field length or increase the defined Timestamp field
length to 86:
8.0
4
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 53 "" 0 Email_Unused SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 33 "" 0 Timestamp SQL_Latin1_General_CP1_CI_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141158732.676526.308630@.t39g2000cwt.googlegroups.com...
> All,
> I'm getting the following error when running a BULK INSERT via T-SQL:
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 3. Make sure the field terminator and row terminator are
> specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
> returned 0x80004005: The provider did not give any information about
> the error.].
> The statement has been terminated.
> My table has the following structure:
> CREATE TABLE [dbo].[TABLE1] (
> [Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [bigint] NULL ,
> [TimeStamp] [datetime] NULL
> ) ON [PRIMARY]
> GO
> This is my T-SQL statement:
> BULK INSERT [Database].[dbo].[TABLE1]
> FROM 'C:\File.txt'
> WITH
> (
> FORMATFILE='C:\format.fmt'
> )
> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
> However, it keeps failing. Can anybody offer any insight to my issue?
> Thanks,
> Neal
>|||Dan,
I appreciate your help. You suggestion worked for me. Just curious,
why do I not need a record terminator "\r\n" on my last column?
Thanks again for your help.
Neal|||With a format file, the row terminator is specified after the last field of
the file. This is normally a carriage return/line feed ('\r\n') for text
files created via Windows applications.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141230410.344952.47950@.t39g2000cwt.googlegroups.com...
> Dan,
> I appreciate your help. You suggestion worked for me. Just curious,
> why do I not need a record terminator "\r\n" on my last column?
> Thanks again for your help.
> Neal
>

Bulk Insert Error

All,
I'm getting the following error when running a BULK INSERT via T-SQL:
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 3. Make sure the field terminator and row terminator are
specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
My table has the following structure:
CREATE TABLE [dbo].[TABLE1] (
[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [bigint] NULL ,
[TimeStamp] [datetime] NULL
) ON [PRIMARY]
GO
This is my T-SQL statement:
BULK INSERT [Database].[dbo].[TABLE1]
FROM 'C:\File.txt'
WITH
(
FORMATFILE='C:\format.fmt'
)
And this is my File Format:
8.0
3
1SQLCHAR011""0IDSQL_Latin1_General_CP1_CI_AS
2SQLCHAR075""1EmailSQL_Latin1_General_CP1_CI_AS
3SQLCHAR08"\r\n"0TimeStampSQL_Latin1_General_CP1_CI_AS
Finally, my file is a fixed width format:
11 for the ID,
128 for the Email
33 for the TimeStamp
However, it keeps failing. Can anybody offer any insight to my issue?
Thanks,
Neal
> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
The field length specification in the format file describes the field length
in the file, not the table column width. If you intention is to import only
the Email field and truncate, you can either add a dummy field to account
for the entire Email field length or increase the defined Timestamp field
length to 86:
8.0
4
1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 53 "" 0 Email_Unused SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 33 "" 0 Timestamp SQL_Latin1_General_CP1_CI_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141158732.676526.308630@.t39g2000cwt.googlegr oups.com...
> All,
> I'm getting the following error when running a BULK INSERT via T-SQL:
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 3. Make sure the field terminator and row terminator are
> specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give
> any information about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
> returned 0x80004005: The provider did not give any information about
> the error.].
> The statement has been terminated.
> My table has the following structure:
> CREATE TABLE [dbo].[TABLE1] (
> [Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ID] [bigint] NULL ,
> [TimeStamp] [datetime] NULL
> ) ON [PRIMARY]
> GO
> This is my T-SQL statement:
> BULK INSERT [Database].[dbo].[TABLE1]
> FROM 'C:\File.txt'
> WITH
> (
> FORMATFILE='C:\format.fmt'
> )
> And this is my File Format:
> 8.0
> 3
> 1 SQLCHAR 0 11 "" 0 ID SQL_Latin1_General_CP1_CI_AS
> 2 SQLCHAR 0 75 "" 1 Email SQL_Latin1_General_CP1_CI_AS
> 3 SQLCHAR 0 8 "\r\n" 0 TimeStamp SQL_Latin1_General_CP1_CI_AS
> Finally, my file is a fixed width format:
> 11 for the ID,
> 128 for the Email
> 33 for the TimeStamp
> However, it keeps failing. Can anybody offer any insight to my issue?
> Thanks,
> Neal
>
|||Dan,
I appreciate your help. You suggestion worked for me. Just curious,
why do I not need a record terminator "\r\n" on my last column?
Thanks again for your help.
Neal
|||With a format file, the row terminator is specified after the last field of
the file. This is normally a carriage return/line feed ('\r\n') for text
files created via Windows applications.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neal" <neal.m.shah@.gmail.com> wrote in message
news:1141230410.344952.47950@.t39g2000cwt.googlegro ups.com...
> Dan,
> I appreciate your help. You suggestion worked for me. Just curious,
> why do I not need a record terminator "\r\n" on my last column?
> Thanks again for your help.
> Neal
>

BULK INSERT and Application role

On a connection I use an application role via command:
sp_setapprole 'AppRole', 'xxxx'
When I use a command like this:
BULK INSERT TableX FROM 'C:\tmp\file.dat' WITH
( FORMATFILE='C:\tmp\file.fmt', ROWS_PER_BATCH=10, TABLOCK )
I got the error:
Msg 4834, Level 16, State 4, Line 4
You do not have permission to use the bulk load statement.
so, I would add bulkadmin permission to my applicatio role.
Is it possible ?The bulkadmin role is a server-level role. Application roles are at the
database level. If you are using SQL Server 2005, you can create a stored
proc where the BULK INSERT is done with elevated privileges and then grant
EXEC to the app role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Max" <Max@.discussions.microsoft.com> wrote in message
news:7167E83B-538F-4D72-BF0D-70F757FC289F@.microsoft.com...
On a connection I use an application role via command:
sp_setapprole 'AppRole', 'xxxx'
When I use a command like this:
BULK INSERT TableX FROM 'C:\tmp\file.dat' WITH
( FORMATFILE='C:\tmp\file.fmt', ROWS_PER_BATCH=10, TABLOCK )
I got the error:
Msg 4834, Level 16, State 4, Line 4
You do not have permission to use the bulk load statement.
so, I would add bulkadmin permission to my applicatio role.
Is it possible ?

Bulk copy size

If I have a 70GB data file that I want to export using bulk copy, what size
can I estimate the resulting exported file to be?
Message posted via http://www.sqlmonster.com
It depends somewhat on what mode you use to export in (native, char, wide
etc) but it will likely be close to the size of the actual data in the db
and not the size of the db itself. Fragmentation or how full your data
pages are can play a big part. Try a sample and see.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:20af1d8e71d34c7b97012ab8bd70ba2a@.SQLMonster.c om...
> If I have a 70GB data file that I want to export using bulk copy, what
> size
> can I estimate the resulting exported file to be?
> --
> Message posted via http://www.sqlmonster.com

Bulk copy size

If I have a 70GB data file that I want to export using bulk copy, what size
can I estimate the resulting exported file to be?
Message posted via http://www.droptable.comIt depends somewhat on what mode you use to export in (native, char, wide
etc) but it will likely be close to the size of the actual data in the db
and not the size of the db itself. Fragmentation or how full your data
pages are can play a big part. Try a sample and see.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:20af1d8e71d34c7b97012ab8bd70ba2a@.SQ
droptable.com...
> If I have a 70GB data file that I want to export using bulk copy, what
> size
> can I estimate the resulting exported file to be?
> --
> Message posted via http://www.droptable.com

Bulk copy size

If I have a 70GB data file that I want to export using bulk copy, what size
can I estimate the resulting exported file to be?
--
Message posted via http://www.sqlmonster.comIt depends somewhat on what mode you use to export in (native, char, wide
etc) but it will likely be close to the size of the actual data in the db
and not the size of the db itself. Fragmentation or how full your data
pages are can play a big part. Try a sample and see.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:20af1d8e71d34c7b97012ab8bd70ba2a@.SQLMonster.com...
> If I have a 70GB data file that I want to export using bulk copy, what
> size
> can I estimate the resulting exported file to be?
> --
> Message posted via http://www.sqlmonster.com

Building a Where Clause

I am not very experienced with stored procs and I'm attempting to write my first one. I am writing a search page via aspx and that page will call my proc and depending on the input parameters, the proc will return the search results. To do this I have built a where clause string but I don't know how to (if it's even possible) make this variable part of my query. Can anyone tell me a way to make the following work (input params left out to conserve space)?

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNTON;

SET ANSI_WARNINGSOFF

SET @.where=''

IF @.JobNoStart!=''SET @.where=+' AND LJOB BETWEEN @.JobNoStart AND @.JobNoEnd'

IF @.OrderDateStart!=''SET @.where=+' AND JOBDATE BETWEEN @.OrderDateStart AND @.OrderDateEnd'

IF @.DueDateStart!=''SET @.where=+' AND DUEDATE BETWEEN @.DueDateStart AND @.DueDateEnd'

IF @.ProofDateStart!=''SET @.where=+' AND PROOFDUE BETWEEN @.ProofDateStart AND @.ProofDateEnd'

IF @.CloseDateStart!=''SET @.where=+' AND CLOSEDATE BETWEEN @.CloseDateStart AND @.CloseDateEnd'

IF @.CogsDateStart!=''SET @.where=+' AND COGSDATE BETWEEN @.CogsDateStart AND @.CogsDateEnd'

IF @.ProductName!=''SET @.where=+' AND PRODUCT = @.ProductName'

IF @.CustomerNumber!=''SET @.where=+' AND FCUSTNO = @.CustomerNumber'

IF @.SalesPerson!=''SET @.where=+' AND FSALESPN = @.SalesPerson'

IF @.CSR!=''SET @.where=+' AND JOBPER = @.CSR'

IF @.Closed= 0SET @.where=+' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'

ELSEIF @.Closed= 1SET @.where=+' AND CLOSEDATE IS NULL'

ELSEIF @.Closed= 2SET @.where=+' AND CLOSEDATE IS NOT NULL'

IF @.Canceled= 0SET @.where=+' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'

ELSEIF @.Canceled= 1SET @.where=+' AND CANCDATE IS NOT NULL'

ELSEIF @.Canceled= 2SET @.where=+' AND CANCDATE IS NULL'

IF @.FinalShip= 0SET @.where=+' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'

ELSEIF @.FinalShip= 1SET @.where=+' AND FINALSHIP IS NOT NULL'

ELSEIF @.FinalShip= 2SET @.where=+' AND FINALSHIP IS NULL'

SELECT LJOB, DUEDATE, FCOMPANY, ID, QUANWHERE LJOBISNOTNULL @.where

END

To answer my own post, this is how you build a dynamic query in a stored proc:

ALTERPROCEDURE [dbo].[cg_JobSearch]

-- Add the parameters for the stored procedure here

@.JobNoStart varchar(10)='',

@.JobNoEnd varchar(10)= @.JobNoStart,

@.OrderDateStart varchar(10)='',

@.OrderDateEnd varchar(10)= @.OrderDateStart,

@.DueDateStart varchar(10)='',

@.DueDateEnd varchar(10)= @.DueDateStart,

@.ProofDateStart varchar(10)='',

@.ProofDateEnd varchar(10)= @.ProofDateStart,

@.CloseDateStart varchar(10)='',

@.CloseDateEnd varchar(10)= @.CloseDateStart,

@.CogsDateStart varchar(10)='',

@.CogsDateEnd varchar(10)= @.CogsDateStart,

@.ProductName varchar(200)='',

@.CustomerNumber varchar(12)='',

@.SalesPerson varchar(3)='',

@.CSR varchar(15)='',

@.Closedint= 0,

@.Canceledint= 0,

@.FinalShipint= 0,

--@.Invoiced int = 0,

@.where varchar(8000)='',

@.sql varchar(8000)=''

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNTON;

SET ANSI_WARNINGSOFF

SET @.where=''

IF @.JobNoStart!=''SET @.where= @.where+' AND LJOB BETWEEN '+ @.JobNoStart+' AND '+ @.JobNoEnd

IF @.OrderDateStart!=''SET @.where= @.where+' AND JOBDATE BETWEEN '+ @.OrderDateStart+' AND '+ @.OrderDateEnd

IF @.DueDateStart!=''SET @.where= @.where+' AND DUEDATE BETWEEN '+ @.DueDateStart+' AND '+ @.DueDateEnd

IF @.ProofDateStart!=''SET @.where= @.where+' AND PROOFDUE BETWEEN '+ @.ProofDateStart+' AND '+ @.ProofDateEnd

IF @.CloseDateStart!=''SET @.where= @.where+' AND CLOSEDATE BETWEEN '+ @.CloseDateStart+' AND '+ @.CloseDateEnd

IF @.CogsDateStart!=''SET @.where= @.where+' AND COGSDATE BETWEEN '+ @.CogsDateStart+' AND '+ @.CogsDateEnd

IF @.ProductName!=''SET @.where= @.where+' AND PRODUCT = '+ @.ProductName

IF @.CustomerNumber!=''SET @.where= @.where+' AND FCUSTNO = '+ @.CustomerNumber

IF @.SalesPerson!=''SET @.where= @.where+' AND FSALESPN = '+ @.SalesPerson

IF @.CSR!=''SET @.where= @.where+' AND JOBPER = '+ @.CSR

IF @.Closed= 0SET @.where= @.where+' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL'

ELSEIF @.Closed= 1SET @.where= @.where+' AND CLOSEDATE IS NULL'

ELSEIF @.Closed= 2SET @.where= @.where+' AND CLOSEDATE IS NOT NULL'

IF @.Canceled= 0SET @.where= @.where+' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL'

ELSEIF @.Canceled= 1SET @.where= @.where+' AND CANCDATE IS NOT NULL'

ELSEIF @.Canceled= 2SET @.where= @.where+' AND CANCDATE IS NULL'

IF @.FinalShip= 0SET @.where= @.where+' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL'

ELSEIF @.FinalShip= 1SET @.where= @.where+' AND FINALSHIP IS NOT NULL'

ELSEIF @.FinalShip= 2SET @.where= @.where+' AND FINALSHIP IS NULL'

SET @.sql='SELECT LJOB, DUEDATE, FCOMPANY, ID, QUAN FROM BBJTHEAD WHERE LJOB IS NOT NULL'

SET @.sql= @.sql+ @.where

PRINT @.sql

EXEC(@.sql)

END

|||

SELECT LJOB, DUEDATE, FCOMPANY, ID, QUANWHERE LJOBISNOTNULL
AND ((@.JobNoStart='') OR (LJOB BETWEEN @.JobNoStart AND @.JobNoEnd))
AND ...
AND ((@.Closed<>0) OR ( CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL))
AND ((@.Closed<>1) OR (CLOSEDATE IS NULL))
AND ((@.Closed<>2) OR (CLOSEDATE IS NOT NULL))
AND ...

Which if you see the pattern, I flip the condition of your IF for the first part, and use the where condition in each of your IFs as the second part. Like:
AND ((reversed IF condition) OR (Your where clause minus the AND))

The SQL in pink is also worthless, it will always be true. Also, the way you are concatenating your OR's and AND's will cause your where clause to not behave the way you want. AND has precedence over OR, where it looks like you want OR to have precedence over AND. As written, if Closed, FinalShip, or Cancelled is 0, you will return data that I suspect you didn't want returned.

If you use EXEC like the above poster suggested (after you fix the AND/OR problems I mentioned, and some errors the above poster made), you'll end up with a SP that can suffer SQL Injection. You are much better off either using the approach I mentioned above, or using the stored procedure that takes both a query string, and a parameter string (Which is a bit more difficult to set up, which is why unless performance is really bad, I use the above). I believe you are looking for the stored procedure sp_ExecuteSQL.

|||Thanks for the advice. It is true that with my current proc I'm susceptable to an injection attack. That would not be good.|||

This is how I ended up doing mine.

CREATE PROCEDURE [dbo].[pe_getAppraisals]

-- Add the parameters for the stored procedure here

@.PTypenvarChar(500),

@.ClientnvarChar(500),

@.PageSizeINT

AS

DECLARE

@.l_SelectnvarChar(4000),

@.l_FromnvarChar(4000),

@.l_SetWherebit,

@.l_PTypenvarChar(500),

@.l_ClientnvarChar(500)

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

--Initialize SetWhere to test if a parameter has Added the keyword WHERE

--Initialize the Where statement in case all parameters are null

SET @.l_SetWhere= 0

--Create WHERE portion of the SQL SELECT Statement

IF(@.PTypeISNOTNULL)AND(@.PType<>'')

BEGIN

SET @.l_PType=' WHERE o.PropertyTypeID='+ @.PType

SET @.l_SetWhere= 1

End

ELSESET @.PType=''

IF(@.ClientISNOTNULL)AND(@.Client<>'')

BEGIN

IF @.l_SetWhere= 0

BEGIN

SET @.l_Client=' WHERE o.ClientID='+ @.Client

SET @.l_SetWhere= 1

END

ELSESET @.l_Client=' AND o.ClientID='+ @.Client

END

ELSESET @.l_Client=''

--Build the SQL SELECT Statement

SET @.l_Select=

'o.OrderID, o.FileNumber, o.OrderDate, o.ClientID, o.ClientFileNumber, o.PropertyTypeID, o.EstimatedValue, o.PurchaseValue,

o.LoanOfficer, o.ReportFee, o.FeeBillInd, o.FeeCollectInd, o.CollectAmt, o.Borrower, o.StreetAddrA, o.StreetAddrB, o.City, o.State, o.Zip,

o.ContactName, o.PhoneA, o.PhoneB, o.ApptDate, o.ApptTime, o.AppraiserID, o.InspectionDate, o.DateMailed, o.TrackingInfo, o.ReviewedBy,

o.StatusID, o.Comments, o.SpecialNotes, o.EmailInd, o.MgmtName, o.MgmtContactName, o.MgmtAddress, o.MgmtPhone, o.MgmtFax,

o.MgmtFee, o.MgmtNotes, o.LoginName, on1.NotesDesc AS PreNotesDesc, on2.NotesDesc AS PostNotesDesc, os.StatusDesc,

ot.ReportDesc, ot.ReportFee AS ReportPrice, ot.ReportSeq, pc.PriceDesc, pt.PropertyTypeDesc, l.LoginName AS AppraiserName'

SET @.l_From=

'Orders AS o LEFT OUTER JOIN

OrderNotes AS on1 ON o.PreNotesID = on1.NotesID LEFT OUTER JOIN

OrderNotes AS on2 ON o.PostNotesID = on2.NotesID LEFT OUTER JOIN

OrderStatus AS os ON o.StatusID = os.StatusID LEFT OUTER JOIN

OrderTypes AS ot ON o.ReportID = ot.ReportID LEFT OUTER JOIN

PriceCodes AS pc ON ot.PriceID = pc.PriceID LEFT OUTER JOIN

PropertyTypes AS pt ON o.PropertyTypeID = pt.PropertyTypeID LEFT OUTER JOIN

Logins AS l ON o.AppraiserID = l.LoginID'

Execute('SELECT TOP('+ @.PageSize+') '+ @.l_Select+' FROM '+ @.l_From+ @.l_PType+ @.l_Client)

Building a report simply by passing a Recordset.

I just attented a Microsoft event where I ask Bernard Wong of Microsoft, if
it was possible to pass a recordset (and some header information) via code
to produce a simple report. He indicated of course and it is well
documented. Does anybody know how to perform this operation, or know where
the documentation on this type of process is?
Byron...The only way I know of how to do this is to read the RDL specification
(posted on Microsoft website). Parse the recordset and make decisions of
things like format, column size and heading. Create the RDL (which is an XML
file but that doesn't mean you don't have to understand the spec). Then you
would have to deploy it with a unique name and then have the client use the
unique name. If using the new controls you could instead use them in local
mode, pass the rdlc and the dataset to the control and have it render it.
The specification is well documented. There are some people who are doing
this. In my mind it is definitely non-trivial.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Byron Hopp" <bhopp@.matrixcomputer.com> wrote in message
news:uKm0uuS7FHA.2012@.TK2MSFTNGP14.phx.gbl...
>I just attented a Microsoft event where I ask Bernard Wong of Microsoft, if
>it was possible to pass a recordset (and some header information) via code
>to produce a simple report. He indicated of course and it is well
>documented. Does anybody know how to perform this operation, or know where
>the documentation on this type of process is?
> Byron...
>|||With RS 2005, the answer to your question is depends on how the report is to
be rendered. If you use the Report Viewer controls in local mode, you can
bind the dataset to the report. If you need to pass the dataset to a
server-based report, you need a custom data extension
(http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Byron Hopp" <bhopp@.matrixcomputer.com> wrote in message
news:uKm0uuS7FHA.2012@.TK2MSFTNGP14.phx.gbl...
>I just attented a Microsoft event where I ask Bernard Wong of Microsoft, if
>it was possible to pass a recordset (and some header information) via code
>to produce a simple report. He indicated of course and it is well
>documented. Does anybody know how to perform this operation, or know where
>the documentation on this type of process is?
> Byron...
>|||I've been trying to do that (RS2005) without much success. I have a rdl file
I have brought in as a rdlc file. I would like to set the datasource to
something in code. If you have any info to point me in the right direction I
would appreciate it. All the examples I see all deal with doing everything
from the designer.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:%23K%23xL8s7FHA.2600@.tk2msftngp13.phx.gbl...
> With RS 2005, the answer to your question is depends on how the report is
> to be rendered. If you use the Report Viewer controls in local mode, you
> can bind the dataset to the report. If you need to pass the dataset to a
> server-based report, you need a custom data extension
> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "Byron Hopp" <bhopp@.matrixcomputer.com> wrote in message
> news:uKm0uuS7FHA.2012@.TK2MSFTNGP14.phx.gbl...
>>I just attented a Microsoft event where I ask Bernard Wong of Microsoft,
>>if it was possible to pass a recordset (and some header information) via
>>code to produce a simple report. He indicated of course and it is well
>>documented. Does anybody know how to perform this operation, or know
>>where the documentation on this type of process is?
>> Byron...
>>
>|||Nevermind, I got it.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%2393%23RAx7FHA.2092@.TK2MSFTNGP12.phx.gbl...
> I've been trying to do that (RS2005) without much success. I have a rdl
> file I have brought in as a rdlc file. I would like to set the datasource
> to something in code. If you have any info to point me in the right
> direction I would appreciate it. All the examples I see all deal with
> doing everything from the designer.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> news:%23K%23xL8s7FHA.2600@.tk2msftngp13.phx.gbl...
>> With RS 2005, the answer to your question is depends on how the report is
>> to be rendered. If you use the Report Viewer controls in local mode, you
>> can bind the dataset to the report. If you need to pass the dataset to a
>> server-based report, you need a custom data extension
>> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "Byron Hopp" <bhopp@.matrixcomputer.com> wrote in message
>> news:uKm0uuS7FHA.2012@.TK2MSFTNGP14.phx.gbl...
>>I just attented a Microsoft event where I ask Bernard Wong of Microsoft,
>>if it was possible to pass a recordset (and some header information) via
>>code to produce a simple report. He indicated of course and it is well
>>documented. Does anybody know how to perform this operation, or know
>>where the documentation on this type of process is?
>> Byron...
>>
>>
>|||Bruce,
Would you mind sharing your success?
Thanks,
Byron...
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uvi2AQ%237FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Nevermind, I got it.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%2393%23RAx7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>> I've been trying to do that (RS2005) without much success. I have a rdl
>> file I have brought in as a rdlc file. I would like to set the datasource
>> to something in code. If you have any info to point me in the right
>> direction I would appreciate it. All the examples I see all deal with
>> doing everything from the designer.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
>> news:%23K%23xL8s7FHA.2600@.tk2msftngp13.phx.gbl...
>> With RS 2005, the answer to your question is depends on how the report
>> is to be rendered. If you use the Report Viewer controls in local mode,
>> you can bind the dataset to the report. If you need to pass the dataset
>> to a server-based report, you need a custom data extension
>> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "Byron Hopp" <bhopp@.matrixcomputer.com> wrote in message
>> news:uKm0uuS7FHA.2012@.TK2MSFTNGP14.phx.gbl...
>>I just attented a Microsoft event where I ask Bernard Wong of Microsoft,
>>if it was possible to pass a recordset (and some header information) via
>>code to produce a simple report. He indicated of course and it is well
>>documented. Does anybody know how to perform this operation, or know
>>where the documentation on this type of process is?
>> Byron...
>>
>>
>>
>|||You get a working demo by downloading the chapter 18 source code of my book
(http://www.prologika.com/Books/0976635305/code/).
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"Byron Hopp" <bhopp@.matrixcomputer.com> wrote in message
news:uUaPKE$8FHA.3416@.TK2MSFTNGP15.phx.gbl...
> Bruce,
> Would you mind sharing your success?
> Thanks,
> Byron...
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uvi2AQ%237FHA.2716@.TK2MSFTNGP11.phx.gbl...
>> Nevermind, I got it.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%2393%23RAx7FHA.2092@.TK2MSFTNGP12.phx.gbl...
>> I've been trying to do that (RS2005) without much success. I have a rdl
>> file I have brought in as a rdlc file. I would like to set the
>> datasource to something in code. If you have any info to point me in the
>> right direction I would appreciate it. All the examples I see all deal
>> with doing everything from the designer.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
>> news:%23K%23xL8s7FHA.2600@.tk2msftngp13.phx.gbl...
>> With RS 2005, the answer to your question is depends on how the report
>> is to be rendered. If you use the Report Viewer controls in local mode,
>> you can bind the dataset to the report. If you need to pass the dataset
>> to a server-based report, you need a custom data extension
>> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "Byron Hopp" <bhopp@.matrixcomputer.com> wrote in message
>> news:uKm0uuS7FHA.2012@.TK2MSFTNGP14.phx.gbl...
>>I just attented a Microsoft event where I ask Bernard Wong of
>>Microsoft, if it was possible to pass a recordset (and some header
>>information) via code to produce a simple report. He indicated of
>>course and it is well documented. Does anybody know how to perform
>>this operation, or know where the documentation on this type of process
>>is?
>> Byron...
>>
>>
>>
>>
>