Hello,
I got Win2k Advanced Server with SQL 2K running.
I am trying to figure out one single query will give a permission to all
tables under a database. A database has 20 - 30 tables with several custom
roles and it is pain to run a query that give a permission on one table at a
time.
Grant select on my_table to my_role
Grant update on my_table1 to my_role1
Can any one turn a light for me on this'
Thanks in advance.
SangHunThere might be another way, but you could use the sp_msforeachtable sproc.
Please note that it's undocumented/unsupported, so insert usual warnings
here (don't rely on it in production code or expect it to be there in the
next release, etc, etc.)
But I digress...
sp_msforeachtable 'grant select on ? to my_role'
Will grant select on every table in the current database to my_role.
Here is an article with more info on that sproc and another related sproc,
sp_msforeachdb:
http://www.dbazine.com/larsen5.shtml
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> Hello,
> I got Win2k Advanced Server with SQL 2K running.
> I am trying to figure out one single query will give a permission to all
> tables under a database. A database has 20 - 30 tables with several
custom
> roles and it is pain to run a query that give a permission on one table at
a
> time.
> Grant select on my_table to my_role
> Grant update on my_table1 to my_role1
> Can any one turn a light for me on this'
> Thanks in advance.
> SangHun|||Why can't you just add the role to db_datareader and db_datawriter?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
There might be another way, but you could use the sp_msforeachtable sproc.
Please note that it's undocumented/unsupported, so insert usual warnings
here (don't rely on it in production code or expect it to be there in the
next release, etc, etc.)
But I digress...
sp_msforeachtable 'grant select on ? to my_role'
Will grant select on every table in the current database to my_role.
Here is an article with more info on that sproc and another related sproc,
sp_msforeachdb:
http://www.dbazine.com/larsen5.shtml
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> Hello,
> I got Win2k Advanced Server with SQL 2K running.
> I am trying to figure out one single query will give a permission to all
> tables under a database. A database has 20 - 30 tables with several
custom
> roles and it is pain to run a query that give a permission on one table at
a
> time.
> Grant select on my_table to my_role
> Grant update on my_table1 to my_role1
> Can any one turn a light for me on this'
> Thanks in advance.
> SangHun|||Does db_reader role has select permission on all existing tables?
I have seem that even I added a user to the db_reader role, the user didn't
have select permission to any table until I grant permission to specific
table for the user.
I thought I might create a read-only role and manage from there.
Thanks,
"Tom Moreau" wrote:
> Why can't you just add the role to db_datareader and db_datawriter?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
> a
>
>|||Thanks Addm,
I will try that out on testing env.
SangHun
"Adam Machanic" wrote:
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
> a
>
>|||The db_reader role has SELECT permission on all tables and views. I've not
seen the problem you describe. Perhaps you can give us a repro script?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:3BF1E1D9-412B-4282-B04F-DCF952713E36@.microsoft.com...
Does db_reader role has select permission on all existing tables?
I have seem that even I added a user to the db_reader role, the user didn't
have select permission to any table until I grant permission to specific
table for the user.
I thought I might create a read-only role and manage from there.
Thanks,
"Tom Moreau" wrote:
> Why can't you just add the role to db_datareader and db_datawriter?
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:eZvZS68eEHA.3100@.TK2MSFTNGP10.phx.gbl...
> There might be another way, but you could use the sp_msforeachtable sproc.
> Please note that it's undocumented/unsupported, so insert usual warnings
> here (don't rely on it in production code or expect it to be there in the
> next release, etc, etc.)
> But I digress...
> sp_msforeachtable 'grant select on ? to my_role'
> Will grant select on every table in the current database to my_role.
> Here is an article with more info on that sproc and another related sproc,
> sp_msforeachdb:
> http://www.dbazine.com/larsen5.shtml
>
> "SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
> news:CF69101B-4AE0-4C38-9466-FDCFE5818569@.microsoft.com...
> custom
at[vbcol=seagreen]
> a
>
>
Showing posts with label running. Show all posts
Showing posts with label running. Show all posts
Tuesday, March 27, 2012
Friday, March 23, 2012
GRANT command error
Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.
Surround the user in square brackets. [CORP\AppDev]
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:
> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>
|||Thanks ... I guess i am very slow today
"AndyP" wrote:
[vbcol=seagreen]
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.
Surround the user in square brackets. [CORP\AppDev]
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:
> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>
|||Thanks ... I guess i am very slow today
"AndyP" wrote:
[vbcol=seagreen]
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
GRANT command error
Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '' "
Please note that same name exist for login and database user i.e CORP\AppDev
.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:
> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '' "
> Please note that same name exist for login and database user i.e CORP\AppD
ev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today
"AndyP" wrote:
[vbcol=seagreen]
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
>
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '' "
Please note that same name exist for login and database user i.e CORP\AppDev
.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:
> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '' "
> Please note that same name exist for login and database user i.e CORP\AppD
ev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today
"AndyP" wrote:
[vbcol=seagreen]
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
>
GRANT command error
Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:
> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today :)
"AndyP" wrote:
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
> > Hello,
> >
> > I am running a very simple command to grant a stored procedure an EXEC
> > permission to a login that is created for a NT group. The command is as
> > follows
> >
> > GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> >
> > I am getting the error like "Incorrect syntax near '\' "
> >
> > Please note that same name exist for login and database user i.e CORP\AppDev.
> >
> > Any help in this matter would be greatly appreciated.
> >
> >
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:
> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today :)
"AndyP" wrote:
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
> > Hello,
> >
> > I am running a very simple command to grant a stored procedure an EXEC
> > permission to a login that is created for a NT group. The command is as
> > follows
> >
> > GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> >
> > I am getting the error like "Incorrect syntax near '\' "
> >
> > Please note that same name exist for login and database user i.e CORP\AppDev.
> >
> > Any help in this matter would be greatly appreciated.
> >
> >
Monday, March 19, 2012
Got Microsoft OLE DB Provider for ODBC Drivers error '80004005' when running Stored Procedures
when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help
Microsoft OLE DB Provider for ODBC Drivers error
Microsoft OLE DB Provider for ODBC Drivers error
'80004005'
[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from
TDS data stream
sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server
or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error
Many thanks, Please help, appreciated
Can you post more information about the configuration:
- What version of SQL Server do you use?
- What OLEDB provider do you use (e.g., SQLOLEDB, SQL Native Client?)?
- What is the definition of relevan tables?
- Code for the stored procedure and for the application calling it (omit any confidential information)?
|||
Thanks Peter:
it's SQL Server 2000 sp4, this problem happens on the OLEDB provider ODBC driver. the detials of sproc is that there is one sproc calling other 4 different sprocs to update 4 different table (about 30,000 columns need to be updated in each table). and it will run only once a month automaticly by Job.
I also tried to run those 4 sprocs separately and manually, still get the same problem, somehow.
cheers
|||anyone can help?|||Could be a network performance or configuration error.
http://support.microsoft.com/default.aspx/kb/176256
Got Microsoft OLE DB Provider for ODBC Drivers error '80004005' when running Stored Procedures
when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help
Microsoft OLE DB Provider for ODBC Drivers error
Microsoft OLE DB Provider for ODBC Drivers error
'80004005'
[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from
TDS data stream
sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server
or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error
Many thanks, Please help, appreciated
Can you post more information about the configuration:
- What version of SQL Server do you use?
- What OLEDB provider do you use (e.g., SQLOLEDB, SQL Native Client?)?
- What is the definition of relevan tables?
- Code for the stored procedure and for the application calling it (omit any confidential information)?
|||
Thanks Peter:
it's SQL Server 2000 sp4, this problem happens on the OLEDB provider ODBC driver. the detials of sproc is that there is one sproc calling other 4 different sprocs to update 4 different table (about 30,000 columns need to be updated in each table). and it will run only once a month automaticly by Job.
I also tried to run those 4 sprocs separately and manually, still get the same problem, somehow.
cheers
|||anyone can help?|||Could be a network performance or configuration error.
http://support.microsoft.com/default.aspx/kb/176256
Googlebot and MSN bot causing sql login errors
Hi there
I am running sql 2005, I recently changed my sql login account for security reasons. The site is connecting to the database fine but in my server logs, all the Search engine bots are causing sql login errors. Its like they are still cacheing the old account
Any ideas?
Thanks
Clinton
I'm not sure this has anything to do with SQL, if the credentials are wrong, then login will be prevented. You should ask this question of the groups that work with Googlebot and MSN bot.
Mike
Friday, March 9, 2012
Good documentation tool
hi all,
if anybody could direct me to a free DB documentation tool for SQL2K that'll
be great. Since we're running out of budget (usual year end crunch, blah
blah!), we cannot budget for buying any new tool...if anyone also knows diff
tools that could be used within SQL2K, pls suggest in brief how to make use
of it for documenting Tables, Triggers, Relation Diagram and Data Dictionary
TIA
I wrote an article on "DB documentation" last year. I have provided the sql
scripts also. Check out http://www.microsoft.com/india/msdn/articles/169.aspx
whether it would be of any help to you.
Pls be adviced that you might need to change the scripts to adhere to ANSI
Joins if at all you are bothered about performance.
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"parasada" wrote:
> hi all,
> if anybody could direct me to a free DB documentation tool for SQL2K that'll
> be great. Since we're running out of budget (usual year end crunch, blah
> blah!), we cannot budget for buying any new tool...if anyone also knows diff
> tools that could be used within SQL2K, pls suggest in brief how to make use
> of it for documenting Tables, Triggers, Relation Diagram and Data Dictionary
> TIA
if anybody could direct me to a free DB documentation tool for SQL2K that'll
be great. Since we're running out of budget (usual year end crunch, blah
blah!), we cannot budget for buying any new tool...if anyone also knows diff
tools that could be used within SQL2K, pls suggest in brief how to make use
of it for documenting Tables, Triggers, Relation Diagram and Data Dictionary
TIA
I wrote an article on "DB documentation" last year. I have provided the sql
scripts also. Check out http://www.microsoft.com/india/msdn/articles/169.aspx
whether it would be of any help to you.
Pls be adviced that you might need to change the scripts to adhere to ANSI
Joins if at all you are bothered about performance.
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"parasada" wrote:
> hi all,
> if anybody could direct me to a free DB documentation tool for SQL2K that'll
> be great. Since we're running out of budget (usual year end crunch, blah
> blah!), we cannot budget for buying any new tool...if anyone also knows diff
> tools that could be used within SQL2K, pls suggest in brief how to make use
> of it for documenting Tables, Triggers, Relation Diagram and Data Dictionary
> TIA
Good Day SQL Guru's
I have a rather lenghty question and I will try and be specific as possible.
I currently have a single server with Windows 2003 Enterprise running a
single instance of SQL Server Standard 2005. The Master and other "default"
databases are in the Default location on the C drive. The "Production"
databases are on "shared" resources on a CX-300 which is attached to my
Server via EMULEX HBA's. Now a Caviat to this configuration is that it used
to be part of a cluster.
When I first started working here I found that it had been configured with
an 8 gig C drive partion and I needed to reconfigure them one at a time, So
when I went to remove SQL off of the second node to be able to "evict" it
from the cluster, I did it through "Add/Remove Programs" and the set up came
up as normal, but after I selected the node and hit 'uninstall' It never
asked me the usual SQL cluster questions and Just uninstalled SQL, removing
all the shared "SQL Group" resources in the cluster" Not a tragedy, because I
still have a node, But very disconcerting and curious
So I rebuilt it and added it back to the cluster and manually configured
everything. It worked pretty good, Everything failed over (SQL services,
MSDTC, Drives and the databases attached okay, Howvere they since the Master
did not match the other, none of the "objects" were there. so what I did was
"stopped the services" on the good node, copied the databases into the
default folder on the new node and brought it up, it seemed to work. But when
I tried to attach my "production databases" it came back and said "recovery
in progress" and would not attach them, it didn't go for very long before My
SQL DB started whining about wanting to revert back because he had to go home.
SO, I am assuming that there was a problem doing it that way or maybe
eventually they would have recovered, We did try a "restore" first, But when
we did a full backup of the Good Node "master" and tried to restore it to the
new node, the versions didin't match.
Now, I have a few questions I was hoping I could get a few pointers on. I
have built two servers that I am going to build a new cluster on, so I want
to "migrate" these SQL "default" database to the new cluster, Since it is
also SAN attached, I am simply going to pull the single node out of the
"Storage Group" that has the LUNS for the SQL Resources and add the two SQL
Cluster NODES, giving the drives the exact same letters (of course attaching
them one node at a time)
So assuming I want to migrate the SQL default database to the Cluster is
there a migration tool that will do this relatively risk free and can I put
this database on a shared drive and have both nodes point to it and when
failover occurs, the node being failed from detaches and the node being
failed to attaches to these databases. The reason I ask is I have built
several SQL Clusters, But they have all been new ones that were going to be
used, I have not built one to replace a single node system.
I haven't done a lot of research myself yet, I am looking for any microsoft
tools that will assist me, But if anyone has done this or has some sagely
advise, it would be greatly appreciated
--
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515I would treat this as a side-by-side migration. I would build a new cluster
and copy the data files to the new system. You can try a detach-move
LUNS-Reattach from the stand-alone machine to the cluster, but there is no
way I would try and migrate the system databases. Treat this as a normal
SQL host migration and you might come out of it OK.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:EF1A1CB2-8192-41C7-8088-4C2B1EC78835@.microsoft.com...
>I have a rather lenghty question and I will try and be specific as
>possible.
> I currently have a single server with Windows 2003 Enterprise running a
> single instance of SQL Server Standard 2005. The Master and other
> "default"
> databases are in the Default location on the C drive. The "Production"
> databases are on "shared" resources on a CX-300 which is attached to my
> Server via EMULEX HBA's. Now a Caviat to this configuration is that it
> used
> to be part of a cluster.
> When I first started working here I found that it had been configured with
> an 8 gig C drive partion and I needed to reconfigure them one at a time,
> So
> when I went to remove SQL off of the second node to be able to "evict" it
> from the cluster, I did it through "Add/Remove Programs" and the set up
> came
> up as normal, but after I selected the node and hit 'uninstall' It never
> asked me the usual SQL cluster questions and Just uninstalled SQL,
> removing
> all the shared "SQL Group" resources in the cluster" Not a tragedy,
> because I
> still have a node, But very disconcerting and curious
> So I rebuilt it and added it back to the cluster and manually configured
> everything. It worked pretty good, Everything failed over (SQL services,
> MSDTC, Drives and the databases attached okay, Howvere they since the
> Master
> did not match the other, none of the "objects" were there. so what I did
> was
> "stopped the services" on the good node, copied the databases into the
> default folder on the new node and brought it up, it seemed to work. But
> when
> I tried to attach my "production databases" it came back and said
> "recovery
> in progress" and would not attach them, it didn't go for very long before
> My
> SQL DB started whining about wanting to revert back because he had to go
> home.
> SO, I am assuming that there was a problem doing it that way or maybe
> eventually they would have recovered, We did try a "restore" first, But
> when
> we did a full backup of the Good Node "master" and tried to restore it to
> the
> new node, the versions didin't match.
> Now, I have a few questions I was hoping I could get a few pointers on. I
> have built two servers that I am going to build a new cluster on, so I
> want
> to "migrate" these SQL "default" database to the new cluster, Since it is
> also SAN attached, I am simply going to pull the single node out of the
> "Storage Group" that has the LUNS for the SQL Resources and add the two
> SQL
> Cluster NODES, giving the drives the exact same letters (of course
> attaching
> them one node at a time)
> So assuming I want to migrate the SQL default database to the Cluster is
> there a migration tool that will do this relatively risk free and can I
> put
> this database on a shared drive and have both nodes point to it and when
> failover occurs, the node being failed from detaches and the node being
> failed to attaches to these databases. The reason I ask is I have built
> several SQL Clusters, But they have all been new ones that were going to
> be
> used, I have not built one to replace a single node system.
> I haven't done a lot of research myself yet, I am looking for any
> microsoft
> tools that will assist me, But if anyone has done this or has some sagely
> advise, it would be greatly appreciated
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515|||Hey Geoff
Okay, I will..Now What is a "Normal Host Migration" and is it possible to
create a single set of system databases(Master, Model, MSDB and Tempdb) on a
shared drive and have both nodes connect to it when they fail over
Just so you know, I am by no stretch of the imagination a SQL guy, My DB is
even having issues
--
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
"Scott Cummins" wrote:
> I have a rather lenghty question and I will try and be specific as possible.
> I currently have a single server with Windows 2003 Enterprise running a
> single instance of SQL Server Standard 2005. The Master and other "default"
> databases are in the Default location on the C drive. The "Production"
> databases are on "shared" resources on a CX-300 which is attached to my
> Server via EMULEX HBA's. Now a Caviat to this configuration is that it used
> to be part of a cluster.
> When I first started working here I found that it had been configured with
> an 8 gig C drive partion and I needed to reconfigure them one at a time, So
> when I went to remove SQL off of the second node to be able to "evict" it
> from the cluster, I did it through "Add/Remove Programs" and the set up came
> up as normal, but after I selected the node and hit 'uninstall' It never
> asked me the usual SQL cluster questions and Just uninstalled SQL, removing
> all the shared "SQL Group" resources in the cluster" Not a tragedy, because I
> still have a node, But very disconcerting and curious
> So I rebuilt it and added it back to the cluster and manually configured
> everything. It worked pretty good, Everything failed over (SQL services,
> MSDTC, Drives and the databases attached okay, Howvere they since the Master
> did not match the other, none of the "objects" were there. so what I did was
> "stopped the services" on the good node, copied the databases into the
> default folder on the new node and brought it up, it seemed to work. But when
> I tried to attach my "production databases" it came back and said "recovery
> in progress" and would not attach them, it didn't go for very long before My
> SQL DB started whining about wanting to revert back because he had to go home.
> SO, I am assuming that there was a problem doing it that way or maybe
> eventually they would have recovered, We did try a "restore" first, But when
> we did a full backup of the Good Node "master" and tried to restore it to the
> new node, the versions didin't match.
> Now, I have a few questions I was hoping I could get a few pointers on. I
> have built two servers that I am going to build a new cluster on, so I want
> to "migrate" these SQL "default" database to the new cluster, Since it is
> also SAN attached, I am simply going to pull the single node out of the
> "Storage Group" that has the LUNS for the SQL Resources and add the two SQL
> Cluster NODES, giving the drives the exact same letters (of course attaching
> them one node at a time)
> So assuming I want to migrate the SQL default database to the Cluster is
> there a migration tool that will do this relatively risk free and can I put
> this database on a shared drive and have both nodes point to it and when
> failover occurs, the node being failed from detaches and the node being
> failed to attaches to these databases. The reason I ask is I have built
> several SQL Clusters, But they have all been new ones that were going to be
> used, I have not built one to replace a single node system.
> I haven't done a lot of research myself yet, I am looking for any microsoft
> tools that will assist me, But if anyone has done this or has some sagely
> advise, it would be greatly appreciated
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515|||What you describe is exactly how clustering is supposed to work. SQL has
binaries on each node, but all databases, including system databases are on
a "shared" storage system. The Cluster service arbitrates which node owns
the resources associated with a particular SQL server instance. The
service, network name, IP address, and all storage disks go together to a
new node when the group fails over or is moved.
So, set up the cluster (single node if necessary) script your logins, jobs,
etc. and get them moved to the new system. You can then move the database
files to the new cluster and attach them, or use a backup-restore technique
to sync the systems. That is the very short version of a normal migration.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:A0FC13A4-857B-4B08-9DDC-BD93F32504A7@.microsoft.com...
> Hey Geoff
> Okay, I will..Now What is a "Normal Host Migration" and is it possible to
> create a single set of system databases(Master, Model, MSDB and Tempdb) on
> a
> shared drive and have both nodes connect to it when they fail over
> Just so you know, I am by no stretch of the imagination a SQL guy, My DB
> is
> even having issues
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515
>
> "Scott Cummins" wrote:
>> I have a rather lenghty question and I will try and be specific as
>> possible.
>> I currently have a single server with Windows 2003 Enterprise running a
>> single instance of SQL Server Standard 2005. The Master and other
>> "default"
>> databases are in the Default location on the C drive. The "Production"
>> databases are on "shared" resources on a CX-300 which is attached to my
>> Server via EMULEX HBA's. Now a Caviat to this configuration is that it
>> used
>> to be part of a cluster.
>> When I first started working here I found that it had been configured
>> with
>> an 8 gig C drive partion and I needed to reconfigure them one at a time,
>> So
>> when I went to remove SQL off of the second node to be able to "evict" it
>> from the cluster, I did it through "Add/Remove Programs" and the set up
>> came
>> up as normal, but after I selected the node and hit 'uninstall' It never
>> asked me the usual SQL cluster questions and Just uninstalled SQL,
>> removing
>> all the shared "SQL Group" resources in the cluster" Not a tragedy,
>> because I
>> still have a node, But very disconcerting and curious
>> So I rebuilt it and added it back to the cluster and manually configured
>> everything. It worked pretty good, Everything failed over (SQL services,
>> MSDTC, Drives and the databases attached okay, Howvere they since the
>> Master
>> did not match the other, none of the "objects" were there. so what I did
>> was
>> "stopped the services" on the good node, copied the databases into the
>> default folder on the new node and brought it up, it seemed to work. But
>> when
>> I tried to attach my "production databases" it came back and said
>> "recovery
>> in progress" and would not attach them, it didn't go for very long before
>> My
>> SQL DB started whining about wanting to revert back because he had to go
>> home.
>> SO, I am assuming that there was a problem doing it that way or maybe
>> eventually they would have recovered, We did try a "restore" first, But
>> when
>> we did a full backup of the Good Node "master" and tried to restore it to
>> the
>> new node, the versions didin't match.
>> Now, I have a few questions I was hoping I could get a few pointers on. I
>> have built two servers that I am going to build a new cluster on, so I
>> want
>> to "migrate" these SQL "default" database to the new cluster, Since it is
>> also SAN attached, I am simply going to pull the single node out of the
>> "Storage Group" that has the LUNS for the SQL Resources and add the two
>> SQL
>> Cluster NODES, giving the drives the exact same letters (of course
>> attaching
>> them one node at a time)
>> So assuming I want to migrate the SQL default database to the Cluster is
>> there a migration tool that will do this relatively risk free and can I
>> put
>> this database on a shared drive and have both nodes point to it and when
>> failover occurs, the node being failed from detaches and the node being
>> failed to attaches to these databases. The reason I ask is I have built
>> several SQL Clusters, But they have all been new ones that were going to
>> be
>> used, I have not built one to replace a single node system.
>> I haven't done a lot of research myself yet, I am looking for any
>> microsoft
>> tools that will assist me, But if anyone has done this or has some sagely
>> advise, it would be greatly appreciated
>> --
>> Scott A Cummins
>> Sr. Systems Engineer
>> Equity Analytics
>> ( A division of Merrill Lynch)
>> 14614 N. Keirland Blvd
>> Scottsdale, AZ 85254
>> 480-998-3515|||Geoff
Thanks again, that is a very good piece of Knowledge to have, For some
reason I had assumed that the system databases were loaded separately on each
node and because it was cluster "aware" it replictaed any changes on one
node to the other node so that the "system databases" were exactly the same.
It seems my DBA has so much info in the system databases, he will probably
have to script everything over. He was hoping just to be bale to "migrate" or
"copy" the current System databases over to the new system.
Again, you are of tremendous help and I really appreciate it
--
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
"Geoff N. Hiten" wrote:
> What you describe is exactly how clustering is supposed to work. SQL has
> binaries on each node, but all databases, including system databases are on
> a "shared" storage system. The Cluster service arbitrates which node owns
> the resources associated with a particular SQL server instance. The
> service, network name, IP address, and all storage disks go together to a
> new node when the group fails over or is moved.
> So, set up the cluster (single node if necessary) script your logins, jobs,
> etc. and get them moved to the new system. You can then move the database
> files to the new cluster and attach them, or use a backup-restore technique
> to sync the systems. That is the very short version of a normal migration.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Scott Cummins" <scummins@.equitymethods.com> wrote in message
> news:A0FC13A4-857B-4B08-9DDC-BD93F32504A7@.microsoft.com...
> > Hey Geoff
> >
> > Okay, I will..Now What is a "Normal Host Migration" and is it possible to
> > create a single set of system databases(Master, Model, MSDB and Tempdb) on
> > a
> > shared drive and have both nodes connect to it when they fail over
> >
> > Just so you know, I am by no stretch of the imagination a SQL guy, My DB
> > is
> > even having issues
> > --
> > Scott A Cummins
> > Sr. Systems Engineer
> > Equity Analytics
> > ( A division of Merrill Lynch)
> > 14614 N. Keirland Blvd
> > Scottsdale, AZ 85254
> > 480-998-3515
> >
> >
> > "Scott Cummins" wrote:
> >
> >> I have a rather lenghty question and I will try and be specific as
> >> possible.
> >>
> >> I currently have a single server with Windows 2003 Enterprise running a
> >> single instance of SQL Server Standard 2005. The Master and other
> >> "default"
> >> databases are in the Default location on the C drive. The "Production"
> >> databases are on "shared" resources on a CX-300 which is attached to my
> >> Server via EMULEX HBA's. Now a Caviat to this configuration is that it
> >> used
> >> to be part of a cluster.
> >>
> >> When I first started working here I found that it had been configured
> >> with
> >> an 8 gig C drive partion and I needed to reconfigure them one at a time,
> >> So
> >> when I went to remove SQL off of the second node to be able to "evict" it
> >> from the cluster, I did it through "Add/Remove Programs" and the set up
> >> came
> >> up as normal, but after I selected the node and hit 'uninstall' It never
> >> asked me the usual SQL cluster questions and Just uninstalled SQL,
> >> removing
> >> all the shared "SQL Group" resources in the cluster" Not a tragedy,
> >> because I
> >> still have a node, But very disconcerting and curious
> >>
> >> So I rebuilt it and added it back to the cluster and manually configured
> >> everything. It worked pretty good, Everything failed over (SQL services,
> >> MSDTC, Drives and the databases attached okay, Howvere they since the
> >> Master
> >> did not match the other, none of the "objects" were there. so what I did
> >> was
> >> "stopped the services" on the good node, copied the databases into the
> >> default folder on the new node and brought it up, it seemed to work. But
> >> when
> >> I tried to attach my "production databases" it came back and said
> >> "recovery
> >> in progress" and would not attach them, it didn't go for very long before
> >> My
> >> SQL DB started whining about wanting to revert back because he had to go
> >> home.
> >>
> >> SO, I am assuming that there was a problem doing it that way or maybe
> >> eventually they would have recovered, We did try a "restore" first, But
> >> when
> >> we did a full backup of the Good Node "master" and tried to restore it to
> >> the
> >> new node, the versions didin't match.
> >>
> >> Now, I have a few questions I was hoping I could get a few pointers on. I
> >> have built two servers that I am going to build a new cluster on, so I
> >> want
> >> to "migrate" these SQL "default" database to the new cluster, Since it is
> >> also SAN attached, I am simply going to pull the single node out of the
> >> "Storage Group" that has the LUNS for the SQL Resources and add the two
> >> SQL
> >> Cluster NODES, giving the drives the exact same letters (of course
> >> attaching
> >> them one node at a time)
> >>
> >> So assuming I want to migrate the SQL default database to the Cluster is
> >> there a migration tool that will do this relatively risk free and can I
> >> put
> >> this database on a shared drive and have both nodes point to it and when
> >> failover occurs, the node being failed from detaches and the node being
> >> failed to attaches to these databases. The reason I ask is I have built
> >> several SQL Clusters, But they have all been new ones that were going to
> >> be
> >> used, I have not built one to replace a single node system.
> >>
> >> I haven't done a lot of research myself yet, I am looking for any
> >> microsoft
> >> tools that will assist me, But if anyone has done this or has some sagely
> >> advise, it would be greatly appreciated
> >> --
> >> Scott A Cummins
> >> Sr. Systems Engineer
> >> Equity Analytics
> >> ( A division of Merrill Lynch)
> >> 14614 N. Keirland Blvd
> >> Scottsdale, AZ 85254
> >> 480-998-3515
>
I currently have a single server with Windows 2003 Enterprise running a
single instance of SQL Server Standard 2005. The Master and other "default"
databases are in the Default location on the C drive. The "Production"
databases are on "shared" resources on a CX-300 which is attached to my
Server via EMULEX HBA's. Now a Caviat to this configuration is that it used
to be part of a cluster.
When I first started working here I found that it had been configured with
an 8 gig C drive partion and I needed to reconfigure them one at a time, So
when I went to remove SQL off of the second node to be able to "evict" it
from the cluster, I did it through "Add/Remove Programs" and the set up came
up as normal, but after I selected the node and hit 'uninstall' It never
asked me the usual SQL cluster questions and Just uninstalled SQL, removing
all the shared "SQL Group" resources in the cluster" Not a tragedy, because I
still have a node, But very disconcerting and curious
So I rebuilt it and added it back to the cluster and manually configured
everything. It worked pretty good, Everything failed over (SQL services,
MSDTC, Drives and the databases attached okay, Howvere they since the Master
did not match the other, none of the "objects" were there. so what I did was
"stopped the services" on the good node, copied the databases into the
default folder on the new node and brought it up, it seemed to work. But when
I tried to attach my "production databases" it came back and said "recovery
in progress" and would not attach them, it didn't go for very long before My
SQL DB started whining about wanting to revert back because he had to go home.
SO, I am assuming that there was a problem doing it that way or maybe
eventually they would have recovered, We did try a "restore" first, But when
we did a full backup of the Good Node "master" and tried to restore it to the
new node, the versions didin't match.
Now, I have a few questions I was hoping I could get a few pointers on. I
have built two servers that I am going to build a new cluster on, so I want
to "migrate" these SQL "default" database to the new cluster, Since it is
also SAN attached, I am simply going to pull the single node out of the
"Storage Group" that has the LUNS for the SQL Resources and add the two SQL
Cluster NODES, giving the drives the exact same letters (of course attaching
them one node at a time)
So assuming I want to migrate the SQL default database to the Cluster is
there a migration tool that will do this relatively risk free and can I put
this database on a shared drive and have both nodes point to it and when
failover occurs, the node being failed from detaches and the node being
failed to attaches to these databases. The reason I ask is I have built
several SQL Clusters, But they have all been new ones that were going to be
used, I have not built one to replace a single node system.
I haven't done a lot of research myself yet, I am looking for any microsoft
tools that will assist me, But if anyone has done this or has some sagely
advise, it would be greatly appreciated
--
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515I would treat this as a side-by-side migration. I would build a new cluster
and copy the data files to the new system. You can try a detach-move
LUNS-Reattach from the stand-alone machine to the cluster, but there is no
way I would try and migrate the system databases. Treat this as a normal
SQL host migration and you might come out of it OK.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:EF1A1CB2-8192-41C7-8088-4C2B1EC78835@.microsoft.com...
>I have a rather lenghty question and I will try and be specific as
>possible.
> I currently have a single server with Windows 2003 Enterprise running a
> single instance of SQL Server Standard 2005. The Master and other
> "default"
> databases are in the Default location on the C drive. The "Production"
> databases are on "shared" resources on a CX-300 which is attached to my
> Server via EMULEX HBA's. Now a Caviat to this configuration is that it
> used
> to be part of a cluster.
> When I first started working here I found that it had been configured with
> an 8 gig C drive partion and I needed to reconfigure them one at a time,
> So
> when I went to remove SQL off of the second node to be able to "evict" it
> from the cluster, I did it through "Add/Remove Programs" and the set up
> came
> up as normal, but after I selected the node and hit 'uninstall' It never
> asked me the usual SQL cluster questions and Just uninstalled SQL,
> removing
> all the shared "SQL Group" resources in the cluster" Not a tragedy,
> because I
> still have a node, But very disconcerting and curious
> So I rebuilt it and added it back to the cluster and manually configured
> everything. It worked pretty good, Everything failed over (SQL services,
> MSDTC, Drives and the databases attached okay, Howvere they since the
> Master
> did not match the other, none of the "objects" were there. so what I did
> was
> "stopped the services" on the good node, copied the databases into the
> default folder on the new node and brought it up, it seemed to work. But
> when
> I tried to attach my "production databases" it came back and said
> "recovery
> in progress" and would not attach them, it didn't go for very long before
> My
> SQL DB started whining about wanting to revert back because he had to go
> home.
> SO, I am assuming that there was a problem doing it that way or maybe
> eventually they would have recovered, We did try a "restore" first, But
> when
> we did a full backup of the Good Node "master" and tried to restore it to
> the
> new node, the versions didin't match.
> Now, I have a few questions I was hoping I could get a few pointers on. I
> have built two servers that I am going to build a new cluster on, so I
> want
> to "migrate" these SQL "default" database to the new cluster, Since it is
> also SAN attached, I am simply going to pull the single node out of the
> "Storage Group" that has the LUNS for the SQL Resources and add the two
> SQL
> Cluster NODES, giving the drives the exact same letters (of course
> attaching
> them one node at a time)
> So assuming I want to migrate the SQL default database to the Cluster is
> there a migration tool that will do this relatively risk free and can I
> put
> this database on a shared drive and have both nodes point to it and when
> failover occurs, the node being failed from detaches and the node being
> failed to attaches to these databases. The reason I ask is I have built
> several SQL Clusters, But they have all been new ones that were going to
> be
> used, I have not built one to replace a single node system.
> I haven't done a lot of research myself yet, I am looking for any
> microsoft
> tools that will assist me, But if anyone has done this or has some sagely
> advise, it would be greatly appreciated
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515|||Hey Geoff
Okay, I will..Now What is a "Normal Host Migration" and is it possible to
create a single set of system databases(Master, Model, MSDB and Tempdb) on a
shared drive and have both nodes connect to it when they fail over
Just so you know, I am by no stretch of the imagination a SQL guy, My DB is
even having issues
--
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
"Scott Cummins" wrote:
> I have a rather lenghty question and I will try and be specific as possible.
> I currently have a single server with Windows 2003 Enterprise running a
> single instance of SQL Server Standard 2005. The Master and other "default"
> databases are in the Default location on the C drive. The "Production"
> databases are on "shared" resources on a CX-300 which is attached to my
> Server via EMULEX HBA's. Now a Caviat to this configuration is that it used
> to be part of a cluster.
> When I first started working here I found that it had been configured with
> an 8 gig C drive partion and I needed to reconfigure them one at a time, So
> when I went to remove SQL off of the second node to be able to "evict" it
> from the cluster, I did it through "Add/Remove Programs" and the set up came
> up as normal, but after I selected the node and hit 'uninstall' It never
> asked me the usual SQL cluster questions and Just uninstalled SQL, removing
> all the shared "SQL Group" resources in the cluster" Not a tragedy, because I
> still have a node, But very disconcerting and curious
> So I rebuilt it and added it back to the cluster and manually configured
> everything. It worked pretty good, Everything failed over (SQL services,
> MSDTC, Drives and the databases attached okay, Howvere they since the Master
> did not match the other, none of the "objects" were there. so what I did was
> "stopped the services" on the good node, copied the databases into the
> default folder on the new node and brought it up, it seemed to work. But when
> I tried to attach my "production databases" it came back and said "recovery
> in progress" and would not attach them, it didn't go for very long before My
> SQL DB started whining about wanting to revert back because he had to go home.
> SO, I am assuming that there was a problem doing it that way or maybe
> eventually they would have recovered, We did try a "restore" first, But when
> we did a full backup of the Good Node "master" and tried to restore it to the
> new node, the versions didin't match.
> Now, I have a few questions I was hoping I could get a few pointers on. I
> have built two servers that I am going to build a new cluster on, so I want
> to "migrate" these SQL "default" database to the new cluster, Since it is
> also SAN attached, I am simply going to pull the single node out of the
> "Storage Group" that has the LUNS for the SQL Resources and add the two SQL
> Cluster NODES, giving the drives the exact same letters (of course attaching
> them one node at a time)
> So assuming I want to migrate the SQL default database to the Cluster is
> there a migration tool that will do this relatively risk free and can I put
> this database on a shared drive and have both nodes point to it and when
> failover occurs, the node being failed from detaches and the node being
> failed to attaches to these databases. The reason I ask is I have built
> several SQL Clusters, But they have all been new ones that were going to be
> used, I have not built one to replace a single node system.
> I haven't done a lot of research myself yet, I am looking for any microsoft
> tools that will assist me, But if anyone has done this or has some sagely
> advise, it would be greatly appreciated
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515|||What you describe is exactly how clustering is supposed to work. SQL has
binaries on each node, but all databases, including system databases are on
a "shared" storage system. The Cluster service arbitrates which node owns
the resources associated with a particular SQL server instance. The
service, network name, IP address, and all storage disks go together to a
new node when the group fails over or is moved.
So, set up the cluster (single node if necessary) script your logins, jobs,
etc. and get them moved to the new system. You can then move the database
files to the new cluster and attach them, or use a backup-restore technique
to sync the systems. That is the very short version of a normal migration.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Scott Cummins" <scummins@.equitymethods.com> wrote in message
news:A0FC13A4-857B-4B08-9DDC-BD93F32504A7@.microsoft.com...
> Hey Geoff
> Okay, I will..Now What is a "Normal Host Migration" and is it possible to
> create a single set of system databases(Master, Model, MSDB and Tempdb) on
> a
> shared drive and have both nodes connect to it when they fail over
> Just so you know, I am by no stretch of the imagination a SQL guy, My DB
> is
> even having issues
> --
> Scott A Cummins
> Sr. Systems Engineer
> Equity Analytics
> ( A division of Merrill Lynch)
> 14614 N. Keirland Blvd
> Scottsdale, AZ 85254
> 480-998-3515
>
> "Scott Cummins" wrote:
>> I have a rather lenghty question and I will try and be specific as
>> possible.
>> I currently have a single server with Windows 2003 Enterprise running a
>> single instance of SQL Server Standard 2005. The Master and other
>> "default"
>> databases are in the Default location on the C drive. The "Production"
>> databases are on "shared" resources on a CX-300 which is attached to my
>> Server via EMULEX HBA's. Now a Caviat to this configuration is that it
>> used
>> to be part of a cluster.
>> When I first started working here I found that it had been configured
>> with
>> an 8 gig C drive partion and I needed to reconfigure them one at a time,
>> So
>> when I went to remove SQL off of the second node to be able to "evict" it
>> from the cluster, I did it through "Add/Remove Programs" and the set up
>> came
>> up as normal, but after I selected the node and hit 'uninstall' It never
>> asked me the usual SQL cluster questions and Just uninstalled SQL,
>> removing
>> all the shared "SQL Group" resources in the cluster" Not a tragedy,
>> because I
>> still have a node, But very disconcerting and curious
>> So I rebuilt it and added it back to the cluster and manually configured
>> everything. It worked pretty good, Everything failed over (SQL services,
>> MSDTC, Drives and the databases attached okay, Howvere they since the
>> Master
>> did not match the other, none of the "objects" were there. so what I did
>> was
>> "stopped the services" on the good node, copied the databases into the
>> default folder on the new node and brought it up, it seemed to work. But
>> when
>> I tried to attach my "production databases" it came back and said
>> "recovery
>> in progress" and would not attach them, it didn't go for very long before
>> My
>> SQL DB started whining about wanting to revert back because he had to go
>> home.
>> SO, I am assuming that there was a problem doing it that way or maybe
>> eventually they would have recovered, We did try a "restore" first, But
>> when
>> we did a full backup of the Good Node "master" and tried to restore it to
>> the
>> new node, the versions didin't match.
>> Now, I have a few questions I was hoping I could get a few pointers on. I
>> have built two servers that I am going to build a new cluster on, so I
>> want
>> to "migrate" these SQL "default" database to the new cluster, Since it is
>> also SAN attached, I am simply going to pull the single node out of the
>> "Storage Group" that has the LUNS for the SQL Resources and add the two
>> SQL
>> Cluster NODES, giving the drives the exact same letters (of course
>> attaching
>> them one node at a time)
>> So assuming I want to migrate the SQL default database to the Cluster is
>> there a migration tool that will do this relatively risk free and can I
>> put
>> this database on a shared drive and have both nodes point to it and when
>> failover occurs, the node being failed from detaches and the node being
>> failed to attaches to these databases. The reason I ask is I have built
>> several SQL Clusters, But they have all been new ones that were going to
>> be
>> used, I have not built one to replace a single node system.
>> I haven't done a lot of research myself yet, I am looking for any
>> microsoft
>> tools that will assist me, But if anyone has done this or has some sagely
>> advise, it would be greatly appreciated
>> --
>> Scott A Cummins
>> Sr. Systems Engineer
>> Equity Analytics
>> ( A division of Merrill Lynch)
>> 14614 N. Keirland Blvd
>> Scottsdale, AZ 85254
>> 480-998-3515|||Geoff
Thanks again, that is a very good piece of Knowledge to have, For some
reason I had assumed that the system databases were loaded separately on each
node and because it was cluster "aware" it replictaed any changes on one
node to the other node so that the "system databases" were exactly the same.
It seems my DBA has so much info in the system databases, he will probably
have to script everything over. He was hoping just to be bale to "migrate" or
"copy" the current System databases over to the new system.
Again, you are of tremendous help and I really appreciate it
--
Scott A Cummins
Sr. Systems Engineer
Equity Analytics
( A division of Merrill Lynch)
14614 N. Keirland Blvd
Scottsdale, AZ 85254
480-998-3515
"Geoff N. Hiten" wrote:
> What you describe is exactly how clustering is supposed to work. SQL has
> binaries on each node, but all databases, including system databases are on
> a "shared" storage system. The Cluster service arbitrates which node owns
> the resources associated with a particular SQL server instance. The
> service, network name, IP address, and all storage disks go together to a
> new node when the group fails over or is moved.
> So, set up the cluster (single node if necessary) script your logins, jobs,
> etc. and get them moved to the new system. You can then move the database
> files to the new cluster and attach them, or use a backup-restore technique
> to sync the systems. That is the very short version of a normal migration.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Scott Cummins" <scummins@.equitymethods.com> wrote in message
> news:A0FC13A4-857B-4B08-9DDC-BD93F32504A7@.microsoft.com...
> > Hey Geoff
> >
> > Okay, I will..Now What is a "Normal Host Migration" and is it possible to
> > create a single set of system databases(Master, Model, MSDB and Tempdb) on
> > a
> > shared drive and have both nodes connect to it when they fail over
> >
> > Just so you know, I am by no stretch of the imagination a SQL guy, My DB
> > is
> > even having issues
> > --
> > Scott A Cummins
> > Sr. Systems Engineer
> > Equity Analytics
> > ( A division of Merrill Lynch)
> > 14614 N. Keirland Blvd
> > Scottsdale, AZ 85254
> > 480-998-3515
> >
> >
> > "Scott Cummins" wrote:
> >
> >> I have a rather lenghty question and I will try and be specific as
> >> possible.
> >>
> >> I currently have a single server with Windows 2003 Enterprise running a
> >> single instance of SQL Server Standard 2005. The Master and other
> >> "default"
> >> databases are in the Default location on the C drive. The "Production"
> >> databases are on "shared" resources on a CX-300 which is attached to my
> >> Server via EMULEX HBA's. Now a Caviat to this configuration is that it
> >> used
> >> to be part of a cluster.
> >>
> >> When I first started working here I found that it had been configured
> >> with
> >> an 8 gig C drive partion and I needed to reconfigure them one at a time,
> >> So
> >> when I went to remove SQL off of the second node to be able to "evict" it
> >> from the cluster, I did it through "Add/Remove Programs" and the set up
> >> came
> >> up as normal, but after I selected the node and hit 'uninstall' It never
> >> asked me the usual SQL cluster questions and Just uninstalled SQL,
> >> removing
> >> all the shared "SQL Group" resources in the cluster" Not a tragedy,
> >> because I
> >> still have a node, But very disconcerting and curious
> >>
> >> So I rebuilt it and added it back to the cluster and manually configured
> >> everything. It worked pretty good, Everything failed over (SQL services,
> >> MSDTC, Drives and the databases attached okay, Howvere they since the
> >> Master
> >> did not match the other, none of the "objects" were there. so what I did
> >> was
> >> "stopped the services" on the good node, copied the databases into the
> >> default folder on the new node and brought it up, it seemed to work. But
> >> when
> >> I tried to attach my "production databases" it came back and said
> >> "recovery
> >> in progress" and would not attach them, it didn't go for very long before
> >> My
> >> SQL DB started whining about wanting to revert back because he had to go
> >> home.
> >>
> >> SO, I am assuming that there was a problem doing it that way or maybe
> >> eventually they would have recovered, We did try a "restore" first, But
> >> when
> >> we did a full backup of the Good Node "master" and tried to restore it to
> >> the
> >> new node, the versions didin't match.
> >>
> >> Now, I have a few questions I was hoping I could get a few pointers on. I
> >> have built two servers that I am going to build a new cluster on, so I
> >> want
> >> to "migrate" these SQL "default" database to the new cluster, Since it is
> >> also SAN attached, I am simply going to pull the single node out of the
> >> "Storage Group" that has the LUNS for the SQL Resources and add the two
> >> SQL
> >> Cluster NODES, giving the drives the exact same letters (of course
> >> attaching
> >> them one node at a time)
> >>
> >> So assuming I want to migrate the SQL default database to the Cluster is
> >> there a migration tool that will do this relatively risk free and can I
> >> put
> >> this database on a shared drive and have both nodes point to it and when
> >> failover occurs, the node being failed from detaches and the node being
> >> failed to attaches to these databases. The reason I ask is I have built
> >> several SQL Clusters, But they have all been new ones that were going to
> >> be
> >> used, I have not built one to replace a single node system.
> >>
> >> I haven't done a lot of research myself yet, I am looking for any
> >> microsoft
> >> tools that will assist me, But if anyone has done this or has some sagely
> >> advise, it would be greatly appreciated
> >> --
> >> Scott A Cummins
> >> Sr. Systems Engineer
> >> Equity Analytics
> >> ( A division of Merrill Lynch)
> >> 14614 N. Keirland Blvd
> >> Scottsdale, AZ 85254
> >> 480-998-3515
>
Sunday, February 26, 2012
Global.asa for MS SQL 2000
Hello,
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
.....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
......
End Sub
</SCRIPT>
Thanks a lot,
Hank
Why would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>
|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>
|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
.....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
......
End Sub
</SCRIPT>
Thanks a lot,
Hank
Why would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>
|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>
|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>
Global.asa for MS SQL 2000
Hello,
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
.....
End Sub
</SCRIPT>
Thanks a lot,
HankWhy would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
.....
End Sub
</SCRIPT>
Thanks a lot,
HankWhy would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>
Global.asa for MS SQL 2000
Hello,
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
.....
End Sub
</SCRIPT>
Thanks a lot,
HankWhy would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
>> Hello,
>> I'd like to use global.asa to connect a database in MS SQL 2000, running
> on
>> the same MS Server 2003. I wondered if any of you know about the code for
>> the two functions:
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Application_OnStart
>> ....
>> End Sub
>> </SCRIPT>
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Session_OnStart
>> .....
>> End Sub
>> </SCRIPT>
>> Thanks a lot,
>> Hank
>>
>|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
>> Hello,
>> I'd like to use global.asa to connect a database in MS SQL 2000, running
> on
>> the same MS Server 2003. I wondered if any of you know about the code for
>> the two functions:
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Application_OnStart
>> ....
>> End Sub
>> </SCRIPT>
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Session_OnStart
>> .....
>> End Sub
>> </SCRIPT>
>> Thanks a lot,
>> Hank
>>
>
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
.....
End Sub
</SCRIPT>
Thanks a lot,
HankWhy would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
>> Hello,
>> I'd like to use global.asa to connect a database in MS SQL 2000, running
> on
>> the same MS Server 2003. I wondered if any of you know about the code for
>> the two functions:
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Application_OnStart
>> ....
>> End Sub
>> </SCRIPT>
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Session_OnStart
>> .....
>> End Sub
>> </SCRIPT>
>> Thanks a lot,
>> Hank
>>
>|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
>> Hello,
>> I'd like to use global.asa to connect a database in MS SQL 2000, running
> on
>> the same MS Server 2003. I wondered if any of you know about the code for
>> the two functions:
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Application_OnStart
>> ....
>> End Sub
>> </SCRIPT>
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Session_OnStart
>> .....
>> End Sub
>> </SCRIPT>
>> Thanks a lot,
>> Hank
>>
>
Friday, February 24, 2012
Global ODBC Settings
I have two SQL Servers currently running SQL2000 I am in the process of upgrading to SQL 2005 is there an easy way of pointing my users to the spare SQL server while I do the upgrade. I wondered if I could run a login script which could change the ODBC setting to the spare server.I have the same issue. Did you get this resolved?
Thanks
TKE402
Subscribe to:
Posts (Atom)