Multi Value Parameters

I just created a report with 2 parameters and it works fine. If I change one of them to MV it still works but if I pick 2 values I get a message about a problem with the "," - Incorrect syntas near ','

Is this a known problem or is it something I'm doing. Is a problem is there a fix for it.

YOu habe to change your query after changing the parameter type, as your query probably look like

WHERE SomeColumn = @.MyParameter

which will evaluate in a MV-Parameter to

WHERE SomeColumn = 'A','B'

This is not valid SQL. Therefore you to write your query like the one below.

WHERE SomeColumn IN (@.MyParameter)

That should work for you and your value list.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Works perfect - thank you.

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 SQL Server (2000 & 2005) Problem

Hi All!

I setup MS SQL Server 2005 first and then I setup MS SQL Server 2000 with instance 'Myname' and the I created 2 database in SQL Server 2005 and 2000, When I Write an application in .NET 2005 to connect to MS SQL Server 2005 it ok, but it's not ok in sql Server 2000 and appear the error:

System.Data.SqlClient.SqlException: Snapshot isolation transaction failed accessing database 'TustenaOS' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at

How to connect to MS SQL Server 2000. Thanks

By "setup" did you mean install? Because it's usually avery bad idea to install a later version, then install a prior version. If that is what you did, I suspect things will never work the way you want them to.

|||

It looks like you are requesting snapshot isolation, and that's not a feature of SQL Server 2000.

Multi Parameter Select query

Hi All,

I have a procdeure as written below.

I have created datasets in te report and in Report parameters clicked the Multi-value Parameter option.

When I run the report, I get all the customer names, when I select one customer report returns correct data. When I select two customers in the list box, the result set is empty. Can anyone guide me on what the error could be?

Thanks

Josh

Procedure:

create procedure MyMultiReport @.customername nvarchar(30), @.businessplantype nvarchar(30), @.businessplanyear nvarchar(10) as

Select PlanDatameta.sort,sysperiod.id,Planmeta.id,Planmonthlydata.Productmainpkey,Country, BusinessDivisiondescription, PlanSegmentPkey, Plantext.referencepkey, Plantext.usage, sheet, name, Plantext.text, Brand, Size, text1, PlanDatameta.sort+' '+Plantext1.text as LineDescription,line, Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12, Total from Planmonthlydata join Plantext on Plantext.referencepkey=Planmonthlydata.Plansegmentpkey join PlanDatameta on PlanDatameta.pkey=Planmonthlydata.PlanDatametapkey join Productdescription on Productdescription.Productmainpkey=Planmonthlydata.Productmainpkey join Productmain on Productdescription.Productmainpkey=Productmain.pkey join Plansegment on Plansegment.pkey=Planmonthlydata.Plansegmentpkey join bpamain on bpamain.pkey=Plansegment.bpamainpkey join sysperiod on sysperiod.pkey=Plansegment.sysperiodpkey join Planmeta on Planmeta.pkey=Plansegment.Planmetapkey join Plantext Plantext1 on PlanDatameta.pkey=Plantext1.referencepkey where Planmonthlydata.status<>'d' and (PlanDatameta.sheet='PlanProductSummary') and Plantext.text<>'' and (PlanDatameta.line='MyPlanBaselineVolumeBasic' or PlanDatameta.line='BaselineVolumes' or PlanDatameta.line='IncrementalVolumes'or PlanDatameta.line='TotalVolumes') and name in (@.customername) order by PlanDatameta.sort,Plantext.text,text1

return

Hi,

If thecustomer name is the multi valued parameter you can't use that customer name @.CustomerName in the Where clause.

suppose Customer Name parameter contain these values:aaa, bbb , ccc then if you select the aaa, bbb from the parameter it will pass to the Stored procedure in the following format:

@.CustomeName='aaa,bbb'

When you selecting the one value from customer name parameter it is passing to the stored procedure like this: 'aaa' this when you used in In cluase will give you the result.

This @.CustomeName you can't directly use IN Cluase of where.

