Determining Security: instance

Showing posts with label instance. Show all posts

Thursday, March 29, 2012

Determine what server i'm running on

I've generated a report that will need to display different information
based upon where it is being run. For instance, if the report is executing
on our DVLP server, i need a title to say "Developement". Is there is simple
way to determine what environment the report is being run in? Check the URL?
Server variables?
Thanks,
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com=System.Environment.MachineName
Warning: You'll need to give expression host FullTrust in order to use this
expression on report server, which may be a security risk.
If your SQL Server is on the same machine, creating a dataset against it and
using SELECT @.@.servername would be a better approach.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"G" <brian.grant@.si-intl-kc.com> wrote in message
news:uZDVm2lbEHA.3988@.tk2msftngp13.phx.gbl...
> I've generated a report that will need to display different information
> based upon where it is being run. For instance, if the report is executing
> on our DVLP server, i need a title to say "Developement". Is there is
simple
> way to determine what environment the report is being run in? Check the
URL?
> Server variables?
> Thanks,
> --
> Brian Grant
> Senior Programmer
> SI International
> www.si-intl.com
>sql

Tuesday, March 27, 2012

Determine running size of memtoleave area

Hi group,
it there an easy way to determine the size of the MemToLeave area on a
running instance?
I know this is caculated at startup as
[max worker threads] * 0.5MB + [-g Memory]
but we want to verify that the instance is using our -g setting.
TIA,We use VMSTAT.EXE to monitor the total amount free and xp_memory_size (which
comes with SQL Litespeed) to monitor the max contiguous region. I can't
remember where we got vmstat from but I think it was probably from PSS.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Gurba" <gurbao@.hotmail.com> wrote in message
news:Xns969E84434E3CDgurbaohotmailcom@.129.250.171.68...
> Hi group,
> it there an easy way to determine the size of the MemToLeave area on a
> running instance?
> I know this is caculated at startup as
> [max worker threads] * 0.5MB + [-g Memory]
> but we want to verify that the instance is using our -g setting.
> TIA,|||Will finding max contig help me?
We are experiencing this message from time to time:
2005-07-26 16:49:41.52 spid63 WARNING: Failed to reserve contiguous
memory of Size= 131072.
2005-07-26 16:49:41.63 spid63 Buffer Distribution: Stolen=17899
Free=5 Procedures=161541
Inram=0 Dirty=17995 Kept=0
I/O=0, Latched=1980, Other=138372
2005-07-26 16:49:41.63 spid63 Buffer Counts: Commited=337792 Target=337792 Hashed=158347
InternalReservation=2266 ExternalReservation=3009 Min
Free=1024
2005-07-26 16:49:41.63 spid63 Procedure Cache: TotalProcs=35140
TotalPages=161541 InUsePages=108631
2005-07-26 16:49:41.63 spid63 Dynamic Memory Manager: Stolen=176976
OS Reserved=10416
OS Committed=9749
OS In Use=8822
Query Plan=171722 Optimizer=0
General=12434
Utilities=1151 Connection=263
2005-07-26 16:49:41.63 spid63 Global Memory Objects: Resource=7963
Locks=105
SQLCache=3311 Replication=4
LockBytes=2 ServerGlobal=56
Xact=93
2005-07-26 16:49:41.63 spid63 Query Memory Manager: Grants=3
Waiting=0 Maximum=163539 Available=158066
For all I know, I may find the size of the memtoleave area in these
numbers?
Regards,
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in
news:eY2UaZVkFHA.1048@.tk2msftngp13.phx.gbl:
> We use VMSTAT.EXE to monitor the total amount free and xp_memory_size
> (which comes with SQL Litespeed) to monitor the max contiguous region.
> I can't remember where we got vmstat from but I think it was probably
> from PSS.
>

Sunday, March 25, 2012

Determine Date Range Falls between other Date Range

I need to figure out how to see if a date in a date range falls between
another date range. For instance - I need to know if any date between
3/1/06 and 3/31/06 falls between a date range of 2/16/03 to 4/1/06.
Does anyone know of a way to code that without using a cursor to go
through every day to see if that day of the month is between the other
date range?
Melissambonsted@.yahoo.com wrote:
> I need to figure out how to see if a date in a date range falls between
> another date range. For instance - I need to know if any date between
> 3/1/06 and 3/31/06 falls between a date range of 2/16/03 to 4/1/06.
> Does anyone know of a way to code that without using a cursor to go
> through every day to see if that day of the month is between the other
> date range?
> Melissa
Here's some sample data and a query:
CREATE TABLE tbl (dt_from DATETIME NOT NULL, dt_to DATETIME NOT NULL,
CHECK (dt_from <= dt_to), PRIMARY KEY (dt_to));
INSERT INTO tbl VALUES ('20060301', '20060331');
DECLARE @.dt_from DATETIME, @.dt_to DATETIME ;
SET @.dt_from = '20030216';
SET @.dt_to = '20060401';
SELECT dt_from, dt_to
FROM tbl
WHERE dt_from >= @.dt_from
AND dt_to <= @.dt_to ;
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
--|||On 1 Apr 2006 14:21:55 -0800, David Portas wrote:

>mbonsted@.yahoo.com wrote:
>Here's some sample data and a query:
>CREATE TABLE tbl (dt_from DATETIME NOT NULL, dt_to DATETIME NOT NULL,
>CHECK (dt_from <= dt_to), PRIMARY KEY (dt_to));
>INSERT INTO tbl VALUES ('20060301', '20060331');
>DECLARE @.dt_from DATETIME, @.dt_to DATETIME ;
>SET @.dt_from = '20030216';
>SET @.dt_to = '20060401';
>SELECT dt_from, dt_to
> FROM tbl
> WHERE dt_from >= @.dt_from
> AND dt_to <= @.dt_to ;
Hi David,
This will only find date ranges completely embedded in another date
range. To find any overlap, change it to
SELECT dt_from, dt_to
FROM tbl
WHERE dt_from <= @.dt_to
AND dt_to >= @.dt_from ;
(Change <= and >= to < and > if one range starting the same day that
another range ends is not considered an overlap)
Hugo Kornelis, SQL Server MVPsql

Thursday, March 22, 2012

Determine a date not in the table

Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegroups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~

Detecting SQL Server 2000 vs 2005 instance

