Passing database names as parameters

Hi all,
I posted this yesterday (thanks Uri Diamant), but there has to be a better
solution.
I have a SQL 2005 instance with 4 dbs in it. In three databases, the structu
re
is identical (data is from 3 seperate companies). Is it possible to access
any of the three databases, from sprocs in the fourth (master) database,
so I don't have to rewrite all the sprocs three times?
Can I pass the database name to a stored procedure, eg
ALTER PROCEDURE [dbo].[usp_Clients_Get]
@.user_ref varchar(20),
@.database_ref varchar(50)
AS
BEGIN
SET NOCOUNT ON
SELECT @.database_ref.[Clients.Co_Name], @.database_ref.[Clients.Client_Ref]
FROM User_Client_Join INNER JOIN
@.database_ref.[Clients] ON [User_Client_Join.Client_Ref] = [Clients.Client_Ref]
WHERE [User_Client_Join].[User_Ref] = @.user_ref
END
I have many queries and I don't want a huge IF...ELSE structure.
Thanks in advance.
JulesJules
Well , you are probably thinjing about dynamic sql. I'm sure you are aware
of downsides of using dynamic sql
alter proc spproc
@.par int,
@.dbname sysname
as
declare @.str as varchar(100)
set @.str=('select * from '+@.dbname+'..orders where orderid='+cast(@.par as
varchar(10)))
exec (@.str)
exec spproc 10248,'northwind'
"Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
news:8e34528b4f0b8c81af33ffd7a0a@.news.microsoft.com...
> Hi all,
> I posted this yesterday (thanks Uri Diamant), but there has to be a better
> solution.
> I have a SQL 2005 instance with 4 dbs in it. In three databases, the
> structure is identical (data is from 3 seperate companies). Is it possible
> to access any of the three databases, from sprocs in the fourth (master)
> database, so I don't have to rewrite all the sprocs three times?
> Can I pass the database name to a stored procedure, eg
> ALTER PROCEDURE [dbo].[usp_Clients_Get] @.user_ref varchar(20),
> @.database_ref varchar(50)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT @.database_ref.[Clients.Co_Name], @.database_ref.[Clients.Client_Ref]
> FROM User_Client_Join INNER JOIN
> @.database_ref.[Clients] ON [User_Client_Join.Client_Ref] =
> [Clients.Client_Ref]
> WHERE [User_Client_Join].[User_Ref] = @.user_ref
> END
> I have many queries and I don't want a huge IF...ELSE structure.
> Thanks in advance.
> Jules
>|||Hi Uri,
The solution you suggest works! What are the downsides of using dynamic sql
in a sproc. If they are the same as in VB, and this is the only way I can
implement this, this will have to do.
Regards
Jules
> Jules
> Well , you are probably thinjing about dynamic sql. I'm sure you are
> aware
> of downsides of using dynamic sql
> alter proc spproc
> @.par int,
> @.dbname sysname
> as
> declare @.str as varchar(100)
> set @.str=('select * from '+@.dbname+'..orders where orderid='+cast(@.par
> as
> varchar(10)))
> exec (@.str)
> exec spproc 10248,'northwind'
> "Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
> news:8e34528b4f0b8c81af33ffd7a0a@.news.microsoft.com...
>|||Downside is performance wise. Since the SP is dynamic, the SQL Server has to
recompile it every time it is called. The normal SP is compiled once and
stored in the cache so it is much quicker to use. It may not make any
difference for you so give it a shot.
"Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
news:8e34528b91598c81afa7fcca47e@.news.microsoft.com...
> Hi Uri,
> The solution you suggest works! What are the downsides of using dynamic
> sql in a sproc. If they are the same as in VB, and this is the only way I
> can implement this, this will have to do.
> Regards
> Jules
>
>|||Thanks Uri & Grant,
I'd rather do it the non-dynamic way, if is this possible. My client could
be adding more databases in the future.
Regards
Jules
> Downside is performance wise. Since the SP is dynamic, the SQL Server
> has to recompile it every time it is called. The normal SP is compiled
> once and stored in the cache so it is much quicker to use. It may not
> make any difference for you so give it a shot.
> "Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
> news:8e34528b91598c81afa7fcca47e@.news.microsoft.com...
>|||Jules,
I think you can achieve this using dynamic SQL, but you will have to create
the SQL command in a string.
e.g. looking at the first bit of your first line.
DECLARE @.SQL nvarchar(1000)
SET @.SQL = 'SELECT ' + @.Database_ref + ".[Clients.Co_Name], ' + etc.....
EXEC (@.SQL)
Robin Hammond
www.enhanceddatasystems.com
"Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
news:8e34528b4f0b8c81af33ffd7a0a@.news.microsoft.com...
> Hi all,
> I posted this yesterday (thanks Uri Diamant), but there has to be a better
> solution.
> I have a SQL 2005 instance with 4 dbs in it. In three databases, the
> structure is identical (data is from 3 seperate companies). Is it possible
> to access any of the three databases, from sprocs in the fourth (master)
> database, so I don't have to rewrite all the sprocs three times?
> Can I pass the database name to a stored procedure, eg
> ALTER PROCEDURE [dbo].[usp_Clients_Get] @.user_ref varchar(20),
> @.database_ref varchar(50)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT @.database_ref.[Clients.Co_Name], @.database_ref.[Clients.Client_Ref]
> FROM User_Client_Join INNER JOIN
> @.database_ref.[Clients] ON [User_Client_Join.Client_Ref] =
> [Clients.Client_Ref]
> WHERE [User_Client_Join].[User_Ref] = @.user_ref
> END
> I have many queries and I don't want a huge IF...ELSE structure.
> Thanks in advance.
> Jules
>|||Read this to know more about Dynamic SAL
http://www.sommarskog.se/dynamic_sql.html
Madhivanan|||Another option you could use is to make mirror copies of procedures in the
customer database which do most of the logic you are looking to impliment
then simply call the procedure in dynamic code. Make sure to use the
sp_executesql call to call the procedure:
@.sql = 'exec ' + @.database_ref + 'dbo.ClentDataProcedure'
exec sp_executesql @.stmt = @.sql
This could mitigate some of the issues with some dynamic implimentations as
the code on the customer db would not itself be dynamic and using
sp_executesql allows for both stored query plans. In addition you could pas
s
through parameters to the dynmic procedure call (see bol under sp_executesql
for the gory details)
--Tony
"Jules Wensley" wrote:

