how do I alter the column with index on it

hi,
I have a sqlserver 2000 db. I want to alter two columns
from one of the tables to not allow nulls.
but these 2 columns (one if char, and the other is
smalldatetime), one have clustered index on it, and the
other one is part of a combined index of two columns.
is the only way for the column alteration is to drop the
index first, and then re-create it later?
many thanks!
JJ
Hi,
Altering the data type of one column which has participated in an index is
not supported. If the modified column participates in an index, the only
type changes that are allowed are increasing the length of a
variable-length type (for example, VARCHAR(10) to VARCHAR(20)), changing
nullability of the column, or both. I believe you have to drop the original
index and rebuild a new index.
Thanks
Hari
MCDBA
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:2131001c45a37$30e96600$a501280a@.phx.gbl...
> hi,
> I have a sqlserver 2000 db. I want to alter two columns
> from one of the tables to not allow nulls.
> but these 2 columns (one if char, and the other is
> smalldatetime), one have clustered index on it, and the
> other one is part of a combined index of two columns.
> is the only way for the column alteration is to drop the
> index first, and then re-create it later?
> many thanks!
> JJ
|||You can user table designer shipped with SQL server client tools or
Visuastudio to change the table.
Lishil, VSDATA Team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Subject: how do I alter the column with index on it
>Date: Thu, 24 Jun 2004 15:04:16 -0700
>Lines: 15
>Message-ID: <2131001c45a37$30e96600$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRaNzDpwp7EieNJRQCcEtOkif2wyg==
>Newsgroups:
microsoft.public.sqlserver.programming,microsoft.p ublic.sqlserver.tools
>Path: cpmsftngxa10.phx.gbl
>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.tools:24659
microsoft.public.sqlserver.programming:453163
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>hi,
>I have a sqlserver 2000 db. I want to alter two columns
>from one of the tables to not allow nulls.
>but these 2 columns (one if char, and the other is
>smalldatetime), one have clustered index on it, and the
>other one is part of a combined index of two columns.
>is the only way for the column alteration is to drop the
>index first, and then re-create it later?
>many thanks!
>JJ
>
|||thanks Lishil. I know the tool to alter it, I just want
to know whether I have to drop the index first before I
alter the column. please see my privious email for detail.
thanks.
JJ
>--Original Message--
>You can user table designer shipped with SQL server
client tools or
>Visuastudio to change the table.
>Lishil, VSDATA Team
>
>--
>microsoft.public.sqlserver.programming,microsoft. public.sq
lserver.tools[vbcol=seagreen]
microsoft.public.sqlserver.tools:24659
>microsoft.public.sqlserver.programming:453163
>.
>
|||thanks Hari. I fear so too. oh well...
thanks.
JJ
>--Original Message--
>Hi,
>Altering the data type of one column which has
participated in an index is
>not supported. If the modified column participates in an
index, the only
>type changes that are allowed are increasing the length
of a
>variable-length type (for example, VARCHAR(10) to VARCHAR
(20)), changing
>nullability of the column, or both. I believe you have to
drop the original
>index and rebuild a new index.
>
>--
>Thanks
>Hari
>MCDBA
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2131001c45a37$30e96600$a501280a@.phx.gbl...
>
>.
>
|||Ok, you already get answer from hari_prasad_k@.hotmail.com.
You do need to drop index in you case.
Lishi Liu, VSData team
--
>Content-Class: urn:content-classes:message
>From: "JJ Wang" <anonymous@.discussions.microsoft.com>
>Sender: "JJ Wang" <anonymous@.discussions.microsoft.com>
>References: <2131001c45a37$30e96600$a501280a@.phx.gbl>
<$AXF2GuXEHA.2352@.cpmsftngxa06.phx.gbl>
>Subject: RE: how do I alter the column with index on it
>Date: Thu, 8 Jul 2004 18:24:14 -0700
>Lines: 61
>Message-ID: <2962a01c46553$72445bb0$a501280a@.phx.gbl>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="iso-8859-1"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>Thread-Index: AcRlU3JEKPTFyjEUT129z6+ojcBcmg==
>Newsgroups:
microsoft.public.sqlserver.tools,microsoft.public. sqlserver.programming
>Path: cpmsftngxa06.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:456466
microsoft.public.sqlserver.tools:24467
>NNTP-Posting-Host: tk2msftngxa13.phx.gbl 10.40.1.165
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>thanks Lishil. I know the tool to alter it, I just want
>to know whether I have to drop the index first before I
>alter the column. please see my privious email for detail.
>thanks.
>JJ
>client tools or
>lserver.tools
>microsoft.public.sqlserver.tools:24659
>

