passing parameter from stored proc to crystal

Im using crystal 11.5.

Ive written a stored proc in my sybase DB which has 2 parameters, one for start date & one for end date. When I execute the proc on the DB I am prompted to enter values for my parameters.

Using an ODBC connection I am able to find my stored proc through crystal, however when I choose my proc, crystal is not prompting me to enter values for the parameters.

In the past Ive used crystal (versions 8 & 9) & when I select the stored proc, it always prompted me to enter values for the parameters.

What am I missing here? Any help would be greatly appreciated.check the sql statement in CR, also, are you using a Command ?,
if not, that may be the way you want to go. Lastly, you could just
add the parameter in CR and have CR prompt you for the date range.|||According to the crystal help, once you choose your stored proc from the list & add it to your report, crystal should prompt you to enter parameter values.

How do I get crystal to prompt me when I add the stored proc to the report?

Passing Object Variable as input parameter to an Execute SQL Task Query

I've encountered a new problem with an SSIS Pkg where I have a seq. of Execute SQL tasks. My question are:

1) In the First Execute SQL Task, I want to store a single row result of @.@.identity type into a User Variable User::LoadID of What type. ( I tried using DBNull Type or Object type which works, not with any other type, it but I can't proceed to step 2 )

2) Now I want to use this User::LoadID as input parameter of What type for the next task (I tried using Numeric, Long, DB_Numeric, Decimal, Double none of there work).

Please give me solutions for the above two..

@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx|||

Darren,

Thanks for your solution,

Still Execute SQL query task fails if I use Int32 type for the @.@.identity single row result Variable.

When I set it to dbnull it works however it's unable to use this Value as an input paraemter for the next Execute SQL task.

Moreover I want the solution for the step 2 as well|||

DarrenSQLIS wrote:

@.@.IDENTITY returns an integer, so your variable type should be Int32. This may help - http://www.sqlis.com/58.aspx

@.@.IDENTITY returns a numeric, not an integer. (http://msdn2.microsoft.com/en-us/library/aa933167(sql.80).aspx)

Just cast it to an integer and all will be well.

select cast(@.@.IDENTITY as int) as 'Ident'|||

Thanks Phil,

It seems that both of your solution will work here. Returns numeric so we can use int32 variable for assigning the result. Came to know that when we use int32 variable as an input parameter to another Execute SQL task, we declare it Long type.

There is one more issue i couldn't resolve. There is a type mismatch when I use to map String Variable as input Parameter to a Varchar type in Execute SQL Task. As such Execute SQL task Fails.

Under Parameter Mapping

User:: StrVar Varchar 0

However When I directly assign the String value in place of the parameter it works.

Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)

Is there any work around for this?

|||

Subhash wrote:

Under Parameter Mapping

User:: StrVar Varchar 0

However When I directly assign the String value in place of the parameter it works.

Select SrcRowCount AS CntLoadID From LoadDetails Where Source = ? ( Replacing this value '32, 2323, 2343,23434' works)

Is there any work around for this?

That works for me, using OLE DB connection though. What type of connection are you using?

|||

Hi Rafael,

I am using OLEDB connection.

When I create an expression: Left(sqlstatement)+@.[user::CntLoadID] + Right(sqlstatement), it works indeed. But it seems an alternate way.

Passing Global Variables from a Execute Package Task

I have a package (Package1) that is run from another package (Package2) via a Execute Package Task. I set a Global Variable called sErrorMessage in the in Package1 and would like to access that Global Variable in an ActiveX Script Task in Package2. How can I do this?Package 1:
Edit your Execute Package Task, and click on the "Outer Package Global Variables" tab. Select all the Global Variables you want available to Package 2. Save and close.

Package 2:
Edit your ActiveX Script Task and use the code below to access the variable:
DTSGlobalVariables("<global variable name>").Value|||Package1 doesnt have an Execute Package Task, Package2 does...

Package1:
ActiveX Script --> Connection1 -TransformData-> Connection2

Package2:
Exec Package1 -Success-> Send Success Email
'-Failure-> ActiveX (msgbox DTSGlobalVariables("sErrorMessage").Value) --> Send Failure Email|||Ooookay.. so substitute 2 for 1 and 1 for 2... done...|||Thats what I figured when you posted, but I tried that and got nothing.....I might have edited the reply since you looked at it...

