I am defining the Output parameter from Sybase Stored Proc.
How can I define the OUT Parm in RS and display that parm result in Header?
Any help is appreciated.I don't think you can. Instead have your last statement be a select
statement.
BTW, did you solve the problem of what provider to use. Did you go with ODBC
or were you able to stick with OleDB.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sujay" <Sujay@.discussions.microsoft.com> wrote in message
news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> I am defining the Output parameter from Sybase Stored Proc.
> How can I define the OUT Parm in RS and display that parm result in
Header?
> Any help is appreciated.|||Bruce,
We are sticking with OLE-DB.
The problem using OLE-DB was , I cannot pass char or varchar datatype as a
i/p parameter to a stored proc.
I got the alternate solution to this problem:
I can define the report as command type of "Text" and call the proc in the
following way:
="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
chr(34)+parameters!dtParm.value+chr(34)
"Bruce L-C [MVP]" wrote:
> I don't think you can. Instead have your last statement be a select
> statement.
> BTW, did you solve the problem of what provider to use. Did you go with ODBC
> or were you able to stick with OleDB.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > I am defining the Output parameter from Sybase Stored Proc.
> > How can I define the OUT Parm in RS and display that parm result in
> Header?
> >
> > Any help is appreciated.
>
>|||Sujay,
I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
Where exactly do you use the syntax you mention below? The SQL pane in the
report designer does not appear to allow anything other than the string that
will be passed to the OLE DB provider (even quotes).
Thanks,
John
"Sujay" wrote:
> Bruce,
> We are sticking with OLE-DB.
> The problem using OLE-DB was , I cannot pass char or varchar datatype as a
> i/p parameter to a stored proc.
> I got the alternate solution to this problem:
> I can define the report as command type of "Text" and call the proc in the
> following way:
> ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> chr(34)+parameters!dtParm.value+chr(34)
>
> "Bruce L-C [MVP]" wrote:
> > I don't think you can. Instead have your last statement be a select
> > statement.
> >
> > BTW, did you solve the problem of what provider to use. Did you go with ODBC
> > or were you able to stick with OleDB.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > I am defining the Output parameter from Sybase Stored Proc.
> > > How can I define the OUT Parm in RS and display that parm result in
> > Header?
> > >
> > > Any help is appreciated.
> >
> >
> >|||You put this in the generic query designer. Your query can be an expression.
Personally I would use the & instead of a + sign. What he is doing is
enclosing any strings in single quote marks.
Also note that parameters are case sensitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:6D8F5670-454D-47E0-97A6-AAD3E695599A@.microsoft.com...
> Sujay,
> I am trying to pass a string parameter to ASE OLE DB Provider for Sybase.
> Where exactly do you use the syntax you mention below? The SQL pane in
the
> report designer does not appear to allow anything other than the string
that
> will be passed to the OLE DB provider (even quotes).
> Thanks,
> John
> "Sujay" wrote:
> > Bruce,
> >
> > We are sticking with OLE-DB.
> > The problem using OLE-DB was , I cannot pass char or varchar datatype as
a
> > i/p parameter to a stored proc.
> > I got the alternate solution to this problem:
> > I can define the report as command type of "Text" and call the proc in
the
> > following way:
> > ="Proc_Name "+chr(34)+parameters!strParm.value+chr(34) + "," +
> > chr(34)+parameters!dtParm.value+chr(34)
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't think you can. Instead have your last statement be a select
> > > statement.
> > >
> > > BTW, did you solve the problem of what provider to use. Did you go
with ODBC
> > > or were you able to stick with OleDB.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Sujay" <Sujay@.discussions.microsoft.com> wrote in message
> > > news:CEDAB7E2-590C-4B74-B937-8FDFA73433A8@.microsoft.com...
> > > > I am defining the Output parameter from Sybase Stored Proc.
> > > > How can I define the OUT Parm in RS and display that parm result in
> > > Header?
> > > >
> > > > Any help is appreciated.
> > >
> > >
> > >sql
Showing posts with label header. Show all posts
Showing posts with label header. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Passing Multiple values in Drill Through Report
I have a report called ReportA which has a group header that is summing
detail rows. In the textbox that I am using to some I have selected the
action property and set it to "Jump to" ReportB. ReportB has a parameter of
StudentID. Based on Summed values from the detail section of ReportA I would
like to pass the StudentID's to ReportB. Basically what I'm saying is I
would like for ReportA to pass ReportB a parameter that has multiple values.
Either this way or any way that based on the action property of a textbox I
could pass the multiple values to another report. Any help or ideas would be
appreciated.Should work similar to other multiple value parameter reports. Search this
newsgroup for "multi-value parameters" and "multiple parameters" for
postings from folks who have asked similar question.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"P" <P@.discussions.microsoft.com> wrote in message
news:BCD0DCB4-C644-491D-91FC-79349F89085E@.microsoft.com...
>I have a report called ReportA which has a group header that is summing
> detail rows. In the textbox that I am using to some I have selected the
> action property and set it to "Jump to" ReportB. ReportB has a parameter
> of
> StudentID. Based on Summed values from the detail section of ReportA I
> would
> like to pass the StudentID's to ReportB. Basically what I'm saying is I
> would like for ReportA to pass ReportB a parameter that has multiple
> values.
> Either this way or any way that based on the action property of a textbox
> I
> could pass the multiple values to another report. Any help or ideas would
> be
> appreciated.|||One problem is that I have a cell on a table that is the sum of other values.
I need to set the action property for this cell that is summing to be able
to pass as a parameter StudentID's of the related records that make up the
summed values. If I can get pass this I can accomplish what I need.
Thanks!
"John H. Miller" wrote:
> Should work similar to other multiple value parameter reports. Search this
> newsgroup for "multi-value parameters" and "multiple parameters" for
> postings from folks who have asked similar question.
> --
> -- "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> jhmiller@.online.microsoft.com
> "P" <P@.discussions.microsoft.com> wrote in message
> news:BCD0DCB4-C644-491D-91FC-79349F89085E@.microsoft.com...
> >I have a report called ReportA which has a group header that is summing
> > detail rows. In the textbox that I am using to some I have selected the
> > action property and set it to "Jump to" ReportB. ReportB has a parameter
> > of
> > StudentID. Based on Summed values from the detail section of ReportA I
> > would
> > like to pass the StudentID's to ReportB. Basically what I'm saying is I
> > would like for ReportA to pass ReportB a parameter that has multiple
> > values.
> > Either this way or any way that based on the action property of a textbox
> > I
> > could pass the multiple values to another report. Any help or ideas would
> > be
> > appreciated.
>
>
detail rows. In the textbox that I am using to some I have selected the
action property and set it to "Jump to" ReportB. ReportB has a parameter of
StudentID. Based on Summed values from the detail section of ReportA I would
like to pass the StudentID's to ReportB. Basically what I'm saying is I
would like for ReportA to pass ReportB a parameter that has multiple values.
Either this way or any way that based on the action property of a textbox I
could pass the multiple values to another report. Any help or ideas would be
appreciated.Should work similar to other multiple value parameter reports. Search this
newsgroup for "multi-value parameters" and "multiple parameters" for
postings from folks who have asked similar question.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"P" <P@.discussions.microsoft.com> wrote in message
news:BCD0DCB4-C644-491D-91FC-79349F89085E@.microsoft.com...
>I have a report called ReportA which has a group header that is summing
> detail rows. In the textbox that I am using to some I have selected the
> action property and set it to "Jump to" ReportB. ReportB has a parameter
> of
> StudentID. Based on Summed values from the detail section of ReportA I
> would
> like to pass the StudentID's to ReportB. Basically what I'm saying is I
> would like for ReportA to pass ReportB a parameter that has multiple
> values.
> Either this way or any way that based on the action property of a textbox
> I
> could pass the multiple values to another report. Any help or ideas would
> be
> appreciated.|||One problem is that I have a cell on a table that is the sum of other values.
I need to set the action property for this cell that is summing to be able
to pass as a parameter StudentID's of the related records that make up the
summed values. If I can get pass this I can accomplish what I need.
Thanks!
"John H. Miller" wrote:
> Should work similar to other multiple value parameter reports. Search this
> newsgroup for "multi-value parameters" and "multiple parameters" for
> postings from folks who have asked similar question.
> --
> -- "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> jhmiller@.online.microsoft.com
> "P" <P@.discussions.microsoft.com> wrote in message
> news:BCD0DCB4-C644-491D-91FC-79349F89085E@.microsoft.com...
> >I have a report called ReportA which has a group header that is summing
> > detail rows. In the textbox that I am using to some I have selected the
> > action property and set it to "Jump to" ReportB. ReportB has a parameter
> > of
> > StudentID. Based on Summed values from the detail section of ReportA I
> > would
> > like to pass the StudentID's to ReportB. Basically what I'm saying is I
> > would like for ReportA to pass ReportB a parameter that has multiple
> > values.
> > Either this way or any way that based on the action property of a textbox
> > I
> > could pass the multiple values to another report. Any help or ideas would
> > be
> > appreciated.
>
>
Tuesday, March 20, 2012
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.
Subscribe to:
Posts (Atom)