Multi Value Parameters

I'm using a sql server Stored Procedure, that uses a defined parameter to pull the records. How can i make this stored procedure a multi value parameter. I select multi value in report parameters, and changed the where clause to "IN" but its still not working, when i select more then one parameter from the drop down list. Here is my stored procedure.

Code Snippet

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

AS

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

This should help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1

See Lisa's posts.

You can't just use IN (@.Region). SQL doesn't automatically understand that. Developing this can be extemely frustrating, but don't despair. We're here to help if you get stuck.

|||

I also faced same problem.In reporting service, even though you select multi values for parameter and send it to stored procedure,it wil go as a single Value.

U got it.In stored procedure,if u included where clause with IN,it wil consider it as a single value only(as 'a,b,c,d').

So, u need to split this (comma seperated).You have to include split function into ur stored procedure.Search in google, u wil get it.

I same i used in my report and its working fine.Its very easy.

|||

There's a reat article on what you need to do here:

http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005%E2%80%94Working_with_Multivalued_Parameters

including some copy and paste code.

Enjoy!

-Eric

Multi Value Parameters

I'm using a sql server Stored Procedure, that uses a defined parameter to pull the records. How can i make this stored procedure a multi value parameter. I select multi value in report parameters, and changed the where clause to "IN" but its still not working, when i select more then one parameter from the drop down list. Here is my stored procedure.

Code Snippet

USE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

AS

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

This should help you:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1705421&SiteID=1

See Lisa's posts.

You can't just use IN (@.Region). SQL doesn't automatically understand that. Developing this can be extemely frustrating, but don't despair. We're here to help if you get stuck.

|||

I also faced same problem.In reporting service, even though you select multi values for parameter and send it to stored procedure,it wil go as a single Value.

U got it.In stored procedure,if u included where clause with IN,it wil consider it as a single value only(as 'a,b,c,d').

So, u need to split this (comma seperated).You have to include split function into ur stored procedure.Search in google, u wil get it.

I same i used in my report and its working fine.Its very easy.

|||

There's a reat article on what you need to do here:

http://en.csharp-online.net/Building_Reports_in_SQL_Server_2005%E2%80%94Working_with_Multivalued_Parameters

including some copy and paste code.

Enjoy!

-Eric

Multi value parameters