When I run Package1 by itself I can see in the Package1 Properties that the Global Variable is being set properly from Package1.

The Execute Package Task in Package2 has the Outer Package Global Variable selected, but when I reference it with DTSGlobalVariables("sErrorMessage").Value in ActiveX Code it shows the variable as being empty.|||Just a update on this:

Seppuku was nice enough the help me via AIM. So far we have come to the conclusion that there is no builtin way for a child to pass data to the parent except maybe through file drops, or writing to a db table.|||Will attempt having the child write to a common table for communication...and will post the results here.

Passing complex parameters

Deepak-
"Failed to execute web request" can be due to several issues. I would suggest starting with a simple xml document on a virtual directory and working up from there. Things to check when working with a web service as a data source:
(1) Permissions (The data provider is probably using integrated security)
(2) Element Path in the XML query (this depends on the structure of the xml returned by the web service)
(3) Encoding (RS web services Base64 encode responses, you will need to specify the proper decoding in the ElementPath in the query)
(4) Set IgnoreNamespaces="true" in the query
Hope this helps.
Jon Heide
SQL Server Reporting Services
--Original Message--
From: Deepak@.discussions.microsoft.com
Posted At: Wednesday, October 05, 2005 2:06 PM
Posted To: microsoft.public.sqlserver.reportingsvcs
Conversation: Passing complex parameters
Subject: RE: Passing complex parameters
Hi Wayne,
I was at PASS last week as well, and saw those demos. This SQL Server 2005
Books Online entry discusses XML data sources for Reporting Services, but I
can't get the example at the end (which simply lists reports from the Report
Server) to work. The dataset query returns an error like "Failed to execute
web request for the specified URL". Maybe Brian Welcker or Bruce L-C can
comment/help?
- Deepak
http://msdn2.microsoft.com/en-us/library/ms228288
>>
SQL Server 2005 Books Online
Retrieving XML Data
XML documents are a supported data source type for reports that you build
and run in SQL Server 2005 Reporting Services. An XML data processing
extension is provided to retrieve and process the data.
...
Example: Retrieving XML Data from a Web service
To build the query, use the generic query designer. Use the following steps
to learn how to specify the connection and return XML data from a Web
service. In this example, the dataset returns items from the report server
database.
...
>>
"Wayne Snyder" wrote:
> I saw someone doing something similar at the PASS conference last week...
> I'll tell you what I remember and hope it helps... They were using a Web
> Service as a data source.
>
> They chose XML as the data provider, then in the connect string pointed to
> the Web service.. asmx
>
> Then in the query for the data source, they did an XML query, passing in the
> SOAP query, which was copied from the web services description...It worked
> for him, but I suspect it would take me a while to duplicate what he had
> done...
>
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
>
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
>
> "Brian Smith" wrote:
>
> > Is it possible to pass a .net DataSet to a Report, and have that used as
> > data within the report?
> > I have a dynamically generated list of items in a .net DataSet that I
> > need to pass to a top-level report which then calls subreports for each
> > item.
> > I'm not sure how you can process such a parameter in the report -
> > obviously its just an xml string, but how can it be re-rendered to data.
> > Alternatively I could pass a comma separated string, but the same
> > question arises. An example would be great (he said hopefully).
> >
> > Also, is there a limit to the size of a string parameter?
> >
> > brian smith
> >Jon,
Thanks for those tips. I've got a SharePoint Lists Web Service (using the
"GetListCollection" method without parameters) source working, but still
can't get results from other methods with parameters. Next I'm going to try
this sample "Report Manager" report from Brian Welcker's blog, which should
be similar to the SQL Server 2005 BOL XML sample:
http://home.ripway.com/2005-6/332074/ReportManager.rdl
"Jon Heide" wrote:
> Deepak-
> "Failed to execute web request" can be due to several issues. I would suggest starting with a simple xml document on a virtual directory and working up from there. Things to check when working with a web service as a data source:
> (1) Permissions (The data provider is probably using integrated security)
> (2) Element Path in the XML query (this depends on the structure of the xml returned by the web service)
> (3) Encoding (RS web services Base64 encode responses, you will need to specify the proper decoding in the ElementPath in the query)
> (4) Set IgnoreNamespaces="true" in the query
> Hope this helps.
> Jon Heide
> SQL Server Reporting Services
>
> --Original Message--
> From: Deepak@.discussions.microsoft.com
> Posted At: Wednesday, October 05, 2005 2:06 PM
> Posted To: microsoft.public.sqlserver.reportingsvcs
> Conversation: Passing complex parameters
> Subject: RE: Passing complex parameters
>
> Hi Wayne,
> I was at PASS last week as well, and saw those demos. This SQL Server 2005
> Books Online entry discusses XML data sources for Reporting Services, but I
> can't get the example at the end (which simply lists reports from the Report
> Server) to work. The dataset query returns an error like "Failed to execute
> web request for the specified URL". Maybe Brian Welcker or Bruce L-C can
> comment/help?
> - Deepak
>
> http://msdn2.microsoft.com/en-us/library/ms228288
> >>
> SQL Server 2005 Books Online
> Retrieving XML Data
> XML documents are a supported data source type for reports that you build
> and run in SQL Server 2005 Reporting Services. An XML data processing
> extension is provided to retrieve and process the data.
> ...
> Example: Retrieving XML Data from a Web service
> To build the query, use the generic query designer. Use the following steps
> to learn how to specify the connection and return XML data from a Web
> service. In this example, the dataset returns items from the report server
> database.
> ...
> >>
>
> "Wayne Snyder" wrote:
> > I saw someone doing something similar at the PASS conference last week...
> > I'll tell you what I remember and hope it helps... They were using a Web
> > Service as a data source.
> >
> > They chose XML as the data provider, then in the connect string pointed to
> > the Web service.. asmx
> >
> > Then in the query for the data source, they did an XML query, passing in the
> > SOAP query, which was copied from the web services description...It worked
> > for him, but I suspect it would take me a while to duplicate what he had
> > done...
> >
> >
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> >
> > I support the Professional Association for SQL Server ( PASS) and it''s
> > community of SQL Professionals.
> >
> >
> > "Brian Smith" wrote:
> >
> > > Is it possible to pass a .net DataSet to a Report, and have that used as
> > > data within the report?
> > > I have a dynamically generated list of items in a .net DataSet that I
> > > need to pass to a top-level report which then calls subreports for each
> > > item.
> > > I'm not sure how you can process such a parameter in the report -
> > > obviously its just an xml string, but how can it be re-rendered to data.
> > > Alternatively I could pass a comma separated string, but the same
> > > question arises. An example would be great (he said hopefully).
> > >
> > > Also, is there a limit to the size of a string parameter?
> > >
> > > brian smith
> > >
>