How do I administer SqlServer 2005

Hi;
I just installed SqlServer 2005 developer edition and I can't find any way
to administer it. There is a "Sql Server Configuration Manager" in the menu
but nothing like the Enterprise Manager in SqlServer 2000.
What am I missing?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
You are missing SQL Server Management Studio.
You may need to put in the disk again, and select INSTALL Client Tools.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:CA288C02-B0C8-4F6E-A075-BB6AE535BCCF@.microsoft.com...
> Hi;
> I just installed SqlServer 2005 developer edition and I can't find any way
> to administer it. There is a "Sql Server Configuration Manager" in the
> menu
> but nothing like the Enterprise Manager in SqlServer 2000.
> What am I missing?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
|||that was it.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Arnie Rowland" wrote:

> You are missing SQL Server Management Studio.
> You may need to put in the disk again, and select INSTALL Client Tools.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:CA288C02-B0C8-4F6E-A075-BB6AE535BCCF@.microsoft.com...
>
>
|||Thanks Dave -and I enjoyed 'Cubicle Wars'. Nice.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:BD10F348-D97E-4B53-9D16-9EE9DEE054EE@.microsoft.com...[vbcol=seagreen]
> that was it.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
>
> "Arnie Rowland" wrote:
|||Hi David
You may install the client tools, then you'll get SQL Server Management
Studio.
If you need a web-based solution, then you can try myLittleAdmin for SQL
Server 2005
http://www.mylittleadmin.com
Best regards
David Thielen wrote:
> Hi;
> I just installed SqlServer 2005 developer edition and I can't find
> any way to administer it. There is a "Sql Server Configuration
> Manager" in the menu but nothing like the Enterprise Manager in
> SqlServer 2000.
> What am I missing?
I'm protected by SpamBrave
http://www.spambrave.com/

How do disable a user to "Generate SQL Scripts"

I have an SQLServer 2000 instance with a DataBase.
I have 2 users: sa, new_user.
The [new_user] has membership role: public, db_datareader, db_datawriter
It has permissions to manipulate some table and stored procedures. He can
not export, import, backup, restore the data base.
The point is that he can generate a script for the all database!!!
How do I disable it?
Can some one give me some help on this issue.
Thanks,
JoaoRegoSince you give the user db_datareader membership, then they can read
anything in the database, including the structure of the tables.
You can deny permissions to system tables like sysobjects to prevent listing
tables for example, like so ->
deny select on sysobjects to user1
This would prevent user1 from getting a list of tables for example.
Another common scheme is to only allow user's access to views and totally
restrict access to the actual underlying tables.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Joao Rego" <Joao Rego@.discussions.microsoft.com> wrote in message
news:4B2E9F96-E012-4CF3-8033-A19A6E0DD797@.microsoft.com...
>I have an SQLServer 2000 instance with a DataBase.
> I have 2 users: sa, new_user.
> The [new_user] has membership role: public, db_datareader, db_datawrit
er
> It has permissions to manipulate some table and stored procedures. He can
> not export, import, backup, restore the data base.
> The point is that he can generate a script for the all database!!!
> How do I disable it?
> Can some one give me some help on this issue.
> Thanks,
> JoaoRegosql

How Detect SMO is installed?

Hi all

I'm using Microsoft.SqlServer.Management.Smo namespace for retrieve a list of avaible SQL Servers.

The problem is when SMO is not installed in the client machine. The application crashes although I put the command in a try - catch block.

try { DataTable dtSQLServers = SmoApplication.EnumAvailableSqlServers(false); }

catch { notInstalled = true }

Are there any easy way to know if SMO is installed , so i can advice the user that he must install SMO first?

Thanks.

You either have to lookup the file references assembly or use a component to check the reference to the GAC: http://www.codeproject.com/dotnet/Assemblydependencies.asp?df=100&forumid=31276&exp=0&select=977211

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

How check the results of the SQL Backups on serveral servers