Can anyone give me the syntax to pass mutliple parameters into a stored
procedure? I know that you have to use IN, but I cannot get it to work. Thank
you in advance.Create your Dataset, select the Proc and execute it in Reporting Services
Designer. It will create your parameters for you, then you can go into the
Report > Parameters section and adjust the settings from there. What are you
using IN for? Can you post some code that you are using?
"KimB" wrote:
> Can anyone give me the syntax to pass mutliple parameters into a stored
> procedure? I know that you have to use IN, but I cannot get it to work. Thank
> you in advance.|||As a side note, RS 2005 supports multi-value parameters natively, so no need
of an expression-based query.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"KimB" <KimB@.discussions.microsoft.com> wrote in message
news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> Can anyone give me the syntax to pass mutliple parameters into a stored
> procedure? I know that you have to use IN, but I cannot get it to work.
> Thank
> you in advance.|||I'm trying to pass a multi-value to a stored procedure as well but it isn't
working. The documentation says that using a SP with multi-value isn't
supported. Huh? Most of my reports use SPs.
Can this be done? Is there a workaround?
This was the #1 feature I was waiting for in RS2005.
Thanks in advance!
"Teo Lachev [MVP]" wrote:
> As a side note, RS 2005 supports multi-value parameters natively, so no need
> of an expression-based query.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> > Can anyone give me the syntax to pass mutliple parameters into a stored
> > procedure? I know that you have to use IN, but I cannot get it to work.
> > Thank
> > you in advance.
>
>|||What doesn't work has nothing really to do with RS but has to do with Stored
Procedures in SQL Server. You cannot do the following in a stored procedure.
Let's say you have a Parameter called @.MyParams
Now you can map that parameter to a multi-value parameter but if in your
stored procedure you try to do this:
select * from sometable where somefield in (@.MyParams)
It won't work. Try it. Create a stored procedure and try to pass a
multi-value parameter to the stored procedure. It won't work.
What you can do is to have a string parameter that is passed 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
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> I'm trying to pass a multi-value to a stored procedure as well but it
> isn't
> working. The documentation says that using a SP with multi-value isn't
> supported. Huh? Most of my reports use SPs.
> Can this be done? Is there a workaround?
> This was the #1 feature I was waiting for in RS2005.
> Thanks in advance!
>
> "Teo Lachev [MVP]" wrote:
>> As a side note, RS 2005 supports multi-value parameters natively, so no
>> need
>> of an expression-based query.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "KimB" <KimB@.discussions.microsoft.com> wrote in message
>> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
>> > Can anyone give me the syntax to pass mutliple parameters into a stored
>> > procedure? I know that you have to use IN, but I cannot get it to work.
>> > Thank
>> > you in advance.
>>|||I understand now-- thanks-- and this worked great for me.
It's too bad the documentation doesn't provide this simple information given
that the multi-value feature is bound to be a highly-desired feature.
Thanks again!
"Bruce L-C [MVP]" wrote:
> What doesn't work has nothing really to do with RS but has to do with Stored
> Procedures in SQL Server. You cannot do the following in a stored procedure.
> Let's say you have a Parameter called @.MyParams
> Now you can map that parameter to a multi-value parameter but if in your
> stored procedure you try to do this:
> select * from sometable where somefield in (@.MyParams)
> It won't work. Try it. Create a stored procedure and try to pass a
> multi-value parameter to the stored procedure. It won't work.
> What you can do is to have a string parameter that is passed 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
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
> message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> > I'm trying to pass a multi-value to a stored procedure as well but it
> > isn't
> > working. The documentation says that using a SP with multi-value isn't
> > supported. Huh? Most of my reports use SPs.
> >
> > Can this be done? Is there a workaround?
> >
> > This was the #1 feature I was waiting for in RS2005.
> >
> > Thanks in advance!
> >
> >
> > "Teo Lachev [MVP]" wrote:
> >
> >> As a side note, RS 2005 supports multi-value parameters natively, so no
> >> need
> >> of an expression-based query.
> >>
> >> --
> >> HTH,
> >> ---
> >> Teo Lachev, MVP, MCSD, MCT
> >> "Microsoft Reporting Services in Action"
> >> "Applied Microsoft Analysis Services 2005"
> >> Home page and blog: http://www.prologika.com/
> >> ---
> >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> >> > Can anyone give me the syntax to pass mutliple parameters into a stored
> >> > procedure? I know that you have to use IN, but I cannot get it to work.
> >> > Thank
> >> > you in advance.
> >>
> >>
> >>
>
>|||I've been using the charlist_to_table function to return my parameters in a
table and it works well but I can't get it to return more than 800 rows--
even if the string sent contains more than 800 entries.
I checked and RS is sending ALL of the selected entries (868 in this case)
but the function is only returning 800 rows.
I used a similiar function from elsewhere on the net and it also returned
only 800 rows.
Is anyone aware of a limitation here-- I don't see any limitation in the
function?
Thanks!
"Michael Creamer" wrote:
> I understand now-- thanks-- and this worked great for me.
> It's too bad the documentation doesn't provide this simple information given
> that the multi-value feature is bound to be a highly-desired feature.
> Thanks again!
>
> "Bruce L-C [MVP]" wrote:
> > What doesn't work has nothing really to do with RS but has to do with Stored
> > Procedures in SQL Server. You cannot do the following in a stored procedure.
> >
> > Let's say you have a Parameter called @.MyParams
> >
> > Now you can map that parameter to a multi-value parameter but if in your
> > stored procedure you try to do this:
> >
> > select * from sometable where somefield in (@.MyParams)
> >
> > It won't work. Try it. Create a stored procedure and try to pass a
> > multi-value parameter to the stored procedure. It won't work.
> >
> > What you can do is to have a string parameter that is passed 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
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
> > message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> > > I'm trying to pass a multi-value to a stored procedure as well but it
> > > isn't
> > > working. The documentation says that using a SP with multi-value isn't
> > > supported. Huh? Most of my reports use SPs.
> > >
> > > Can this be done? Is there a workaround?
> > >
> > > This was the #1 feature I was waiting for in RS2005.
> > >
> > > Thanks in advance!
> > >
> > >
> > > "Teo Lachev [MVP]" wrote:
> > >
> > >> As a side note, RS 2005 supports multi-value parameters natively, so no
> > >> need
> > >> of an expression-based query.
> > >>
> > >> --
> > >> HTH,
> > >> ---
> > >> Teo Lachev, MVP, MCSD, MCT
> > >> "Microsoft Reporting Services in Action"
> > >> "Applied Microsoft Analysis Services 2005"
> > >> Home page and blog: http://www.prologika.com/
> > >> ---
> > >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> > >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> > >> > Can anyone give me the syntax to pass mutliple parameters into a stored
> > >> > procedure? I know that you have to use IN, but I cannot get it to work.
> > >> > Thank
> > >> > you in advance.
> > >>
> > >>
> > >>
> >
> >
> >|||It is working now.
I had been using a similar function, UtilSplit, but I've now switched to the
charlist_to_table function and all the rows are being returned correctly.
"Michael Creamer" wrote:
> I've been using the charlist_to_table function to return my parameters in a
> table and it works well but I can't get it to return more than 800 rows--
> even if the string sent contains more than 800 entries.
> I checked and RS is sending ALL of the selected entries (868 in this case)
> but the function is only returning 800 rows.
> I used a similiar function from elsewhere on the net and it also returned
> only 800 rows.
> Is anyone aware of a limitation here-- I don't see any limitation in the
> function?
> Thanks!
> "Michael Creamer" wrote:
> > I understand now-- thanks-- and this worked great for me.
> >
> > It's too bad the documentation doesn't provide this simple information given
> > that the multi-value feature is bound to be a highly-desired feature.
> >
> > Thanks again!
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > What doesn't work has nothing really to do with RS but has to do with Stored
> > > Procedures in SQL Server. You cannot do the following in a stored procedure.
> > >
> > > Let's say you have a Parameter called @.MyParams
> > >
> > > Now you can map that parameter to a multi-value parameter but if in your
> > > stored procedure you try to do this:
> > >
> > > select * from sometable where somefield in (@.MyParams)
> > >
> > > It won't work. Try it. Create a stored procedure and try to pass a
> > > multi-value parameter to the stored procedure. It won't work.
> > >
> > > What you can do is to have a string parameter that is passed 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
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
> > > message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
> > > > I'm trying to pass a multi-value to a stored procedure as well but it
> > > > isn't
> > > > working. The documentation says that using a SP with multi-value isn't
> > > > supported. Huh? Most of my reports use SPs.
> > > >
> > > > Can this be done? Is there a workaround?
> > > >
> > > > This was the #1 feature I was waiting for in RS2005.
> > > >
> > > > Thanks in advance!
> > > >
> > > >
> > > > "Teo Lachev [MVP]" wrote:
> > > >
> > > >> As a side note, RS 2005 supports multi-value parameters natively, so no
> > > >> need
> > > >> of an expression-based query.
> > > >>
> > > >> --
> > > >> HTH,
> > > >> ---
> > > >> Teo Lachev, MVP, MCSD, MCT
> > > >> "Microsoft Reporting Services in Action"
> > > >> "Applied Microsoft Analysis Services 2005"
> > > >> Home page and blog: http://www.prologika.com/
> > > >> ---
> > > >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
> > > >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
> > > >> > Can anyone give me the syntax to pass mutliple parameters into a stored
> > > >> > procedure? I know that you have to use IN, but I cannot get it to work.
> > > >> > Thank
> > > >> > you in advance.
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >|||I had passed on your question to the author of the charlist_to_table
function. I'll tell him nevermind.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
message news:540D9E63-DD7F-478B-89E1-D36B6BF1FC4B@.microsoft.com...
> It is working now.
> I had been using a similar function, UtilSplit, but I've now switched to
> the
> charlist_to_table function and all the rows are being returned correctly.
> "Michael Creamer" wrote:
>> I've been using the charlist_to_table function to return my parameters in
>> a
>> table and it works well but I can't get it to return more than 800 rows--
>> even if the string sent contains more than 800 entries.
>> I checked and RS is sending ALL of the selected entries (868 in this
>> case)
>> but the function is only returning 800 rows.
>> I used a similiar function from elsewhere on the net and it also returned
>> only 800 rows.
>> Is anyone aware of a limitation here-- I don't see any limitation in the
>> function?
>> Thanks!
>> "Michael Creamer" wrote:
>> > I understand now-- thanks-- and this worked great for me.
>> >
>> > It's too bad the documentation doesn't provide this simple information
>> > given
>> > that the multi-value feature is bound to be a highly-desired feature.
>> >
>> > Thanks again!
>> >
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > What doesn't work has nothing really to do with RS but has to do with
>> > > Stored
>> > > Procedures in SQL Server. You cannot do the following in a stored
>> > > procedure.
>> > >
>> > > Let's say you have a Parameter called @.MyParams
>> > >
>> > > Now you can map that parameter to a multi-value parameter but if in
>> > > your
>> > > stored procedure you try to do this:
>> > >
>> > > select * from sometable where somefield in (@.MyParams)
>> > >
>> > > It won't work. Try it. Create a stored procedure and try to pass a
>> > > multi-value parameter to the stored procedure. It won't work.
>> > >
>> > > What you can do is to have a string parameter that is passed 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
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
>> > > message news:AB275F9C-93BD-4FBE-9BC6-7AAF5B3D806F@.microsoft.com...
>> > > > I'm trying to pass a multi-value to a stored procedure as well but
>> > > > it
>> > > > isn't
>> > > > working. The documentation says that using a SP with multi-value
>> > > > isn't
>> > > > supported. Huh? Most of my reports use SPs.
>> > > >
>> > > > Can this be done? Is there a workaround?
>> > > >
>> > > > This was the #1 feature I was waiting for in RS2005.
>> > > >
>> > > > Thanks in advance!
>> > > >
>> > > >
>> > > > "Teo Lachev [MVP]" wrote:
>> > > >
>> > > >> As a side note, RS 2005 supports multi-value parameters natively,
>> > > >> so no
>> > > >> need
>> > > >> of an expression-based query.
>> > > >>
>> > > >> --
>> > > >> HTH,
>> > > >> ---
>> > > >> Teo Lachev, MVP, MCSD, MCT
>> > > >> "Microsoft Reporting Services in Action"
>> > > >> "Applied Microsoft Analysis Services 2005"
>> > > >> Home page and blog: http://www.prologika.com/
>> > > >> ---
>> > > >> "KimB" <KimB@.discussions.microsoft.com> wrote in message
>> > > >> news:90B09407-0351-4118-B381-99481F9DBC58@.microsoft.com...
>> > > >> > Can anyone give me the syntax to pass mutliple parameters into a
>> > > >> > stored
>> > > >> > procedure? I know that you have to use IN, but I cannot get it
>> > > >> > to work.
>> > > >> > Thank
>> > > >> > you in advance.
>> > > >>
>> > > >>
>> > > >>
>> > >
>> > >
>> > >