Passing Arguments from an Alert to a SQL Server Job

I have an alert that will execute a job. Is there a way to pass parameters
from an alert to a sql job?
Thanks,
Ken
You can not pass parameters to a job, but you can insert a number of
different tokens in the text of the jobstep. These tokens will be replaced
with items like the database name, error message, current time etc when the
job is executed by the alert. This will work for any jobstep, T-SQL, CmdExec
etc. You can find the syntax in the topic for sp_add_jobstep in Books
Online.
Jacco Schalkwijk
SQL Server MVP
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:CDF1CA50-8308-42EC-9F0B-7D5DC49F2307@.microsoft.com...
>I have an alert that will execute a job. Is there a way to pass parameters
> from an alert to a sql job?
> Thanks,
> Ken

Passing Arguments from an Alert to a SQL Server Job

I have an alert that will execute a job. Is there a way to pass parameters
from an alert to a sql job?
Thanks,
KenYou can not pass parameters to a job, but you can insert a number of
different tokens in the text of the jobstep. These tokens will be replaced
with items like the database name, error message, current time etc when the
job is executed by the alert. This will work for any jobstep, T-SQL, CmdExec
etc. You can find the syntax in the topic for sp_add_jobstep in Books
Online.
Jacco Schalkwijk
SQL Server MVP
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:CDF1CA50-8308-42EC-9F0B-7D5DC49F2307@.microsoft.com...
>I have an alert that will execute a job. Is there a way to pass parameters
> from an alert to a sql job?
> Thanks,
> Ken

Passing Arguments from an Alert to a SQL Server Job