Hi, I'm looking for a simple an easy solution to check the sqlserver backups
,
every day we run some backups jobs in diferent servers. How i can verify the
backup jobs everyday without check the enterprise manager of ervery server?
Is posible create a report where the copys are succesfuly per server and per
sql server database?
Thanks in advance
Fernando... Or use xp_smtp_sendmail and remove xp_sendmail and create a version of
xp_sendmail which in turn
calls xp_smtp_sendmail. As described on www.sqldev.net.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:092001c490db$294323c0$a401280a@.phx.gbl...[vbcol=seagreen]
> You could get SQL Server Agent to email you results of the
> backup, but that is dependant upon SQL Agent having a
> domain user id with a mailbox.
> Thanks
> Peter
> "There is no such thing as public opinion. There is only
> published opinion."
> Winston Churchill
>
> the sqlserver backups,
> How i can verify the
> of ervery server?
> per server and per

How check the results of the SQL Backups on serveral servers

Hi, I'm looking for a simple an easy solution to check the sqlserver backups,
every day we run some backups jobs in diferent servers. How i can verify the
backup jobs everyday without check the enterprise manager of ervery server?
Is posible create a report where the copys are succesfuly per server and per
sql server database?
Thanks in advance
FernandoYou could get SQL Server Agent to email you results of the
backup, but that is dependant upon SQL Agent having a
domain user id with a mailbox.
Thanks
Peter
"There is no such thing as public opinion. There is only
published opinion."
Winston Churchill
>--Original Message--
>Hi, I'm looking for a simple an easy solution to check
the sqlserver backups,
>every day we run some backups jobs in diferent servers.
How i can verify the
>backup jobs everyday without check the enterprise manager
of ervery server?
>Is posible create a report where the copys are succesfuly
per server and per
>sql server database?
>Thanks in advance
>Fernando
>.
>|||... Or use xp_smtp_sendmail and remove xp_sendmail and create a version of xp_sendmail which in turn
calls xp_smtp_sendmail. As described on www.sqldev.net.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:092001c490db$294323c0$a401280a@.phx.gbl...
> You could get SQL Server Agent to email you results of the
> backup, but that is dependant upon SQL Agent having a
> domain user id with a mailbox.
> Thanks
> Peter
> "There is no such thing as public opinion. There is only
> published opinion."
> Winston Churchill
>
> >--Original Message--
> >Hi, I'm looking for a simple an easy solution to check
> the sqlserver backups,
> >every day we run some backups jobs in diferent servers.
> How i can verify the
> >backup jobs everyday without check the enterprise manager
> of ervery server?
> >Is posible create a report where the copys are succesfuly
> per server and per
> >sql server database?
> >
> >Thanks in advance
> >Fernando
> >.
> >

How check the results of the SQL Backups on serveral servers

Hi, I'm looking for a simple an easy solution to check the sqlserver backups,
every day we run some backups jobs in diferent servers. How i can verify the
backup jobs everyday without check the enterprise manager of ervery server?
Is posible create a report where the copys are succesfuly per server and per
sql server database?
Thanks in advance
Fernando
... Or use xp_smtp_sendmail and remove xp_sendmail and create a version of xp_sendmail which in turn
calls xp_smtp_sendmail. As described on www.sqldev.net.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:092001c490db$294323c0$a401280a@.phx.gbl...[vbcol=seagreen]
> You could get SQL Server Agent to email you results of the
> backup, but that is dependant upon SQL Agent having a
> domain user id with a mailbox.
> Thanks
> Peter
> "There is no such thing as public opinion. There is only
> published opinion."
> Winston Churchill
>
> the sqlserver backups,
> How i can verify the
> of ervery server?
> per server and per

How Change FullText Path?

