Solved

Creating a database connection string with a variable in MS SQL

Posted on 2009-07-07
28
822 Views
Last Modified: 2012-05-07
Hello experts,

I'm working on a project where I need to have a cross-database reference in my query. (remote database actually resides on a completely different server.). I have setup linked servers, so that's not a problem.

Here's my challenge though. Our DBAs create a new database name for each school year, which means, any scripts that i have pointing to the year would have to be repointed correctly.
I have created a "middleware" database for my purposes, in which I create a table that references the database version in the remote database. I'm able to query that field, and get that value updated automatically within my script by simply changing the database reference value.

Now here's my question. In some instances, where I'm actually running an SQL cross-database query, I have a need to reference the remote database name, which of course, if this is not a variable, I have to revisit any scripts that have this kind of query in them, and change it manually.

I've never done any work with queries that contain variables within SQL, so please be gentle :)
I have declared the @DB variable on top, what I'm trying to do is to reference that variable in place of "dbname" in sissql.dbname.dbo.

Simply replacing the db name with the variable like so: sissql.@DB.dbo.CAR didn't seem to work.

Is there any way I could fairly easily do this?

TIA!
Georges,
DECLARE @DB varchar(50)

SET @DB = (Select (AeriesDBReference) FROM AeriesSettings) 
 

SELECT     ENR.ID AS enrID, STU.FN AS stuFN, STU.MN AS stuMN, STU.LN AS stuLN,ENR.SC AS enrSC, ENR.GR AS enrGR, STU.BD AS stuBD, STU.ZC AS stuZC, STU.TL AS stuTL, STU.LS AS stuLS, ENR.YR AS enrYR, 

                      ENR.ED AS enrED, ENR.LD AS enrLD, ENR.DTS AS enrDTS, CAR.CN AS carCN, 

                      CAR.DS AS carDS, CAR.DE AS carDE, CAR.DTS AS carDTS

FROM         sissql.dbname.dbo.CAR AS CAR LEFT OUTER JOIN

                      sissql.dbname.dbo.ENR AS ENR INNER JOIN

                      sissql.dbname.dbo.STU AS STU ON ENR.SC = STU.SC AND ENR.SN = STU.SN INNER JOIN

                      dbo.AssetInfo ON ENR.SC = dbo.AssetInfo.SchoolNumber ON CAR.SC = ENR.SC AND CAR.SN = ENR.SN

WHERE     (ENR.DEL = 0) AND (ENR.DTS > DATEADD(day, - 1, { fn NOW() })) AND (ENR.YR = 2009) AND (ENR.SC < 600) AND (STU.DEL = 0) OR

                      (ENR.DEL = 0) AND (ENR.YR = datepart(year, getdate())-1) AND (ENR.SC < 600) AND (STU.DEL = 0) AND (CAR.DTS > DATEADD(day, - 1, { fn NOW() })) AND 

                      (dbo.AssetInfo.SchoolNumber = ENR.SC)

ORDER BY enrDTS DESC

Open in new window

0
Comment
Question by:cvservices
  • 14
  • 14
28 Comments
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
Unfortunately this means using dynamic SQL, constructing the query and the running it using sp_executesql

Or... you could probably set up a linked server to yourself, connecting to a different database? Then you still use the same link, but it links to a different place.

Rob
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
Sorry - missed that you're already using linked servers.

So... can you connect using a different default database, and just use: sissql..dbo.STU  (to mean 'default database')?

Rob
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Hm.  I guess both of those suggestions are beyond what I've done. I'm not quite sure what the sp_executesql does. I will google that.

As for the linked server, I actually am using the linked server (remote database) to my own local SQL database. I'm not sure if this is what you were referring to. Again, your suggestions were a bit vague for my level of knowledge on this topic. sorry.

I will research some more regarding the dynamic SQL to see if that'll lead me anywhere though.
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
Well, you can do this:

DECLARE @qry nvarchar(max);

SELECT @qry =
'SELECT     ENR.ID AS enrID, STU.FN AS stuFN, STU.MN AS stuMN, STU.LN AS stuLN,ENR.SC AS enrSC, ENR.GR AS enrGR, STU.BD AS stuBD, STU.ZC AS stuZC, STU.TL AS stuTL, STU.LS AS stuLS, ENR.YR AS enrYR,
                      ENR.ED AS enrED, ENR.LD AS enrLD, ENR.DTS AS enrDTS, CAR.CN AS carCN,
                      CAR.DS AS carDS, CAR.DE AS carDE, CAR.DTS AS carDTS