I have the following two questions:
1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list of
all the available sql servers (in the form of Namelist) but how can I detect
which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
instances programmatically either using VB6 or C#?
2) If I have a few machines in my network and one of them has an instance
called
"mycomputer/myinstance", the method "ListAvailableSQLServers" does not list
it. Could that be related to the windows firewall being turned on?
--
ANeelimaYou'd want to use smo instead of dmo to interact with both sql2k5 and sql2k.
Take a look at Server.PingSqlServerVersion() method under
Microsoft.SqlServer.Management.Smo namespace.
--
-oj
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:B178E81E-C9DA-46FC-B0FB-504537B030D3@.microsoft.com...
>I have the following two questions:
> 1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list
> of
> all the available sql servers (in the form of Namelist) but how can I
> detect
> which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
> instances programmatically either using VB6 or C#?
> 2) If I have a few machines in my network and one of them has an instance
> called
> "mycomputer/myinstance", the method "ListAvailableSQLServers" does not
> list
> it. Could that be related to the windows firewall being turned on?
> --
> ANeelima|||Hi,
Thanks for your post!
From your description, I understand that:
Your 1st question was that you wanted to detect each SQL Server version
corresponding to each SQL Server instances.
Your 2nd question was that you found you couldn't get the list of all SQL
Server instances in your network via ListAvailableSQLServers.
If I have misunderstood, please let me know.
For the first question, I recommend you:
1) Create a SQLDMO.SQLServer object.
2) Connect to the SQL Server by server name.
3) Get the version information by the property SQLServer.VersionString or
SQLServer.VersionMajor.
For the second question, I recommend you check your network settings and
ensure your application machine can access any SQL Server instance.
I write a sample and get the named instances that my machine can access in
network:
SQLDMO.Application app = new SQLDMO.ApplicationClass();
SQLDMO.NameList nl = app.ListAvailableSQLServers();
for (int i = 0; i < nl.Count; ++i)
{
string s = nl.Item(i);
this.label1.Text += s + "\n";
}
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks.
My application is in VB6.0. How can I use SMO from VB6.0? I can't
seem to find it in the list of references in VB6.0. What is the reference
name?
ANeelima
"oj" wrote:
> You'd want to use smo instead of dmo to interact with both sql2k5 and sql2k.
> Take a look at Server.PingSqlServerVersion() method under
> Microsoft.SqlServer.Management.Smo namespace.
> --
> -oj
>
> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
> news:B178E81E-C9DA-46FC-B0FB-504537B030D3@.microsoft.com...
> >I have the following two questions:
> >
> > 1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list
> > of
> > all the available sql servers (in the form of Namelist) but how can I
> > detect
> > which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
> > instances programmatically either using VB6 or C#?
> >
> > 2) If I have a few machines in my network and one of them has an instance
> > called
> > "mycomputer/myinstance", the method "ListAvailableSQLServers" does not
> > list
> > it. Could that be related to the windows firewall being turned on?
> >
> > --
> > ANeelima
>
>|||Well, I was hoping that I don't have to connect to the server to get the
version name.
I wanted to simply use ListAvailableSQLServers, get the namelist, loop
through the servers and find the version without connecting.
It appears like I can do that with SMO using something like
----
DataTable dataTable = SmoApplication.EnumAvailableSqlServers();
foreach (DataRow dataRow in dataTable.Rows )
{
row.Append(
"Server: " + dataRow["Name"] + " Version: " + dataRow["Version"]);
}
Console.WriteLine(row.ToString());
----
But I can't do that with SQLDMO.
My application is in VB6.0 and if I need to use SMO in VB6.0 how can I do
that?
--
ANeelima
"Charles Wang[MSFT]" wrote:
> Hi,
> Thanks for your post!
> From your description, I understand that:
> Your 1st question was that you wanted to detect each SQL Server version
> corresponding to each SQL Server instances.
> Your 2nd question was that you found you couldn't get the list of all SQL
> Server instances in your network via ListAvailableSQLServers.
> If I have misunderstood, please let me know.
> For the first question, I recommend you:
> 1) Create a SQLDMO.SQLServer object.
> 2) Connect to the SQL Server by server name.
> 3) Get the version information by the property SQLServer.VersionString or
> SQLServer.VersionMajor.
> For the second question, I recommend you check your network settings and
> ensure your application machine can access any SQL Server instance.
> I write a sample and get the named instances that my machine can access in
> network:
> SQLDMO.Application app = new SQLDMO.ApplicationClass();
> SQLDMO.NameList nl = app.ListAvailableSQLServers();
> for (int i = 0; i < nl.Count; ++i)
> {
> string s = nl.Item(i);
> this.label1.Text += s + "\n";
> }
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||As long as SQL Browser service is online (and it should), you don't have to
connect to the SQL instances to check their versions. You can send a packet
to UDP 1434 and get the version info in the reply. If you don't want to
program sockets firectly, you can leverage the output of SQLPing.exe and
parse the result text for the instance names and their versions. You can find
SQLPing.exe via Google.
Linchi
"ANeelima" wrote:
> I have the following two questions:
> 1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list of
> all the available sql servers (in the form of Namelist) but how can I detect
> which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
> instances programmatically either using VB6 or C#?
> 2) If I have a few machines in my network and one of them has an instance
> called
> "mycomputer/myinstance", the method "ListAvailableSQLServers" does not list
> it. Could that be related to the windows firewall being turned on?
> --
> ANeelima|||Unfortunately, SMO doesn't support COM. You might want to take a look at the
updated DMO.
"Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions
of Data Transformation Services 2000 runtime (DTS), SQL Distributed
Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL
Virtual Device Interface (SQLVDI). These versions have been updated for
compatibility with SQL Server 2005 and include all fixes shipped through SQL
Server 2000 SP4."
http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en
--
-oj
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E9A07FEA-8F2F-40C5-BADE-599D41B88FB4@.microsoft.com...
> Thanks.
> My application is in VB6.0. How can I use SMO from VB6.0? I can't
> seem to find it in the list of references in VB6.0. What is the reference
> name?
>
> --
> ANeelima
>
> "oj" wrote:
>> You'd want to use smo instead of dmo to interact with both sql2k5 and
>> sql2k.
>> Take a look at Server.PingSqlServerVersion() method under
>> Microsoft.SqlServer.Management.Smo namespace.
>> --
>> -oj
>>
>> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
>> news:B178E81E-C9DA-46FC-B0FB-504537B030D3@.microsoft.com...
>> >I have the following two questions:
>> >
>> > 1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a
>> > list
>> > of
>> > all the available sql servers (in the form of Namelist) but how can I
>> > detect
>> > which ones are SQL Server 2000/MSDE 2000 instances and which ones are
>> > 2005
>> > instances programmatically either using VB6 or C#?
>> >
>> > 2) If I have a few machines in my network and one of them has an
>> > instance
>> > called
>> > "mycomputer/myinstance", the method "ListAvailableSQLServers" does not
>> > list
>> > it. Could that be related to the windows firewall being turned on?
>> >
>> > --
>> > ANeelima
>>|||Hi,
Thanks for your response.
SMO is included in SQL Server 2005. You can find the assemblies in:
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"
You can directly add the references in VS.NET 2003/2005.
The assemblies names are:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.SqlEnum
If you want to use SMO in VB6, I recommend you use VS.Net 2003/2005 to wrap
the assembly into a COM library.
It's easy to realize this wrap in VS.Net 2003/2005.
You can refer to this article:
Can I Interest You in 5000 Classes?
Using the Full .NET Framework from Visual Basic 6
http://msdn.microsoft.com/vbrun/vbfusion/default.aspx?pull=/library/en-us/dv
_vstechart/html/VB5000Cl.asp
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
How do you use the assemblies SMO if you don't install sql2005?
I have a web application in vs net 2003, and i have use de object
SQLServer for print a list of databases, tables, etc of other server
with sql2005
but in my server i don't have SQL2005, exist any client for that?
thanks
Charles Wang[MSFT] wrote:
> Hi,
> Thanks for your response.
> SMO is included in SQL Server 2005. You can find the assemblies in:
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"
> You can directly add the references in VS.NET 2003/2005.
> The assemblies names are:
> Microsoft.SqlServer.ConnectionInfo
> Microsoft.SqlServer.Smo
> Microsoft.SqlServer.SmoEnum
> Microsoft.SqlServer.SqlEnum
> If you want to use SMO in VB6, I recommend you use VS.Net 2003/2005 to wrap
> the assembly into a COM library.
> It's easy to realize this wrap in VS.Net 2003/2005.
> You can refer to this article:
> Can I Interest You in 5000 Classes?
> Using the Full .NET Framework from Visual Basic 6
> http://msdn.microsoft.com/vbrun/vbfusion/default.aspx?pull=/library/en-us/dv
> _vstechart/html/VB5000Cl.asp
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
How do you use the assemblies SMO if you don't install sql2005?
I have a web application in vs net 2003, and i have use de object
SQLServer for print a list of databases, tables, etc of other server
with sql2005
but in my server i don't have SQL2005, exist any client for that?
thanks
Charles Wang[MSFT] wrote:
> Hi,
> Thanks for your response.
> SMO is included in SQL Server 2005. You can find the assemblies in:
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"
> You can directly add the references in VS.NET 2003/2005.
> The assemblies names are:
> Microsoft.SqlServer.ConnectionInfo
> Microsoft.SqlServer.Smo
> Microsoft.SqlServer.SmoEnum
> Microsoft.SqlServer.SqlEnum
> If you want to use SMO in VB6, I recommend you use VS.Net 2003/2005 to wrap
> the assembly into a COM library.
> It's easy to realize this wrap in VS.Net 2003/2005.
> You can refer to this article:
> Can I Interest You in 5000 Classes?
> Using the Full .NET Framework from Visual Basic 6
> http://msdn.microsoft.com/vbrun/vbfusion/default.aspx?pull=/library/en-us/dv
> _vstechart/html/VB5000Cl.asp
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi, xahaymar,
Thanks for your participation on this issue.
I need to appologize firstly I didn't perform a test that those SMO
assemblies are .net 2.0 assemblies and can't be applied in .net 1.1.
If you want to use the SMO assemblies you need to install SQL Server 2005.
Otherwise, you should use SQL-DMO.
However, you can install SQL Server 2005 Express on that machine. Those
assemblies are included in SQL 2005 Express.
SQL Server 2005 Express is free and you can directly download it from:
http://msdn.microsoft.com/vstudio/express/sql/download/
Enjoy your day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Neelima,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Detecting SQL Server 2000 vs 2005 instance