We needed to change the drive path for one of our SQL
Server Instances. All system databases have been changed,
but we are having trouble changing the FullText. We have
edited the Registry, but the FullText service still fails
with a path error when attempting to bring online within
Cluster Administrator. What are the steps to changing the
FullText path?
| Content-Class: urn:content-classes:message
| From: "michael [multnomah]" <anonymous@.discussions.microsoft.com>
| Sender: "michael [multnomah]" <anonymous@.discussions.microsoft.com>
| Subject: How Change FullText Path?
| Date: Tue, 5 Oct 2004 19:32:51 -0700
| Lines: 7
| Message-ID: <0f0501c4ab4c$c7058b30$a601280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| thread-index: AcSrTMcFUHcHTCYIRLe5RnswyCyFGQ==
| Newsgroups: microsoft.public.sqlserver.clustering
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.clustering:15145
| NNTP-Posting-Host: tk2msftngxa14.phx.gbl 10.40.1.166
| X-Tomcat-NG: microsoft.public.sqlserver.clustering
|
| We needed to change the drive path for one of our SQL
| Server Instances. All system databases have been changed,
| but we are having trouble changing the FullText. We have
| edited the Registry, but the FullText service still fails
| with a path error when attempting to bring online within
| Cluster Administrator. What are the steps to changing the
| FullText path?
|
################################################## ###########
Hello Michael,
Have you gone through the following KB artilce that discusses how to move
full text catalogs:
240867 How to move, copy, and back up full-text catalog folders and files
http://support.microsoft.com/?id=240867
If the above does not seem to help you, then you can go about rebuilding
the full text resources in your cluster environmnet using the steps in the
below KB artilce:
812666 How to recover a failed full-text search resource on a clustered
http://support.microsoft.com/?id=812666
HTH,
Shashank Pawar
SQL Server Support Engineer, Microsoft
This posting is provided "AS IS" with no warranties, and confers no rights.

How can you force a password, from a sql login, to expire

How can you force a password, from a sql login, to expire?

I would like to use the password expiration feature for sql logins in SqlServer 2005. The msdn document provides example code for SqlClient SqlConnection.ChangePassword like in Bob Beauchemin's book. http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.changepassword.aspx

There is a modify_date in the system view sys.sql_logins but that is read-only.

Thanks,

Karl

If you are on a Windows 2003 Server, and the password policy for expiratiopn is enabled this will be automatically enabled for SQL Logins. Password policies for SQL Server 2005 are only available on a Windows 2003 Server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks Jens for replying,

I want to test the client side code in the scenario when the password expires for a sql server login. So I'm hoping for a method that I could repeat several times to test my client code.

Yes I am using Windows Server 2003. The password expiration is set to 90 days in the security policy. However, I don't want to wait 90 days to see If my test sql login account will expire, so I can test my client side code. I'm not the administrator for the Windows Server, but I am the administrator for SQL Server 2005.

It sounds like one possible approach would be to set the security policy to 1 day on a test server then wait one day, but that would only be good for one test?

Karl

|||If you enforce password policies from Windows 2003 Server one test should be enough. Just make sure to handle the appropiate codes like 18468 "Password Expired." and 18487 "Password has to be changed at first logon".

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

If you want to programatically modify the global password policy setting to a value that you can test can finish fast, you can try NetUserModalsSet(...) to set max_passwd_age to a value in seconds.

http://windowssdk.msdn.microsoft.com/en-us/library/ms707221.aspx

How can you force a password, from a sql login, to expire

How can you force a password, from a sql login, to expire?

I would like to use the password expiration feature for sql logins in SqlServer 2005. The msdn document provides example code for SqlClient SqlConnection.ChangePassword like in Bob Beauchemin's book. http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.changepassword.aspx

There is a modify_date in the system view sys.sql_logins but that is read-only.

Thanks,

Karl

If you are on a Windows 2003 Server, and the password policy for expiratiopn is enabled this will be automatically enabled for SQL Logins. Password policies for SQL Server 2005 are only available on a Windows 2003 Server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks Jens for replying,

I want to test the client side code in the scenario when the password expires for a sql server login. So I'm hoping for a method that I could repeat several times to test my client code.

Yes I am using Windows Server 2003. The password expiration is set to 90 days in the security policy. However, I don't want to wait 90 days to see If my test sql login account will expire, so I can test my client side code. I'm not the administrator for the Windows Server, but I am the administrator for SQL Server 2005.

It sounds like one possible approach would be to set the security policy to 1 day on a test server then wait one day, but that would only be good for one test?

Karl

|||If you enforce password policies from Windows 2003 Server one test should be enough. Just make sure to handle the appropiate codes like 18468 "Password Expired." and 18487 "Password has to be changed at first logon".

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

If you want to programatically modify the global password policy setting to a value that you can test can finish fast, you can try NetUserModalsSet(...) to set max_passwd_age to a value in seconds.

http://windowssdk.msdn.microsoft.com/en-us/library/ms707221.aspx

How Can Insert A Data In To This Table?