FROM         sissql.' + @DB + '.dbo.CAR AS CAR LEFT OUTER JOIN
                      sissql.' + @DB + '.dbo.ENR AS ENR INNER JOIN
                      sissql.' + @DB + '.dbo.STU AS STU ON ENR.SC = STU.SC AND ENR.SN = STU.SN INNER JOIN
                      dbo.AssetInfo ON ENR.SC = dbo.AssetInfo.SchoolNumber ON CAR.SC = ENR.SC AND CAR.SN = ENR.SN
WHERE     (ENR.DEL = 0) AND (ENR.DTS > DATEADD(day, - 1, { fn NOW() })) AND (ENR.YR = 2009) AND (ENR.SC < 600) AND (STU.DEL = 0) OR
                      (ENR.DEL = 0) AND (ENR.YR = datepart(year, getdate())-1) AND (ENR.SC < 600) AND (STU.DEL = 0) AND (CAR.DTS > DATEADD(day, - 1, { fn NOW() })) AND
                      (dbo.AssetInfo.SchoolNumber = ENR.SC)
ORDER BY enrDTS DESC
';

exec sp_executesql @qry;

--Noticing particularly the bits where it says:   ' + @DB + '

This should work just fine. But I wonder if changing the default database on the remote server might do the job too, with less overhead.

Rob
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Rob,
Yes, If I understand you correctly, the only default database that I can use is mine, since I have very limited control over the remote database, so I can't make IT the default database. Even if I do though, I would have the same problem coming back to my local database (as being the remote database).

I'm able to build the connection string with a script without a problem. (meaning the ODBC System DSN, for instance)
 
But if I were to create that same query to build a view that shows me a remote database, i'm only able to create that query with a static reference to the database name. I was hoping to be able to create that query with the variable name referencing that database, as in the initial example I gave.

As a side issue I'm having, is that I'm trying to create a cross-database constraint, which I'm not quite sure how to do. Aside from creating the constraint, I would suspect I'd have to make the same type of linked server connection within that query as well.

As an example of that:
I have my remote (authoritative) database which has course listings and information.
I have a local db table which contains a select number of courses I'm interested in, which will be manually populated. However, I want to create a constraint in my local db field to only allow values from the master course list, which resides in the remote database. hope that makes sense.
(sorry if this one kind of went on a tangent), I guess I could put this in a separate question.
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Ahhh . ok, so your solution may work. I think I just wasn't aware of the sp_executesql in combination with having variables within the query. I was also omitting the single quotes around the variable.

As for changing the default database, I'm not sure that this works, because, I have a particular piece of software (User Management Resource Administrator) which uses i setup to use an ODBC connection, and the database name is within the that ODBC connection string, so even if I change the ODBC connection itself, I'm not sure that the already existing connection string will like a different default database name specified in it. (i have A LOT of these connection strings in my scripts), that's why I resorted to populated my database with a table specifically referencing the database name of the remote database. this works at least for the connection string situation. But I think that your  suggested solution may end up being what I need.

Would what you suggested work in creating a query for a View?
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
Unfortunately you won't be able to use dynamic SQL for a view, because it's multi-statement.

You could use a table-valued function, and then refer to it with brackets. You won't be able to take advantage of simplification though (ie, it will run your function, and use the results of that in your outer query, rather than being able to look for smarter ways to simplify it).

On the default database subject... if you took the database name out of the connection string, it should connect to the default database of the user (which could then be changed more easily perhaps). But I agree that it's probably more of a headache if you have other software that sets these things up.

Rob
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
Oh, when I say "refer to it with brackets", I mean:

select *
from dbo.MyFunction() f

rather than

select *
from dbo.MyView v

Rob
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Ok, you lost me again on the table-valued function :) ... however, I think It got clearer what you were saying regarding the default database. in my case, that may actually work out, and eliminate the need for me to have to query my local database for the remote database name, and just change out my server ODBC connection to change all my script pointers. I 'm going to give that a shot and see how that works out.
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Ok, so I've been experimenting with the default catalogue suggestion you made...
my connection string looks like that now:
Provider=MSDASQL.1;
Persist Security Info=True;
Extended Properties="DSN=AeriesDB;
UID=;
APP=UMRA Console - User Management Resource Administrator;
WSID=DO-GMK-VM00;
DATABASE=;
Trusted_Connection=Yes";
Initial Catalog=

Prior to the change, the database name showed up after DATABASE= and Initial Catalog=