Select .. from Where name in('aaa,bbb') this will not give any result just you check by running the above select.

Instead you can do one thing

a)First create a table valued function like the following which will take the @.CustomerName as Input parameter and will return the table containg the

aaa

bbb splits the input string by comma and place in the Table.

ALTER function [dbo].[GetCSV]

(@.array varchar(max))

Returns @.t Table (Col1 varchar(max))

as

Begin

DECLARE @.separator_position INT

,@.array_value VARCHAR(1000)

,@.separator CHAR(1)

Set @.separator=','

--For my loop to work I need an extra separator at the end. I always look to the

-- left of the separator character for each array value

SET @.array = @.array + @.separator

-- patindex matches the a pattern against a string

WHILE PATINDEX('%' + @.separator + '%', @.array) <> 0

BEGIN

SELECT @.separator_position = PATINDEX('%' + @.separator + '%',@.array)

SELECT @.array_value = LEFT(@.array, @.separator_position - 1)

INSERT INTO @.t SELECT @.array_value

-- This replaces what we just processed with and empty string

SELECT @.array = STUFF(@.array, 1, @.separator_position, '')

END

Return

End

And use that resulted table in the where clause of your select statement

Select ..

from ..

Where name in (Select * from dbo.GetCSV(@.CustomerName))

It will give you the result.

Hope this helps.

Thanks

|||