I have an alert that will execute a job. Is there a way to pass parameters
from an alert to a sql job?
Thanks,
KenYou can not pass parameters to a job, but you can insert a number of
different tokens in the text of the jobstep. These tokens will be replaced
with items like the database name, error message, current time etc when the
job is executed by the alert. This will work for any jobstep, T-SQL, CmdExec
etc. You can find the syntax in the topic for sp_add_jobstep in Books
Online.
--
Jacco Schalkwijk
SQL Server MVP
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:CDF1CA50-8308-42EC-9F0B-7D5DC49F2307@.microsoft.com...
>I have an alert that will execute a job. Is there a way to pass parameters
> from an alert to a sql job?
> Thanks,
> Ken

Passing a variable to the from statement

Hi,

I have the following sql that I execute against a flat file. The flat file has a fixed length header and trailer but variable length data section. I execute this sql to get the header and trailer details the data section is put through a process in integration services:

SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

,substring(bulkcolumn,charindex('ICMST',bulkcolumn)+6,4)--<RecordCount, varchar(6),>

,getdate()

FROM OPENROWSET(BULK N'c:\filename.txt',single_clob) as doc

My problem is that I want to be able to pass a variable to the filename, but cannot find a way to do it. It doesn't seem to like a normal stored procedure parameter passed to it, i.e BULK N'@.param1',single_clob, i get

'Cannot bulk load. The file "@.param" does not exist.'

The reason for me getting the header details like this is that integration services doesn't seem to fit well when we have a combination of variable and fixed length records.

Any other suggestions as to how I could solve this would be greatly appreciated.

Paul

dynamic sql would be a TSQL solution, though I would imagine SSIS has a "file emulator task" as well...

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = 'SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

,substring(bulkcolumn,charindex('ICMST',bulkcolumn)+6,4)--<RecordCount, varchar(6),>

,getdate()