Multi Threading in Stored Procedures?

hi,
I want to execute two user created stored procedures in a multithreaded manner in ms-sql server . Can some tell me how i can do that.
Thanks in advance.
Regards,
ManpreetOriginally posted by chugh_manpreet
hi,

I want to execute two users


Ya know....I've ALWAYS wanted to do that...

but then who would use the system?

Well, I guess if you limit to just 2....

To answer though...every exec od a sproc is threaded...it's not serial...

Are you talking about withIN the sproc itself?|||By its design the SQL engine is single-process multi-threaded. A series of asynchronous calls always result in multi-threaded processing on the server. I guess I have the same question as Brett, - are you talking about multi-threading within a stored procedure? And why do you need that?|||No chuckles?

Anyway...To thread in a sproc, create jobs and start'em...

They'll be aysyncronus and thread...

although I swear (and can't confirm) that using xp_cmdshell does...even though everywhere I read it's synchronous...(I couldn;t explain some blocking awhile back...still don't know why)|||I can! In pre-SP3 era when everybody and their mother was firing it while trying to be cute, and coming back with OSQL through it. There was a hell of a blocking going on. It is actually due to the nature of OSQL, rather than xp_cmdshell, but those who were using it didn't even bother to check into the possibility of it to occur, and we were stuck to debug, and what's most frustrating, - explain, how it happened. Now they are all squealing, because they can't do any more damage :)|||Yeah, but in a sproc if you do...

DELETE FROM myTable99

master xp_cmdshell 'bcp command...for myTable99'

Wouldn't you expect the DELETE to be completed before the bcp?

It actually launches 2 spids (of course) but spid 1 (the execution of the sproc and the delete) blocks spid 2, the execution of xp_cmdshell

huh?

Is this a fundamental thing I'm totally missing?|||hi,

thanks for your reply. Actually i have written two stored procedures which call a dll(non-sql) funtion. Now i want these two stored procedures simultaneously in a multi threaded manner.

I tried executing it like this

exec sp_Procedure1
exec sp_Procedure2

but the second stored procedures is not getting executed untill first gets executed. I want both the stored procedures execute simultaneously in a multi threaded manner.

Any inputs in this regard are welcome

Regards,
Manpreet

Originally posted by Brett Kaiser
No chuckles?

Anyway...To thread in a sproc, create jobs and start'em...

They'll be aysyncronus and thread...

although I swear (and can't confirm) that using xp_cmdshell does...even though everywhere I read it's synchronous...(I couldn;t explain some blocking awhile back...still don't know why)|||hi,

thanks for your reply. Actually i have written two stored procedures which call a dll(non-sql) funtion. Now i want these two stored procedures simultaneously in a multi threaded manner.

I tried executing it like this

exec sp_Procedure1
exec sp_Procedure2

but the second stored procedures is not getting executed untill first gets executed. I want both the stored procedures execute simultaneously in a multi threaded manner.

Any inputs in this regard are welcome

Regards,
Manpreet
Sorry for bumping an old post, but I am looking to do the same thing, pseudocode below for those that want to know why.

---------------
CREATE PROC COMPANY_ISP
@.company_id INT
AS

INSERT COMPANY(COMPANY_ID)
SELECT @.company_id

EXEC COMPANY_REBUILD_INDEX @.company_id

RETURN
---------------

The proc is actaully a little longer then the above but you get the idea, COMPANY_REBUILD_INDEX takes about 10 seconds to run and waiting for it to process is not critical to the web page calling this proc, but rather a hindrance since the application needs to wait for this process to finish.

I suppose the application could call this proc separately behind the scenes somehow, I can talk with the .Net developer to see what he thinks, but thought it would be nice to have a solution that would work in SQL. xp_cmdshell? I thought that was only for DOS commands, does not seem like it is the solution I am looking for.

Thanks,
-John|||Applicaton design issues should be handled by the application designers.

If I want an application to run two stored procedures simultaneously, then I expect the application to thread the executions. If the applicaton wants serial execution, then serialize them.

You should consider not only the near term results, but also the long term maintainability. Cute solutions usually compromise the latter for the former.

For your solution, it seems you are expecting an inherently single threaded application (browser) to handle asynchronous communications.

The reindex should probably be done via a job that detects the request (writes a flag somewhere), and then you can write a control that polls a progress table for completion. This way, a user can refresh their browser w/o losing (needing to maintain) the state of the ReIndex.|||first thing that bothers me here is the fact that you are reindexing after every insert. why not just reindex the table overnight in a job.

I wonder what effect of (and I would never try this exepcially with a 10 second execution) is of putting your reindex into an insert trigger (which I use sparingly) on the table. Would the ASP page go about it's merry business before the trigger completed or would you still get held up on your page waiting on the trigger to execute.

By the way if your trying to insert and reindex at the same time on the same table I do imagine there would be some blocking even if you used multi-threading as implimented in JAVA.|||first thing that bothers me here is the fact that you are reindexing after every insert. why not just reindex the table overnight in a job.

I wonder what effect of (and I would never try this exepcially with a 10 second execution) is of putting your reindex into an insert trigger (which I use sparingly) on the table. Would the ASP page go about it's merry business before the trigger completed or would you still get held up on your page waiting on the trigger to execute.

By the way if your trying to insert and reindex at the same time on the same table I do imagine there would be some blocking even if you used multi-threading as implimented in JAVA.

I was curious myself about the trigger, if I created a trigger would the calling proc wait for the trigger to finish before returning to the application?

The reason I need to incrementally rebuild the index (I currently have an overnight job btw) is that when a user adds a new company they need a way to be able to search for it later, our search page does a phonetic search for companies (in case they misspelled it), if the job only runs nightly then the user would have to wait until the next day to see the company in the search results. This is an intranet application for about 50 users and adding companies is not going to be a very frequent process so I am not concerned with the strain on the server.

For anyone interested in implementing a phonetic search here is the pseudocode:

1. Split the company name into words
2. Translate each word into its phonetic representation (soundex is one option albeit bad - I implemented a double metaphone translation)
3. Remove duplicate phonetic words per company
4. Aggregate the occurrences of each phonetic
5. Give a score based on (in)frequency of each phonetic to aid in improved search results.
6. Increase the score for exact word matches
7. Increase the score for exact phrase matches
8. Return the results by score descending

There are about 40k unique phonetic words that are created as a result of this (out of a pool of close to a million companies).|||I was curious myself about the trigger, if I created a trigger would the calling proc wait for the trigger to finish before returning to the application?

Try it and let me know.|||Maybe I'm missing something really fundamental, but what on earth are you trying to accomplish? Reindexing is a performance issue, it can improve the internal structure of an index after massive inserts or deletes. Reindexing had better not have any effect at all on what rows are visible in the table!

Did I miss a meeting, or is this entire discussion moot?

-PatP|||Pat,

from what it sounds like his sp (COMPANY_REBUILD_INDEX) is a misnomer. It sounds like he is really doing all of his processing for his phonetic thingy majiggy and not a DBCC DBREINDEX. (which sounds like a lot of trouble to go through to tame some bad data entry).|||It's a nessary evil, we have over 100k council members that enter their own data, the search is used to help normalize the company name that they enter into their job history profile (each member can enter multiple job profiles based on their work history). The search had to be as flexible as possible and had to search on a per word basis and handle spelling mistakes, I think in the next version they want us to use a thesaurus to be able to match firm to company but in my mind THAT is going way overboard.|||did you try the trigger thing. i assumed you were't really doing a reindex as in dbcc. how did it go?

Multi Threading exception

Apologies if this is not the right forum.

We have some c~ code which calls a stored proc to trnsfer data from one db to another.

We are using multi threading to allow a whole days data to be processed in parrallel.

However, we are experiencing problems. After a while we get the following message:

"An exception during changed data captured caused all processing threads to abort".

Can anyone tell me what posssible reasons would cause this to happen?

It is not an area in which I have a lot of expertise, the code was written by a third party.

Many thanks

This is not an error message thrown by SQL Server (or the CLR), so it's likely one produced by the third-party code. You'll need to look at their code to see what actual error is happening to cause them to display this message.

Steven

multi keyword search SP

Is it possible to write a Stored Procedure that takes a string of
search keywords as argument and returns the recordset? At the mo I am
passing the WHERE String as argument.

I got this technique from an Extreme Ultradev tutorial by Rick Curtis
it looked quite ok:
http://www.princeton.edu/~rcurtis/u...tutorial12.html

I have to admit, one of the main reason for passing the WHERE string is
that I do not know how to do the string splitting / parsing and putting
together in a Stored Procedure. I bet T-SQL would be just as powerful
as VBScript if I just knew it well enough.

What I liked about having built them on the web script was the
flexibility allowing to potentially build an advanced search without
having to change the stored procedure - but this is not crucial I could
always write several stored procedures or add parameters to the SP.

Here is what I have achieved in this way:
User can enter one ore more keywords separated by space.
Search algorithm returns results across a number of fields where ALL
search words are contained in any of these.
Search results will always be formatted a certain way and displayed in
a html table no matter how the search procedure / criteria is varied.

Here is the algorithm (that now works in ASP)
1. split search string into separate keywords
2. build where condition based on single keyword, concatenating all
searched fields (" AND f1+' '+f2+{' '+f<n>} LIKE %<keyword>%")
3. concatenate all these where conditions and pass to stored procedure.
4. stored procedure takes care of all other logic (e.g. Joins, which
fields are searched etc.). It uses a string variable @.SQL to build the
complete search string and then does
execute (@.SQL);
to create the recordset.

I bet there is a way to move 1. 2. and 3. into the SP (and I would feel
better if it was) but I don't have the expertise to do this. If anybody
wants to help me this is very welcome.

I can also post my original code to clarify, just want to avoid too
long posts.

Cheers
AxelThese links might give you some ideas (the first is similar to the
article you linked to, but from a pure TSQL perspective):

http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/arrays-in-sql.html

TSQL string functions are not as powerful as those in other languages,
so if you have lots of string parsing, it would probably be best to do
it in the ASP code rather than in TSQL.

If your requirements become more complex, you could consider some sort
of reporting tool, rather than writing and maintaining code yourself.

Simon|||Thanks Simon

I had actually already read that article but it takes a different
approach - the SP takes a named list of parameters (similar to an
advanced search form) so it presumes the user knows where to look for
the keyword. My approch concatenates all candidate fields into one
large string and then looks for the search string within that long
string. It loops this process with multilpe kewoards and concatenates
using the AND condition.

If for example we assume for simplicities sake there is on table
tblProduct with the fields Name, Desc, Comment, Category, ID and I have
a record with "Iron Screw", "17 inch amalgated Screw - green", "useful
for outdoor use","consumables","1234-23"
this would be caught by passing "screw iron 17 outdoor" to the search
procedure. The user does not need to know in which fields the keywords
are contained but at the same time he will be able to narrow down the
search results very efficiently by adding more search words.
In my little loop I am comparing
'Iron Screw 17 inch amalgated Screw - green useful for outdoor use
consumables 1234-23' Like '%screw%'
AND
'Iron Screw 17 inch amalgated Screw - green useful for outdoor use
consumables 1234-23' Like '%iron%'
AND
'Iron...' Like '%17%'
AND
'Iron...' Like '%outdoor%'

what I need is like a split() and loop in T-SQL; also I would have to
eliminate invalid characters e.g. single quotes for the search string
to avoid any attempts at hacking.

I was told using dynamic SQL the way I have done here is wrong I only
want to get it right. Maybe there is another approach? MOre opinions
welcome...

thx,
Axel|||On 17 Jun 2005 03:56:57 -0700, Axel wrote:

>My approch concatenates all candidate fields into one
>large string and then looks for the search string within that long
>string.
(snip example)

Hi Axel,

The example clarifies what you want to do.

First, revisit this page: http://www.sommarskog.se/arrays-in-sql.html.
It will explain how you can break the collected search words ("screw
iron 17 outdoor" in your example) into a set of rows.

To find the Products that match at least one of the terms given, use

SELECT DISTINCT p.Product
FROM Products AS p
INNER JOIN #SearchWords AS s-- The search words in a table;
-- See link above for details.
ON p.Name + p.Desc + p.Comment + p.Category + p.ID
LIKE '%' + s.Word + '%'

And if you only want the products that match ALL the terms given:

SELECT p.Product
FROM Products AS p
INNER JOIN #SearchWords AS s-- The search words in a table;
-- See link above for details.
ON p.Name + p.Desc + p.Comment + p.Category + p.ID
LIKE '%' + s.Word + '%'
GROUP BY p.Product
HAVING COUNT(*) = (SELECT COUNT(*) FROM #SearchWords)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Axel (realraven2000@.hotmail.com) writes:
> I had actually already read that article but it takes a different
> approach - the SP takes a named list of parameters (similar to an
> advanced search form) so it presumes the user knows where to look for
> the keyword. My approch concatenates all candidate fields into one
> large string and then looks for the search string within that long
> string. It loops this process with multilpe kewoards and concatenates
> using the AND condition.

If I were you, I would look into full-text search. Not that I know
whether this is the answer to your problem, because I have never
used full-text myself. But I would look into it, before I started
to build a complex engine myself.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

multi databases or partitions

Hi all

i have 3 million customer stored in a billing system.

currently i dived them to 40 database running sql2000.

i plan to migrate to sql 2005.

Is it better to let all customer in one partioned table based on city.?

or divide them one database for city.

taking into account there is 15 million transaction per month.

i want to get best performance and maintainance for backup.

thanks

Hi,

I would go for the partioned table. It is easier to remain within 1 database. I think there is more overhead in going to multiple databases anyway. With the partioned tables in 2005, you can span your data on multiple disks.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi Geert Verhoeven

Thank you for reply.

I use one storage Raid-5 for database storage composed of of 8 disk*73GB . In that case ,can that fullfill "span data on multiple disks".

Is there overhead for table partition compared to multi databases.

Just i want to know the side effect before starting merging data in one database using partitioned tables.

thanks

|||

Hi,

Indeed since you are using Raid, the spanning multiple disks is not an argument. Partioned tables can be processed by different processors but that is the same for multiple databases.

I still think that multiple databases give you more overhead then remaining in one database but it is hard to tell. Either way, I don't think that partitioned tables will be slower than multiple databases. Partitioned tables are easier to manage then multiple databases.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Since you have 40 different databases, I assume you want to be able to backup a portion (i.e. database in this case) and also that your data for one city does not interact with data in another city. With table partitioning, you can get the same convenience. you can partition by the city. You can possibly put each partition in its own file group there by mapped to diiferentg disk, you can backup/restore at filegroup granularity. Since partitioned table is managed as one logical unit, you won't need to worry about making schema changes in 40 databases, for example if you want to create an index.

multi databases or partitions

Hi all

i have 3 million customer stored in a billing system.

currently i dived them to 40 database running sql2000.

i plan to migrate to sql 2005.

Is it better to let all customer in one partioned table based on city.?

or divide them one database for city.

taking into account there is 15 million transaction per month.

i want to get best performance and maintainance for backup.

thanks

Hi,

I would go for the partioned table. It is easier to remain within 1 database. I think there is more overhead in going to multiple databases anyway. With the partioned tables in 2005, you can span your data on multiple disks.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi Geert Verhoeven

Thank you for reply.

I use one storage Raid-5 for database storage composed of of 8 disk*73GB . In that case ,can that fullfill "span data on multiple disks".

Is there overhead for table partition compared to multi databases.

Just i want to know the side effect before starting merging data in one database using partitioned tables.

thanks

|||

Hi,

Indeed since you are using Raid, the spanning multiple disks is not an argument. Partioned tables can be processed by different processors but that is the same for multiple databases.

I still think that multiple databases give you more overhead then remaining in one database but it is hard to tell. Either way, I don't think that partitioned tables will be slower than multiple databases. Partitioned tables are easier to manage then multiple databases.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Since you have 40 different databases, I assume you want to be able to backup a portion (i.e. database in this case) and also that your data for one city does not interact with data in another city. With table partitioning, you can get the same convenience. you can partition by the city. You can possibly put each partition in its own file group there by mapped to diiferentg disk, you can backup/restore at filegroup granularity. Since partitioned table is managed as one logical unit, you won't need to worry about making schema changes in 40 databases, for example if you want to create an index.

sql

Mulitple stored proc parameters

Hi,
I have the following command text as my dataset :
declare @.SQL varchar(255)
select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
@.RoofSection
exec (@.SQL)
Both parameters are nvarchar(50) strings. However if I want my query to work
when I enter the parameter i need to put quotes around the @.RoofSection
parameters otherwise the query doesn't work.
What troubles me the most is that @.Facility doesn't need quotes :s
Any input on this?
ThxIf you are going to do this you need to plan on putting single quotes around
all text parameters (I noticed from query analyzer that sometimes it is OK
with this for the first parameter but it depends, for instance, if I do a %
then it wants it in single quotes).
Unless you are needing to dynamically switch databases then this is all you
have to do:
sp_rptRoofSection @.Facility , @.RoofSection
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Generally speaking, if an SP character type parameter ( the actual parameter
value I mean) does NOT contain spaces or other special characters, it does
not have to be quoted. Quotes are required when the param value does contain
the special chars... So it is a good idea to always quote, then you do not
have to worry about it further.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Yes I do indeed plan to dynamically change Database.
How can I put the quotes in my command string so the parameters are
automatically surrounded by quotes when they are passed to the stored proc?
My params do contain spaces and have a mix of numbers and chars into them.
Every single combination of quotes I enter makes an error.
Here is the command string again (the one that does work when I manually
enter my quotes into the values of the params):
declare @.SQL varchar(255)
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
@.RoofSection
exec (@.SQL)
thx
"Bruce L-C [MVP]" wrote:
> If you are going to do this you need to plan on putting single quotes around
> all text parameters (I noticed from query analyzer that sometimes it is OK
> with this for the first parameter but it depends, for instance, if I do a %
> then it wants it in single quotes).
> Unless you are needing to dynamically switch databases then this is all you
> have to do:
> sp_rptRoofSection @.Facility , @.RoofSection
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> > Hi,
> >
> > I have the following command text as my dataset :
> >
> > declare @.SQL varchar(255)
> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> > @.RoofSection
> > exec (@.SQL)
> >
> > Both parameters are nvarchar(50) strings. However if I want my query to
> > work
> > when I enter the parameter i need to put quotes around the @.RoofSection
> > parameters otherwise the query doesn't work.
> >
> > What troubles me the most is that @.Facility doesn't need quotes :s
> >
> > Any input on this?
> >
> > Thx
> >
> >
>
>|||Note that you do not have to use a script like this. I use an expression
because with an expression I can first assign it to a textbox so I can see
the result. Then when I have it correct I then use the expression as the
source (in generic query window).
= Parameters!DBName.Value & ".dbo.sp_rptRoofSection " & "'" &
Parameters!Facility.Value & "'"
Note it is double quote, single quote, double quote.
If you want to use the script then what you do is you put two single quotes
for every single quote you want. For instance:
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ''' + @.Facility + ''', '''+
@.RoofSection + ''''
So this '''' (four single quotes) ends up with 1 single quote. The outer two
are enclosing the string. In the modification above everything you see are
single quotes.
Again, I like using an expression because it makes it easier to test, plus
enclosing a string in double quotes and just putting a single quote where
you need it is easier to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:2F087327-F40A-4D85-BB07-2EC8C5F72815@.microsoft.com...
> Yes I do indeed plan to dynamically change Database.
> How can I put the quotes in my command string so the parameters are
> automatically surrounded by quotes when they are passed to the stored
> proc?
> My params do contain spaces and have a mix of numbers and chars into them.
> Every single combination of quotes I enter makes an error.
> Here is the command string again (the one that does work when I manually
> enter my quotes into the values of the params):
> declare @.SQL varchar(255)
> select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
> @.RoofSection
> exec (@.SQL)
> thx
> "Bruce L-C [MVP]" wrote:
>> If you are going to do this you need to plan on putting single quotes
>> around
>> all text parameters (I noticed from query analyzer that sometimes it is
>> OK
>> with this for the first parameter but it depends, for instance, if I do a
>> %
>> then it wants it in single quotes).
>> Unless you are needing to dynamically switch databases then this is all
>> you
>> have to do:
>> sp_rptRoofSection @.Facility , @.RoofSection
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Eric" <Eric@.discussions.microsoft.com> wrote in message
>> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
>> > Hi,
>> >
>> > I have the following command text as my dataset :
>> >
>> > declare @.SQL varchar(255)
>> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
>> > @.RoofSection
>> > exec (@.SQL)
>> >
>> > Both parameters are nvarchar(50) strings. However if I want my query to
>> > work
>> > when I enter the parameter i need to put quotes around the @.RoofSection
>> > parameters otherwise the query doesn't work.
>> >
>> > What troubles me the most is that @.Facility doesn't need quotes :s
>> >
>> > Any input on this?
>> >
>> > Thx
>> >
>> >
>>sql

Mulitple Queries in a Stored Procedure

I have written a stored procedure that contains queries which return data.
It works correctly in Query Analyzer, returning each of the labled fields.
When I create a report the only return field the report wizard shows is from
the first query. When I add each of the other return fields and run the
report in VS I get an out of index error.RS only supports a single resultset being returned.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bassbuster" <bassbuster@.discussions.microsoft.com> wrote in message
news:39FAA817-C491-493D-81BB-FDA153D7DB57@.microsoft.com...
> I have written a stored procedure that contains queries which return data.
> It works correctly in Query Analyzer, returning each of the labled fields.
> When I create a report the only return field the report wizard shows is
> from
> the first query. When I add each of the other return fields and run the
> report in VS I get an out of index error.

mulitiple selects in a stored procedure

Hi,
I am trying to create a pagination within a stored procedure but I need
to select from several tables:
First I am inserting the fields into a temp table and this works fine:
INSERT INTO #TempItems (Name)
SELECT Name FROM tblName
I thought I could get data from other tables using UNION:
INSERT INTO #TempItems (Name,Address,Telephone,Street)
SELECT Name FROM tblName
UNION
SELECT Address FROM tblAddress
UNION
SELECT Telephone FROM tblTelephone
UNION
SELECT Street FROM tblStreet
*These are bogus fields I have used as examples.
But the error I get is that the number of insert fields is less then the
select? Could anyone help on how the best way to achieve this?
many thanks in advance
Peter
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!You can google the newsgroups (suggest .programming) for pagination
techniques. Your error is due to a misunderstanding of how union works.
Union merely combines separate result sets into one; you are attempting to
use it (incorrectly) as you would a join. Assuming that a union could be
used, you must specify 4 items within the select list of each select
statement that is part of the union, corresponding to the 4 columns to be
inserted. There are other flaws in your logic, but this should get you
started.
You should be inserting using something like the following
insert ...
select ...
from tblName inner join tblAddress on ...
inner join tblTelephone on ...
inner join tblStreet on ...
where ...
How those joins are made (and their type - inner, outer, cross, etc) I
cannot answer without knowing the relationships between the table.
"Peter Rooney" <peter@.whoba.co.uk> wrote in message
news:OoqGZO3rDHA.2456@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to create a pagination within a stored procedure but I need
> to select from several tables:
> First I am inserting the fields into a temp table and this works fine:
> INSERT INTO #TempItems (Name)
> SELECT Name FROM tblName
> I thought I could get data from other tables using UNION:
> INSERT INTO #TempItems (Name,Address,Telephone,Street)
> SELECT Name FROM tblName
> UNION
> SELECT Address FROM tblAddress
> UNION
> SELECT Telephone FROM tblTelephone
> UNION
> SELECT Street FROM tblStreet
> *These are bogus fields I have used as examples.
>
> But the error I get is that the number of insert fields is less then the
> select? Could anyone help on how the best way to achieve this?
> many thanks in advance
> Peter
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

MSsQL2005; OPENROWSET, BLOB/IMAGE and STORED PROCEDURE problems

All,

I work with Microsoft SQL Server 2005 on windows XP professional.
I'd like to create stored procdure to add image to my database (jpg file).
I managed to do it using VARCHAR variable in stored procedure
and then using EXEC, but it don't work directly.

My Table definiton:
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Login] [char](10),
[Password] [char](20),
[Avatar] [image] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My working solution using stored procedure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
SET @.Query = 'INSERT INTO USERS ' + CHAR(13)
+ 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)
+ '''' + @.Password + ''' AS Password,' + CHAR(13)
+ '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''', SINGLE_BLOB) AS OBRAZEK)'
EXECUTE (@.Query)
SET @.UserID = @.@.IDENTITY
END

I'd like to use statement in the stored procdure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
DECLARE
@.Query AS VARCHAR(MAX)

SET @.AvatarFileLocation = 'C:\hitman1.jpg'
INSERT INTO USERS
SELECT @.Login AS Login,
@.Password AS Password,
(SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS OBRAZEK)

SET @.UserID = @.@.IDENTITY

END

It generates error:
Incorrect syntax near '@.AvatarFileLocation'.

My question is:
Why it does not work and how to write the stored procedure code to run this code without errors.

Thanks for any reply

You can't use a variable inside OPENROWSET.

What you are doing, in any case, IS VERY DANGEROUS. There

are many ways in which dynamic SQL is vulnerable to SQL

injection. Please read about it, so that you don't lose

everything you have when a malicious user joins your

site/forum with a password like

O',0x; delete from USERS where Password <> 'O';return 0;--

You might start reading here:

http://www.sommarskog.se/dynamic_sql.html

Steve Kass

Drew University

Michal1979@.discussions.microsoft.com wrote:

> All,

>

> I work with Microsoft SQL Server 2005 on windows XP professional.

> I'd like to create stored procdure to add image to my database (jpg

> file).

> I managed to do it using VARCHAR variable in stored procedure

> and then using EXEC, but it don't work directly.

>

> My Table definiton:

> CREATE TABLE [dbo].[Users](

> [UserID] [int] IDENTITY(1,1) NOT NULL,

> [Login] [char](10),

> [Password] [char](20),

> [Avatar] [image] NULL,

> CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED

> (

> [UserID] ASC

> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

>

> My working solution using stored procedure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> SET @.Query = 'INSERT INTO USERS ' + CHAR(13)

> + 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)

> + '''' + @.Password + ''' AS Password,' + CHAR(13)

> + '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''',

> SINGLE_BLOB) AS OBRAZEK)'

> EXECUTE (@.Query)

> SET @.UserID = @.@.IDENTITY

> END

>

> I'd like to use statement in the stored procdure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> DECLARE

> @.Query AS VARCHAR(MAX)

>

> SET @.AvatarFileLocation = 'C:\hitman1.jpg'

> INSERT INTO USERS

> SELECT @.Login AS Login,

> @.Password AS Password,

> (SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS

> OBRAZEK)

>

>

> SET @.UserID = @.@.IDENTITY

>

> END

>

>

> It generates error:

> Incorrect syntax near '@.AvatarFileLocation'.

>

> My question is:

> Why it does not work and how to write the stored procedure code to run

> this code without errors.

>

> Thanks for any reply

>

>

>

>|||

NNTP User

Lot thanks for the repy,

The article link is very interesting.

I understand risks from using dynamic sql, but I'm not goiing to allow user call my stored procedure directly.

All arguments of the stored procedure will be validated and constructed my my application.

My real question is why can I use OPENROWSET function in stored procedure when I build the query

using varchar variable and then execute it, although I can't do it direclty using OPENROWSET in

stored procedure.

|||You cannot use a variable like this:

OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB)