I have the following two questions:
1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list of
all the available sql servers (in the form of Namelist) but how can I detect
which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
instances programmatically either using VB6 or C#?
2) If I have a few machines in my network and one of them has an instance
called
"mycomputer/myinstance", the method "ListAvailableSQLServers" does not list
it. Could that be related to the windows firewall being turned on?
ANeelimaYou'd want to use smo instead of dmo to interact with both sql2k5 and sql2k.
Take a look at Server.PingSqlServerVersion() method under
Microsoft.SqlServer.Management.Smo namespace.
-oj
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:B178E81E-C9DA-46FC-B0FB-504537B030D3@.microsoft.com...
>I have the following two questions:
> 1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list
> of
> all the available sql servers (in the form of Namelist) but how can I
> detect
> which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
> instances programmatically either using VB6 or C#?
> 2) If I have a few machines in my network and one of them has an instance
> called
> "mycomputer/myinstance", the method "ListAvailableSQLServers" does not
> list
> it. Could that be related to the windows firewall being turned on?
> --
> ANeelima|||Hi,
Thanks for your post!
From your description, I understand that:
Your 1st question was that you wanted to detect each SQL Server version
corresponding to each SQL Server instances.
Your 2nd question was that you found you couldn't get the list of all SQL
Server instances in your network via ListAvailableSQLServers.
If I have misunderstood, please let me know.
For the first question, I recommend you:
1) Create a SQLDMO.SQLServer object.
2) Connect to the SQL Server by server name.
3) Get the version information by the property SQLServer.VersionString or
SQLServer.VersionMajor.
For the second question, I recommend you check your network settings and
ensure your application machine can access any SQL Server instance.
I write a sample and get the named instances that my machine can access in
network:
SQLDMO.Application app = new SQLDMO.ApplicationClass();
SQLDMO.NameList nl = app.ListAvailableSQLServers();
for (int i = 0; i < nl.Count; ++i)
{
string s = nl.Item(i);
this.label1.Text += s + "\n";
}
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks.
My application is in VB6.0. How can I use SMO from VB6.0? I can't
seem to find it in the list of references in VB6.0. What is the reference
name?
ANeelima
"oj" wrote:

> You'd want to use smo instead of dmo to interact with both sql2k5 and sql2
k.
> Take a look at Server.PingSqlServerVersion() method under
> Microsoft.SqlServer.Management.Smo namespace.
> --
> -oj
>
> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
> news:B178E81E-C9DA-46FC-B0FB-504537B030D3@.microsoft.com...
>
>|||Well, I was hoping that I don't have to connect to the server to get the
version name.
I wanted to simply use ListAvailableSQLServers, get the namelist, loop
through the servers and find the version without connecting.
It appears like I can do that with SMO using something like
----
DataTable dataTable = SmoApplication.EnumAvailableSqlServers();
foreach (DataRow dataRow in dataTable.Rows )
{
row.Append(
"Server: " + dataRow["Name"] + " Version: " + dataRow["Version"]);
}
Console.WriteLine(row.ToString());
----
--
But I can't do that with SQLDMO.
My application is in VB6.0 and if I need to use SMO in VB6.0 how can I do
that?
ANeelima
"Charles Wang[MSFT]" wrote:

> Hi,
> Thanks for your post!
> From your description, I understand that:
> Your 1st question was that you wanted to detect each SQL Server version
> corresponding to each SQL Server instances.
> Your 2nd question was that you found you couldn't get the list of all SQL
> Server instances in your network via ListAvailableSQLServers.
> If I have misunderstood, please let me know.
> For the first question, I recommend you:
> 1) Create a SQLDMO.SQLServer object.
> 2) Connect to the SQL Server by server name.
> 3) Get the version information by the property SQLServer.VersionString or
> SQLServer.VersionMajor.
> For the second question, I recommend you check your network settings and
> ensure your application machine can access any SQL Server instance.
> I write a sample and get the named instances that my machine can access in
> network:
> SQLDMO.Application app = new SQLDMO.ApplicationClass();
> SQLDMO.NameList nl = app.ListAvailableSQLServers();
> for (int i = 0; i < nl.Count; ++i)
> {
> string s = nl.Item(i);
> this.label1.Text += s + "\n";
> }
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others:
> https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||As long as SQL Browser service is online (and it should), you don't have to
connect to the SQL instances to check their versions. You can send a packet
to UDP 1434 and get the version info in the reply. If you don't want to
program sockets firectly, you can leverage the output of SQLPing.exe and
parse the result text for the instance names and their versions. You can fin
d
SQLPing.exe via Google.
Linchi
"ANeelima" wrote:

> I have the following two questions:
> 1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list
of
> all the available sql servers (in the form of Namelist) but how can I dete
ct
> which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
> instances programmatically either using VB6 or C#?
> 2) If I have a few machines in my network and one of them has an instance
> called
> "mycomputer/myinstance", the method "ListAvailableSQLServers" does not lis
t
> it. Could that be related to the windows firewall being turned on?
> --
> ANeelima|||Unfortunately, SMO doesn't support COM. You might want to take a look at the
updated DMO.
"Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions
of Data Transformation Services 2000 runtime (DTS), SQL Distributed
Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL
Virtual Device Interface (SQLVDI). These versions have been updated for
compatibility with SQL Server 2005 and include all fixes shipped through SQL
Server 2000 SP4."
http://www.microsoft.com/downloads/...&displaylang=en
-oj
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E9A07FEA-8F2F-40C5-BADE-599D41B88FB4@.microsoft.com...[vbcol=seagreen]
> Thanks.
> My application is in VB6.0. How can I use SMO from VB6.0? I can't
> seem to find it in the list of references in VB6.0. What is the reference
> name?
>
> --
> ANeelima
>
> "oj" wrote:
>|||Hi,
Thanks for your response.
SMO is included in SQL Server 2005. You can find the assemblies in:
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"
You can directly add the references in VS.NET 2003/2005.
The assemblies names are:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.SqlEnum
If you want to use SMO in VB6, I recommend you use VS.Net 2003/2005 to wrap
the assembly into a COM library.
It's easy to realize this wrap in VS.Net 2003/2005.
You can refer to this article:
Can I Interest You in 5000 Classes?
Using the Full .NET Framework from Visual Basic 6
http://msdn.microsoft.com/vbrun/vbf...ibrary/en-us/dv
_vstechart/html/VB5000Cl.asp
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
How do you use the assemblies SMO if you don't install sql2005?
I have a web application in vs net 2003, and i have use de object
SQLServer for print a list of databases, tables, etc of other server
with sql2005
but in my server i don't have SQL2005, exist any client for that?
thanks
Charles Wang[MSFT] wrote:[vbcol=seagreen]
> Hi,
> Thanks for your response.
> SMO is included in SQL Server 2005. You can find the assemblies in:
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"
> You can directly add the references in VS.NET 2003/2005.
> The assemblies names are:
> Microsoft.SqlServer.ConnectionInfo
> Microsoft.SqlServer.Smo
> Microsoft.SqlServer.SmoEnum
> Microsoft.SqlServer.SqlEnum
> If you want to use SMO in VB6, I recommend you use VS.Net 2003/2005 to wra
p
> the assembly into a COM library.
> It's easy to realize this wrap in VS.Net 2003/2005.
> You can refer to this article:
> Can I Interest You in 5000 Classes?
> Using the Full .NET Framework from Visual Basic 6
> [url]http://msdn.microsoft.com/vbrun/vbfusion/default.aspx?pull=/library/en-us/dv[/ur
l]
> _vstechart/html/VB5000Cl.asp
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others:
> https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights.[/vbcol
]|||Hi
How do you use the assemblies SMO if you don't install sql2005?
I have a web application in vs net 2003, and i have use de object
SQLServer for print a list of databases, tables, etc of other server
with sql2005
but in my server i don't have SQL2005, exist any client for that?
thanks
Charles Wang[MSFT] wrote:[vbcol=seagreen]
> Hi,
> Thanks for your response.
> SMO is included in SQL Server 2005. You can find the assemblies in:
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"
> You can directly add the references in VS.NET 2003/2005.
> The assemblies names are:
> Microsoft.SqlServer.ConnectionInfo
> Microsoft.SqlServer.Smo
> Microsoft.SqlServer.SmoEnum
> Microsoft.SqlServer.SqlEnum
> If you want to use SMO in VB6, I recommend you use VS.Net 2003/2005 to wra
p
> the assembly into a COM library.
> It's easy to realize this wrap in VS.Net 2003/2005.
> You can refer to this article:
> Can I Interest You in 5000 Classes?
> Using the Full .NET Framework from Visual Basic 6
> [url]http://msdn.microsoft.com/vbrun/vbfusion/default.aspx?pull=/library/en-us/dv[/ur
l]
> _vstechart/html/VB5000Cl.asp
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others:
> https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights.[/vbcol
]

Detecting local Sql instances

I would like to find out what instances of sql are installed on any given machine. For instance, if a machine has SQL Server 2005, SQL Express 2005 and MSDE 2000 installed. I would like to be able to know the versions of each, and ultimately choose the one which is the best for the user to use.

For example, I would like to rank the instances, 1. SQL Server 2005 2. SQL server 2000, 3. Sql express, 4. MSDE

So I would basically like to default to the best choice available locally for the user.

Is there any way to do this using SMO?

Thanks...

Hi Johnny,

Yes, you could:

DataTable dt;

dt = SmoApplication.EnumAvailableSqlServers("JohnnysServer");

for (int i = 0; i < dt.Rows.Count; i++)
{
MessageBox.Show( dt.Rows Idea ["Version"].ToString() );
}

Cheers,
Rob

|||Thanks for your response Robert,

Just a few questions about this...

I have tried running it on a few machines, and on some of them the version was null...do you know why this would happen?

Also, does this specific version differ for the fully blown versions vs the light (sql express/msde) versions? Because I noticed that they can have identical versions....

Thanks again...|||

Hi Johnny,

Yes, the versions will be the same, however the "Edition" property (from memory) will display the actual, well...edition. Have a browse of the datatable colums as there's a heap of properties/columns returned.

Not sure why version would be returned as null, unless of course the service wasn't running or otherwise unavailable.

Cheers,
Rob

Wednesday, March 21, 2012

Detecting (local) sql-server

Can someone please point me to some code (preferably C#, but C++ or C or VB
will work) that will detect if the (local) instance of SQL Server is
running on a machine or not? Many thanks!

-- Rob"Rob Gibson" <xnews@.rgibREMOVEson.net> wrote in message
news:Xns95FADFA7C4F67defffft1078@.216.196.97.142...
> Can someone please point me to some code (preferably C#, but C++ or C or
> VB
> will work) that will detect if the (local) instance of SQL Server is
> running on a machine or not? Many thanks!
> -- Rob

MSSQL is just another service, so you should look for code which shows how
to retrieve service states from C# - it looks as if there's a sample with
Visual Studio:

http://msdn.microsoft.com/library/d...scontroller.asp

If you have multiple instances, then see questions 12 and 13 here for how to
find the instance names:

http://support.microsoft.com/defaul...6&Product=sql2k

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in news:420dc8db$1_1@.news.bluewin.ch:

> MSSQL is just another service, so you should look for code which shows
> how to retrieve service states from C# - it looks as if there's a
> sample with Visual Studio:
> http://msdn.microsoft.com/library/d...ry/en-us/cssamp
> le/html/vcsamprocesscontroller.asp
> If you have multiple instances, then see questions 12 and 13 here for
> how to find the instance names:
> http://support.microsoft.com/defaul...257716&Product=
> sql2k
> Simon

Thank you, Simon! That's *EXACTLY* what I was looking for!

-- Rob

Detect installed MSDE version

I'm building a setup program that needs to install an instance of SQL Server
if it's not already installed or if it's not up to date. What I need to know
is... how do I know if an instance of SQL Server is installed, and get it's
build version? I can get info about the default instance in the registry at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\CurrentVersion
But what about a named instance? If I install an instance called "ABC",
where are settings stored in the registry?
Thanks in advance,
EtienneHi
You may want to cycle around instances in the subkeys of
HKLM\Software\Microsoft\Microsoft SQL Server\
John
"Etienne" <mysteryx93_nosspam@.hotmail.com> wrote in message
news:u$S7IX7jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> I'm building a setup program that needs to install an instance of SQL
> Server if it's not already installed or if it's not up to date. What I
> need to know is... how do I know if an instance of SQL Server is
> installed, and get it's build version? I can get info about the default
> instance in the registry at
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\CurrentVersi
on
> But what about a named instance? If I install an instance called "ABC",
> where are settings stored in the registry?
> Thanks in advance,
> Etienne
>

Detect installed MSDE version

I'm building a setup program that needs to install an instance of SQL Server
if it's not already installed or if it's not up to date. What I need to know
is... how do I know if an instance of SQL Server is installed, and get it's
build version? I can get info about the default instance in the registry at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\CurrentVersion
But what about a named instance? If I install an instance called "ABC",
where are settings stored in the registry?
Thanks in advance,
Etienne
Hi
You may want to cycle around instances in the subkeys of
HKLM\Software\Microsoft\Microsoft SQL Server\
John
"Etienne" <mysteryx93_nosspam@.hotmail.com> wrote in message
news:u$S7IX7jFHA.3144@.TK2MSFTNGP12.phx.gbl...
> I'm building a setup program that needs to install an instance of SQL
> Server if it's not already installed or if it's not up to date. What I
> need to know is... how do I know if an instance of SQL Server is
> installed, and get it's build version? I can get info about the default
> instance in the registry at
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\CurrentVersion
> But what about a named instance? If I install an instance called "ABC",
> where are settings stored in the registry?
> Thanks in advance,
> Etienne
>