> Thanks Uri & Grant,
> I'd rather do it the non-dynamic way, if is this possible. My client could
> be adding more databases in the future.
> Regards
> Jules
>
>
>|||On Tue, 21 Mar 2006 12:31:23 +0000 (UTC), Jules Wensley wrote:

> What are the downsides of using dynamic sql
>in a sproc.
http://www.sommarskog.se/dynamic_sql.html
Hugo Kornelis, SQL Server MVP

Passing ArrayList into Sql Query or any other way around

Hi

I have an arraylist that contains the names of those userid's that i need to check in an online db to check out if they are online now.
If the UserId column of the SqlTable matches any of the name of that of the arraylist, then i need to import the values of two corresponding fields say age, Nick etc.
I would be very grateful to anyone who could kindly tell me how to do this in an Sql Query i.e How to actually send an arraylist into sql query or any other way around this problem.

Say the arraylist to be verified against the table is:
public ArrayList BuddyList = new ArrayList();

Thank You.

Regards.

Check this, it would treat your issue in different way
http://weblogs.asp.net/pleloup/archive/2003/04/14/5569.aspx
HTH

Passing & Using variables in Stored Procs for table names

Hi all
SQL 2k, win 2kpro, vbscript asp
I'm just getting into stored procs and coming on quite good imho :)
but I'm stuck on a little problem
I want to pass a stored proc a variable eg "G001_"
create procedure dbo.myproc
@.prefix vchar (20)
as
I want to use the "prefix within the stored proc on table names I'm
wanting to grant access for a user on several tables with this prefix
eg
grant select on @.prefix+tablenameOne to UserName
grant select on @.prefix+tablenameTwo to UserName
grant select on @.prefix+tablenameThree to UserName
grant select, update on @.prefix+tablenamefour to UserName
grant select on @.prefix+tablenamefive (col1, col2) to UserName
Would look like this if fixed:
grant select on G001_tablenameOne to UserName
grant select on G001_tablenameTwo to UserName
etc.
but I cant seem to get this to work... How can I do this?
The tables will be created from a different stored proc and then I
want to grant users to be able to use them tables
thanks for any help.
Alyou will have to execute the 'GRANT' statment using EXEC.
Ex:
exec ('grant select on ' + @.prefix + ' tablenameOne to UserName')
--
-Vishal
"Harag" <harag@.softhome.net> wrote in message
news:5175kv4u71063eekbo7i46bjl03u0ok7um@.4ax.com...
> Hi all
> SQL 2k, win 2kpro, vbscript asp
> I'm just getting into stored procs and coming on quite good imho :)
> but I'm stuck on a little problem
> I want to pass a stored proc a variable eg "G001_"
>
> create procedure dbo.myproc
> @.prefix vchar (20)
> as
>
> I want to use the "prefix within the stored proc on table names I'm
> wanting to grant access for a user on several tables with this prefix
> eg
> grant select on @.prefix+tablenameOne to UserName
> grant select on @.prefix+tablenameTwo to UserName
> grant select on @.prefix+tablenameThree to UserName
> grant select, update on @.prefix+tablenamefour to UserName
> grant select on @.prefix+tablenamefive (col1, col2) to UserName
> Would look like this if fixed:
> grant select on G001_tablenameOne to UserName
> grant select on G001_tablenameTwo to UserName
> etc.
> but I cant seem to get this to work... How can I do this?
> The tables will be created from a different stored proc and then I
> want to grant users to be able to use them tables
> thanks for any help.
> Al

Pass record to user-defined function and create xml

Does anyone know if there is a way to pass a record from a select to a
user defined function in SQL Server 2000? The number and names of the
columns in the record will vary depending on the upload temp table
selected from... You should be able to see what I am trying to
accomplish bellow... If you have another idea, I'm open to
suggestions.
Example:
Declare @.CurrentFiscalYear smallint
Set @.CurrentFiscalYear = 2005
Create table ClaimEditLog(
TransactionId int,
XMLData varchar(7500),
EditDesc varchar(200),
LastUpdateId varchar(20),
LastUpdate datetime
)
Create Table #tbClaimUploadData(
TransactionId int IDENTITY (1, 1) NOT NULL ,
FiscalYear smallint,
AmountTypeId int,
Amount money
)
Insert #tbClaimUploadData
Select 2005, 2, 556.98
Insert #tbClaimUploadData
Select 2006, 2, 56.90
Insert into ClaimEditLog
Select TransactionId,
dbo.UDF_ConvertRecordToXML(*),
'The Fiscal Year is incorrect.',
'jporscha',
GetDate()
>From #tbClaimUploadData
Where FiscalYear <> @.CurrentFiscalYear
-- UDF_ConvertRecordToXML - Convert record to XML
Select * from ClaimEditLog
--Output
2,
'<XMLData><Record><TransactionId>2<TransactionId><FiscalYear>2006</FiscalYea
r><AmountTypeId>2</AmountTypeId><Amount>56.90</Amount></Record></XMLData>',
'The Fiscal Year is incorrect.',
2006-05-31 06:41:32.527You could store the complete record and use a computed column to give a XML
representation of the output...
So your table definition would be this...
Create table ClaimEditLog(
TransactionId int,
FiscalYear smallint,
AmountTypeId int,
Amount money,
XMLData AS
'<XMLData><Record><TransactionId>'
+ CAST( TransactionId as varchar(20) )
+ '<FiscalYear>' + CAST( FiscalYear AS char(4) ) + '
etc...',
EditDesc varchar(200),
LastUpdateId varchar(20),
LastUpdate datetime
)
Your insert would be...
insert claimeditlog (
TransactionId,
FiscalYear,
AmountTypeId,
Amount,
EditDesc,
LastUpdateId,
LastUpdate )
select TransactionId,
FiscalYear,
AmountTypeId,
Amount,
'The Fiscal Year is incorrect.',
'jporscha',
GetDate()
from #tbClaimUploadData
Select * from ClaimEditLog
And you'd get the XMLData output as text XML.
Make sense?
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
<porsch55@.yahoo.com> wrote in message
news:1149077706.631013.220560@.y43g2000cwc.googlegroups.com...
> Does anyone know if there is a way to pass a record from a select to a
> user defined function in SQL Server 2000? The number and names of the
> columns in the record will vary depending on the upload temp table
> selected from... You should be able to see what I am trying to
> accomplish bellow... If you have another idea, I'm open to
> suggestions.
> Example:
> Declare @.CurrentFiscalYear smallint
> Set @.CurrentFiscalYear = 2005
> Create table ClaimEditLog(
> TransactionId int,
> XMLData varchar(7500),
> EditDesc varchar(200),
> LastUpdateId varchar(20),
> LastUpdate datetime
> )
> Create Table #tbClaimUploadData(
> TransactionId int IDENTITY (1, 1) NOT NULL ,
> FiscalYear smallint,
> AmountTypeId int,
> Amount money
> )
> Insert #tbClaimUploadData
> Select 2005, 2, 556.98
> Insert #tbClaimUploadData
> Select 2006, 2, 56.90
> Insert into ClaimEditLog
> Select TransactionId,
> dbo.UDF_ConvertRecordToXML(*),
> 'The Fiscal Year is incorrect.',
> 'jporscha',
> GetDate()
> Where FiscalYear <> @.CurrentFiscalYear
> -- UDF_ConvertRecordToXML - Convert record to XML
> Select * from ClaimEditLog
> --Output
> 2,
> '<XMLData><Record><TransactionId>2<TransactionId><FiscalYear>2006</FiscalY
ear><AmountTypeId>2</AmountTypeId><Amount>56.90</Amount></Record></XMLData>'
,
> 'The Fiscal Year is incorrect.',
> 2006-05-31 06:41:32.527
>

pass filename

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

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

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

Within your loop:-

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

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

Hope that makes sense.

|||

This is the error I get:

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

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

Yes. Still the same error.

These are my settings

SQLSourceType: DirectInput

ParameterMapping --> User::FileName

datatype: varchar

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

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

Cheers

|||

Solved.

Had to replace 0 with ?

Thanks guys.

|||

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

Good luck!

Pass a "Begin...End" Block from ASP

Is it okay to pass a Begin...End block to Sql Server from an ASP web page?
I
have a situation where one of my tables contains the column names that I nee
d
to select from another table. I have always used two separate select
statements (with two separate trips to the db) to get the values I need, but
I recently found that I can accomplish the same thing by passing a
Begin...End block like this...
begin
declare @.col_list varchar(8000)
select @.col_list = coalesce(@.col_list + ', ', '') +
approverlabel from approvers
where formid=6 order by approverorder
exec('select ' + @.col_list + ' from formconfigs where pid=2701')
end
Is there a reason why this should not be done? I realize this would be
better if it was implemented in a stored procedure.Yes this would be best in a stored procedure so it can reuse the query plan.
But in any case you don't need a BEGIN - END. If you send it as one batch
it will work fine.
Andrew J. Kelly SQL MVP
"creed1" <creed1@.discussions.microsoft.com> wrote in message
news:75660A09-951F-4D34-88BE-BFC26E13FED7@.microsoft.com...
> Is it okay to pass a Begin...End block to Sql Server from an ASP web page?
> I
> have a situation where one of my tables contains the column names that I
> need
> to select from another table. I have always used two separate select
> statements (with two separate trips to the db) to get the values I need,
> but
> I recently found that I can accomplish the same thing by passing a
> Begin...End block like this...
> begin
> declare @.col_list varchar(8000)
> select @.col_list = coalesce(@.col_list + ', ', '') +
> approverlabel from approvers
> where formid=6 order by approverorder
> exec('select ' + @.col_list + ' from formconfigs where pid=2701')
> end
> Is there a reason why this should not be done? I realize this would be
> better if it was implemented in a stored procedure.