We have a SQL 2000 Database in both a production and test environment.
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?
You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegr oups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>
|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?
|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegr oups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>
Showing posts with label dynamic. Show all posts
Showing posts with label dynamic. Show all posts
Wednesday, March 28, 2012
Production vs. Test Environment, Linked Servers = Lots of Dynamic SQL!
We have a SQL 2000 Database in both a production and test environment.
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegroups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Hi
I am not sure why you would need to change this unless the linked servers
had different names.
John
"bdtmike" wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Well, they do have separate names (they are separate servers).
John Bell wrote:
> Hi
> I am not sure why you would need to change this unless the linked servers
> had different names.
> John
> "bdtmike" wrote:
> > We have a SQL 2000 Database in both a production and test environment.
> > There are numerous processes and procedures in this database that must
> > communicate cross-server with another SQL 2000 database. This other
> > server also has a corresponding production / test environment.
> >
> > There are many references in my database to the linked server (dts &
> > sprocs). Add to this, when my database is being operated in the test
> > environment, the procedures and processes must reference the
> > corresponding test server for the other database. In other words, my
> > production database on my production server must reference their
> > production database on their production server. My database in the
> > testing server must reference their database in the testing server.
> >
> > I'd love to keep both databases on the same server for test &
> > production, but for business reasons, it simply can't be done.
> >
> > My problem is that when I move my production into testing (and back), I
> > must go through and meticulously change all the references in my
> > sprocs. Failure to change them all would be disasterous. Instead, I
> > wanted a dynamic way to do this so that the reference to the linked
> > server could be a variable which is set, depending on whether I'm
> > Production or Testing.
> >
> > Unfortunately, table names in SQL statements cannot be variable so what
> > I end up with is a TON of dynamic SQL. Certainly there must be a
> > better way to do this!
> >
> > Anybody have encountered this situation before?
> >
> >|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegroups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>|||But the linked server name can be the same!
Check out example 2 in the sp_addlinkedserver examples in BOL, specify
different data sources and the same linked server name.
John
"bdtmike" wrote:
> Well, they do have separate names (they are separate servers).
> John Bell wrote:
> > Hi
> >
> > I am not sure why you would need to change this unless the linked servers
> > had different names.
> >
> > John
> >
> > "bdtmike" wrote:
> >
> > > We have a SQL 2000 Database in both a production and test environment.
> > > There are numerous processes and procedures in this database that must
> > > communicate cross-server with another SQL 2000 database. This other
> > > server also has a corresponding production / test environment.
> > >
> > > There are many references in my database to the linked server (dts &
> > > sprocs). Add to this, when my database is being operated in the test
> > > environment, the procedures and processes must reference the
> > > corresponding test server for the other database. In other words, my
> > > production database on my production server must reference their
> > > production database on their production server. My database in the
> > > testing server must reference their database in the testing server.
> > >
> > > I'd love to keep both databases on the same server for test &
> > > production, but for business reasons, it simply can't be done.
> > >
> > > My problem is that when I move my production into testing (and back), I
> > > must go through and meticulously change all the references in my
> > > sprocs. Failure to change them all would be disasterous. Instead, I
> > > wanted a dynamic way to do this so that the reference to the linked
> > > server could be a variable which is set, depending on whether I'm
> > > Production or Testing.
> > >
> > > Unfortunately, table names in SQL statements cannot be variable so what
> > > I end up with is a TON of dynamic SQL. Certainly there must be a
> > > better way to do this!
> > >
> > > Anybody have encountered this situation before?
> > >
> > >
>|||Yes, in theory they could. But I'm saying that in my situation they
arent' the same name. And the chance that I'm going to get this
organization to change their server name just for me is...Zero.|||Any changes to code after it has been tested introduces a risk which they
should be made aware off. If this risk is removed by having a better
configuration then it should be acceptable.
John
"bdtmike" wrote:
> Yes, in theory they could. But I'm saying that in my situation they
> arent' the same name. And the chance that I'm going to get this
> organization to change their server name just for me is...Zero.
>
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegroups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Hi
I am not sure why you would need to change this unless the linked servers
had different names.
John
"bdtmike" wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Well, they do have separate names (they are separate servers).
John Bell wrote:
> Hi
> I am not sure why you would need to change this unless the linked servers
> had different names.
> John
> "bdtmike" wrote:
> > We have a SQL 2000 Database in both a production and test environment.
> > There are numerous processes and procedures in this database that must
> > communicate cross-server with another SQL 2000 database. This other
> > server also has a corresponding production / test environment.
> >
> > There are many references in my database to the linked server (dts &
> > sprocs). Add to this, when my database is being operated in the test
> > environment, the procedures and processes must reference the
> > corresponding test server for the other database. In other words, my
> > production database on my production server must reference their
> > production database on their production server. My database in the
> > testing server must reference their database in the testing server.
> >
> > I'd love to keep both databases on the same server for test &
> > production, but for business reasons, it simply can't be done.
> >
> > My problem is that when I move my production into testing (and back), I
> > must go through and meticulously change all the references in my
> > sprocs. Failure to change them all would be disasterous. Instead, I
> > wanted a dynamic way to do this so that the reference to the linked
> > server could be a variable which is set, depending on whether I'm
> > Production or Testing.
> >
> > Unfortunately, table names in SQL statements cannot be variable so what
> > I end up with is a TON of dynamic SQL. Certainly there must be a
> > better way to do this!
> >
> > Anybody have encountered this situation before?
> >
> >|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegroups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>|||But the linked server name can be the same!
Check out example 2 in the sp_addlinkedserver examples in BOL, specify
different data sources and the same linked server name.
John
"bdtmike" wrote:
> Well, they do have separate names (they are separate servers).
> John Bell wrote:
> > Hi
> >
> > I am not sure why you would need to change this unless the linked servers
> > had different names.
> >
> > John
> >
> > "bdtmike" wrote:
> >
> > > We have a SQL 2000 Database in both a production and test environment.
> > > There are numerous processes and procedures in this database that must
> > > communicate cross-server with another SQL 2000 database. This other
> > > server also has a corresponding production / test environment.
> > >
> > > There are many references in my database to the linked server (dts &
> > > sprocs). Add to this, when my database is being operated in the test
> > > environment, the procedures and processes must reference the
> > > corresponding test server for the other database. In other words, my
> > > production database on my production server must reference their
> > > production database on their production server. My database in the
> > > testing server must reference their database in the testing server.
> > >
> > > I'd love to keep both databases on the same server for test &
> > > production, but for business reasons, it simply can't be done.
> > >
> > > My problem is that when I move my production into testing (and back), I
> > > must go through and meticulously change all the references in my
> > > sprocs. Failure to change them all would be disasterous. Instead, I
> > > wanted a dynamic way to do this so that the reference to the linked
> > > server could be a variable which is set, depending on whether I'm
> > > Production or Testing.
> > >
> > > Unfortunately, table names in SQL statements cannot be variable so what
> > > I end up with is a TON of dynamic SQL. Certainly there must be a
> > > better way to do this!
> > >
> > > Anybody have encountered this situation before?
> > >
> > >
>|||Yes, in theory they could. But I'm saying that in my situation they
arent' the same name. And the chance that I'm going to get this
organization to change their server name just for me is...Zero.|||Any changes to code after it has been tested introduces a risk which they
should be made aware off. If this risk is removed by having a better
configuration then it should be acceptable.
John
"bdtmike" wrote:
> Yes, in theory they could. But I'm saying that in my situation they
> arent' the same name. And the chance that I'm going to get this
> organization to change their server name just for me is...Zero.
>
Production vs. Test Environment, Linked Servers = Lots of Dynamic SQL!
We have a SQL 2000 Database in both a production and test environment.
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegroups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegroups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>
There are numerous processes and procedures in this database that must
communicate cross-server with another SQL 2000 database. This other
server also has a corresponding production / test environment.
There are many references in my database to the linked server (dts &
sprocs). Add to this, when my database is being operated in the test
environment, the procedures and processes must reference the
corresponding test server for the other database. In other words, my
production database on my production server must reference their
production database on their production server. My database in the
testing server must reference their database in the testing server.
I'd love to keep both databases on the same server for test &
production, but for business reasons, it simply can't be done.
My problem is that when I move my production into testing (and back), I
must go through and meticulously change all the references in my
sprocs. Failure to change them all would be disasterous. Instead, I
wanted a dynamic way to do this so that the reference to the linked
server could be a variable which is set, depending on whether I'm
Production or Testing.
Unfortunately, table names in SQL statements cannot be variable so what
I end up with is a TON of dynamic SQL. Certainly there must be a
better way to do this!
Anybody have encountered this situation before?You can use the SQL Client Network utility to create an alias for an
instance. This way you define the alias on both your test and production
servers with the same alias name but pointing at different instances. Thus
in your code you just refer to the alias name and it will work without
alteration in either environment.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143777464.390453.261130@.i39g2000cwa.googlegroups.com...
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
>|||Sounds like a good idea--never read up on these. So let's say you have
a SQL server somewhere out on the domain called 'FRED'. Do you define
an alias on each workstation called, for instance, 'WILMA' but it
really points to 'FRED'? If it has to be done at each Workstation, is
there a way to automate this for our 200 users?|||bdtmike wrote:
> We have a SQL 2000 Database in both a production and test environment.
> There are numerous processes and procedures in this database that must
> communicate cross-server with another SQL 2000 database. This other
> server also has a corresponding production / test environment.
> There are many references in my database to the linked server (dts &
> sprocs). Add to this, when my database is being operated in the test
> environment, the procedures and processes must reference the
> corresponding test server for the other database. In other words, my
> production database on my production server must reference their
> production database on their production server. My database in the
> testing server must reference their database in the testing server.
> I'd love to keep both databases on the same server for test &
> production, but for business reasons, it simply can't be done.
> My problem is that when I move my production into testing (and back), I
> must go through and meticulously change all the references in my
> sprocs. Failure to change them all would be disasterous. Instead, I
> wanted a dynamic way to do this so that the reference to the linked
> server could be a variable which is set, depending on whether I'm
> Production or Testing.
> Unfortunately, table names in SQL statements cannot be variable so what
> I end up with is a TON of dynamic SQL. Certainly there must be a
> better way to do this!
> Anybody have encountered this situation before?
Parameterize the names in the installation script. Never in the runtime
code. Create views (or synonyms in 2005) for each table outside the
current database, then reference the views in your procs. That way the
number of places that the name is specified is kept to a minimum. Don't
reference server names or database names directly in procs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||These are linked servers so I assume they are only defined on the server. So
if you have a proc like
create proc linkcall
as
exec LINKEDSERVER.db.dbo.otherproc
and you have a linked server defined in your production environment that
points to server PROD and a linked server in your development environment
that points at DEV then rather than having the following code in PROD
create proc linkcall
as
exec PROD.db.dbo.otherproc
which won't work in dev, you create an alias on the server that this
procedure runs on e.g. LINK that points at the server PROD. You do the same
in dev but you point it at server DEV. Then your code becomes
create proc linkcall
as
exec LINK.db.dbo.otherproc
and this will work regardless of the environment. One of the points of
client aliases defined on a server are to allow you to use a logical
servername to refer to different physical server in different environments.
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"bdtmike" <mike.aes@.gmail.com> wrote in message
news:1143829371.682086.238210@.u72g2000cwu.googlegroups.com...
> Sounds like a good idea--never read up on these. So let's say you have
> a SQL server somewhere out on the domain called 'FRED'. Do you define
> an alias on each workstation called, for instance, 'WILMA' but it
> really points to 'FRED'? If it has to be done at each Workstation, is
> there a way to automate this for our 200 users?
>
Saturday, February 25, 2012
process add and configuration
SSIS Destination Adapter ProcessADD does not correctly use the Analysis Services Connection with and dynamic connection string.
I use process add for cube Processing - we use Production and Development environment. The analysis Services Connection is parameterized with a differnt Database name.
The Process add - does not like all other analysis services processing tasks, allow you to use an expression for the processing command - it only gives you the UI with design time tools.
But I guess like all other SSIS Components with SSAS the Database Name is hardcoded in the prcessing command. For the Processing Task I could change this with an expression.
But Howto change the Processing Command withing the Process ADD Destination -
or how do I get it working while the database name changes (at the moment if the database name changes by configuration all mappings are lost and execution fails)
Best Regards
HANNES
I am doing something wrong? or is a configuration for data datasource of the olap destination is ssis not supported?
Best regards, HANNES
|||I got it
The Database is always part of the XMLA command and therefore not affected by the connection manager initial catalog property.
You always have to code the database name in the XML Command
HANNES
process add and configuration
SSIS Destination Adapter ProcessADD does not correctly use the Analysis Services Connection with and dynamic connection string.
I use process add for cube Processing - we use Production and Development environment. The analysis Services Connection is parameterized with a differnt Database name.
The Process add - does not like all other analysis services processing tasks, allow you to use an expression for the processing command - it only gives you the UI with design time tools.
But I guess like all other SSIS Components with SSAS the Database Name is hardcoded in the prcessing command. For the Processing Task I could change this with an expression.
But Howto change the Processing Command withing the Process ADD Destination -
or how do I get it working while the database name changes (at the moment if the database name changes by configuration all mappings are lost and execution fails)
Best Regards
HANNES
I am doing something wrong? or is a configuration for data datasource of the olap destination is ssis not supported?
Best regards, HANNES
|||I got it
The Database is always part of the XMLA command and therefore not affected by the connection manager initial catalog property.
You always have to code the database name in the XML Command
HANNES
Monday, February 20, 2012
Process 54 generated fatal exception c0000005 EXCEPTION_ACCESS_VIO
Hi..
I'm getting the following error when data is selected from views using
dynamic sql. Running SQL Server 2000 with SP3.
Process 54 generated fatal exception c0000005 EXCEPTION_A
CCESS_VIOLATION. SQL Server is terminating this process.
Thanks.Search KB and make sure you are on current service pack. If problem persists, open a case with MS
Support (these types of errors are most often bugs in SQL Server).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"siddu" <siddu@.discussions.microsoft.com> wrote in message
news:F6112406-B866-4B2F-8DCB-87041C8D3E27@.microsoft.com...
> Hi..
> I'm getting the following error when data is selected from views using
> dynamic sql. Running SQL Server 2000 with SP3.
> Process 54 generated fatal exception c0000005 EXCEPTION_A
> CCESS_VIOLATION. SQL Server is terminating this process.
> Thanks.
I'm getting the following error when data is selected from views using
dynamic sql. Running SQL Server 2000 with SP3.
Process 54 generated fatal exception c0000005 EXCEPTION_A
CCESS_VIOLATION. SQL Server is terminating this process.
Thanks.Search KB and make sure you are on current service pack. If problem persists, open a case with MS
Support (these types of errors are most often bugs in SQL Server).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"siddu" <siddu@.discussions.microsoft.com> wrote in message
news:F6112406-B866-4B2F-8DCB-87041C8D3E27@.microsoft.com...
> Hi..
> I'm getting the following error when data is selected from views using
> dynamic sql. Running SQL Server 2000 with SP3.
> Process 54 generated fatal exception c0000005 EXCEPTION_A
> CCESS_VIOLATION. SQL Server is terminating this process.
> Thanks.
Subscribe to:
Posts (Atom)