February 29, 2012 at 8:15 am
Here is the challenge. We provide custom reports/scripts for 100+ clients across the US where they are all independently owned companies running the same ERP system. So our scripts work across all these independent databases if we install them directly into each clients main erp database. I'd like to implement a solution whereby we would have our own consistent database at each client that would store all our procedures / scripts and have those run against each clients live ERP database so that we would no longer have to actually install our scripts into the ERP database.
The challenge is that each clients DatabaseName is different, so the fully qualified table name would vary for every client at the [DatabaseName] level. So that is the challenge, how we can write our stored procedures / scripts so that we don't have to manually reset the databasename piece of the fully qualified table name. I'm open to any suggestions as to how best implement this strategy in MSSQL (2005 & Higher)...thru aliasing, etc where procs in our database can execute against tables in a variable database (same server instance).
February 29, 2012 at 8:37 am
You would have to use dynamic sql for every single query. I don't know that is the way I would go with it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 29, 2012 at 8:59 am
Agree with Sean...
You could create some sort of a Lookup table to store details about each client ie.(ServerName, DBName, Schema, etc) and then create queries that use the lookup information to populate parameters in a stored-procedure (pass them in), and/or pass in the info needed as parameters to everything
Perhaps something along the lines of the code below, except of course your reporting procedure would accept in the values needed as parameters, this would make it pretty easy to do (as long as you don't mind using dynamic SQLDECLARE @sql nvarchar(1500), @DB nvarchar(50), @Schema nvarchar(3)
SELECT @DB = 'msdb', @Schema = 'dbo'
SET @sql ='
SELECT
job_id,
name,
enabled,
description
FROM ' + @DB + '.' + @Schema + '.sysjobs'
EXECUTE sp_executeSQL @sql
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 29, 2012 at 9:52 am
I haven't had time to check out any strange implications this may have but I would experiment with synonyms
You can create a synonym for a dbname.schema.name.tablename combination, so your code could still just refererence the single name. Creating a script of "create synonym" statetements could be a one-off job at installation time and you would include the dbname in that generated script.
Certainly easier than dynamic sql I would think.
Mike
February 29, 2012 at 10:30 am
Thanks Mike...i think i'm going to try that as it's more in line of what i was looking for.
And thanks to everyone else on the dynamic sql. I was trying to avoid compiling large Strings to exec but figured that was the only way so utilize the dynamics.
February 29, 2012 at 11:50 am
Mike, one question on the Synonyms. What is their "lifetime" in the server ...meaning if i create the synonym how long will the server track that synonym for me and does the synonym exist as a global variable or just until i either drop it OR close down the SQL session.? Just wondering if i should treat them like temp tables that i typically drop once the stored procedure has run...
March 1, 2012 at 12:38 am
Hi Michael
Synonyms are permanent, so exist until dropped.
From your original description I would have thought you could create them all once on an installation, and the code remains the same for all time. Only drop and recreate if the database name changes.
Good luck, I would be interested to know how it works out.
Mike
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy