extended stored procedures

Hello!
I have a difficult situation with a clustered server. It consists of two
parts - Cluster_1 and Cluster_2 computers with WINDOWS Server2003. Cluster_1
and Cluster_2 are with 6 GB RAM.
The whole clustered server is named Cluster.
SQL Server 2000 is installed on Cluster.
Service Pack 4 for SQL Server 2000 is installed and latest hot-fix KB899761
is installed too.
I have some extended stored procedures implemented in xp_parser.dll.
One of those is - lets say xp_MyProc.
The problem is - when I call xp_MyProc, the returned result is correct, but
the execution time is extremely long.
xp_parser.dll is placed in C:\WINDOWS\System32 on both Cluster_1 and
Cluster_2.
The shared hard disk between Cluster_1 and Cluster_2 is F:
A copy of xp_parser.dll is not located on F:
What is the cause of the extremely long execution time of xp_MyProc?
When I call xp_MyProc for second and third time - the situation is not
better.
Thank you!
What other environment did this xp run quick? Did it ever? What does it
do?
Here's my car and it runs slow Mr. Mechanic, but I'm not going to tell you
what type of car it is. Good luck.
Sincerely,
Anthony Thomas

"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:%23OX0z563FHA.2816@.tk2msftngp13.phx.gbl...
> Hello!
>
> I have a difficult situation with a clustered server. It consists of two
> parts - Cluster_1 and Cluster_2 computers with WINDOWS Server2003.
Cluster_1
> and Cluster_2 are with 6 GB RAM.
> The whole clustered server is named Cluster.
>
> SQL Server 2000 is installed on Cluster.
> Service Pack 4 for SQL Server 2000 is installed and latest hot-fix
KB899761
> is installed too.
>
> I have some extended stored procedures implemented in xp_parser.dll.
> One of those is - lets say xp_MyProc.
> The problem is - when I call xp_MyProc, the returned result is correct,
but
> the execution time is extremely long.
>
> xp_parser.dll is placed in C:\WINDOWS\System32 on both Cluster_1 and
> Cluster_2.
> The shared hard disk between Cluster_1 and Cluster_2 is F:
> A copy of xp_parser.dll is not located on F:
>
> What is the cause of the extremely long execution time of xp_MyProc?
> When I call xp_MyProc for second and third time - the situation is not
> better.
>
> Thank you!
>
|||XP's should be installed in the BINN directory, which is on the shared
resource disk, same as all the other SQL Server XP's
In that case you do not have to register the XP with an absolute PATH so it
simply works.
With regards to your performance, does it perform when not running on a
cluster?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"ggeshev" <ggeshev@.tonegan.bg> wrote in message
news:%23OX0z563FHA.2816@.tk2msftngp13.phx.gbl...
> Hello!
>
> I have a difficult situation with a clustered server. It consists of two
> parts - Cluster_1 and Cluster_2 computers with WINDOWS Server2003.
> Cluster_1
> and Cluster_2 are with 6 GB RAM.
> The whole clustered server is named Cluster.
>
> SQL Server 2000 is installed on Cluster.
> Service Pack 4 for SQL Server 2000 is installed and latest hot-fix
> KB899761
> is installed too.
>
> I have some extended stored procedures implemented in xp_parser.dll.
> One of those is - lets say xp_MyProc.
> The problem is - when I call xp_MyProc, the returned result is correct,
> but
> the execution time is extremely long.
>
> xp_parser.dll is placed in C:\WINDOWS\System32 on both Cluster_1 and
> Cluster_2.
> The shared hard disk between Cluster_1 and Cluster_2 is F:
> A copy of xp_parser.dll is not located on F:
>
> What is the cause of the extremely long execution time of xp_MyProc?
> When I call xp_MyProc for second and third time - the situation is not
> better.
>
> Thank you!
>

expression-based connection strings difficult to manage

Expression-based connection strings are great but they are specific to each report which makes them difficult to maintain. Shared expression-based connection strings are not supported.

I'm looking for a way around this. I'm hoping to write a custom data extension, and in the custom data extension, do all the "dynamic" logic that sets the connection string (which you normally would be doing in your expression-based connection string).

I know the above can be done. What I don't know is how, inside a custom data extension, to get the value of expressions like User!UserID? (I want the connection to run under stored credentials, but I want to customize the connection string and add a property to it based on the UserID who is logged into Report Manager.)

Create a assembly with a static method that takes a text parameter(userid) and returns the connectionstring and add that assembly (look in BOL for custom code or something like that on how to add it to a report and deploy the assembly to the report server) and then in the datasource it is something like this =Assembly.Class.Function(User!UserID).

I have done it myself, but in my case it was depending on the reportpath.|||

Interesting workaround, SteffoS. Thanks for the reply. That may be the best available. The only downside is that you have to actually use expression-based connection strings. And in doing that, there are two unfortunate problems:

1. Every report must call that expression instead of doing development as normal

2. (More importantly) you can't preview your dataset when it's tied to an expression-based connection string. The workaround to that is switching to a shared datasource during development, then making it an expression before you deploy, but that's a pain in the rear.

Anyone else have thoughts?

Exporting SPROCS

Ok, im trying to the difficult task of taking a DB i have on my laptop (SQL 2005) and putting it on my server (SQL 2000). How I got to this situation is somewhat od, just got roped in to do some part time development work.

Anyway heres my problem, I have transferred all the data and tables across no problems, but when I try to xfer my Stored Procedures across i get the following error.

Msg 208, Level 16, State 1, Line 1

Invalid object name 'sys.objects'.

I get to this point by going 'Generate Scripts' instead of Export Data, I then get it to generate the script making sure to click the generate as SQL 2000 property. Yet I still get this error. Any help you can offer is much appreciated. Thanks in advance

Dan

Hi,

I haven't tried to export sp's from 2005 -> 2000, but it seems that you have the wrong syntax in your sp's. Check out what syntax differences there are between 200 and 2005 regarding sysobjects etc.

What you are getting from "GENERATE AS SQL 2000" is that the wrapping t-sql is compliant with sql 2000, not what you have written in your sp's.

Regards