The file name must be hard-coded, like this:

OPENROWSET(BULK 'C:\picture.jpg', SINGLE_BLOB)

SK

Michal1979@.discussions.microsoft.com wrote:

> NNTP User

>

>

> Lot thanks for the repy,

>

> The article link is very interesting.

>

> I understand risks from using dynamic sql, but I'm not goiing to allow

> user call my stored procedure directly.

>

> All arguments of the stored procedure will be validated and constructed

> my my application.

>

> My real question is why can I use OPENROWSET function in stored

> procedure when I build the query

>

> using varchar variable and then execute it, although I can't do it

> direclty using OPENROWSET in

>

> stored procedure.

>

>

>

>

>

>|||

NNTP User,

Yes,I can not use directly OPENROWSET function in strored procedure, but I can use dynamic sql.

My problem is not how to store image using stored procedure (I can do it, however I don't like the way I do it)

but why can't I do it directly.

By the way I tried to use sp_executesql to run the code but it didn't work as well - so for now the only way

to store image or file using stored procedure is dynamic sql.

But I'm still wandering WHY? Is it a bug or something like that?

|||

As Steve indicated, you will have to specify the filename as a literal in the OPENROWSET call. Otherwise you will have to use dynamic SQL to form the entire statement and execute it. And with dynamic SQL you will have protect against SQL injection attacks. Optionally, you can do the following for a bulk import process:

1. Create temporary table to hold the user accounts

2. Use the new BulkCopy managed API to stream the user data from the client to server

3. Write SP to dump the rows from temporary table to the main table

For adding or modifying single values, you can just have a SP with image parameter and manipulate the data in the table. There is no need to use OPENROWSET which requires a file (creation of file by client, server having permissions to access file, etc) among other things and parameterization is not straight-forward.

|||

Umachandar Jayachandran - MS,

I'd like to know if there is other than OPENROWSET form of loading images or binary files into MSSQL,

I could missed it in documentation. It is sure that there will be no possibility to sql injection using dynamic sql

in my case.

Probably I should mentioned it before, the problem I described is not critical for me, becouse I found solution.

The solution may not be ideal (especialy for me) but it still works. Anyway probably I'll use C# and ADO.NET to

perform put and get image from database.

Maybe you know why the only way to use OPENROWSET in stored procedure is to use dynamic sql.

Thanks

MSsQL2005; OPENROWSET, BLOB/IMAGE and STORED PROCEDURE problems

All,

I work with Microsoft SQL Server 2005 on windows XP professional.
I'd like to create stored procdure to add image to my database (jpg file).
I managed to do it using VARCHAR variable in stored procedure
and then using EXEC, but it don't work directly.

My Table definiton:
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Login] [char](10),
[Password] [char](20),
[Avatar] [image] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My working solution using stored procedure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
SET @.Query = 'INSERT INTO USERS ' + CHAR(13)
+ 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)
+ '''' + @.Password + ''' AS Password,' + CHAR(13)
+ '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''', SINGLE_BLOB) AS OBRAZEK)'
EXECUTE (@.Query)
SET @.UserID = @.@.IDENTITY
END