SALAM SIR,
CREATE TABLE Airlines_Master
( Aircode CHAR(2),
Airlines_name VARCHAR(15))
THIS IS MADE A TABLE IN SQLSERVER 2000.BUT HOW CAN INSERT DATA INTO THIS TABLE?Lookup and read the section on SELECT and INSERT statements in Books Online.|||Lookup and read the section on SELECT and INSERT statements in Books Online.
Well,Batman this post is too much.People asking questions on insert and select ....there sould be some limit..:shocked:
Joydeep|||Well,Batman this post is too much.People asking questions on insert and select ....there sould be some limit..:shocked:
JoydeepEvery time I create something "foolproof", mother nature goes out and creates a better fool. Following that same line of logic, there is no "lower limit" on how basic a question can be... Someone, somewhere, will ask anything you can imagine, and probably several things that you can't imagine too! It is just the nature of the beast, so sit back and enjoy, don't get your knickers in a twist!

-PatP

How can I verify that the sqlserver on my development is accessable from internet

Hello!
I'm trying to figure out if it's possible to reach the database on my
development machine from the outside (internet). To test this I'm trying to
connect to the database from the development machine by using my public IP
address. I also opned port 1433 in my firewall.
Is there any other ports I need to open? How can I verify that the sqlserver
on my development is accessable from internet?
Thanks!"Nutshell" <nospam@.nospam.com> wrote in message
news:%239FNk8bgHHA.596@.TK2MSFTNGP05.phx.gbl...

> I'm trying to figure out if it's possible to reach the database on my
> development machine from the outside (internet). To test this I'm trying
> to connect to the database from the development machine by using my public
> IP address. I also opned port 1433 in my firewall.
> Is there any other ports I need to open?
You'll want to give this a read (and some of the related links):
http://support.microsoft.com/kb/823938/en-us

> How can I verify that the sqlserver on my development is accessible from
> internet?
You should be able to use Query Analyzer or OSQL as a basic test
mechanism...
Steve|||Hello Steve,
I'll guess that the SQL Server need to use a static port?
1. From SQL Server Network Utility I'll see that port 1433 is default.
2. In the registry the settings looks like dynamic port allocation
3. The log displays:
2007-04-19 07:49:42.21 server SQL server listening on 192.168.0.6: 1433.
2007-04-19 07:49:42.34 server SQL server listening on 127.0.0.1: 1433.
2007-04-19 07:49:42.34 server SuperSocket Info: Bind failed on TCP port
1433.
2007-04-19 07:49:42.60 server SuperSocket Info: Bind failed on TCP port
1433.
I have tested to change the port to 1500, but I get the same bind error,
"Bind failed on TCP port 1500".
I have both SQL Server 9, and SQL Server Express on this machine. Is that
the problem?
Thanks!
"Steve Thompson" <stevethompson@.nomail.please> skrev i meddelandet
news:eOtYGIfgHHA.4916@.TK2MSFTNGP06.phx.gbl...
> "Nutshell" <nospam@.nospam.com> wrote in message
> news:%239FNk8bgHHA.596@.TK2MSFTNGP05.phx.gbl...
>
> You'll want to give this a read (and some of the related links):
> http://support.microsoft.com/kb/823938/en-us
>
> You should be able to use Query Analyzer or OSQL as a basic test
> mechanism...
> Steve
>

How can I verify that the sqlserver on my development is accessable from internet

Hello!
I'm trying to figure out if it's possible to reach the database on my
development machine from the outside (internet). To test this I'm trying to
connect to the database from the development machine by using my public IP
address. I also opned port 1433 in my firewall.
Is there any other ports I need to open? How can I verify that the sqlserver
on my development is accessable from internet?
Thanks!
"Nutshell" <nospam@.nospam.com> wrote in message
news:%239FNk8bgHHA.596@.TK2MSFTNGP05.phx.gbl...

> I'm trying to figure out if it's possible to reach the database on my
> development machine from the outside (internet). To test this I'm trying
> to connect to the database from the development machine by using my public
> IP address. I also opned port 1433 in my firewall.
> Is there any other ports I need to open?
You'll want to give this a read (and some of the related links):
http://support.microsoft.com/kb/823938/en-us