Detect (local) SQL Server

Can someone please point me to some code (preferably C#, but C++ or C or VB
will work) that will detect if the (local) instance of SQL Server is
running on a machine or not? Many thanks!
-- RobHi Rob
Try using the the ServiceController class to check to see if the MSSQLServer
service is running (ServiceControllerStatus.Running).
Cheers
J.
"Rob Gibson" <xnews@.rgibREMOVEson.net> wrote in message
news:Xns95FAE91589AFBdefffft1078@.216.196.97.142...
> Can someone please point me to some code (preferably C#, but C++ or C or
VB
> will work) that will detect if the (local) instance of SQL Server is
> running on a machine or not? Many thanks!
> -- Rob

Sunday, March 11, 2012

detached database disappears?

Hi All:
Running SQL2005 sp1 on Windows Server 2003 R2.
I have Management Studio installed locally on the server. I connected to the
local instance, backed up a database with no problems, and then detached the
database, and now the physical files are gone? not in the "data" folder, not
anywhere...I still have the backup file, but I'm a little freaked out by
this.
Has anyone experience this? I'm logged in as the administrator so I don't
think the files are hidden from view (all the other databases are in the
data folder)
> I have Management Studio installed locally on the server. I connected to
> the local instance, backed up a database with no problems, and then
> detached the database, and now the physical files are gone? not in the
> "data" folder, not anywhere...I still have the backup file, but I'm a
> little freaked out by this.
> Has anyone experience this?
No, I've never seen this. Are you sure you searched all of the drives,
since often we don't place user databases under the program files structure,
but rather on a separate disk altogether.
|||>> I have Management Studio installed locally on the server. I connected to
> No, I've never seen this. Are you sure you searched all of the drives,
> since often we don't place user databases under the program files
> structure, but rather on a separate disk altogether.
Yeah, there's only one drive/partition on the system, and I've searched
everywhere...it's g-o-n-e.
I performed a full dB backup as well as TLog backup before detaching it. I'm
having trouble restoring from the backup now.
First, I tried to restore by typing in the name of the dB in the "to
database" and attaching the backup file. The backup shows the name of the dB
in the backup set, but when I select the "full database backup" and click
OK, I get a strange error:
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: File "EMS_Metro_Data" cannot be restored
over the existing "C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\EMS_Metro_Data_new.MDF". Reissue the RESTORE
statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE
to identify an alternate location. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&LinkId=20476[/url]
BUT the database name referenced in the error is NOT the database I'm trying
to restore!
So, I then created a new database with the same name as the one I detached,
and selected "restore" and went through the same process, and I then get a
different error:
TITLE: Microsoft SQL Server Management Studio
Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The backup set holds a backup of a database other than the existing
'EMS_Ultimate' database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error:
3154)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476[/url]
EMS_Ultimate is the dB I'm trying to restore! And that's the name of the dB
in the backup file...now I'm really nervous!
|||Sounds to me like the MDF file is still in the Data folder.
And the error message references the name of the *file* no the name of the
database. They do not have to be the same.
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:ekhyVxeKHHA.320@.TK2MSFTNGP06.phx.gbl...
> Yeah, there's only one drive/partition on the system, and I've searched
> everywhere...it's g-o-n-e.
> I performed a full dB backup as well as TLog backup before detaching it.
> I'm having trouble restoring from the backup now.
> First, I tried to restore by typing in the name of the dB in the "to
> database" and attaching the backup file. The backup shows the name of the
> dB in the backup set, but when I select the "full database backup" and
> click OK, I get a strange error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: File "EMS_Metro_Data" cannot be restored
> over the existing "C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\EMS_Metro_Data_new.MDF". Reissue the RESTORE
> statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE
> to identify an alternate location. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&LinkId=20476[/url]
> --
> BUT the database name referenced in the error is NOT the database I'm
> trying to restore!
> So, I then created a new database with the same name as the one I
> detached, and selected "restore" and went through the same process, and I
> then get a different error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The backup set holds a backup of a database other than the existing
> 'EMS_Ultimate' database.
> RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error:
> 3154)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476[/url]
> --
> EMS_Ultimate is the dB I'm trying to restore! And that's the name of the
> dB in the backup file...now I'm really nervous!
>
|||Sounds to me like the MDF file is still in the Data folder.
And the error message references the name of the *file* no the name of the
database. They do not have to be the same.
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:ekhyVxeKHHA.320@.TK2MSFTNGP06.phx.gbl...
> Yeah, there's only one drive/partition on the system, and I've searched
> everywhere...it's g-o-n-e.
> I performed a full dB backup as well as TLog backup before detaching it.
> I'm having trouble restoring from the backup now.
> First, I tried to restore by typing in the name of the dB in the "to
> database" and attaching the backup file. The backup shows the name of the
> dB in the backup set, but when I select the "full database backup" and
> click OK, I get a strange error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: File "EMS_Metro_Data" cannot be restored
> over the existing "C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\DATA\EMS_Metro_Data_new.MDF". Reissue the RESTORE
> statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE
> to identify an alternate location. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&LinkId=20476[/url]
> --
> BUT the database name referenced in the error is NOT the database I'm
> trying to restore!
> So, I then created a new database with the same name as the one I
> detached, and selected "restore" and went through the same process, and I
> then get a different error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Restore failed for Server 'proto'. (Microsoft.SqlServer.Smo)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&Ev tID=Restore+Server&LinkId=20476[/url]
> --
> ADDITIONAL INFORMATION:
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The backup set holds a backup of a database other than the existing
> 'EMS_Ultimate' database.
> RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error:
> 3154)
> For help, click:
> [url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .2047&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476[/url]
> --
> EMS_Ultimate is the dB I'm trying to restore! And that's the name of the
> dB in the backup file...now I'm really nervous!
>
|||
> Sounds to me like the MDF file is still in the Data folder.
> And the error message references the name of the *file* no the name of the
> database. They do not have to be the same.
>
OK, this is very bizarre...but when I clicked on the "options" in the
"restore database" window, all the settings seemed to be from a previous
restore I did months and months ago on a different database...I don't
understand why those settings would be preserved, when on the first panel
("general") I'm inputting completely different information and selecting a
completely different backup file, but I guess that's one of the mysteries of
the SQL2005.
The errors below were occuring because the MDF and LDF files existed for the
database specified in the options pane...not for the one I was trying to
restore. When I modified the settings in the "options" pane it worked
properly...oh well...at least I have the data back...I was worried the wrong
database was in the backup file.
Thanks for bearing with me!

>
> "geek-y-guy" <noone@.nowhere.org> wrote in message
> news:ekhyVxeKHHA.320@.TK2MSFTNGP06.phx.gbl...
>

Friday, March 9, 2012

Detach sql2000 msde database using SMO.

Hello,

I am trying to detach a database from an instance of msde 2000 using smo. When I call the Server.DetachDatabase function using my valid server object I get the following error.

"This method or property is accessible only while working against SQL Server 2005 or later."

Does anyone know if it is possible to detach a msde database using the .net smo objects.

What I am trying to do is detach the database and then re-atach to an instance of sqlexpress. I thought about just stopping the server and copying the mdf file to a new location but I cannot figure out how to stop the server and restart it using smo.

Thanks,

Oh you don't have to go through all of that trouble just use the transfer class. Be sure to look at the information in MSDN regarding the Transfer class (specifically regarding its limitations). But going from 2000 to a 2005 DB should be well within its capabilities. Use the overloaded constructor that takes an existing database object to get started.

Hope this helps.

Michael

|||I suspect this may be a bug that you are running into. Detaching a SQL Server 2000 Database is supported by SMO. Could you post a small repro source code here so I can take a look?|||

Here is the main part of the code. I get an exception on the detach function that tells me that this function is valid on sql2005 databases only. The server object is valid and connected and the function will work on a 2005 database.

Dim KBMSS As Server = Nothing

Dim svc As New ServerConnection

With svc

.ApplicationName = Application.ProductName

.ServerInstance = ServerName

.StatementTimeout = 30

.LoginSecure = False

.Login = "sa"

.Password = "kbmsa"

End With

KBMSS = New Server(svc)

KBMSS.DetachDatabase("KBM", False)

|||

I've had a similar message when attempting to itterate through triggers in a database

connects =

m_srvconnSelectedServerConnection.ServerInstance = comboServer.Text;
m_srvconnSelectedServerConnection.LoginSecure = false;
m_srvconnSelectedServerConnection.Login = this.textUser.Text;
m_srvconnSelectedServerConnection.Password = this.textPassword.Text;
m_serverSelectedServer = new Server(m_srvconnSelectedServerConnection);

itterate triggers =

Database SelectedDatabase = m_serverSelectedServer.Databases[comboDB.Text];

foreach (Trigger trgTRG in SelectedDatabase.Triggers)
{
}

version of connected server reports =

?m_serverSelectedServer.Information.Version
{8.0.760}

cheers

Detach sql2000 msde database using SMO.

Hello,

I am trying to detach a database from an instance of msde 2000 using smo. When I call the Server.DetachDatabase function using my valid server object I get the following error.

"This method or property is accessible only while working against SQL Server 2005 or later."

Does anyone know if it is possible to detach a msde database using the .net smo objects.

Thanks,

Hope this helps.

Michael

|||I suspect this may be a bug that you are running into. Detaching a SQL Server 2000 Database is supported by SMO. Could you post a small repro source code here so I can take a look?|||

Dim KBMSS As Server = Nothing

Dim svc As New ServerConnection

With svc

.ApplicationName = Application.ProductName

.ServerInstance = ServerName

.StatementTimeout = 30

.LoginSecure = False

.Login = "sa"

.Password = "kbmsa"

End With

KBMSS = New Server(svc)

KBMSS.DetachDatabase("KBM", False)

|||

I've had a similar message when attempting to itterate through triggers in a database

connects =

itterate triggers =

Database SelectedDatabase = m_serverSelectedServer.Databases[comboDB.Text];

foreach (Trigger trgTRG in SelectedDatabase.Triggers)
{
}

version of connected server reports =

?m_serverSelectedServer.Information.Version
{8.0.760}

cheers

Detach sql2000 msde database using SMO.

Hello,

I am trying to detach a database from an instance of msde 2000 using smo. When I call the Server.DetachDatabase function using my valid server object I get the following error.

"This method or property is accessible only while working against SQL Server 2005 or later."

Does anyone know if it is possible to detach a msde database using the .net smo objects.

Thanks,

Hope this helps.

Michael

|||I suspect this may be a bug that you are running into. Detaching a SQL Server 2000 Database is supported by SMO. Could you post a small repro source code here so I can take a look?|||

Dim KBMSS As Server = Nothing

Dim svc As New ServerConnection

With svc

.ApplicationName = Application.ProductName

.ServerInstance = ServerName

.StatementTimeout = 30

.LoginSecure = False

.Login = "sa"

.Password = "kbmsa"

End With

KBMSS = New Server(svc)

KBMSS.DetachDatabase("KBM", False)

|||

I've had a similar message when attempting to itterate through triggers in a database

connects =

itterate triggers =

Database SelectedDatabase = m_serverSelectedServer.Databases[comboDB.Text];

foreach (Trigger trgTRG in SelectedDatabase.Triggers)
{
}

version of connected server reports =

?m_serverSelectedServer.Information.Version
{8.0.760}

cheers

Detach and Attach functions in SQL Server 2005

Hi,

I'm trying to port my ASP.NET web application to the production system.

I'm connection to the SQL Server 2005 instance on my hosting server via CTP. I've uploaded the .mdf and .ldf files of my DB via FTP to the hosting server, and then tried to attach using this command:

use master gosp_attach_db'tgp','F:\webspace\disk20\db\TGP.mdf','F:\webspace\disk20\db\TGP_log.ldf' go

but then there is an error (obviously) stating that I don't have permissions to create a database in database master.

I must admit that I'm prettycluelessin this area. My hostingservicesalready created a "place holder" for my database (tgp), but I don't know how to proceed from here in order attach the database files in the production environment. Is this something I can do myself, or must I involve the hosting services?

Thanks,

Alon

sp_attach_db requires the same permissions as CREATE DATABASE - so you're likely not going to be able to do this (if so, let me know who your host is - I could use the free/extra space they'd let me set up *grin*).