I'd like to use statement in the stored procdure:
ALTER PROCEDURE [dbo].[AddUser]
@.Login AS VARCHAR(255),
@.Password AS VARCHAR(255),
@.AvatarFileLocation AS VARCHAR(255),
@.UserId AS INT OUTPUT
AS
BEGIN
DECLARE
@.Query AS VARCHAR(MAX)

SET @.AvatarFileLocation = 'C:\hitman1.jpg'
INSERT INTO USERS
SELECT @.Login AS Login,
@.Password AS Password,
(SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS OBRAZEK)

SET @.UserID = @.@.IDENTITY

END

It generates error:
Incorrect syntax near '@.AvatarFileLocation'.

My question is:
Why it does not work and how to write the stored procedure code to run this code without errors.

Thanks for any reply

You can't use a variable inside OPENROWSET.

What you are doing, in any case, IS VERY DANGEROUS. There

are many ways in which dynamic SQL is vulnerable to SQL

injection. Please read about it, so that you don't lose

everything you have when a malicious user joins your

site/forum with a password like

O',0x; delete from USERS where Password <> 'O';return 0;--

You might start reading here:

http://www.sommarskog.se/dynamic_sql.html

Steve Kass

Drew University

Michal1979@.discussions.microsoft.com wrote:

> All,