> How can I verify that the sqlserver on my development is accessible from
> internet?
You should be able to use Query Analyzer or OSQL as a basic test
mechanism...
Steve
|||Hello Steve,
I'll guess that the SQL Server need to use a static port?
1. From SQL Server Network Utility I'll see that port 1433 is default.
2. In the registry the settings looks like dynamic port allocation
3. The log displays:
2007-04-19 07:49:42.21 server SQL server listening on 192.168.0.6: 1433.
2007-04-19 07:49:42.34 server SQL server listening on 127.0.0.1: 1433.
2007-04-19 07:49:42.34 server SuperSocket Info: Bind failed on TCP port
1433.
2007-04-19 07:49:42.60 server SuperSocket Info: Bind failed on TCP port
1433.
I have tested to change the port to 1500, but I get the same bind error,
"Bind failed on TCP port 1500".
I have both SQL Server 9, and SQL Server Express on this machine. Is that
the problem?
Thanks!
"Steve Thompson" <stevethompson@.nomail.please> skrev i meddelandet
news:eOtYGIfgHHA.4916@.TK2MSFTNGP06.phx.gbl...
> "Nutshell" <nospam@.nospam.com> wrote in message
> news:%239FNk8bgHHA.596@.TK2MSFTNGP05.phx.gbl...
>
> You'll want to give this a read (and some of the related links):
> http://support.microsoft.com/kb/823938/en-us
>
> You should be able to use Query Analyzer or OSQL as a basic test
> mechanism...
> Steve
>

How can I verify that SQLServer is recognizing the "-g" startup option?

