Passing multi-select params to a stored procedure

i've already read the MS help file saying this can't be done, but there are
lots of people out there smarter than MS. Has anyone come up with a way to
take a multiple select paramter and pass it into a stored procedure yet? I
noticed there is a built in function string.join which concatonates the
parameters you've chosen, so I'm curious why you could'nt just pass that
string into an "IN" clause. Anyway, let me know if you've got a work around
for this, I'd appreciate it.
MichaelHere is the solution that I came up with. What I did was created a function
that would parse a string based on a delimiter and return the in clause. For
example lets say you were going to pass the string "NY""CA"NC" where the "
symbol is my delimiter into a stored procedure.
Create proc ReturnData
(
@.multivalue varchar(100)
)
as
Decalre @.in varchar(100), @.sqlcmd varchar(500)
set @.in = dbo.RSMultiValue(@.multivalue,'"')
set @.sqlcmd = 'select * from table where state ' + @.in
exec(@.sqlcmd)
The trick is to configure your parameter list correctlly in reporting
services. For example lets say you had a static list of the parameters, what
you want to do is set the value of each parameter value 1 = "NY" parameter
value 2 = "CA" etc...
The string that will be passed = "NY""CA" basically you are just creating
your own list and parsing the data with a function.
I hope this helps
"Michael C" wrote:
> i've already read the MS help file saying this can't be done, but there are
> lots of people out there smarter than MS. Has anyone come up with a way to
> take a multiple select paramter and pass it into a stored procedure yet? I
> noticed there is a built in function string.join which concatonates the
> parameters you've chosen, so I'm curious why you could'nt just pass that
> string into an "IN" clause. Anyway, let me know if you've got a work around
> for this, I'd appreciate it.
> Michael|||You are missing the point about passing multi-select parameters to a stored
procedure. You can easily do this. What you can't do is in the stored
procedure do this:
select * from some table where somefield in (@.MyParam).
The reason has nothing to do with RS. It has to do with stored procedures.
Stored procedures do not allow this because it is dangerous. It leads you
open to security attacks. Again, absolutely nothing to do with RS.
Now, as the other poster suggested you can create your sql on the fly. The
other possibilty is to use a UDF that takes a comma separated list and turns
it into a table variable which you can join with. If you are interested in
the function I can post it for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:97EA5DB7-F6C9-4402-88F3-84315DE094E8@.microsoft.com...
> i've already read the MS help file saying this can't be done, but there
> are
> lots of people out there smarter than MS. Has anyone come up with a way
> to
> take a multiple select paramter and pass it into a stored procedure yet?
> I
> noticed there is a built in function string.join which concatonates the
> parameters you've chosen, so I'm curious why you could'nt just pass that
> string into an "IN" clause. Anyway, let me know if you've got a work
> around
> for this, I'd appreciate it.
> Michael|||Using a user defined function isn't the only way to achive this with SQL
Server.
The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
performance.
Andrew|||Excellent article. It turns out that I got my code from Erland.
Here is the function I use that I got from him: What you can do is to have a
string parameter that is pass ed as a multivalue parameter and then change
the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
"Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> Using a user defined function isn't the only way to achive this with SQL
> Server.
> The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
> performance.
> Andrew
>|||Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
going to take me a bit to get my head around this, but let me see if I
understand this:
Basically your passing the entire list of values as a string
(string.join,",") into the function, then creating a temp table in the
function which adds a row for each parameter, and joining my sql statement
inside my stored procedure to this newly created temp table to select the
required rows?
Again, i'm new to this so thanks for your patience and help!
Michael
"Bruce L-C [MVP]" wrote:
> Excellent article. It turns out that I got my code from Erland.
> Here is the function I use that I got from him: What you can do is to have a
> string parameter that is pass ed as a multivalue parameter and then change
> the string into a table.
> This technique was told to me by SQL Server MVP, Erland Sommarskog
> For example I have done this
> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> So note this is NOT an issue with RS, it is strictly a stored procedure
> issue.
> Here is the function:
> CREATE FUNCTION charlist_to_table
> (@.list ntext,
> @.delimiter nchar(1) = N',')
> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> str varchar(4000),
> nstr nvarchar(2000)) AS
> BEGIN
> DECLARE @.pos int,
> @.textpos int,
> @.chunklen smallint,
> @.tmpstr nvarchar(4000),
> @.leftover nvarchar(4000),
> @.tmpval nvarchar(4000)
> SET @.textpos = 1
> SET @.leftover = ''
> WHILE @.textpos <= datalength(@.list) / 2
> BEGIN
> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> SET @.textpos = @.textpos + @.chunklen
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> WHILE @.pos > 0
> BEGIN
> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> SET @.pos = charindex(@.delimiter, @.tmpstr)
> END
> SET @.leftover = @.tmpstr
> END
> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> ltrim(rtrim(@.leftover)))
> RETURN
> END
> GO
>
> "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
> news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> > Using a user defined function isn't the only way to achive this with SQL
> > Server.
> >
> > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
> > performance.
> >
> > Andrew
> >
> >
>
>|||In my example below @.STO was a multi-select parameter. RS automatically is
sending the selection as a comma separated string. You don't need to do
anything to it. Then I pass that to the function I show below. That function
returns a table variable that you can then join to.
Note that you can test this from query analyzer by passing a comma separated
string to your stored procedure.
exec mystoredprocedure 'blah,bleh,etc'
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:B3D41925-5A5A-4827-8786-FF2F50906B88@.microsoft.com...
> Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
> going to take me a bit to get my head around this, but let me see if I
> understand this:
> Basically your passing the entire list of values as a string
> (string.join,",") into the function, then creating a temp table in the
> function which adds a row for each parameter, and joining my sql statement
> inside my stored procedure to this newly created temp table to select the
> required rows?
> Again, i'm new to this so thanks for your patience and help!
> Michael
>
> "Bruce L-C [MVP]" wrote:
>> Excellent article. It turns out that I got my code from Erland.
>> Here is the function I use that I got from him: What you can do is to
>> have a
>> string parameter that is pass ed as a multivalue parameter and then
>> change
>> the string into a table.
>> This technique was told to me by SQL Server MVP, Erland Sommarskog
>> For example I have done this
>> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
>> So note this is NOT an issue with RS, it is strictly a stored procedure
>> issue.
>> Here is the function:
>> CREATE FUNCTION charlist_to_table
>> (@.list ntext,
>> @.delimiter nchar(1) = N',')
>> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> str varchar(4000),
>> nstr nvarchar(2000)) AS
>> BEGIN
>> DECLARE @.pos int,
>> @.textpos int,
>> @.chunklen smallint,
>> @.tmpstr nvarchar(4000),
>> @.leftover nvarchar(4000),
>> @.tmpval nvarchar(4000)
>> SET @.textpos = 1
>> SET @.leftover = ''
>> WHILE @.textpos <= datalength(@.list) / 2
>> BEGIN
>> SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
>> SET @.textpos = @.textpos + @.chunklen
>> SET @.pos = charindex(@.delimiter, @.tmpstr)
>> WHILE @.pos > 0
>> BEGIN
>> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> SET @.pos = charindex(@.delimiter, @.tmpstr)
>> END
>> SET @.leftover = @.tmpstr
>> END
>> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> ltrim(rtrim(@.leftover)))
>> RETURN
>> END
>> GO
>>
>> "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
>> news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
>> > Using a user defined function isn't the only way to achive this with
>> > SQL
>> > Server.
>> >
>> > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
>> > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity
>> > for
>> > performance.
>> >
>> > Andrew
>> >
>> >
>>|||So question... Here is my code. Where would I insert this? the @.Mill, @.Port
can be mulitples (the @.Trip is always a single parameter)
ALTER PROCEDURE [dbo].[LumberLineUp]
@.Mill varchar(100) = '0',
@.Trip varchar(100) = '0',
@.Port varchar(100) = '0'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- INITIAL TABLES
CREATE TABLE [dbo].[#LumberLineUp](
[Vessel] [nvarchar](50) NULL,
[LoadPort][nvarchar](50) NULL,
[Trip][nvarchar](50) NULL,
[OrderID][nvarchar](20) NOT NULL,
[Mill][nvarchar](50) NULL,
[Mark][nvarchar](20) NULL,
[Product][nvarchar](160) NULL,
[Parent][nvarchar](160) NULL,
[FBM][real]NULL,
[Pkgs][real]NULL,
[Stowage][nvarchar](5) NULL,
[Grade][nvarchar](10) NULL,
[Length][nvarchar](10)NULL,
[LengthDesc][nvarchar](10) NULL,
[Port][nvarchar](50)NULL,
[LoadDate][nvarchar](50)NULL,
[ETADate][nvarchar](50)NULL
)
CREATE TABLE [dbo].[#LengthTally](
[OrderID] [varchar](20) NULL,
[Mark] [varchar](20) NULL,
[Tally] [varchar](120) NULL,
[Prod] [varchar](160)NULL,
[OrderPrev] [varchar](20) NULL,
[MarkPrev] [varchar](20) NULL,
[ProductPrev] [varchar](160)NULL,
[Length] [nvarchar](10) NULL,
[LengthPrev] [nvarchar](10) NULL,
[Count] [varchar](10) NULL,
[Test][varchar](10) NULL
)
-- Data for LumberLineUp Table
INSERT INTO dbo.#LumberLineUp
SELECT Vessel.Description, LoadPorts.Description AS LoadPort,
VesselTrips.ID AS Trip, order_header.order_id AS OrderID,
location.location_id AS Mill,
order_detail.Mark, product_master.descrip AS Product,
product_parent.descrip AS parent,
CASE order_detail.qty_unit WHEN 'm3' THEN
order_detail.qty * product_master.m3_per_lin WHEN 'FBM' THEN order_detail.qty
END AS FBM,
order_detail.alt_qty AS Pkgs, order_detail.Stowage,
product_master.grade, length_master.sort_fact AS Length,
length_master.descrip AS Legnth,
port.port_id AS Port, VesselTrips.LoadingDate,
tripdestinations.ETADate
FROM Vessel AS Vessel INNER JOIN
VesselVoyages AS VesselVoyages ON
VesselVoyages.Vessel_ID = Vessel.ID INNER JOIN
VesselTrips AS VesselTrips ON
VesselTrips.VesselVoyage_ID = VesselVoyages.ID INNER JOIN
order_header AS order_header ON
order_header.VesselTrip_ID = VesselTrips.ID INNER JOIN
location AS location ON location.location_id =order_header.location INNER JOIN
order_detail AS order_detail ON order_detail.order_id
= order_header.order_id INNER JOIN
product_master AS product_master ON
product_master.product_id = order_detail.product_id INNER JOIN
product_master AS product_parent ON
product_master.product_part = product_parent.product_id INNER JOIN
length_master AS length_master ON
product_master.length_part = length_master.len_id INNER JOIN
TripDestinations AS tripdestinations ON
tripdestinations.Trip_ID = order_header.VesselTrip_ID AND
tripdestinations.Port_ID =order_header.Destination_Port INNER JOIN
port AS port ON tripdestinations.Port_ID =port.port_id INNER JOIN
LoadPorts ON VesselTrips.LoadPort = LoadPorts.ID
WHERE (port.port_id IN (@.Port)) AND (location.location_id IN (@.Mill))
AND (VesselTrips.ID IN (@.Trip)) OR
(location.location_id IN (@.Mill)) AND (VesselTrips.ID
IN (@.Trip)) AND (@.Port = '0')
ORDER BY OrderID, order_detail.Mark, Product, Length
"Michael C" wrote:
> Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
> going to take me a bit to get my head around this, but let me see if I
> understand this:
> Basically your passing the entire list of values as a string
> (string.join,",") into the function, then creating a temp table in the
> function which adds a row for each parameter, and joining my sql statement
> inside my stored procedure to this newly created temp table to select the
> required rows?
> Again, i'm new to this so thanks for your patience and help!
> Michael
>
> "Bruce L-C [MVP]" wrote:
> > Excellent article. It turns out that I got my code from Erland.
> >
> > Here is the function I use that I got from him: What you can do is to have a
> > string parameter that is pass ed as a multivalue parameter and then change
> > the string into a table.
> > This technique was told to me by SQL Server MVP, Erland Sommarskog
> >
> > For example I have done this
> >
> > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> >
> > So note this is NOT an issue with RS, it is strictly a stored procedure
> > issue.
> >
> > Here is the function:
> >
> > CREATE FUNCTION charlist_to_table
> >
> > (@.list ntext,
> >
> > @.delimiter nchar(1) = N',')
> >
> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> >
> > str varchar(4000),
> >
> > nstr nvarchar(2000)) AS
> >
> > BEGIN
> >
> > DECLARE @.pos int,
> >
> > @.textpos int,
> >
> > @.chunklen smallint,
> >
> > @.tmpstr nvarchar(4000),
> >
> > @.leftover nvarchar(4000),
> >
> > @.tmpval nvarchar(4000)
> >
> > SET @.textpos = 1
> >
> > SET @.leftover = ''
> >
> > WHILE @.textpos <= datalength(@.list) / 2
> >
> > BEGIN
> >
> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
> >
> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> >
> > SET @.textpos = @.textpos + @.chunklen
> >
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >
> > WHILE @.pos > 0
> >
> > BEGIN
> >
> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> >
> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> >
> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> >
> > SET @.pos = charindex(@.delimiter, @.tmpstr)
> >
> > END
> >
> > SET @.leftover = @.tmpstr
> >
> > END
> >
> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> >
> > ltrim(rtrim(@.leftover)))
> >
> > RETURN
> >
> > END
> >
> > GO
> >
> >
> >
> > "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
> > news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> > > Using a user defined function isn't the only way to achive this with SQL
> > > Server.
> > >
> > > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> > > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity for
> > > performance.
> > >
> > > Andrew
> > >
> > >
> >
> >
> >|||Add another inner join with @.port
inner join charlist_to_table(@.port,Default)f on port.port_id = f.str
remove (port.port_id IN (@.Port))
Follow the same pattern as above with your other multi-value parameter
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
news:48471539-1EA0-489C-B3F6-85240B1FE04E@.microsoft.com...
> So question... Here is my code. Where would I insert this? the @.Mill,
> @.Port
> can be mulitples (the @.Trip is always a single parameter)
> ALTER PROCEDURE [dbo].[LumberLineUp]
> @.Mill varchar(100) = '0',
> @.Trip varchar(100) = '0',
> @.Port varchar(100) = '0'
> AS
> BEGIN
> -- SET NOCOUNT ON added to prevent extra result sets from
> -- interfering with SELECT statements.
> SET NOCOUNT ON;
> -- INITIAL TABLES
> CREATE TABLE [dbo].[#LumberLineUp](
> [Vessel] [nvarchar](50) NULL,
> [LoadPort][nvarchar](50) NULL,
> [Trip][nvarchar](50) NULL,
> [OrderID][nvarchar](20) NOT NULL,
> [Mill][nvarchar](50) NULL,
> [Mark][nvarchar](20) NULL,
> [Product][nvarchar](160) NULL,
> [Parent][nvarchar](160) NULL,
> [FBM][real]NULL,
> [Pkgs][real]NULL,
> [Stowage][nvarchar](5) NULL,
> [Grade][nvarchar](10) NULL,
> [Length][nvarchar](10)NULL,
> [LengthDesc][nvarchar](10) NULL,
> [Port][nvarchar](50)NULL,
> [LoadDate][nvarchar](50)NULL,
> [ETADate][nvarchar](50)NULL
> )
> CREATE TABLE [dbo].[#LengthTally](
> [OrderID] [varchar](20) NULL,
> [Mark] [varchar](20) NULL,
> [Tally] [varchar](120) NULL,
> [Prod] [varchar](160)NULL,
> [OrderPrev] [varchar](20) NULL,
> [MarkPrev] [varchar](20) NULL,
> [ProductPrev] [varchar](160)NULL,
> [Length] [nvarchar](10) NULL,
> [LengthPrev] [nvarchar](10) NULL,
> [Count] [varchar](10) NULL,
> [Test][varchar](10) NULL
> )
> -- Data for LumberLineUp Table
> INSERT INTO dbo.#LumberLineUp
> SELECT Vessel.Description, LoadPorts.Description AS LoadPort,
> VesselTrips.ID AS Trip, order_header.order_id AS OrderID,
> location.location_id AS Mill,
> order_detail.Mark, product_master.descrip AS Product,
> product_parent.descrip AS parent,
> CASE order_detail.qty_unit WHEN 'm3' THEN
> order_detail.qty * product_master.m3_per_lin WHEN 'FBM' THEN
> order_detail.qty
> END AS FBM,
> order_detail.alt_qty AS Pkgs, order_detail.Stowage,
> product_master.grade, length_master.sort_fact AS Length,
> length_master.descrip AS Legnth,
> port.port_id AS Port, VesselTrips.LoadingDate,
> tripdestinations.ETADate
> FROM Vessel AS Vessel INNER JOIN
> VesselVoyages AS VesselVoyages ON
> VesselVoyages.Vessel_ID = Vessel.ID INNER JOIN
> VesselTrips AS VesselTrips ON
> VesselTrips.VesselVoyage_ID = VesselVoyages.ID INNER JOIN
> order_header AS order_header ON
> order_header.VesselTrip_ID = VesselTrips.ID INNER JOIN
> location AS location ON location.location_id => order_header.location INNER JOIN
> order_detail AS order_detail ON order_detail.order_id
> = order_header.order_id INNER JOIN
> product_master AS product_master ON
> product_master.product_id = order_detail.product_id INNER JOIN
> product_master AS product_parent ON
> product_master.product_part = product_parent.product_id INNER JOIN
> length_master AS length_master ON
> product_master.length_part = length_master.len_id INNER JOIN
> TripDestinations AS tripdestinations ON
> tripdestinations.Trip_ID = order_header.VesselTrip_ID AND
> tripdestinations.Port_ID => order_header.Destination_Port INNER JOIN
> port AS port ON tripdestinations.Port_ID => port.port_id INNER JOIN
> LoadPorts ON VesselTrips.LoadPort = LoadPorts.ID
> WHERE (port.port_id IN (@.Port)) AND (location.location_id IN (@.Mill))
> AND (VesselTrips.ID IN (@.Trip)) OR
> (location.location_id IN (@.Mill)) AND (VesselTrips.ID
> IN (@.Trip)) AND (@.Port = '0')
> ORDER BY OrderID, order_detail.Mark, Product, Length
>
> "Michael C" wrote:
>> Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
>> going to take me a bit to get my head around this, but let me see if I
>> understand this:
>> Basically your passing the entire list of values as a string
>> (string.join,",") into the function, then creating a temp table in the
>> function which adds a row for each parameter, and joining my sql
>> statement
>> inside my stored procedure to this newly created temp table to select the
>> required rows?
>> Again, i'm new to this so thanks for your patience and help!
>> Michael
>>
>> "Bruce L-C [MVP]" wrote:
>> > Excellent article. It turns out that I got my code from Erland.
>> >
>> > Here is the function I use that I got from him: What you can do is to
>> > have a
>> > string parameter that is pass ed as a multivalue parameter and then
>> > change
>> > the string into a table.
>> > This technique was told to me by SQL Server MVP, Erland Sommarskog
>> >
>> > For example I have done this
>> >
>> > inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
>> >
>> > So note this is NOT an issue with RS, it is strictly a stored procedure
>> > issue.
>> >
>> > Here is the function:
>> >
>> > CREATE FUNCTION charlist_to_table
>> >
>> > (@.list ntext,
>> >
>> > @.delimiter nchar(1) = N',')
>> >
>> > RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
>> >
>> > str varchar(4000),
>> >
>> > nstr nvarchar(2000)) AS
>> >
>> > BEGIN
>> >
>> > DECLARE @.pos int,
>> >
>> > @.textpos int,
>> >
>> > @.chunklen smallint,
>> >
>> > @.tmpstr nvarchar(4000),
>> >
>> > @.leftover nvarchar(4000),
>> >
>> > @.tmpval nvarchar(4000)
>> >
>> > SET @.textpos = 1
>> >
>> > SET @.leftover = ''
>> >
>> > WHILE @.textpos <= datalength(@.list) / 2
>> >
>> > BEGIN
>> >
>> > SET @.chunklen = 4000 - datalength(@.leftover) / 2
>> >
>> > SET @.tmpstr = @.leftover + substring(@.list, @.textpos,
>> > @.chunklen)
>> >
>> > SET @.textpos = @.textpos + @.chunklen
>> >
>> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> >
>> > WHILE @.pos > 0
>> >
>> > BEGIN
>> >
>> > SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
>> >
>> > INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
>> >
>> > SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
>> >
>> > SET @.pos = charindex(@.delimiter, @.tmpstr)
>> >
>> > END
>> >
>> > SET @.leftover = @.tmpstr
>> >
>> > END
>> >
>> > INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
>> >
>> > ltrim(rtrim(@.leftover)))
>> >
>> > RETURN
>> >
>> > END
>> >
>> > GO
>> >
>> >
>> >
>> > "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
>> > news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
>> > > Using a user defined function isn't the only way to achive this with
>> > > SQL
>> > > Server.
>> > >
>> > > The article http://www.sommarskog.se/arrays-in-sql-2005.html by
>> > > Erland
>> > > Sommarskog (SQL Server MVP) has a few alternatives trading
>> > > simplicity for
>> > > performance.
>> > >
>> > > Andrew
>> > >
>> > >
>> >
>> >
>> >|||Hello,
This works perfectly for me. I was wondering if there is a way to
incorporate the parameter "All" to select the entire list?
Thanks,
Deb
"Bruce L-C [MVP]" wrote:
> In my example below @.STO was a multi-select parameter. RS automatically is
> sending the selection as a comma separated string. You don't need to do
> anything to it. Then I pass that to the function I show below. That function
> returns a table variable that you can then join to.
> Note that you can test this from query analyzer by passing a comma separated
> string to your stored procedure.
> exec mystoredprocedure 'blah,bleh,etc'
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Michael C" <MichaelC@.discussions.microsoft.com> wrote in message
> news:B3D41925-5A5A-4827-8786-FF2F50906B88@.microsoft.com...
> > Thanks All! I'm new to using SP's so i'm sorry i missed the point. Its
> > going to take me a bit to get my head around this, but let me see if I
> > understand this:
> >
> > Basically your passing the entire list of values as a string
> > (string.join,",") into the function, then creating a temp table in the
> > function which adds a row for each parameter, and joining my sql statement
> > inside my stored procedure to this newly created temp table to select the
> > required rows?
> >
> > Again, i'm new to this so thanks for your patience and help!
> >
> > Michael
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Excellent article. It turns out that I got my code from Erland.
> >>
> >> Here is the function I use that I got from him: What you can do is to
> >> have a
> >> string parameter that is pass ed as a multivalue parameter and then
> >> change
> >> the string into a table.
> >> This technique was told to me by SQL Server MVP, Erland Sommarskog
> >>
> >> For example I have done this
> >>
> >> inner join charlist_to_table(@.STO,Default)f on b.sto = f.str
> >>
> >> So note this is NOT an issue with RS, it is strictly a stored procedure
> >> issue.
> >>
> >> Here is the function:
> >>
> >> CREATE FUNCTION charlist_to_table
> >>
> >> (@.list ntext,
> >>
> >> @.delimiter nchar(1) = N',')
> >>
> >> RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> >>
> >> str varchar(4000),
> >>
> >> nstr nvarchar(2000)) AS
> >>
> >> BEGIN
> >>
> >> DECLARE @.pos int,
> >>
> >> @.textpos int,
> >>
> >> @.chunklen smallint,
> >>
> >> @.tmpstr nvarchar(4000),
> >>
> >> @.leftover nvarchar(4000),
> >>
> >> @.tmpval nvarchar(4000)
> >>
> >> SET @.textpos = 1
> >>
> >> SET @.leftover = ''
> >>
> >> WHILE @.textpos <= datalength(@.list) / 2
> >>
> >> BEGIN
> >>
> >> SET @.chunklen = 4000 - datalength(@.leftover) / 2
> >>
> >> SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
> >>
> >> SET @.textpos = @.textpos + @.chunklen
> >>
> >> SET @.pos = charindex(@.delimiter, @.tmpstr)
> >>
> >> WHILE @.pos > 0
> >>
> >> BEGIN
> >>
> >> SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
> >>
> >> INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
> >>
> >> SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
> >>
> >> SET @.pos = charindex(@.delimiter, @.tmpstr)
> >>
> >> END
> >>
> >> SET @.leftover = @.tmpstr
> >>
> >> END
> >>
> >> INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
> >>
> >> ltrim(rtrim(@.leftover)))
> >>
> >> RETURN
> >>
> >> END
> >>
> >> GO
> >>
> >>
> >>
> >> "Duke (AN247)" <Duke@.newsgroup.nospam> wrote in message
> >> news:4917A7F5-2FC4-4031-B00D-498A1B593CF2@.microsoft.com...
> >> > Using a user defined function isn't the only way to achive this with
> >> > SQL
> >> > Server.
> >> >
> >> > The article http://www.sommarskog.se/arrays-in-sql-2005.html by Erland
> >> > Sommarskog (SQL Server MVP) has a few alternatives trading simplicity
> >> > for
> >> > performance.
> >> >
> >> > Andrew
> >> >
> >> >
> >>
> >>
> >>
>
>sql