When I removed the database name, and run the query, oddly, I actually don't get an error, but also don't get a result at all. The default database set within the ODBC connection itself is pointing to the database I'm interested in querying. Is there something wrong I'm doing? perhaps there's some sort of "default" string that I need to place after the DATABASE= to tell the connection string to point to the default database?
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
What do you get when you run:

select count(*) from sissql..dbo.CAR

?

Rob
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Hm interesting result. I did select count(*) STU      ( sissql name is only accessibly from a private network, which I can't get to over VPN).
Either way though, without the database name specified, the result was 0 (no error though), with the database name, I got 11000+ records..
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
If you did:
select count(*) STU

That would give you a column called STU, and won't go near the linked server at all.

Is the AeriesDB DSN including a database name?

Rob
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Yes, that's correct. at this point, I was mostly just testing out your default database theory, in isolation of the linked server.
So basically, my ODBC connection is straight to the remote database with a default database set to DST09000CVUSD

My ODBC String, is without a database specification. According to your theory, this should mean that it should take whatever the ODBC connection's default is.

the query I did for that, was select count(*) STU, which is, indeed, a query to DST09000CVUSD. so that connection is even simpled than a linked server (one less variable to worry about at this point, and yet it still didn't work. I'm sure it's some small detail that I'm forgetting about.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
You used "FROM" though, right? select count(*) FROM stu

I have to disappear - end of my day, no more breaks to take.

Rob
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Oh .. haha. yes yes, of course, I guess for some reason I've been typing it without the FROM on here without thinking, as yours didn't have the FROM in it as well. Obviously though, without the FROM, the query would fail miserably :) I don't have that window  where I did the test saved, but since I got an actual result, Im' positive that I put the "FROM"
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
I'm confused... I asked you:

What do you get when you run:

select count(*) from sissql..dbo.CAR

?

Rob
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Yes, I did run:
SELECT Count(*) from dbo.CAR
(I don't need to put sissql, because the DSN connection is to the sissql database.

When I run the query without specifying the database name.i.e:  DATABASE=;
the result is 0

When I run the query and specifying the database name: i.e: DATABASE=DST09000CVUSD;
I get 111476

I would've expected the first one to actually error out, not just return 0 rows.
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
Hmm... try running it through SQL. I think perhaps however you're connecting to the DSN, it may be inferring a database some other way.

And yes, I would've expected an error along the lines of "What CAR table?"

Rob
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Through SQL it works. I'm assuming you're referring to running through my DBMS, right? if so. is there a way I can use that same ODBC DSN for my database connection within the DBMS? I usually just connect straight to my SQL server with my credentials; in which case, the query works, and returns the 111476.

It's the omitting of the database name within the string DSN that is causing this query to fail.
I will try to call the manufacturer of the software and see if they've used anything like that before.
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
No, I was meaning via the linked server connection.

I'll try to find some time to investigate using default databases via a linked server using DSNs myself. It should all be fine, but there may be something.

Rob
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
You know, one reason i've been hesitant to go that route in testing, is because something happens within the linked servers that I'm not understanding, so maybe if you can help me understand this, I can go along with you more in what you're trying to have me test. It's potentially a network thing.

You see, my SQL servers are on a 192.168. isolated network, and Clustered. The cluster name is sissql.
I am on a 10.40. network, which has no direct access to the 192.168. network.

an nslookup of sisql, actually returns the 192.168. address, so it is in DNS, but pointing to its own network.

Somehow, however, when I'm connecting via a linked server statement INSIDE a query, I refer to the query by the server name as such:
SELECT * FROM sissql.databasename.dbo.tablename  AS STU
and that works, so somehow, the dbms, and/or the query, have a reference to sissql that is not IP related, but logical to the SQL DBMS ? ...

For some reason, anytime I try to run this kind of query within my application, it does not accept this query as a trusted connection, meaning, passing through the ODBC connection's credentials. Instead, I need to explicitly specify the username and password with which I want to connect to the database, my local one, not the linked one.  In other words, my connection string HAS to include a username and password in it, and that's the only case where I ever am forced to do it. every other time, I can use the ODBC connection while using a "Trusted Connection" and all works fine, and the username and password never show up in the connection string. In my mind, this is more a security issue than anything else.

That being said, back to what you were trying to have me do, and the reason why it won't work.
The whole purpose of our testing, is to try and specify a default database.
Let me define those real quick:
Local server: do-mgtweb  Database: UserNameStore
Remote Server: sissql Database: <remotedatabasename>

If I go and run the query as you asked, and in order for your query to work exactly as you wrote it, meaning.. :
Select Count(*) FROM sissql..dbo.CAR
I would have to be connected to the Local Server, since I'm calling to the Remote Server. otherwise, there would be no need for it.
In the DSN connection to the Local Server, I have no need to mess with default databases, because my database name never changes. it's UserNameStore, and will always be there, it's the <remotedatabasename> that changes every year, and I would need the default database business to work only when I'm connecting directly to that database via its ODBC connection, not through linked servers.

I think this whole thing got confused a bit, because we sort of mixed up two questions, initially about putting variables within linked server names, and now we're discussing Default databases within an ODBC string.

Wow, I just realized how long this post is. Sorry about that.

0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
Just checking...

Within SQL you must have a linked server called sissql, which is using an odbc provider and a dsn.

The linked server will also contain authentication info.

Without the linked server (under 'server objects' in Mgmt Studio), SQL can't see sissql.

So however the linked server connects, that user must have a default database.

Rob
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Yes, I do have a linked server under the Server Objects/Linked Servers. the command I used to do this is the following:
sp_addlinkedserver @server='do-mgtweb', @srvproduct='SQL Server'  on sissql.

I did this after verifying that I have a user that does have access rights on on sissql, which I basically created an identical user on sissql, as the one that exists on do-mgtweb, and I gave it the rights it needs on the sissql database.

During this whole process, I never had to create an ODBC connection, and all queries worked without a problem.
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
Yup... so now can you query it without providing a database name?

select count(*)
from sissql..dbo.CAR

...to rely on the default database of the user that you're connecting as? (And this is WITHOUT using a DSN)

Rob
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
ok.. let me tell you what i CAN run:

from do-mgtweb:
select count(*) from [sissql].[databasename].dbo.CAR

from the remote database, I can run:
select count(*) from databasename..dbo.CAR

these are the only two that will actually work.
I'm not sure if referring to sissql alone without a reference to the database would work, as I would think I would need to tell it the db name, in addition to the server to target.

Hope that makes sense.

By the way, both of those queries work fine without any issues.
0
 
LVL 14

Accepted Solution

by:
rob_farley earned 125 total points
Comment Utility
I'm a bit confused about which machine is which, so let's call them Server1 and Server2.

Server1 is the one you connect to in Mgmt Studio. It has a Linked Server object in it, which we'll call LinkSrvObj.

Server2 is where the Linked Server points to. It contains a database that has a table called dbo.CAR.

So when you open Mgmt Studio, you connect to Server1, and run:

select count(*) from LinkSrvObj..dbo.CAR;

So... I'm not really sure which one is "do-mgtweb" - Server1 or Server2.

And... I think for this you may need to set your system to NOT use the "SQL Server" type of linked server. Try using the OLE DB for SQL Server, and seeing how that goes. Or even try OLE DB for ODBC, using your DSN.

At some point, you should be able to browse through the catalog of tables on the linked server. When you can, find the table you want, right-click on it and choose "Script as SELECT". But... you want your linked server to be set up in a way that when you do this scripting, it doesn't put the database name in there (which used to be okay if you had done it through a SQL Server link, but I think now you may need an OLE DB connection to make it work).

Hope this helps - let me know which server is Server1 and which is Server2.

Rob
0
 
LVL 1

Author Comment

by:cvservices
Comment Utility
Rob,
yes, you're correct.
Server 1 (do-mgtweb) is my local SQL server, and that is in fact the one that I connect to in Mgt. Studio
and Server2  (sissql) is where the Linked Server points to, and that is the database that contains dbo.car.

Now if I run the query:
select count(*) from sissql..dbo.CAR

I get this error:

Msg 7314, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI10" for linked server "sissql" does not contain the table ""dbo"."CAR"". The table either does not exist or the current user does not have permissions on that table.

I kind of expect that, because, nowhere in the linked Server object, did I specify the database, so I don't think this query knows which database to connect to.
I did try to do something like this: (mostly because it made logical sense to me, though I think the syntax is wrong)

select Count(*) FROM sissql..[DST09000CVUSD].dbo.CAR
But in this case, I got this error:

Msg 117, Level 15, State 1, Line 1
The object name 'sissql..DST09000CVUSD.dbo.CAR' contains more than the maximum number of prefixes. The maximum is 3.

When i expand "Catalogs" under the linked server, and try to expand "System Catalogs", which by the way, is the only one under Catalogs. I don't see databases under there. I get a permission denied error. but somehow, I'm still able to run my query cross server without a problem.

Hope this cleared things up just a bit?

By the way, all of those are setup with no ODBC connection (Not OLE DB for ODBC  nor OLE DB for SQL)



0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now