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 and the ERRORFILE parameter

Can someone tell me if the ERRORFILE parameter is a valid (and
functioning) parameter for the BULK INSERT command when using SQL
Server 2000?
Finding documentation on this has been difficult, but it appears to be
a new parameter under the BULK INSERT command for 2005. If I look at
the list of parameters for this command under 2000, it doesn't appear
(see
http://msdn.microsoft.com/library/?...lref/ts_ba-b...
)
However if I run a BULK INSERT command under 2000 with the ERRORFILE
parameter, it doesn't give me a syntax error, it just appears to ignore
it.
Was the ERRORFILE option available under SQL 2000?
Thanks in advance,
Nick.Hi
ERRORFILE does not seem to produce and error in SQL 2000, but as you have
found it does seem to be ignored. The syntax in books online does not have
this option, therefore I think it is a new feature for SQL 2005. You could
use BCP if you really need this.
John
"Nick" wrote:

> Can someone tell me if the ERRORFILE parameter is a valid (and
> functioning) parameter for the BULK INSERT command when using SQL
> Server 2000?
> Finding documentation on this has been difficult, but it appears to be
> a new parameter under the BULK INSERT command for 2005. If I look at
> the list of parameters for this command under 2000, it doesn't appear
> (see
> http://msdn.microsoft.com/library/?...lref/ts_ba-b...
> )
> However if I run a BULK INSERT command under 2000 with the ERRORFILE
> parameter, it doesn't give me a syntax error, it just appears to ignore
> it.
> Was the ERRORFILE option available under SQL 2000?
> Thanks in advance,
> Nick.
>

BULK INSERT and the ERRORFILE parameter

Can someone tell me if the ERRORFILE parameter is a valid (and
functioning) parameter for the BULK INSERT command when using SQL
Server 2000?
Finding documentation on this has been difficult, but it appears to be
a new parameter under the BULK INSERT command for 2005. If I look at
the list of parameters for this command under 2000, it doesn't appear
(see
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_ba-b...
)
However if I run a BULK INSERT command under 2000 with the ERRORFILE
parameter, it doesn't give me a syntax error, it just appears to ignore
it.
Was the ERRORFILE option available under SQL 2000?
Thanks in advance,
Nick.Hi
ERRORFILE does not seem to produce and error in SQL 2000, but as you have
found it does seem to be ignored. The syntax in books online does not have
this option, therefore I think it is a new feature for SQL 2005. You could
use BCP if you really need this.
John
"Nick" wrote:
> Can someone tell me if the ERRORFILE parameter is a valid (and
> functioning) parameter for the BULK INSERT command when using SQL
> Server 2000?
> Finding documentation on this has been difficult, but it appears to be
> a new parameter under the BULK INSERT command for 2005. If I look at
> the list of parameters for this command under 2000, it doesn't appear
> (see
> http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_ba-b...
> )
> However if I run a BULK INSERT command under 2000 with the ERRORFILE
> parameter, it doesn't give me a syntax error, it just appears to ignore
> it.
> Was the ERRORFILE option available under SQL 2000?
> Thanks in advance,
> Nick.
>

bulk insert accepting param

I cant figure out how to set the lastrow parameter to a var. This var would be sent in from a c# program. Below is what I'm trying to do but I get a syntax error on the @.lastRow.

is there a way to do this

ALTER PROCEDURE [dbo].[p_temp]
--@.lastRow int
AS
BEGIN
--SELECT <@.Param1, sysname, @.p1>, <@.Param2, sysname, @.p2>
BULK INSERT PHX_WCISFile
FROM 'C:\100.TXT'
WITH
(
FIRSTROW = 2,

LASTROW = @.lastRow;

MAXERRORS = 20,
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)
END

I believe that BCP is unable to utilize parameters in that manner.

You could, however, create the entire BULK INSERT statement as a string and execute it using EXEC(). That would allow you to pass in parameters to substitute in the statement.

|||Arnie is correct. Unforunitely you will need to use dynamic SQL to do this.

Building text files

Ok, I have an sp that'll build an SAP feed based on parameter input (params control which type of file I want to create). I want to build 9 files in total.

I have a table set up with my parameters and output file names.

Question 1: Process from DTS
I have a DTS package which will build a file based on params and filename from table, pulled with a Dynamic Properties task. How can I iterate through my table of parmas to create the muliple files?