Passing contents of text file into variable?

Is there a way to get the contents of a text into a variable in PL/SQL?Hello,

if you mean you want to read a file into a pl/sql var the use the
SYS.UTL_FILE to load th content.

Hope that helps ?

Best regards

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

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.

Passing a SQL Query / MDX Query to RDL

Has anybody passed a SQL Query or an MDX Query as a source to an RDL file?
This is what I am trying to do:
I have a Report whose format is exactly the same but I need to use it
for creating different reports.
For Example) The Report prints Employee name and address and I want to use
the same RDL to print Customer Name and Address.
I am redefining the query so, that column names are same
Ex) EMP_NAME as NAME and EMP_ADDRESS as ADDRESS
ThanksOn Jul 13, 11:36 am, jvn <sha...@.online.nospam> wrote:
> Has anybody passed a SQL Query or an MDX Query as a source to an RDL file?
> This is what I am trying to do:
> I have a Report whose format is exactly the same but I need to use it
> for creating different reports.
> For Example) The Report prints Employee name and address and I want to use
> the same RDL to print Customer Name and Address.
> I am redefining the query so, that column names are same
> Ex) EMP_NAME as NAME and EMP_ADDRESS as ADDRESS
> Thanks
You should be able to do this if you are dynamically creating the RDL
file in a custom application. You will want to change the query found
in this location: <DataSets><DataSet><Query><CommandText>...</
CommandText></Query></DataSet></DataSets>
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I'm relatively new to Reporting Services. I'm working with SSRS 2000,
perhaps you are working with SSRS 2005.
I'm reading the book 'Hitchikers Guide to SQL Server 2000 Reporting
Services'.
The suggested approach in this book is to add a report parameter (e.g.
ReportSubject) with the values 'employees' and 'customers'.
Create a new dataset, select command type 'Text'. Manually add the
field names referring tot database field names NAME and ADRESS.
Enter the following expression:
= IIF(Parameters!ReportSubject.Value = 'employees', "SELECT EMP_NAME
as NAME, EMP_ADDRESS as ADDRESS FROM ...", "SELECT CUST_NAME as
NAME, CUST_ADDRESS as ADDRESS FROM ...")
This may become rather complex when you also want to use the report
for other purposes (nested IFF statements). But in that case you could
create a custom code function in VB so you can you CASE-statements
etc. This is not as complex as you might think.
Regards,
Willy

Passing a file path to a SP

I'm trying to write a SP that
accept in input a parameter with the name
of a file (with complete path)
but I noticed some problems...

It's right this way? Thanks!

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[BI]
@.FileToImport nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @.SQL nvarchar(200)

SET @.SQL = "BULK INSERT tmptable FROM '"+@.FileToImport+"'"
EXEC (@.SQL)
ENDTry enclosing the literal in single-quotes. Specify 2 single-quotes inside
the literal where you have an embedded single-quote:

SET @.SQL = 'BULK INSERT tmptable FROM '''+@.FileToImport+''''

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Maury" <maurizio.alberti_TOGLI_@.gmail.com> wrote in message
news:qS7Yf.64843$A83.1587071@.twister1.libero.it...
> I'm trying to write a SP that
> accept in input a parameter with the name
> of a file (with complete path)
> but I noticed some problems...
> It's right this way? Thanks!
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
> ALTER PROCEDURE [dbo].[BI]
> @.FileToImport nvarchar(100)
> AS
> BEGIN
> SET NOCOUNT ON;
> DECLARE @.SQL nvarchar(200)
> SET @.SQL = "BULK INSERT tmptable FROM '"+@.FileToImport+"'"
> EXEC (@.SQL)
> END|||Dan Guzman ha scritto:
> Try enclosing the literal in single-quotes. Specify 2 single-quotes inside
> the literal where you have an embedded single-quote:
> SET @.SQL = 'BULK INSERT tmptable FROM '''+@.FileToImport+''''
IT'S OK!!!!
Thank you very very much....
(and sorry I'm a newbie in SQL Server)

Pass XML Data to a Stored Procedure

Does anyone know how to pass a XML File to a MS SQL 2005 Stored Procedure (INSERT/UPDATE), and how to create the stored procedure so it will accept the XML values using VB 2005.

Here is an example of the XML File.

Code:
<MYROOT>
<TableName>
<Field1>String</Field1>
<Field2>String</Field2>
<Field3>String</Field3>
</TableName>
</MYROOT>

Thank you.
bty The following link provided by Microsoft does not work in VB 2005, I have tried that

http://support.microsoft.com/default.aspx?scid=kb;en-us;555266

You can do this using XQuery to strip the values and pass them to the stored proc. You can view a good article on using XQuery here:

http://www.15seconds.com/issue/050803.htm

***

Download this free script (WSP Snapshot 1.0) to take snapshot sample of your web server(s) from anywhere/anytime using a browser. View the stats (cpu and disk stress, available memory, requests queued, request wait time and more) on a cellphone or PDA also. http://www.ifusionsoft.com

|||

Also check out Sushil's weblog on this ->

http://blogs.msdn.com/sushilc/archive/2004/08/03/207162.aspx

This explains the basics of sending XML to SQL 2005 as a parameter.

pass filename

Using SSIS foreach loop I get the files names inside a folder on the network.
How do I pass this variable i.e. file name to a stored procedure?
Thanks

I've achived something similar to this by doing the following:

(i've assumed you've already got to the stage of populating the variable with the name of the file)

Within your loop:-

1) add an execute sql task and enter the following in your SQLStatement section: - EXEC yourproc ? (the ? is the placeholder for the parameter)

2) set up the parameter to your variable in the Parameter Mapping section remembering to set the correct type. I don't believe the parameter name is important in this scenario.

Hope that makes sense.

|||

This is the error I get:

[Execute SQL Task] Error: Executing the query "exec uspMarketValuesUploadXMLfileReader ?" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||Did you fill out the parameter mapping tab?|||

Yes. Still the same error.

These are my settings

SQLSourceType: DirectInput

ParameterMapping --> User::FileName

datatype: varchar

|||Use a "0" for the parameter name. Also, you won't be able to parse the query.|||

Ah, so it looks like my comment about parameter name not mattering is not quite correct. Apologies. In the parameter mapping section, set the parameter name to be a valid sql parameter eg @.filepath. Hopefully that should do the trick.

Cheers

|||

Solved.

Had to replace 0 with ?

Thanks guys.

|||

Don't forget to mark posts as answered...

Good luck!

Pass Datasource Name as input parameter

hi,

how to pass Datasource Name as input parameter from command propmt to rdl file in sql server 2005?

Thanks,

shanthi

Hi,

Unfortunately this is not possible. What you can do is use a stored procedure which returns a different set based on a parameter.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

The closest you can get is to use an expression-based data source connection string. An example is shown here: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

Pass Datasource Name as input parameter

hi,

how to pass Datasource Name as input parameter from command propmt to rdl file in sql server 2005?

Thanks,

shanthi

Hi,

Unfortunately this is not possible. What you can do is use a stored procedure which returns a different set based on a parameter.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

The closest you can get is to use an expression-based data source connection string. An example is shown here: http://blogs.msdn.com/bwelcker/archive/2005/04/29/413343.aspx

-- Robert

pass all data in a file as a string into a stored procedure

Hi,
Using SSIS 2005 how is it possible to loop through a folder on the network, look at each file, pass the data inside each file as a string into a stored procedure.
Thanks

Use the For Each Loop container to enumerate the files. I'd probably just use a Script Task top open the file and grab the data into a variable. Use the Exec SQL Task to call the procedure with a parameter mapped to the variable.

If it was large amounts of data then I'd probably put the procedure call in the script too to more effectively handle the large size, and save passing it around as much. Use an ADO.NET connection manager, you can work with this in the script.

Pass a parameter to SSIS from .NET App

I'd like to pass a parameter value from a ASP.NET application to an SSIS package. My SSIS package pulls data from SQL and loads it a flat file based on the parameter value.

Is this possible?

Very much possible.

Check out the example @. http://www.codeproject.com/useritems/CallSSISFromCSharp.asp

More details on "Loading and Running a Package Programmatically " @. http://msdn2.microsoft.com/en-us/library/ms136090.aspx

Thanks,
Loonysan

|||Sweet! Thanks :)

Pass a parameter to an url in the default webbrowser

hi

i would like to launch an crystal report file from my vb.net program. This report has 1 parameter field (ID). I would like to give this parameter directly in the URL.

at the moment i use something like this but the parameterwindow still appears.

System.Diagnostics.Process.Start("http://Server/Reports/Report.rpt?ID="&Id)

anyone has a solution?

tnx in advance!
GeertI think there is method to suppress parameterwindow
If there is method like enablepopupmenu then set it to false|||tnx but will supressing the window pass the value? i believe not...

or is there a workaround this problem? like loading the report en then export it?

pass a parameter to a SqlDataSource

I'm trying to pass my SqlDataSource a parameter that is defined in the code-behind file for the same page. I've tried the method below but it does not work. Is there a better way?

SubmitForm.ascx page:

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ connection string..... %>"
SelectCommand="sp_CourseMaterialShipment_GetCourses" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter DefaultValue="<% ProgramID %>" Name="programID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

SubmitForm.ascx.vb page:

Private ProgramID as string = "25"

Public ReadOnly Property ProgramID() As String
Get
Return _ProgramID
End Get
End Property

Thanks

Jason

Hello my friend,

If this is what you want to do, take the DefaultValue attribute out of the aspx page and use the following in your code behind: -

sqlDataSource2.SelectParameters["programID"].DefaultValue = ProgramID;

Kind regards

Scotty

|||

worked. Thank you.

Jason

Partitions not stored in the cube file

[Sorry - I'm throwing alot up here today - I know]

All partition info is stored in a <cubename>.partitions file.

I'm just wondering why this information is not stored in the <cubename>.cube file in the BIDS project. Especially considering that, if you view the code of <cubename>.cube there is an empty <Partitions /> tag for each Measure group.

Seems a bit strange to me. Any idea why this is?

-Jamie

The partitions are written in a separate file because it's common for projects to have many partitions and they can create a performance problem. The infrastructure for BI projects doesn't allow (or it allows with considerable effort not worth doing) loading a project file partially, on-demand as various elements are accessed in UI. Instead, the entire project file content is loaded into memory structures (AMO objects) that are displayed and edited in UI. By having the partitions into a separate file, we can open the cube editor for common task without loading all the partitions. And when the user goes to the Partitions tab (of cube editor), then partitions are loaded.

Adrian Dumitrascu

|||

Adrian,

Thanks, that makes sense. One other question then, why is there an empty <Partitions> element in the .cube file for each measure group?

Regards

Jamie

|||

The empty Partitions element is optional there. As it's now implemented, it's a sign that the MeasureGroup has Partitions in the separate .partitions file, but this might change in the future, it's not a guaranteed behaviour.

Adrian Dumitrascu

|||

Adrian Dumitrascu wrote:

The empty Partitions element is optional there. As it's now implemented, it's a sign that the MeasureGroup has Partitions in the separate .partitions file, but this might change in the future, it's not a guaranteed behaviour.

Adrian Dumitrascu

Thanks again Adrian!

partitions

Hi,
I have a question in the middle beetween Windows and SQL.
I have RAID 5 system and then I can put SQL database and log file where i
want...
But..
Can be important create more logical partitions in RAID system for improve
defrag efficiency?
Example: I have only one database in SQL. I create a system partition C, a
database partition D and a log partition E.
Can I have better performance putting database in D and log in E?
Thank's a lot.. and sotty for my english..
Andrea
Since the drives are all a single Raid 5 you will not gain any performance by
spreading the data to the D drive and logs to the E drive.
"Rusty73" wrote:

> Hi,
> I have a question in the middle beetween Windows and SQL.
> I have RAID 5 system and then I can put SQL database and log file where i
> want...
> But..
> Can be important create more logical partitions in RAID system for improve
> defrag efficiency?
> Example: I have only one database in SQL. I create a system partition C, a
> database partition D and a log partition E.
> Can I have better performance putting database in D and log in E?
> Thank's a lot.. and sotty for my english..
> Andrea
>
>
|||I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...[vbcol=seagreen]
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
|||The best way to combat file fragmentation is to create the database files
once, as large as you will need them. If the files are allocated
contigueously, and never grow, then no file fragmentation will occur. Make
sure you give enough room for the Index Defrags, that will use internal data
pages, not file fragments to reorganize, again, as long as the file does not
grow.
Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in case
you've underestimated the space you will need.
Besides, file fragmentation will only affect scans and DSS type queries.
For a true OLTP system, the disk and database page access will be pretty
random, which will not be affected as much by file level fragmentation.
Sincerely,
Anthony Thomas

"Rusty73" <rusty77@.libero.it> wrote in message
news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...[vbcol=seagreen]
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
|||Hi there,
Raid 5 will give you better performance for the DB.
For logs is best to create a mirror pair for improved performance, exactly
because of sequential writing.
My server is configured as follows:
system partition C - array of 2 mirrored drives - RAID 1
DB partition - array of a few disks in raid 5
log partition - another pair of mirrored drives - RAID 1
I agree with the previous comment that having different logical partitions
on the same disk array will not improve performance.
Hope this helps.
Kind regards,
Doru
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u7bc6z$tFHA.2848@.TK2MSFTNGP10.phx.gbl...
> The best way to combat file fragmentation is to create the database files
> once, as large as you will need them. If the files are allocated
> contigueously, and never grow, then no file fragmentation will occur.
> Make
> sure you give enough room for the Index Defrags, that will use internal
> data
> pages, not file fragments to reorganize, again, as long as the file does
> not
> grow.
> Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in
> case
> you've underestimated the space you will need.
> Besides, file fragmentation will only affect scans and DSS type queries.
> For a true OLTP system, the disk and database page access will be pretty
> random, which will not be affected as much by file level fragmentation.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Rusty73" <rusty77@.libero.it> wrote in message
> news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
> I know that log files are writing sequentially to the disk btu with RAID 5
> i
> can decide to block the writing only on log disk...
> but I can have no better performance to the defrag of log?
> "fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
> news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...
>

partitions

Hi,
I have a question in the middle beetween Windows and SQL.
I have RAID 5 system and then I can put SQL database and log file where i
want...
But..
Can be important create more logical partitions in RAID system for improve
defrag efficiency'
Example: I have only one database in SQL. I create a system partition C, a
database partition D and a log partition E.
Can I have better performance putting database in D and log in E?
Thank's a lot.. and sotty for my english..
AndreaSince the drives are all a single Raid 5 you will not gain any performance b
y
spreading the data to the D drive and logs to the E drive.
"Rusty73" wrote:

> Hi,
> I have a question in the middle beetween Windows and SQL.
> I have RAID 5 system and then I can put SQL database and log file where i
> want...
> But..
> Can be important create more logical partitions in RAID system for improve
> defrag efficiency'
> Example: I have only one database in SQL. I create a system partition C, a
> database partition D and a log partition E.
> Can I have better performance putting database in D and log in E?
> Thank's a lot.. and sotty for my english..
> Andrea
>
>|||I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...[vbcol=seagreen]
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
>|||The best way to combat file fragmentation is to create the database files
once, as large as you will need them. If the files are allocated
contigueously, and never grow, then no file fragmentation will occur. Make
sure you give enough room for the Index Defrags, that will use internal data
pages, not file fragments to reorganize, again, as long as the file does not
grow.
Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in case
you've underestimated the space you will need.
Besides, file fragmentation will only affect scans and DSS type queries.
For a true OLTP system, the disk and database page access will be pretty
random, which will not be affected as much by file level fragmentation.
Sincerely,
Anthony Thomas
"Rusty73" <rusty77@.libero.it> wrote in message
news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
I know that log files are writing sequentially to the disk btu with RAID 5 i
can decide to block the writing only on log disk...
but I can have no better performance to the defrag of log?
"fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...[vbcol=seagreen]
> Since the drives are all a single Raid 5 you will not gain any performance
> by
> spreading the data to the D drive and logs to the E drive.
>
> "Rusty73" wrote:
>|||Hi there,
Raid 5 will give you better performance for the DB.
For logs is best to create a mirror pair for improved performance, exactly
because of sequential writing.
My server is configured as follows:
system partition C - array of 2 mirrored drives - RAID 1
DB partition - array of a few disks in raid 5
log partition - another pair of mirrored drives - RAID 1
I agree with the previous comment that having different logical partitions
on the same disk array will not improve performance.
Hope this helps.
Kind regards,
Doru
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:u7bc6z$tFHA.2848@.TK2MSFTNGP10.phx.gbl...
> The best way to combat file fragmentation is to create the database files
> once, as large as you will need them. If the files are allocated
> contigueously, and never grow, then no file fragmentation will occur.
> Make
> sure you give enough room for the Index Defrags, that will use internal
> data
> pages, not file fragments to reorganize, again, as long as the file does
> not
> grow.
> Go ahead and leave the AUTOGROW feature on, but only as a fail-safe, in
> case
> you've underestimated the space you will need.
> Besides, file fragmentation will only affect scans and DSS type queries.
> For a true OLTP system, the disk and database page access will be pretty
> random, which will not be affected as much by file level fragmentation.
> Sincerely,
>
> Anthony Thomas
>
> --
> "Rusty73" <rusty77@.libero.it> wrote in message
> news:%23iqlJn6tFHA.3932@.TK2MSFTNGP15.phx.gbl...
> I know that log files are writing sequentially to the disk btu with RAID 5
> i
> can decide to block the writing only on log disk...
> but I can have no better performance to the defrag of log?
> "fnguy" <fnguy@.discussions.microsoft.com> ha scritto nel messaggio
> news:B17E4200-29D1-429B-AD10-B2A9C41DC43B@.microsoft.com...
>