Creating a database connection string with a variable in MS SQL

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

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

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.

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

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.

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?

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

Oh, when I say "refer to it with brackets", I mean:

select *
from dbo.MyFunction() f

rather than

select *
from dbo.MyView v

Rob

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.

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?

What do you get when you run:

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

?

Rob

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..

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

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.

You used "FROM" though, right? select count(*) FROM stu

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

Rob

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"

I'm confused... I asked you:

What do you get when you run:

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

?

Rob

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.

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

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.

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

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.

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

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.

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

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.

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)