Question 2: Process from a stored proc
I have a stored proc, from which the interation through values is simple. How can I create and export to the text files from the stored proc? I think I may be having a mental fart on this one. I could create a text linked server dynamically, but I have not played with them much, How to I write to one (create table etc).

PS: The file data cannot include cilumn headings

TIA -
bpdI'd go with the sproc and bcp out

Just change the IN to OUT

SET @.cmd = 'bcp ' + @.db_name + '..ETRS_ASI_FED_TEMP in '
+ @.FilePathAndName + ' -t"\t" -c -S' + @.@.servername + ' -Uscrub -Pscrub'

SET @.Command_string = 'EXEC master..xp_cmdshell ''' + @.cmd + ''''

Select @.Command_String

Exec(@.Command_String)|||Given this problem, I would probably try a VB or PERL script to extract the data. I know how to call bcp from PERL, but VB is still a bit new to me. Fortunately, if the data set you are exporting is small (few thousand rows), you could get away with just using FileObject writes. Biggest problem I have had with bcp is remembering to check the error file for any problems. Again, easy for me in PERL, but VB...|||Thanks! bcp is what I was looking for. Glad I can avoid DTS all together.

-bpd

Building a date in SQL

Is there any sql method that takes 3 parameter like, day, month and year . And return me the date.

For example

function(10,3,2007) and it returns 10-03-2007

Thanks,

There is no such function as part of SQL2000 or SQL2005, however you could write your own function. You refer to the function returning 10-03-2007 as an example, however is it as string (char(10)) or as datetime? Also what datatype are the input parameters - integer?

|||

I have find the solution.

Thanks

Build Parameter from C#/Custom Code

Hello,

Is it possible to add a multivalued parameter to a report from C#? I already have a C# .dll which I reference in the report. If so, could someone give some hints on how to do it?

As I searched the net for an answer to this, I came across ReportExecutionService. It looks like it's what I want, but I cant find the dll to reference in order to used this class. Does anyone know?

If it is not possible to do this from C#, then can someone please give an example on how to do it from Custom Code?

Thanks
/Peter

Hi,

The book SQL Reporting Services Step by Step Microsoft gave me most of my help,

Although, I'm trying to do a similar thing & am having a similar problem

I have a stored procedure which takes an array of values from the parameter list, but I want to process the value when the user clicks view report using the calculated parameter value not the actual parameter itself.

Code Snippet

CREATE PROCEDURE [dbo].[AStoredProc]
@.ArrayofValues ntext
AS

DECLARE @.DocHandle int

EXEC sp_xml_preparedocument @.DocHandle OUTPUT, @.ArrayofValues

SELECT
ColumnA, ColumnB

FROM
TableA
WHERE
ColumnA IN ( SELECT x.AnItem FROM OPENXML (@.DocHandle,N'/Root/SelectedItems,1)
WITH ( AnItem nvarchar(31)
) as x )

If I feed the parameter directly with XML eg.

In my custom code : The intention is to pass the array values via the parameter of a multi selected list which I've populated from a seperate query

eg so I have values AValue1 to AValuen in the list and I select from human readable form the parameter.

In my custom c# assembly I've created a public static function...

Code Snippet

public static string ConvertMultiParametersToXml(string ArrayofItems)
{
string retval = "";
const string rootNode = "Root";
const string ItemList = "SelectedItems";
const string Items= "AnItem";
XmlDocument xmlDoc = new XmlDocument();
XmlElement rootElement = xmlDoc.CreateElement(rootNode);
xmlDoc.AppendChild(rootElement);
string[] TheItemArray = ArrayofItems.Split(',');
for (int i = 0; i < TheItemArray .Length; i++)
{
XmlElement procElement = xmlDoc.CreateElement(ItemList);
procElement.SetAttribute(Items, TheItemArray [i].ToString());
rootElement.AppendChild(procElement);
// ProcArray[i]
}
retval = xmlDoc.InnerXml.ToString();
return retval;
}

Then after adding a reference, and inserting into a table I call

Code Snippet

=.ConvertMultiParametersToXml(Fields!ColumnA.Value)

but I get a dom error because when the report is processesed it uses the parameter value rather than the calculated value when processing the dataset.

Is there a way to stop the redirect the parameter value via code on the initial processing?

Kind Regards

Rich