You can also use dynamic SQL and filter the records and put them in a temporary table (#temp) first and use this query clause in main query:

code for dynamic SQL:

DELCARE @.strSQL VARCHAR(MAX)

CREATE TABLE #temp (name VARCHAR(50))

SET @.strSQL = 'SELECT name INTO #temp FROM [Table] WHERE name IN (' + @.customername + ')'

EXEC(@.strSQL)

code for main query:

AND name in (SELECT name FROM #temp1) AND ....

Also, dont forget to change the datatype of the input parameter @.customername to NVARCHAR(MAX).

Shyam

MTD

I have created the following calculated member on my cube
Ancestor([Time].CurrentMember, [Time].[Month]), i want to link this to the
measures so that i can give me previous month versus the current months
sales figures, at the moment when i process the cube this member returns
nothing on the cube, how do i do this?
You'll have to explain a bit more fully because my understanding from that is
that you just want to compare 2 months side by side which is just a matter of
referencing those 2 months in your MDX statement e.g. {Time.January,
Time.February ON ROWS}. This is very easy to do so I'm guessing your problem
is a little more complex than that.
The subject of this thread leads me to think you are after MonthToDate or
similar.
Can you explain further?
Regards
Jamie Thomson
"MANDLA MKHWANAZI" wrote:

> I have created the following calculated member on my cube
> Ancestor([Time].CurrentMember, [Time].[Month]), i want to link this to the
> measures so that i can give me previous month versus the current months
> sales figures, at the moment when i process the cube this member returns
> nothing on the cube, how do i do this?
>
>

MTD

I have created the following calculated member on my cube
Ancestor([Time].CurrentMember, [Time].[Month]), i want to link t
his to the
measures so that i can give me previous month versus the current months
sales figures, at the moment when i process the cube this member returns
nothing on the cube, how do i do this?You'll have to explain a bit more fully because my understanding from that i
s
that you just want to compare 2 months side by side which is just a matter o
f
referencing those 2 months in your MDX statement e.g. {Time.January,
Time.February ON ROWS}. This is very easy to do so I'm guessing your problem
is a little more complex than that.
The subject of this thread leads me to think you are after MonthToDate or
similar.
Can you explain further?
Regards
Jamie Thomson
"MANDLA MKHWANAZI" wrote:

> I have created the following calculated member on my cube
> Ancestor([Time].CurrentMember, [Time].[Month]), i want to link
this to the
> measures so that i can give me previous month versus the current months
> sales figures, at the moment when i process the cube this member returns
> nothing on the cube, how do i do this?
>
>

MSSQLServer

Hi
I have created a ghost of a system running SQL server 7 (on windows 2000 server).
When I try and start MSSQLServer I get the message:
Your SQL Server installation is either corrupt ot has been tampered with (unknown package id).
Any ideas on how I can fix this without rerunning setup.
Thanks
Conor
Re-run setup. See http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Conor" <anonymous@.discussions.microsoft.com> wrote in message
news:479704EB-01A0-4AF2-BCB0-5D699ECD2ABA@.microsoft.com...
> Hi
> I have created a ghost of a system running SQL server 7 (on windows 2000 server).
> When I try and start MSSQLServer I get the message:
> Your SQL Server installation is either corrupt ot has been tampered with (unknown package id).
> Any ideas on how I can fix this without rerunning setup.
> Thanks
> Conor
|||This is correct. SQL Server 7.0 does not support ghosted installations.
Rand
This posting is provided "as is" with no warranties and confers no rights.

MSSQLServer

Hi
I have created a ghost of a system running SQL server 7 (on Windows 2000 ser
ver).
When I try and start MSSQLServer I get the message:
Your SQL Server installation is either corrupt ot has been tampered with (un
known package id).
Any ideas on how I can fix this without rerunning setup.
Thanks
ConorRe-run setup. See l]
Tibor Karaszi, SQL Server MVP
[url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
"Conor" <anonymous@.discussions.microsoft.com> wrote in message
news:479704EB-01A0-4AF2-BCB0-5D699ECD2ABA@.microsoft.com...
> Hi
> I have created a ghost of a system running SQL server 7 (on Windows 2000 s
erver).
> When I try and start MSSQLServer I get the message:
> Your SQL Server installation is either corrupt ot has been tampered with (
unknown package id).
> Any ideas on how I can fix this without rerunning setup.
> Thanks
> Conor|||This is correct. SQL Server 7.0 does not support ghosted installations.
Rand
This posting is provided "as is" with no warranties and confers no rights.

MSSQLServer

Hi
I have created a ghost of a system running SQL server 7 (on windows 2000 server)
When I try and start MSSQLServer I get the message
Your SQL Server installation is either corrupt ot has been tampered with (unknown package id).
Any ideas on how I can fix this without rerunning setup
Thank
ConorRe-run setup. See http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Conor" <anonymous@.discussions.microsoft.com> wrote in message
news:479704EB-01A0-4AF2-BCB0-5D699ECD2ABA@.microsoft.com...
> Hi
> I have created a ghost of a system running SQL server 7 (on windows 2000 server).
> When I try and start MSSQLServer I get the message:
> Your SQL Server installation is either corrupt ot has been tampered with (unknown package id).
> Any ideas on how I can fix this without rerunning setup.
> Thanks
> Conor|||This is correct. SQL Server 7.0 does not support ghosted installations.
Rand
This posting is provided "as is" with no warranties and confers no rights.

MSSQL Script execute in .bat

Hi Guys, anyone out there who could help me with my problem?

I have created a .bat file that would execute the mssql script and give an .txt file as an output. The script run successfully except for the output, the output save as .txt file was not in order unlike the output that was derrived from the query analyzer. As I try to solve my problem I observe that the lines separating the header and the data was the cause of un-order format, the lines doubles its length when I use the .bat file to execute my script. I have tried using the substring syntax to limit the length but same results achieved. Could anyone help me to remove those line automatically when I run my .bat file?

Please see the outputs below. Thank you very much.

output from query analyzer

object_name counter_name
-------- -----------
SQLServer:Buffer Manager Buffer cache hit ratio
SQLServer:Buffer Manager Buffer cache hit ratio base

(2 row(s) affected)

ouput from .bat

object_name
counter_name
----------------
----------------------
SQLServer:Buffer Manager
Buffer cache hit ratio
SQLServer:Buffer Manager
Buffer cache hit ratio baseFor anyone who will encounter same problem as I did, the solution for my problem was on the .bat file and not in MSSQL. I added the -W in the command in my .bat file.