Greetings, all
When starting SQLServer 2005, it is possible to set the "-g" option to
reserve a minimum amount of memory for the MemToLeave region. After
starting SQLServer, is there a way to verify that the number specified
in the -g option has been recognized and used properly?
I had originally expected the Target buffer count in the DBCC
MemoryStatus command to drop when specifying a larger-than-default
MemToLeave region, and also a lowered number in the VM Reserved (KB)
value in the Memory Manager table. In my case, neither of these values
changed.
Situation:
I am using a SQL Server 2005 Enterprise Edition installation on a 32-
bit Windows 2003 Enterprise Edition Server with 3.38GB of RAM. Given
the standard configuration of a 2GB VAS, I expected that the Buffer
Pool for this SQL server would be approximately 1.6 GB, with the
default MemToLeave region of approximately 384MB, and the initial
numbers reported by DBCC MemoryStatus were consistent with those
expectations. I changed the -g setting to reserve 1GB of memory for
MemToLeave, and I expected to see the Buffer Pool decrease to 1GB
accordingly, but as described, such is not the case. I now suspect
that the -g option is not being recognized.
I specified the "-g 1024" parameter in the SQL Configuration Manager
under "Startup Parameters," and restarted the server immediately
after adding the option. No errors were recorded during startup.
I would appreciate any assist on this someone could offer.
-DavidHi David, check SQL Server error log to see if if -g is being used or not (It
logs this during startup)
Also, are you heavily using OLE automation objects (ex: SP_OA) or any
extended stored procedures. (Hope the obejcts are being cleared/destroyed
after it's use using SP_OADestroy) *I've seen cases in the past where it
doesn't.
However, the default value for -g inboth SQL Server 2000 and 2005 is 256 MB
and if you wish to increment this value then you'll need to do this in the
increments of 128 MB. (Try specifying -g 384 and restart SQL Server services
to see if that helps)
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
Articles, SQL Clinic and a lot of SQL fun.
Register (Free):
http://sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?returnurl=%2fdefault.aspx
"SoonerDEW@.gmail.com" wrote:
> Greetings, all
> When starting SQLServer 2005, it is possible to set the "-g" option to
> reserve a minimum amount of memory for the MemToLeave region. After
> starting SQLServer, is there a way to verify that the number specified
> in the -g option has been recognized and used properly?
> I had originally expected the Target buffer count in the DBCC
> MemoryStatus command to drop when specifying a larger-than-default
> MemToLeave region, and also a lowered number in the VM Reserved (KB)
> value in the Memory Manager table. In my case, neither of these values
> changed.
> Situation:
> I am using a SQL Server 2005 Enterprise Edition installation on a 32-
> bit Windows 2003 Enterprise Edition Server with 3.38GB of RAM. Given
> the standard configuration of a 2GB VAS, I expected that the Buffer
> Pool for this SQL server would be approximately 1.6 GB, with the
> default MemToLeave region of approximately 384MB, and the initial
> numbers reported by DBCC MemoryStatus were consistent with those
> expectations. I changed the -g setting to reserve 1GB of memory for
> MemToLeave, and I expected to see the Buffer Pool decrease to 1GB
> accordingly, but as described, such is not the case. I now suspect
> that the -g option is not being recognized.
> I specified the "-g 1024" parameter in the SQL Configuration Manager
> under "Startup Parameters," and restarted the server immediately
> after adding the option. No errors were recorded during startup.
> I would appreciate any assist on this someone could offer.
> -David
>|||Hi, Saleem
Thank you so much for your assistance.
I was able to review the logs and verify that the -g option is being
recognized.
That brings me to my problem. My intent here is to free up enough
MemToLeave area to allow one of several SQLCLR procedures to run.
These procedures will run fine on a local SQLEXPRESS box, but fail
with 701 (Insufficient Memory) errors when run on one of our 32-bit
SQL Server Enterprise servers running Win 2K3 Server EE.
My investigation led me to believe that my local SQLExpress box was
actually being given a greater chunk in the MemToLeave region for
these SQLCLR procedures by virtue of it's 1GB buffer pool allocation,
leaving 1GB in MemToLeave, whereas the server, with just over 3GB of
physical RAM (but no /3GB boot.ini switch nor are AWE enabled in SQL
Server), was being given only the default of 256MB (plus the thread
stack space). I theorized that forcing MemToLeave at some rather
ridiculous value, such as 1GB (1024), would have been ample for these
procedures, but the error persists. Hence, I suspected the -g
parameter was not being accepted. This is obviously not the case.
Should I not see a difference in the buffer status and related
allocations when the -g parameter is specified?
Again, many thanks for your assistance,
David
On Oct 17, 5:41 am, Saleem Hakani
<SaleemHak...@.discussions.microsoft.com> wrote:
> Hi David, check SQL Server error log to see if if -g is being used or not (It
> logs this during startup)
> Also, are you heavily using OLE automation objects (ex: SP_OA) or any
> extended stored procedures. (Hope the obejcts are being cleared/destroyed
> after it's use using SP_OADestroy) *I've seen cases in the past where it
> doesn't.
> However, the default value for -g inboth SQL Server 2000 and 2005 is 256 MB
> and if you wish to increment this value then you'll need to do this in the
> increments of 128 MB. (Try specifying -g 384 and restart SQL Server services
> to see if that helps)
> --
> Thank you,
> Saleem Hakani
> HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
> SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> Articles, SQL Clinic and a lot of SQL fun.
> Register (Free):http://sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?r...
>
> "Sooner...@.gmail.com" wrote:
> > Greetings, all
> > When starting SQLServer 2005, it is possible to set the "-g" option to
> > reserve a minimum amount of memory for the MemToLeave region. After
> > starting SQLServer, is there a way to verify that the number specified
> > in the -g option has been recognized and used properly?
> > I had originally expected the Target buffer count in the DBCC
> > MemoryStatus command to drop when specifying a larger-than-default
> > MemToLeave region, and also a lowered number in the VM Reserved (KB)
> > value in the Memory Manager table. In my case, neither of these values
> > changed.
> > Situation:
> > I am using a SQL Server 2005 Enterprise Edition installation on a 32-
> > bit Windows 2003 Enterprise Edition Server with 3.38GB of RAM. Given
> > the standard configuration of a 2GB VAS, I expected that the Buffer
> > Pool for this SQL server would be approximately 1.6 GB, with the
> > default MemToLeave region of approximately 384MB, and the initial
> > numbers reported by DBCC MemoryStatus were consistent with those
> > expectations. I changed the -g setting to reserve 1GB of memory for
> > MemToLeave, and I expected to see the Buffer Pool decrease to 1GB
> > accordingly, but as described, such is not the case. I now suspect
> > that the -g option is not being recognized.
> > I specified the "-g 1024" parameter in the SQL Configuration Manager
> > under "Startup Parameters," and restarted the server immediately
> > after adding the option. No errors were recorded during startup.
> > I would appreciate any assist on this someone could offer.
> > -David- Hide quoted text -
> - Show quoted text -|||Everyone,
I have resolved this problem, and thought I should share my findings.
It turns out that the "-g" option should, indeed, be reflected as a
decrease in the size of the reserved buffer pool in SQL Server, as I
had surmised. Our servers were not honoring the "-g" parameter at
startup merely be restarting SQLServer; it was honored only after the
server itself had been rebooted. DBCC MEMORYSTATUS correctly reflected
the proper/expected buffer pool reservation size *only* after a server
reboot.
Our SQLCLR procedure now runs happily.
Thanks to all!
-David
On Oct 17, 8:28 am, Sooner...@.gmail.com wrote:
> Hi, Saleem
> Thank you so much for your assistance.
> I was able to review the logs and verify that the -g option is being
> recognized.
> That brings me to my problem. My intent here is to free up enough
> MemToLeave area to allow one of several SQLCLR procedures to run.
> These procedures will run fine on a local SQLEXPRESS box, but fail
> with 701 (Insufficient Memory) errors when run on one of our 32-bit
> SQL Server Enterprise servers running Win 2K3 Server EE.
> My investigation led me to believe that my local SQLExpress box was
> actually being given a greater chunk in the MemToLeave region for
> these SQLCLR procedures by virtue of it's 1GB buffer pool allocation,
> leaving 1GB in MemToLeave, whereas the server, with just over 3GB of
> physical RAM (but no /3GB boot.ini switch nor are AWE enabled in SQL
> Server), was being given only the default of 256MB (plus the thread
> stack space). I theorized that forcing MemToLeave at some rather
> ridiculous value, such as 1GB (1024), would have been ample for these
> procedures, but the error persists. Hence, I suspected the -g
> parameter was not being accepted. This is obviously not the case.
> Should I not see a difference in the buffer status and related
> allocations when the -g parameter is specified?
> Again, many thanks for your assistance,
> David
> On Oct 17, 5:41 am, Saleem Hakani
>
> <SaleemHak...@.discussions.microsoft.com> wrote:
> > Hi David, check SQL Server error log to see if if -g is being used or not (It
> > logs this during startup)
> > Also, are you heavily using OLE automation objects (ex: SP_OA) or any
> > extended stored procedures. (Hope the obejcts are being cleared/destroyed
> > after it's use using SP_OADestroy) *I've seen cases in the past where it
> > doesn't.
> > However, the default value for -g inboth SQL Server 2000 and 2005 is 256 MB
> > and if you wish to increment this value then you'll need to do this in the
> > increments of 128 MB. (Try specifying -g 384 and restart SQL Server services
> > to see if that helps)
> > --
> > Thank you,
> > Saleem Hakani
> > HTTP://WWW.SQLCOMMUNITY.COM (World Wide Microsoft SQL Server Community)
> > SQLTips, SQL Forums, SQL Blogs, SQL RADIO, SQL Events, SQL Scripts, SQL
> > Articles, SQL Clinic and a lot of SQL fun.
> > Register (Free):http://sqlcommunity.com/RegistrationLoginPage/tabid/68/Default.aspx?r...
> > "Sooner...@.gmail.com" wrote:
> > > Greetings, all
> > > When starting SQLServer 2005, it is possible to set the "-g" option to
> > > reserve a minimum amount of memory for the MemToLeave region. After
> > > starting SQLServer, is there a way to verify that the number specified
> > > in the -g option has been recognized and used properly?
> > > I had originally expected the Target buffer count in the DBCC
> > > MemoryStatus command to drop when specifying a larger-than-default
> > > MemToLeave region, and also a lowered number in the VM Reserved (KB)
> > > value in the Memory Manager table. In my case, neither of these values
> > > changed.
> > > Situation:
> > > I am using a SQL Server 2005 Enterprise Edition installation on a 32-
> > > bit Windows 2003 Enterprise Edition Server with 3.38GB of RAM. Given
> > > the standard configuration of a 2GB VAS, I expected that the Buffer
> > > Pool for this SQL server would be approximately 1.6 GB, with the
> > > default MemToLeave region of approximately 384MB, and the initial
> > > numbers reported by DBCC MemoryStatus were consistent with those
> > > expectations. I changed the -g setting to reserve 1GB of memory for
> > > MemToLeave, and I expected to see the Buffer Pool decrease to 1GB
> > > accordingly, but as described, such is not the case. I now suspect
> > > that the -g option is not being recognized.
> > > I specified the "-g 1024" parameter in the SQL Configuration Manager
> > > under "Startup Parameters," and restarted the server immediately
> > > after adding the option. No errors were recorded during startup.
> > > I would appreciate any assist on this someone could offer.
> > > -David- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -

How can I use SMo in VS 2002 SP1 c# applications?

I have got error: cannot add reference to Microsoft.sqlserver.smo.dll .....

I understand VS2005 is bettrer than VS2002 but I have what I have.

Thank you.

You need VS2005 as SMO depends on .NET Framework 2.0.|||

what if i install Framework 2.0 on Visual Studio 2003?