You'll either need to contact them and have them hook up your DB, or look into using user-instance/attachable SQL Express functionality.|||

You have two options just backup your database and use management studio to restore your database on the host SQL Server after you have registered the host SQL Server in your management studio. In the backup and restore wizard choose the restore from device option. The other option is try the thread below for Attach database code modify it for your needs and use it. Hope this helps.

http://forums.asp.net/thread/981274.aspx

|||

Thak you for your reply.

I tried to backup/restore, but when I tried to point to the location of the backup file (on the remote server), got the error message that I'm not authorized... I've just sent a request from the hosting firm to handle this.

I have a general question: what are the guidelines when moving from the test/development system to the production system? I couldn't find any article summarizing the process.

Thank you,

Alon

|||You should only use a company that gives you access to management studio so you can use the backup and restore wizard because if you don't backup your database they will charge you to back it up. Here in the US most hosting company will give you the access. Hope this helps.|||

Yep, this helps Smile [:)]

Alon

Detach and Attach

Can we use mdf and ldf files from SQl 7 and attach into SQL 2000 instance
ThanksYes. The attach process will perform all necessary upgrades. The changes
are to the system stuff only. You will need to update statistics on the
newly upgraded database immediately or query plans for the newly attached
database will be goofy.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:0D2356C7-4E49-458E-B133-1613939DBFB6@.microsoft.com...
> Can we use mdf and ldf files from SQl 7 and attach into SQL 2000 instance
> Thanks

Tuesday, February 14, 2012

DESIGN FLAWS IN MANAGEMENT STUDIO ??