>

> I work with Microsoft SQL Server 2005 on windows XP professional.

> I'd like to create stored procdure to add image to my database (jpg

> file).

> I managed to do it using VARCHAR variable in stored procedure

> and then using EXEC, but it don't work directly.

>

> My Table definiton:

> CREATE TABLE [dbo].[Users](

> [UserID] [int] IDENTITY(1,1) NOT NULL,

> [Login] [char](10),

> [Password] [char](20),

> [Avatar] [image] NULL,

> CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED

> (

> [UserID] ASC

> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

>

> My working solution using stored procedure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> SET @.Query = 'INSERT INTO USERS ' + CHAR(13)

> + 'SELECT '''+ @.Login + ''' AS Login, ' + CHAR(13)

> + '''' + @.Password + ''' AS Password,' + CHAR(13)

> + '(SELECT * FROM OPENROWSET(BULK ''' + @.AvatarFileLocation + ''',

> SINGLE_BLOB) AS OBRAZEK)'

> EXECUTE (@.Query)

> SET @.UserID = @.@.IDENTITY

> END

>

> I'd like to use statement in the stored procdure:

> ALTER PROCEDURE [dbo].[AddUser]

> @.Login AS VARCHAR(255),

> @.Password AS VARCHAR(255),

> @.AvatarFileLocation AS VARCHAR(255),

> @.UserId AS INT OUTPUT

> AS

> BEGIN

> DECLARE

> @.Query AS VARCHAR(MAX)

>

> SET @.AvatarFileLocation = 'C:\hitman1.jpg'

> INSERT INTO USERS

> SELECT @.Login AS Login,

> @.Password AS Password,

> (SELECT * FROM OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB) AS

> OBRAZEK)

>

>

> SET @.UserID = @.@.IDENTITY

>

> END

>

>

> It generates error:

> Incorrect syntax near '@.AvatarFileLocation'.

>

> My question is:

> Why it does not work and how to write the stored procedure code to run

> this code without errors.

>

> Thanks for any reply

>

>

>

>|||

NNTP User

Lot thanks for the repy,

The article link is very interesting.

I understand risks from using dynamic sql, but I'm not goiing to allow user call my stored procedure directly.

All arguments of the stored procedure will be validated and constructed my my application.

My real question is why can I use OPENROWSET function in stored procedure when I build the query

using varchar variable and then execute it, although I can't do it direclty using OPENROWSET in

stored procedure.

|||You cannot use a variable like this:

OPENROWSET(BULK @.AvatarFileLocation, SINGLE_BLOB)

The file name must be hard-coded, like this:

OPENROWSET(BULK 'C:\picture.jpg', SINGLE_BLOB)

SK

Michal1979@.discussions.microsoft.com wrote:

> NNTP User

>

>

> Lot thanks for the repy,

>

> The article link is very interesting.

>

> I understand risks from using dynamic sql, but I'm not goiing to allow

> user call my stored procedure directly.

>

> All arguments of the stored procedure will be validated and constructed

> my my application.

>

> My real question is why can I use OPENROWSET function in stored

> procedure when I build the query

>

> using varchar variable and then execute it, although I can't do it

> direclty using OPENROWSET in

>

> stored procedure.

>

>

>

>

>

>|||

NNTP User,

Yes,I can not use directly OPENROWSET function in strored procedure, but I can use dynamic sql.

My problem is not how to store image using stored procedure (I can do it, however I don't like the way I do it)

but why can't I do it directly.

By the way I tried to use sp_executesql to run the code but it didn't work as well - so for now the only way

to store image or file using stored procedure is dynamic sql.

But I'm still wandering WHY? Is it a bug or something like that?

|||

As Steve indicated, you will have to specify the filename as a literal in the OPENROWSET call. Otherwise you will have to use dynamic SQL to form the entire statement and execute it. And with dynamic SQL you will have protect against SQL injection attacks. Optionally, you can do the following for a bulk import process:

1. Create temporary table to hold the user accounts

2. Use the new BulkCopy managed API to stream the user data from the client to server

3. Write SP to dump the rows from temporary table to the main table

For adding or modifying single values, you can just have a SP with image parameter and manipulate the data in the table. There is no need to use OPENROWSET which requires a file (creation of file by client, server having permissions to access file, etc) among other things and parameterization is not straight-forward.

|||

Umachandar Jayachandran - MS,

I'd like to know if there is other than OPENROWSET form of loading images or binary files into MSSQL,

I could missed it in documentation. It is sure that there will be no possibility to sql injection using dynamic sql

in my case.

Probably I should mentioned it before, the problem I described is not critical for me, becouse I found solution.

The solution may not be ideal (especialy for me) but it still works. Anyway probably I'll use C# and ADO.NET to

perform put and get image from database.

Maybe you know why the only way to use OPENROWSET in stored procedure is to use dynamic sql.

Thanks

MSSQL Stored procedure Query using SELECT TOP

Is there a way to do a SELECT TOP # using a variable for the #?

In other words I'm doing a SELECT TOP 50* FROM DATATABLE

If I pass an @.value for the number

SELECT TOP @.value* FROM DATATABLE doesn't work

I am generating a random sampling of data and I want to allow the user to select the number of results they choose to have.

Thanks in advance.

I believe that is possible in 2005 and not in 2000. You can however use SET ROWCOUNT in 2000.

DECLARE @.tintSET @.t = 10SET ROWCOUNT @.tSELECT *FROM YourTableSET ROWCOUNT 0

|||

In 2005, the following works:

DECLARE @.t int
SET @.t = 20
SELECT TOP(@.t) * FROM yourTable

mssql server

hello

can anyone tell me if it is possible to use activeX components (com/dcom) in
MS-sql stored procedures?

tiayes it is! Here is an example from a german forum (using Delphi for
programming the com+ object):

Schritt 1: COM+ Objekt (alias ActiveX alias COM-Objekt) in Delphi erzeugen

unit OSSPCOM_Impl;
{$WARN SYMBOL_PLATFORM OFF} interface uses
ActiveX, Mtsobj, Mtx, ComObj, OSSPCOM_TLB, StdVcl; type
TOSSPCOMObj = class(TMtsAutoObject, IOSSPCOMObj)
protected
function Get_Daten: OleVariant; safecall;
procedure DoWork(vInput: OleVariant; out vOutput: OleVariant); safecall;
{ Protected-Deklarationen }
end; implementation uses ComServ; function TOSSPCOMObj.Get_Daten:
OleVariant;
begin
Result := 'Zeichenkette aus dem COM+ Objekt';
SetComplete;
end; procedure TOSSPCOMObj.DoWork(vInput: OleVariant; out vOutput:
OleVariant);
begin
vOutput := vInput + ' (ok)';
SetComplete;
end; initialization
TAutoObjectFactory.Create(ComServer, TOSSPCOMObj, Class_OSSPCOMObj,
ciMultiInstance, tmApartment);
end.

Das kompilierte COM+ Objekt wird danach in eine COM+ Anwendung installiert
(siehe Komponentendienste von Windows 2000/XP/2003).

Schritt 2: Stored Procedure ruft das eigene COM+ Objekt auf

CREATE PROCEDURE spCallCOMplusObj
@.sTXT VARCHAR(50) OUTPUT
AS
Declare @.Object int, @.hr int, @.RetVal int, @.iStatus int
SET @.iStatus = 1
-- Objektinstanz erzeugen
Exec @.hr = sp_OACreate '{0E9447C4-EF22-4570-B202-45D50D3B5EFB}', @.Object
OUTPUT
IF @.hr = 0
BEGIN
SET @.iStatus = 2
END
-- Interface-Methode (Property) abfragen
Exec @.hr = sp_OAGetProperty @.Object, 'Daten', @.sTXT OUTPUT
IF @.hr=0
BEGIN
SET @.iStatus = 3
END
Exec @.hr = sp_OAMethod @.Object, 'DoWork'
IF @.hr=0
BEGIN
SET @.iStatus = 4
END
Exec @.hr = sp_OADestroy @.Object
Return @.iStatus

Schritt 3: Funktion im Query Analyzer des MS SQL Servers testen

DECLARE @.sTEXT VARCHAR(50)
DECLARE @.iResult INT
EXEC @.iResult = spCallCOMplusObj @.sTEXT OUTPUT
SELECT @.iResult,@.sTEXT
Als Ergebnis muss die Zeichenkette aus dem COM+ Objekt sichtbar sein.

hth,
Helmut

"udo polder" <udo@.polder.cd> schrieb im Newsbeitrag
news:bg7l5b$kft$03$1@.news.t-online.com...
> hello
> can anyone tell me if it is possible to use activeX components (com/dcom)
in
> MS-sql stored procedures?
> tia