USE QCDB_160309

• mithun.gite

  SSCrazy

  Points: 2333

  More actions

  April 1, 2009 at 4:44 am

  #198742

  hi Friends,,

  I got stuck up a bit,,,

  I am passing my DB name dynamically to the variable.... and

  then concanate that variavble to USE @string like this but ..

  now if i want to pass the differnet server name also then how can we handle it???

  i means is there any way we can pass the server name also

  so like we selecting db using "USE" we can even select the server name also...

  Any help would be appreciated...

  Thanks in Advance

  Regards,

  Mithun Gite

• Lowell

  SSC Guru

  Points: 323518

  More actions

  April 1, 2009 at 4:58 am

  #969720

  In order to "Use" a server, a connection has to already be established...SSMS does not have any commands that you can use in TSQL to change or modify your connection...so you cannot save a script that has that info. Oracle can do that with a CONNECT command I think, but there is no equivalent in SQL

  the typical thing you might do, is have linked servers to other SQL servers. That link maintains the connection info, but you'll have to modify your scripts accordingly, because you have to name all your objects with the 4 part naming convention:

  SELECT * FROM OtherServer.DbName.SchemaName.TableName;

  a real example for me is Web1.Geo.dbo.Counties for example.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• mithun.gite

  SSCrazy

  Points: 2333

  More actions

  April 1, 2009 at 5:17 am

  #969730

  yeah but the problem with us is that we are planning to break the current db in two db and will be moving second part to differnet server .... so we want based on the dates user is selecting we will populate the server and database....

  using linked server is not seems like the solution as it will increase lot of work....

  any other thought???

  Mithun

• Lowell

  SSC Guru

  Points: 323518

  More actions

  April 1, 2009 at 6:15 am

  #969762

  it really depends...the devil is in the details.

  why are you breaking up related data into two separate servers? This looks like a red flag to me; are you archiving the data? why not archive to a db on the same server, especially if you seem to need to update it?

  a linked server is no big deal; you can update thru it as well;

  a good example might be creating an updatable view of a table that is on the other server:

  CREATE VIEW Whatever As SELECT * FROM RemoteServer.Databasename.dbo.AnyTableWithAPrimaryKey

  UPDATE Whatever...

  INSERT INTO Whatever...

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• GSquared

  SSC Guru

  Points: 260824

  More actions

  April 1, 2009 at 9:13 am

  #969965

  You can also create synonyms for remote objects. I've found that very useful in database refactoring, where I've needed to move a database or table to another server, or simply into another database on the same server.

  For example, if you have a linked server called MyServer2, with a database called MyOtherDatabase, and a table called MyRemoteTable, but you have code that accesses that table as "dbo.MyLocalTable". Well, create a synonym "create synonym dbo.MyLocalTable for MyServer2.MyOtherDatabase.MyRemoteTable". The code should just keep working.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• mithun.gite

  SSCrazy

  Points: 2333

  More actions

  April 1, 2009 at 11:08 pm

  #970369

  yeah this seems good one today i will be trying it will post it back the results,,,, thanks for it and also the reply earlier one also very help ful thanks for that also.....

  Thank u very much all sirssss.....

  Regards,

  Mithun Gite