Greetings,
Please tell me we don't have to live with this annoying thing where if
you try to open multiple .sql files it opens a new instance of
Management Studio and prompts you to connect to a server'
I JUST want to modify some sql scripts dangitt.
If so, MS please get on the ball to get this corrected. I am switching
my .sql files back to opening in Visual Studio and refuse to use this
tool until corrected. In addition it seems very bloated and load times
for the application are annoying. Am I the only one seeing this?
Thanks,
MikeI agree Mike. I like the way that Enterprise Manager and Query Analyzer are
integrated into one tool, but Management Studio is clunky and much slower
than the old tools. It drives me nuts when I open a SQL file, and MS creates
a new tabbed window and connection instead of opening the file in my existing
window.
Also, when generating the script for a table, you no longer gives you the
ability to leave out the junk you don't want, like collation, unless you go
through the script wizard, which is slow. I just want to show the script for
a single table for crying out loud.
I just installed SP1 hoping that they changed some of these things, but it
looks like Microsoft ignored Management Studio.
Tom
"mike.mcneer@.gmail.com" wrote:
> Greetings,
> Please tell me we don't have to live with this annoying thing where if
> you try to open multiple .sql files it opens a new instance of
> Management Studio and prompts you to connect to a server'
> I JUST want to modify some sql scripts dangitt.
> If so, MS please get on the ball to get this corrected. I am switching
> my .sql files back to opening in Visual Studio and refuse to use this
> tool until corrected. In addition it seems very bloated and load times
> for the application are annoying. Am I the only one seeing this?
> Thanks,
> Mike
>|||http://lab.msdn.microsoft.com/productfeedback/Default.aspx
You can file bugs as well as suggestions out here. Filing a suggestion that
basically says "it sucks, fix it" is a quick way to be completely ignored.
Detail exactly what you are seeing, what you expect it to do, and what it
actually does.
Then the nice thing about this site? The entire world has the ability to
vote on suggestions as well as post comments also. If enough people agree
that something should be done, it will get done. Having messages scattered
across a newsgroup related to product improvements isn't going to help
determine what is important to all of us and what isn't.
I personally don't find an issue with the performance of Management Studio.
BUT, I use Query Analyzer for my day to day SQL Server 2005 tasks. It's
like any managed code application I've ever come across, it is VERY slow the
first time it is launched, because all kinds of assemblies and other stuff
have to be loaded. Everytime I start it after that, it takes very little
time to launch. So, I launch SSMS when I start my machine up and since I
don't reboot it unless something forces me to, it get decent performance out
of SSMS for weeks at a time.
As for the connection dialog, well, it didn't work that way initially. Back
in the early betas, SSMS would launch without ever requiring a connection
and scripts would also open up without requiring a connection. However, a
bunch of people screamed about it not being connected and those voices were
a lot louder than the ones who didn't want to always connect to an instance,
so SSMS now pops up a dialog to connect to an instance when you launch it.
If enough people want it the other way, it isn't that hard to make the
change.
Also, you are taking about functionality changes. There isn't an actual bug
in anything that you pointed out. So, don't expect anything to be done
about it until the next version. They are very careful to not introduce new
functionality, which is what you are asking for, in a service pack.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:E68D3248-4B78-4BC7-9B40-319A660C29C2@.microsoft.com...
>I agree Mike. I like the way that Enterprise Manager and Query Analyzer
>are
> integrated into one tool, but Management Studio is clunky and much slower
> than the old tools. It drives me nuts when I open a SQL file, and MS
> creates
> a new tabbed window and connection instead of opening the file in my
> existing
> window.
> Also, when generating the script for a table, you no longer gives you the
> ability to leave out the junk you don't want, like collation, unless you
> go
> through the script wizard, which is slow. I just want to show the script
> for
> a single table for crying out loud.
> I just installed SP1 hoping that they changed some of these things, but it
> looks like Microsoft ignored Management Studio.
> Tom
> "mike.mcneer@.gmail.com" wrote:
>> Greetings,
>> Please tell me we don't have to live with this annoying thing where if
>> you try to open multiple .sql files it opens a new instance of
>> Management Studio and prompts you to connect to a server'
>> I JUST want to modify some sql scripts dangitt.
>> If so, MS please get on the ball to get this corrected. I am switching
>> my .sql files back to opening in Visual Studio and refuse to use this
>> tool until corrected. In addition it seems very bloated and load times
>> for the application are annoying. Am I the only one seeing this?
>> Thanks,
>> Mike
>>|||On Fri, 28 Apr 2006 09:07:02 -0700, Tom wrote:
> It drives me nuts when I open a SQL file, and MS creates
>a new tabbed window and connection instead of opening the file in my existing
>window.
Hi Tom,
What would yu prefer, then? Would you want MS to discard whatever is
currently in the active query window and replace it with the contents of
the file you double-clicked? What would your reaction be if you lost two
hours worth of query-writing because you accidentally doouble-clicked a
SQL file?
BTW, I have set notepad to be the default application for the .sql
suffix. Maybe an idea for you too?
>Also, when generating the script for a table, you no longer gives you the
>ability to leave out the junk you don't want, like collation, unless you go
>through the script wizard, which is slow. I just want to show the script for
>a single table for crying out loud.
Agreed - this is a major pain.
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:60u4521197dgmlfjkvhn0uo88h7tdhsm5c@.4ax.com...
> On Fri, 28 Apr 2006 09:07:02 -0700, Tom wrote:
> > It drives me nuts when I open a SQL file, and MS creates
> >a new tabbed window and connection instead of opening the file in my
existing
> >window.
> Hi Tom,
> What would yu prefer, then? Would you want MS to discard whatever is
> currently in the active query window and replace it with the contents of
> the file you double-clicked? What would your reaction be if you lost two
> hours worth of query-writing because you accidentally doouble-clicked a
> SQL file?
>
Not sure what exactly he's referring to, but I know what annoys me.
With QA with SQL 2000, if I had say 5 .SQL files in a directory and opened
each one (double clicking) it would open a new window in the existing copy
of QA (I believe using the current connection info).
Now, with SMS, it opens a new COPY of SMS each time. Which is much slower
and a royal pain.
I've also noticed that for some strange reason I seem to "mistype" the
password in SMS when I try to connect a LOT more than in QA. It's very
weird, it's almost like it doesn't see me type a character, or is missing it
when I hit the shift key. I know at least 1-2 others that have said they
have a similar experience.
> BTW, I have set notepad to be the default application for the .sql
> suffix. Maybe an idea for you too?
That's probably what I'm going to do.
> >Also, when generating the script for a table, you no longer gives you the
> >ability to leave out the junk you don't want, like collation, unless you
go
> >through the script wizard, which is slow. I just want to show the script
for
> >a single table for crying out loud.
> Agreed - this is a major pain.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 28 Apr 2006 21:57:42 -0400, Greg D. Moore (Strider) wrote:
(snip)
>With QA with SQL 2000, if I had say 5 .SQL files in a directory and opened
>each one (double clicking) it would open a new window in the existing copy
>of QA (I believe using the current connection info).
>Now, with SMS, it opens a new COPY of SMS each time. Which is much slower
>and a royal pain.
Hi Greg,
Strange. If I restore the original default action for .SQL files and
doubleclick one, it loads in a new tab in my open SSMS (though it does
bug me for connection details).
If I drag across a bunch of .SQL files to select them alll and hit
enter, than I do get several instances of SSMS. One of the query loads
in the existing SSMS, the others all load a new instance. Not good.
>I've also noticed that for some strange reason I seem to "mistype" the
>password in SMS when I try to connect a LOT more than in QA.
I use Windows Authentication, so that's not a problem for me. :-)
--
Hugo Kornelis, SQL Server MVP|||I agree totally :-( Something went terribly wrong in the year 2005. Not only
this, but also Visausl Studio 2005 has serious problems :-(
PS: I still use Query analyzer: much much faster :-)|||thanks guys, I agree I shouldn't have probably griped too much but, I
just wanted to make sure I wasn't the only one dealing with this and
maybe there was a way around other than default my sql files back to
something else. When you have 40 Storedp rocedures, 10 triggers and
table change scripts for a build every other week you need something
speedy and quick to get those opened and compiled.

DESIGN FLAWS IN MANAGEMENT STUDIO ??

Greetings,
Please tell me we don't have to live with this annoying thing where if
you try to open multiple .sql files it opens a new instance of
Management Studio and prompts you to connect to a server'
I JUST want to modify some sql scripts dangitt.
If so, MS please get on the ball to get this corrected. I am switching
my .sql files back to opening in Visual Studio and refuse to use this
tool until corrected. In addition it seems very bloated and load times
for the application are annoying. Am I the only one seeing this?
Thanks,
MikeI agree Mike. I like the way that Enterprise Manager and Query Analyzer are
integrated into one tool, but Management Studio is clunky and much slower
than the old tools. It drives me nuts when I open a SQL file, and MS create
s
a new tabbed window and connection instead of opening the file in my existin
g
window.
Also, when generating the script for a table, you no longer gives you the
ability to leave out the junk you don't want, like collation, unless you go
through the script wizard, which is slow. I just want to show the script fo
r
a single table for crying out loud.
I just installed SP1 hoping that they changed some of these things, but it
looks like Microsoft ignored Management Studio.
Tom
"mike.mcneer@.gmail.com" wrote:

> Greetings,
> Please tell me we don't have to live with this annoying thing where if
> you try to open multiple .sql files it opens a new instance of
> Management Studio and prompts you to connect to a server'
> I JUST want to modify some sql scripts dangitt.
> If so, MS please get on the ball to get this corrected. I am switching
> my .sql files back to opening in Visual Studio and refuse to use this
> tool until corrected. In addition it seems very bloated and load times
> for the application are annoying. Am I the only one seeing this?
> Thanks,
> Mike
>|||http://lab.msdn.microsoft.com/produ...ck/Default.aspx
You can file bugs as well as suggestions out here. Filing a suggestion that
basically says "it sucks, fix it" is a quick way to be completely ignored.
Detail exactly what you are seeing, what you expect it to do, and what it
actually does.
Then the nice thing about this site? The entire world has the ability to
vote on suggestions as well as post comments also. If enough people agree
that something should be done, it will get done. Having messages scattered
across a newsgroup related to product improvements isn't going to help
determine what is important to all of us and what isn't.
I personally don't find an issue with the performance of Management Studio.
BUT, I use Query Analyzer for my day to day SQL Server 2005 tasks. It's
like any managed code application I've ever come across, it is VERY slow the
first time it is launched, because all kinds of assemblies and other stuff
have to be loaded. Everytime I start it after that, it takes very little
time to launch. So, I launch SSMS when I start my machine up and since I
don't reboot it unless something forces me to, it get decent performance out
of SSMS for weeks at a time.
As for the connection dialog, well, it didn't work that way initially. Back
in the early betas, SSMS would launch without ever requiring a connection
and scripts would also open up without requiring a connection. However, a
bunch of people screamed about it not being connected and those voices were
a lot louder than the ones who didn't want to always connect to an instance,
so SSMS now pops up a dialog to connect to an instance when you launch it.
If enough people want it the other way, it isn't that hard to make the
change.
Also, you are taking about functionality changes. There isn't an actual bug
in anything that you pointed out. So, don't expect anything to be done
about it until the next version. They are very careful to not introduce new
functionality, which is what you are asking for, in a service pack.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:E68D3248-4B78-4BC7-9B40-319A660C29C2@.microsoft.com...[vbcol=seagreen]
>I agree Mike. I like the way that Enterprise Manager and Query Analyzer
>are
> integrated into one tool, but Management Studio is clunky and much slower
> than the old tools. It drives me nuts when I open a SQL file, and MS
> creates
> a new tabbed window and connection instead of opening the file in my
> existing
> window.
> Also, when generating the script for a table, you no longer gives you the
> ability to leave out the junk you don't want, like collation, unless you
> go
> through the script wizard, which is slow. I just want to show the script
> for
> a single table for crying out loud.
> I just installed SP1 hoping that they changed some of these things, but it
> looks like Microsoft ignored Management Studio.
> Tom
> "mike.mcneer@.gmail.com" wrote:
>|||On Fri, 28 Apr 2006 09:07:02 -0700, Tom wrote:

> It drives me nuts when I open a SQL file, and MS creates
>a new tabbed window and connection instead of opening the file in my existi
ng
>window.
Hi Tom,
What would yu prefer, then? Would you want MS to discard whatever is
currently in the active query window and replace it with the contents of
the file you double-clicked? What would your reaction be if you lost two
hours worth of query-writing because you accidentally doouble-clicked a
SQL file?
BTW, I have set notepad to be the default application for the .sql
suffix. Maybe an idea for you too?

>Also, when generating the script for a table, you no longer gives you the
>ability to leave out the junk you don't want, like collation, unless you go
>through the script wizard, which is slow. I just want to show the script f
or
>a single table for crying out loud.
Agreed - this is a major pain.
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:60u4521197dgmlfjkvhn0uo88h7tdhsm5c@.
4ax.com...
> On Fri, 28 Apr 2006 09:07:02 -0700, Tom wrote:
>
existing[vbcol=seagreen]
> Hi Tom,
> What would yu prefer, then? Would you want MS to discard whatever is
> currently in the active query window and replace it with the contents of
> the file you double-clicked? What would your reaction be if you lost two
> hours worth of query-writing because you accidentally doouble-clicked a
> SQL file?
>
Not sure what exactly he's referring to, but I know what annoys me.
With QA with SQL 2000, if I had say 5 .SQL files in a directory and opened
each one (double clicking) it would open a new window in the existing copy
of QA (I believe using the current connection info).
Now, with SMS, it opens a new COPY of SMS each time. Which is much slower
and a royal pain.
I've also noticed that for some strange reason I seem to "mistype" the
password in SMS when I try to connect a LOT more than in QA. It's very
weird, it's almost like it doesn't see me type a character, or is missing it
when I hit the shift key. I know at least 1-2 others that have said they
have a similar experience.

> BTW, I have set notepad to be the default application for the .sql
> suffix. Maybe an idea for you too?
That's probably what I'm going to do.

>
go[vbcol=seagreen]
for[vbcol=seagreen]
> Agreed - this is a major pain.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 28 Apr 2006 21:57:42 -0400, Greg D. Moore (Strider) wrote:
(snip)
>With QA with SQL 2000, if I had say 5 .SQL files in a directory and opened
>each one (double clicking) it would open a new window in the existing copy
>of QA (I believe using the current connection info).
>Now, with SMS, it opens a new COPY of SMS each time. Which is much slower
>and a royal pain.
Hi Greg,
Strange. If I restore the original default action for .SQL files and
doubleclick one, it loads in a new tab in my open SSMS (though it does
bug me for connection details).
If I drag across a bunch of .SQL files to select them alll and hit
enter, than I do get several instances of SSMS. One of the query loads
in the existing SSMS, the others all load a new instance. Not good.

>I've also noticed that for some strange reason I seem to "mistype" the
>password in SMS when I try to connect a LOT more than in QA.
I use Windows Authentication, so that's not a problem for me. :-)
Hugo Kornelis, SQL Server MVP|||I agree totally :-( Something went terribly wrong in the year 2005. Not only
this, but also Visausl Studio 2005 has serious problems :-(
PS: I still use Query analyzer: much much faster :-)|||thanks guys, I agree I shouldn't have probably griped too much but, I
just wanted to make sure I wasn't the only one dealing with this and
maybe there was a way around other than default my sql files back to
something else. When you have 40 Storedp rocedures, 10 triggers and
table change scripts for a build every other week you need something
speedy and quick to get those opened and compiled.

Thursday, March 29, 2012

Determine what server i'm running on

Tuesday, March 27, 2012

Determine running size of memtoleave area

Sunday, March 25, 2012

Determine Date Range Falls between other Date Range

Thursday, March 22, 2012

Determine a date not in the table

Detecting SQL Server 2000 vs 2005 instance

Detecting SQL Server 2000 vs 2005 instance

Detecting local Sql instances

Wednesday, March 21, 2012

Detecting (local) sql-server

Detect installed MSDE version

Detect installed MSDE version

Detect (local) SQL Server

Sunday, March 11, 2012

detached database disappears?

Friday, March 9, 2012

Detach sql2000 msde database using SMO.

Detach sql2000 msde database using SMO.

Detach sql2000 msde database using SMO.

Detach and Attach functions in SQL Server 2005

Detach and Attach

Tuesday, February 14, 2012

DESIGN FLAWS IN MANAGEMENT STUDIO ??

DESIGN FLAWS IN MANAGEMENT STUDIO ??

Determining Security

Blog Archive

About Me