FROM OPENROWSET(BULK N' + ''' + @.File + ''' + ',single_clob) as doc'

exec sp_executesql @.SQL

|||

I couldn't get this to work. It still doesn't recognise the @.file parameter passed in to the select

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = '

declare @.file nvarchar(500)

set @.file = ''c:\interface files\CostCentreImportFile.txt''

select @.file

SELECT

*

FROM OPENROWSET(BULK N'+''' + @.file + ''' + ',single_clob) as doc'

exec sp_executesql @.SQL

It returns....

Msg 4860, Level 16, State 1, Line 5

Cannot bulk load. The file " + @.file + " does not exist.

Even if I were to get that sorted I fear I'd still have a problem as it doesn't seem to recognise the 'bulkcolumn' keyword

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = '

declare @.file nvarchar(500)

set @.file = ''c:\interface files\CostCentreImportFile.txt''

SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

FROM OPENROWSET(BULK N''c:\interface files\CostCentreImportFile.txt'',single_clob) as doc'

--

exec sp_executesql @.SQL

Msg 207, Level 16, State 1, Line 5

Invalid column name 'bulkcolumn'.

The File data source in SSIS, I felt, wasn't adequate as I have two fixed length headers(different format), a variable length data section and then a fixed length trailer. I'd have to define four different file formats and ignore the other record formats on processing. Although I'm slowly talking myself into that approach

|||

THIS WORKS...

DECLARE

@.SQL NVARCHAR(500),

@.file nvarchar(500)

SET @.file = 'c:\interface files\CostCentreImportFile.txt'

SET @.SQL = 'SELECT * FROM OPENROWSET(BULK N''' + @.file + ''',single_clob) AS doc'

PRINT @.SQL

EXEC sp_executesql @.SQL

|||

Paul,

Did this work for you? If not please provide more info or mark answer.

thanks,

derek

|||

Apologies for the delay, I must admit I don't monitor my older posts.

Yes, thank you very much, that works.

|||

This not working for me.

I need something like this select but I would like to get content of this select into variable.

Could someone help me?

Code Snippet

SELECT @.strXML = x

FROM OPENROWSET(BULK @.filename, SINGLE_CLOB) AS result(x)

|||Hello Culprit, the problem with your statement is that it sends a variable (@.filename) as a parameter to the OPENROWSET function. This is not possible, because OPENROWSET does not allow its parameters to be variables (only literals).

My suggestion to you is that you create another variable, let's say [@.sql varchar(255)]. This variable will store the text of the whole OPENROWSET statement. Having this variable all you have to do is execute the newly created statement. Here's the code:

DECLARE @.sql varchar(255)
SELECT @.sql = 'OPENROWSET(BULK ' + @.filename + ', SINGLE_CLOB)'

SELECT @.strXML = x
FROM EXEC(@.sql) AS result(x)

I hope this helps. Let me know how it goes. Ciao.

Passing a variable to the from statement

Hi,

I have the following sql that I execute against a flat file. The flat file has a fixed length header and trailer but variable length data section. I execute this sql to get the header and trailer details the data section is put through a process in integration services:

SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

,substring(bulkcolumn,charindex('ICMST',bulkcolumn)+6,4)--<RecordCount, varchar(6),>

,getdate()

FROM OPENROWSET(BULK N'c:\filename.txt',single_clob) as doc

My problem is that I want to be able to pass a variable to the filename, but cannot find a way to do it. It doesn't seem to like a normal stored procedure parameter passed to it, i.e BULK N'@.param1',single_clob, i get

'Cannot bulk load. The file "@.param" does not exist.'

The reason for me getting the header details like this is that integration services doesn't seem to fit well when we have a combination of variable and fixed length records.

Any other suggestions as to how I could solve this would be greatly appreciated.

Paul

dynamic sql would be a TSQL solution, though I would imagine SSIS has a "file emulator task" as well...

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = 'SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

,substring(bulkcolumn,charindex('ICMST',bulkcolumn)+6,4)--<RecordCount, varchar(6),>

,getdate()

FROM OPENROWSET(BULK N' + ''' + @.File + ''' + ',single_clob) as doc'

exec sp_executesql @.SQL

|||

I couldn't get this to work. It still doesn't recognise the @.file parameter passed in to the select

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = '

declare @.file nvarchar(500)

set @.file = ''c:\interface files\CostCentreImportFile.txt''

select @.file

SELECT

*

FROM OPENROWSET(BULK N'+''' + @.file + ''' + ',single_clob) as doc'

exec sp_executesql @.SQL

It returns....

Msg 4860, Level 16, State 1, Line 5

Cannot bulk load. The file " + @.file + " does not exist.

Even if I were to get that sorted I fear I'd still have a problem as it doesn't seem to recognise the 'bulkcolumn' keyword

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = '

declare @.file nvarchar(500)

set @.file = ''c:\interface files\CostCentreImportFile.txt''

SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

FROM OPENROWSET(BULK N''c:\interface files\CostCentreImportFile.txt'',single_clob) as doc'

--

exec sp_executesql @.SQL

Msg 207, Level 16, State 1, Line 5

Invalid column name 'bulkcolumn'.

The File data source in SSIS, I felt, wasn't adequate as I have two fixed length headers(different format), a variable length data section and then a fixed length trailer. I'd have to define four different file formats and ignore the other record formats on processing. Although I'm slowly talking myself into that approach

|||

THIS WORKS...

DECLARE

@.SQL NVARCHAR(500),

@.file nvarchar(500)

SET @.file = 'c:\interface files\CostCentreImportFile.txt'

SET @.SQL = 'SELECT * FROM OPENROWSET(BULK N''' + @.file + ''',single_clob) AS doc'

PRINT @.SQL

EXEC sp_executesql @.SQL

|||

Paul,

Did this work for you? If not please provide more info or mark answer.

thanks,

derek

|||

Apologies for the delay, I must admit I don't monitor my older posts.

Yes, thank you very much, that works.

|||

This not working for me.

I need something like this select but I would like to get content of this select into variable.

Could someone help me?

Code Snippet

SELECT @.strXML = x

FROM OPENROWSET(BULK @.filename, SINGLE_CLOB) AS result(x)

|||Hello Culprit, the problem with your statement is that it sends a variable (@.filename) as a parameter to the OPENROWSET function. This is not possible, because OPENROWSET does not allow its parameters to be variables (only literals).

My suggestion to you is that you create another variable, let's say [@.sql varchar(255)]. This variable will store the text of the whole OPENROWSET statement. Having this variable all you have to do is execute the newly created statement. Here's the code:

DECLARE @.sql varchar(255)
SELECT @.sql = 'OPENROWSET(BULK ' + @.filename + ', SINGLE_CLOB)'

SELECT @.strXML = x
FROM EXEC(@.sql) AS result(x)

I hope this helps. Let me know how it goes. Ciao.

Passing a variable to the from statement

Hi,

I have the following sql that I execute against a flat file. The flat file has a fixed length header and trailer but variable length data section. I execute this sql to get the header and trailer details the data section is put through a process in integration services:

SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

,substring(bulkcolumn,charindex('ICMST',bulkcolumn)+6,4)--<RecordCount, varchar(6),>

,getdate()

FROM OPENROWSET(BULK N'c:\filename.txt',single_clob) as doc

My problem is that I want to be able to pass a variable to the filename, but cannot find a way to do it. It doesn't seem to like a normal stored procedure parameter passed to it, i.e BULK N'@.param1',single_clob, i get

'Cannot bulk load. The file "@.param" does not exist.'

The reason for me getting the header details like this is that integration services doesn't seem to fit well when we have a combination of variable and fixed length records.

Any other suggestions as to how I could solve this would be greatly appreciated.

Paul

dynamic sql would be a TSQL solution, though I would imagine SSIS has a "file emulator task" as well...

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = 'SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

,substring(bulkcolumn,charindex('ICMST',bulkcolumn)+6,4)--<RecordCount, varchar(6),>

,getdate()

FROM OPENROWSET(BULK N' + ''' + @.File + ''' + ',single_clob) as doc'

exec sp_executesql @.SQL

|||

I couldn't get this to work. It still doesn't recognise the @.file parameter passed in to the select

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = '

declare @.file nvarchar(500)

set @.file = ''c:\interface files\CostCentreImportFile.txt''

select @.file

SELECT

*

FROM OPENROWSET(BULK N'+''' + @.file + ''' + ',single_clob) as doc'

exec sp_executesql @.SQL

It returns....

Msg 4860, Level 16, State 1, Line 5

Cannot bulk load. The file " + @.file + " does not exist.

Even if I were to get that sorted I fear I'd still have a problem as it doesn't seem to recognise the 'bulkcolumn' keyword

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = '

declare @.file nvarchar(500)

set @.file = ''c:\interface files\CostCentreImportFile.txt''

SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

FROM OPENROWSET(BULK N''c:\interface files\CostCentreImportFile.txt'',single_clob) as doc'

--

exec sp_executesql @.SQL

Msg 207, Level 16, State 1, Line 5

Invalid column name 'bulkcolumn'.

The File data source in SSIS, I felt, wasn't adequate as I have two fixed length headers(different format), a variable length data section and then a fixed length trailer. I'd have to define four different file formats and ignore the other record formats on processing. Although I'm slowly talking myself into that approach

|||

THIS WORKS...

DECLARE

@.SQL NVARCHAR(500),

@.file nvarchar(500)

SET @.file = 'c:\interface files\CostCentreImportFile.txt'

SET @.SQL = 'SELECT * FROM OPENROWSET(BULK N''' + @.file + ''',single_clob) AS doc'

PRINT @.SQL

EXEC sp_executesql @.SQL

|||

Paul,

Did this work for you? If not please provide more info or mark answer.

thanks,

derek

|||

Apologies for the delay, I must admit I don't monitor my older posts.

Yes, thank you very much, that works.

|||

This not working for me.

I need something like this select but I would like to get content of this select into variable.

Could someone help me?

Code Snippet

SELECT @.strXML = x

FROM OPENROWSET(BULK @.filename, SINGLE_CLOB) AS result(x)

|||Hello Culprit, the problem with your statement is that it sends a variable (@.filename) as a parameter to the OPENROWSET function. This is not possible, because OPENROWSET does not allow its parameters to be variables (only literals).

My suggestion to you is that you create another variable, let's say [@.sql varchar(255)]. This variable will store the text of the whole OPENROWSET statement. Having this variable all you have to do is execute the newly created statement. Here's the code:

DECLARE @.sql varchar(255)
SELECT @.sql = 'OPENROWSET(BULK ' + @.filename + ', SINGLE_CLOB)'

SELECT @.strXML = x
FROM EXEC(@.sql) AS result(x)

I hope this helps. Let me know how it goes. Ciao.

Passing a variable to the from statement

Hi,

I have the following sql that I execute against a flat file. The flat file has a fixed length header and trailer but variable length data section. I execute this sql to get the header and trailer details the data section is put through a process in integration services:

SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

,substring(bulkcolumn,charindex('ICMST',bulkcolumn)+6,4)--<RecordCount, varchar(6),>

,getdate()

FROM OPENROWSET(BULK N'c:\filename.txt',single_clob) as doc

My problem is that I want to be able to pass a variable to the filename, but cannot find a way to do it. It doesn't seem to like a normal stored procedure parameter passed to it, i.e BULK N'@.param1',single_clob, i get

'Cannot bulk load. The file "@.param" does not exist.'

The reason for me getting the header details like this is that integration services doesn't seem to fit well when we have a combination of variable and fixed length records.

Any other suggestions as to how I could solve this would be greatly appreciated.

Paul

dynamic sql would be a TSQL solution, though I would imagine SSIS has a "file emulator task" as well...

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = 'SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

,substring(bulkcolumn,charindex('ICMST',bulkcolumn)+6,4)--<RecordCount, varchar(6),>

,getdate()

FROM OPENROWSET(BULK N' + ''' + @.File + ''' + ',single_clob) as doc'

exec sp_executesql @.SQL

|||

I couldn't get this to work. It still doesn't recognise the @.file parameter passed in to the select

DECLARE @.SQL NVARCHAR(500)

SET @.SQL ='

declare @.file nvarchar(500)

set @.file = ''c:\interface files\CostCentreImportFile.txt''

select @.file

SELECT

*

FROM OPENROWSET(BULK N'+''' + @.file + '''+',single_clob) as doc'

execsp_executesql @.SQL

It returns....

Msg 4860, Level 16, State 1, Line 5

Cannot bulk load. The file " + @.file + " does not exist.

Even if I were to get that sorted I fear I'd still have a problem as it doesn't seem to recognise the 'bulkcolumn' keyword

DECLARE @.SQL NVARCHAR(500)

SET @.SQL ='

declare @.file nvarchar(500)

set @.file = ''c:\interface files\CostCentreImportFile.txt''

SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

FROM OPENROWSET(BULK N''c:\interface files\CostCentreImportFile.txt'',single_clob) as doc'

--

execsp_executesql @.SQL

Msg 207, Level 16, State 1, Line 5

Invalid column name 'bulkcolumn'.

The File data source in SSIS, I felt, wasn't adequate as I have two fixed length headers(different format), a variable length data section and then a fixed length trailer. I'd have to define four different file formats and ignore the other record formats on processing. Although I'm slowly talking myself into that approach

|||

THIS WORKS...

DECLARE

@.SQL NVARCHAR(500),

@.file nvarchar(500)

SET @.file ='c:\interface files\CostCentreImportFile.txt'

SET @.SQL ='SELECT * FROM OPENROWSET(BULK N'''+ @.file +''',single_clob) AS doc'

PRINT @.SQL

EXECsp_executesql @.SQL

|||

Paul,

Did this work for you? If not please provide more info or mark answer.

thanks,

derek

|||

Apologies for the delay, I must admit I don't monitor my older posts.

Yes, thank you very much, that works.

|||

This not working for me.

I need something like this select but I would like to get content of this select into variable.

Could someone help me?

Code Snippet

SELECT @.strXML = x

FROMOPENROWSET(BULK@.filename,SINGLE_CLOB)AS result(x)

|||Hello Culprit, the problem with your statement is that it sends a variable (@.filename) as a parameter to the OPENROWSET function. This is not possible, because OPENROWSET does not allow its parameters to be variables (only literals).

My suggestion to you is that you create another variable, let's say [@.sql varchar(255)]. This variable will store the text of the whole OPENROWSET statement. Having this variable all you have to do is execute the newly created statement. Here's the code:

DECLARE @.sql varchar(255)
SELECT @.sql ='OPENROWSET(BULK ' +@.filename +', SINGLE_CLOB)'

SELECT @.strXML= x
FROM EXEC(@.sql) AS result(x)

I hope this helps. Let me know how it goes. Ciao.

Passing a variable to the from statement

Hi,

I have the following sql that I execute against a flat file. The flat file has a fixed length header and trailer but variable length data section. I execute this sql to get the header and trailer details the data section is put through a process in integration services:

SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

,substring(bulkcolumn,charindex('ICMST',bulkcolumn)+6,4)--<RecordCount, varchar(6),>

,getdate()

FROM OPENROWSET(BULK N'c:\filename.txt',single_clob) as doc

My problem is that I want to be able to pass a variable to the filename, but cannot find a way to do it. It doesn't seem to like a normal stored procedure parameter passed to it, i.e BULK N'@.param1',single_clob, i get

'Cannot bulk load. The file "@.param" does not exist.'

The reason for me getting the header details like this is that integration services doesn't seem to fit well when we have a combination of variable and fixed length records.

Any other suggestions as to how I could solve this would be greatly appreciated.

Paul

dynamic sql would be a TSQL solution, though I would imagine SSIS has a "file emulator task" as well...

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = 'SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

,substring(bulkcolumn,charindex('ICMST',bulkcolumn)+6,4)--<RecordCount, varchar(6),>

,getdate()

FROM OPENROWSET(BULK N' + ''' + @.File + ''' + ',single_clob) as doc'

exec sp_executesql @.SQL

|||

I couldn't get this to work. It still doesn't recognise the @.file parameter passed in to the select

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = '

declare @.file nvarchar(500)

set @.file = ''c:\interface files\CostCentreImportFile.txt''

select @.file

SELECT

*

FROM OPENROWSET(BULK N'+''' + @.file + ''' + ',single_clob) as doc'

exec sp_executesql @.SQL

It returns....

Msg 4860, Level 16, State 1, Line 5

Cannot bulk load. The file " + @.file + " does not exist.

Even if I were to get that sorted I fear I'd still have a problem as it doesn't seem to recognise the 'bulkcolumn' keyword

DECLARE @.SQL NVARCHAR(500)

SET @.SQL = '

declare @.file nvarchar(500)

set @.file = ''c:\interface files\CostCentreImportFile.txt''

SELECT

substring(bulkcolumn,1,5)--<HeaderIdentifier, char(5),>

,substring(bulkcolumn,6,10)--<SenderIdentifier, char(10),>

,substring(bulkcolumn,16,10)--<RecipientIdentifier, char(10),>

,substring(bulkcolumn,26,30)--<FileType, char(30),>

,substring(bulkcolumn,56,8)--<CreationDate, char(8),>

,substring(bulkcolumn,64,6)--<CreationTime, char(6),>

,substring(bulkcolumn,70,8) as SeqNo--<SequenceNumber, int,>

,substring(bulkcolumn,82,1)--<FeedType, char(1),>

FROM OPENROWSET(BULK N''c:\interface files\CostCentreImportFile.txt'',single_clob) as doc'

--

exec sp_executesql @.SQL

Msg 207, Level 16, State 1, Line 5

Invalid column name 'bulkcolumn'.

The File data source in SSIS, I felt, wasn't adequate as I have two fixed length headers(different format), a variable length data section and then a fixed length trailer. I'd have to define four different file formats and ignore the other record formats on processing. Although I'm slowly talking myself into that approach

|||

THIS WORKS...

DECLARE

@.SQL NVARCHAR(500),

@.file nvarchar(500)

SET @.file = 'c:\interface files\CostCentreImportFile.txt'

SET @.SQL = 'SELECT * FROM OPENROWSET(BULK N''' + @.file + ''',single_clob) AS doc'

PRINT @.SQL

EXEC sp_executesql @.SQL

|||

Paul,

Did this work for you? If not please provide more info or mark answer.

thanks,

derek

|||

Apologies for the delay, I must admit I don't monitor my older posts.

Yes, thank you very much, that works.

|||

This not working for me.

I need something like this select but I would like to get content of this select into variable.

Could someone help me?

Code Snippet

SELECT @.strXML = x

FROM OPENROWSET(BULK @.filename, SINGLE_CLOB) AS result(x)

|||Hello Culprit, the problem with your statement is that it sends a variable (@.filename) as a parameter to the OPENROWSET function. This is not possible, because OPENROWSET does not allow its parameters to be variables (only literals).

My suggestion to you is that you create another variable, let's say [@.sql varchar(255)]. This variable will store the text of the whole OPENROWSET statement. Having this variable all you have to do is execute the newly created statement. Here's the code:

DECLARE @.sql varchar(255)
SELECT @.sql = 'OPENROWSET(BULK ' + @.filename + ', SINGLE_CLOB)'

SELECT @.strXML = x
FROM EXEC(@.sql) AS result(x)

I hope this helps. Let me know how it goes. Ciao.