error execute sp_addlinkedserver in stored procedure

Error Message:

Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

T-SQL:

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

Environement:

SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.

Symptoms:

1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.

2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)

3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

Hello,

What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?

From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.

If you have no joy, can you post the entire sp code?

Cheers

Rob

|||

The stored procedure:

ALTER PROCEDURE [dbo].[LoadConvertsDB]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Create Linked Server

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

--StockTable

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];

SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable

--More Tables here

-- Drop linked server

EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

END

The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.

1. If the linked server is already the system, this sp will work.

2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.

3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.

I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.

Thanks!

|||

Did you find the solution for your error.

I am also stuck with the similar kind of error

|||

Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.

When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.

So the solution is:

1. Create a linked server by using sp_addlinkedserver

2. Declare @.SQL varchar(1000)

3. SET @.SQL = 'SELECT * FROM ......'

4. EXEC (@.SQL)

You won't get error when complie the stored procedure and execute it.

error execute sp_addlinkedserver in stored procedure

Error Message:

Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

T-SQL:

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

Environement:

SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.

Symptoms:

1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.

2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)

3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

Hello,

What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?

From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.

If you have no joy, can you post the entire sp code?

Cheers

Rob

|||

The stored procedure:

ALTER PROCEDURE [dbo].[LoadConvertsDB]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Create Linked Server

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

--StockTable

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];

SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable

--More Tables here

-- Drop linked server

EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

END

The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.

1. If the linked server is already the system, this sp will work.

2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.

3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.

I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.

Thanks!

|||

Did you find the solution for your error.

I am also stuck with the similar kind of error

|||

Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.

When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.

So the solution is:

1. Create a linked server by using sp_addlinkedserver

2. Declare @.SQL varchar(1000)

3. SET @.SQL = 'SELECT * FROM ......'

4. EXEC (@.SQL)

You won't get error when complie the stored procedure and execute it.

error execute sp_addlinkedserver in stored procedure

Error Message:

Msg 7202, Level 11, State 2, Procedure LoadConvertsDB, Line 24
Could not find server 'CONVERTSDB' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

T-SQL:

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

Environement:

SQL 2005 Std, Win2000 Pro SP4, same computer. F is the network drive. SQL Log with the Windows NT domain account. F is the network drive.

Symptoms:

1. I can execute the T-SQL in the Query window in the SQL2005 Management Studio without any issue. After that, CONVERTSDB is in the Linked Servers lists.

2. If I put the T-SQL into a stored procedure, it does not work, and generate the above error. (EXEC dbo.LoadConvertsDB)

3. In the same Query window, If the above T-SQL is executed first and then execute the stored procedure, it will succeed. In the stored procedure, only the below T-SQL is before the sp_addlinkedserver.

BEGIN

SET NOCOUNT ON;

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

Hello,

What happens if you create a testing proc that has only the required commands WITHOUT any control-of-flow logic? Does it run OK then?

From point 3, it sounds like a logic issue. You may like to explicitly qualify the if statement with begin/end.

If you have no joy, can you post the entire sp code?

Cheers

Rob

|||

The stored procedure:

ALTER PROCEDURE [dbo].[LoadConvertsDB]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Create Linked Server

IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CONVERTSDB') EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

EXEC master.dbo.sp_addlinkedserver @.server = N'CONVERTSDB', @.srvproduct=N'Access', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'F:\Converts.mdb';

--StockTable

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StockTable]') AND type in (N'U')) DROP TABLE [dbo].[StockTable];

SELECT * INTO dbo.StockTable FROM CONVERTSDB...StockTable

--More Tables here

-- Drop linked server

EXEC master.dbo.sp_dropserver @.server=N'CONVERTSDB', @.droplogins='droplogins';

END

The purpose is to load tables from Access to SQL Server, and then perform some data check. This stored procedure is to 1) create a linked server to Access on F, 2) Load the data, 3) delete the linked server.

1. If the linked server is already the system, this sp will work.

2. It only failed when the first launch Management Studio after a computer restart, and execute when there is no linked server. That means, after create a linked server, delete a linked server, and restart the Management Studio, it also works.

3.I further isolate the first two T-SQL which drop and create linked server to a seperate sp, this time the new sp works in the first launch of Management Studio. As long as the T-SQL to load the first table is added to the sp, the same error re-appears.

I guess it mighted be related to SQL Server sp caching, and first time parsing the T-SQL in the sp. The workaround is to have the seperate sp which drop and recreate the linked server, and have the LoadConvertsDB sp call that sp in stead. But just feel strange why it failed the first time launch, and why it has to be seperated.

Thanks!

|||

Did you find the solution for your error.

I am also stuck with the similar kind of error

|||

Creating a linked server by using sp_addlinkedserver is no problem. But there is problem when you put select statement from that linked server next.

When you create or alter the stored procedure, SQL compiles the stored procedure only. The linked server is not created. However SQL will get error when it check the the linked server in Select statement which it is NOT existed.

So the solution is:

1. Create a linked server by using sp_addlinkedserver

2. Declare @.SQL varchar(1000)

3. SET @.SQL = 'SELECT * FROM ......'

4. EXEC (@.SQL)

You won't get error when complie the stored procedure and execute it.

Error encountered while executing a UDF

Hi,

I get the following error while trying to execute a UDF.

Msg 6522, Level 16, State 1, Line 1

A .NET Framework error occurred during execution of user defined routine or aggregate 'test':

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at SalesCentral_Database.UserDefinedFunctions.udfTest()

My function is defined as follows:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions

<Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read)> _

Public Shared Function udfTest() As SqlString

SqlContext.Pipe.Send(System.DateTime.Now().ToString())

Dim conn As New SqlConnection("context connection=true")

conn.Open()

Dim cmd As New SqlCommand("SELECT MAX(ACCTPERIOD) FROM TBL_OPPORTUNITY_HIS", conn)

Return New SqlString(cmd.ExecuteScalar().ToString())

End Function

End Class

SQL Server

CREATE ASSEMBLY SalesCentral_Database from '\\isew5l6c\tranf\SFS_Sales\SalesCentral_Database.dll' WITH PERMISSION_SET = SAFE

CREATE FUNCTION test() RETURNS NVARCHAR(10) AS EXTERNAL NAME [SalesCentral_Database].[SalesCentral_Database.UserDefinedFunctions].[udfTest]

SELECT dbo.test()

- Here I get the error mentioned above. Can anyone please tell me what am I missing.

Thanks.

Hi,

This is the same error as in this thread. You should remove the "SqlContext.Pipe.Send..." line from your code. I'm curious what you intent is with that linethe pipe is really to return results from within a stored procedure.

Cheers,
-Isaac

|||Thanks for bringing that to my notice. Sorry it has slipped out of my mind :)

error defragging index

I received this meesage when trying to defrag an index.
DBCC: Compaction phase of index 'tmxvesta_2.dbo.RNWLWEEK' is 38% complete.
Server: Msg 3624, Level 20, State 1, Line 1
Location: pageref.cpp:1191
Expression: 0 == pageFull
SPID: 68
Process ID: 724
Connection Broken
Can anyone tell me what it means?
Thanks,
Dan D.
Looks like a bug or the database has a corrupt page. What SP are you
running?
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:78F2C631-D958-475C-88C4-5C610C8E557F@.microsoft.com...
> I received this meesage when trying to defrag an index.
> DBCC: Compaction phase of index 'tmxvesta_2.dbo.RNWLWEEK' is 38% complete.
> Server: Msg 3624, Level 20, State 1, Line 1
> Location: pageref.cpp:1191
> Expression: 0 == pageFull
> SPID: 68
> Process ID: 724
> Connection Broken
> Can anyone tell me what it means?
> Thanks,
> --
> Dan D.
|||When I run "select @.@.version" I get:
Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Is service pack 4 for windows or Sql Server?
Dan D.
"Paul S Randal [MS]" wrote:

> Looks like a bug or the database has a corrupt page. What SP are you
> running?
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:78F2C631-D958-475C-88C4-5C610C8E557F@.microsoft.com...
>
>
|||You're on SP2 and you've hit a bug that is fixed in SP3. You should upgrade
if possible or contact PSS as there may be a hotfix you can apply to SP2.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8DAA85A5-072D-4A3F-8E3C-4025D79BD770@.microsoft.com...
> When I run "select @.@.version" I get:
> Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50
Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)[vbcol=seagreen]
> Is service pack 4 for windows or Sql Server?
> --
> Dan D.
>
> "Paul S Randal [MS]" wrote:
rights.[vbcol=seagreen]
complete.[vbcol=seagreen]

Error creating login - Incorrect syntax near 'LOGIN'.

Hi,
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
EshaWorks for me. The two most likely cause I can think of is there's a
non-printable character somewhere that didn't get copied to the post or you
have a Beta version of SQL Server 2005 (or SQL Server 2000) that had a
different CREATE LOGIN syntax.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Esha" <eshhyasi@.gmail.com> wrote in message
news:1151465435.377462.192620@.p79g2000cwp.googlegroups.com...
> Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
>
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
>
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
>|||Esha,
CREATE LOGIN is a new T-SQL statement in SQL Server 2005.
Since you are using SQL Server 2000, you need to use sp_addlogin
or sp_grantlogin. Management Studio should use the version-appropriate
T-SQL depending on whether you are adding a login to a 2000 instance
or a 2005 instance. If this is not working for you, can you tell us what
steps you are taking in Management Studio in your attempt to create
the new login?
Steve Kass
Drew University
Esha wrote:

>Hi,
>I am using Sql server 2005 and when I try to create a new login from
>the sql server mgmnt studio, I get this error.
>
>Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'LOGIN'.
>
>I have logged in as "sa" and the login statement that I am using is:
>CREATE LOGIN test WITH PASSWORD = 'beta'
>The product version is 8.00.2039.
>Anybody knows whats wrong'
>Thanks for helping out.
>Esha
>
>|||Hi Steve,
Thanks for the quick response.
I just verified and you are right. My database engine is SQL server
2000 but my management studio is 2005. I used sp_addlogin and it
worked.
I created a new user for the new login from the management studio and I
assigned the roles of db_datareader and db_datawriter. If I need to
allow the user, read and write access only, but not "execute" access
for scripts, do I need to assign any other roles apart from the above
two?
Thanks a lot
Esha
Steve Kass wrote:
> Esha,
> CREATE LOGIN is a new T-SQL statement in SQL Server 2005.
> Since you are using SQL Server 2000, you need to use sp_addlogin
> or sp_grantlogin. Management Studio should use the version-appropriate
> T-SQL depending on whether you are adding a login to a 2000 instance
> or a 2005 instance. If this is not working for you, can you tell us what
> steps you are taking in Management Studio in your attempt to create
> the new login?
> Steve Kass
> Drew University
> Esha wrote:
>|||Thanks Roger,
I was using management studio 2005 to connect to SQL server 2000 and
hence it was not working. I used sp_addlogin as suggested by Steve and
it worked.
Thanks a lot,
Esha
Roger Wolter[MSFT] wrote:
> Works for me. The two most likely cause I can think of is there's a
> non-printable character somewhere that didn't get copied to the post or yo
u
> have a Beta version of SQL Server 2005 (or SQL Server 2000) that had a
> different CREATE LOGIN syntax.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Esha" <eshhyasi@.gmail.com> wrote in message
> news:1151465435.377462.192620@.p79g2000cwp.googlegroups.com...|||Esha,
Whether the user can "execute" a script (a T-SQL statement?) depends on
what the script does. A data reader can execute a SELECT statement,
and a data writer can execute DELETE, UPDATE, or INSERT statements.
You can grant or deny permissions on individual stored procedures, tables,
and other objects more specifically with GRANT and DENY. The security
models for 2000 and 2005 are somewhat different, and I suggest you refer to
Books Online for the appropriate version of your database instance for more
details.
SK
Esha wrote:

>Hi Steve,
>Thanks for the quick response.
>I just verified and you are right. My database engine is SQL server
>2000 but my management studio is 2005. I used sp_addlogin and it
>worked.
>I created a new user for the new login from the management studio and I
>assigned the roles of db_datareader and db_datawriter. If I need to
>allow the user, read and write access only, but not "execute" access
>for scripts, do I need to assign any other roles apart from the above
>two?
>Thanks a lot
>Esha
>Steve Kass wrote:
>
>
>

Error creating login - Incorrect syntax near 'LOGIN'.

Hi,
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
EshaThis is a multi-part message in MIME format.
--060001040307010407040902
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
instance by the sounds of it. What's the version of the SQL instance
(run "SELECT SERVERPROPERTY('ProductVersion')")?
CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
to a SQL 2000 instance, regardless of which SQL client tool you use, you
need to use sp_addlogin or sp_grantlogin (depending on whether it's a
standard SQL login or a trusted login we're talking about). I think the
GUI in Management Studio ought to take care of this automatically (ie.
use different commands depending on the server version) but if you want
to use T-SQL yourself then you need to write the correct statements for
the SQL version the server is running.
--
*mike hodgson*
http://sqlnerd.blogspot.com
eshhyasi@.gmail.com wrote:
>Hi,
>I am using Sql server 2005 and when I try to create a new login from
>the sql server mgmnt studio, I get this error.
>Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near 'LOGIN'.
>I have logged in as "sa" and the login statement that I am using is:
>CREATE LOGIN test WITH PASSWORD = 'beta'
>The product version is 8.00.2039.
>Anybody knows whats wrong'
>Thanks for helping out.
>Esha
>
>
--060001040307010407040902
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
instance by the sounds of it. What's the version of the SQL instance
(run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
<br>
CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
logins to a SQL 2000 instance, regardless of which SQL client tool you
use, you need to use sp_addlogin or sp_grantlogin (depending on whether
it's a standard SQL login or a trusted login we're talking about). I
think the GUI in Management Studio ought to take care of this
automatically (ie. use different commands depending on the server
version) but if you want to use T-SQL yourself then you need to write
the correct statements for the SQL version the server is running.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhyasi@.gmail.com</a> wrote:
<blockquote
cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
type="cite">
<pre wrap="">Hi,
I am using Sql server 2005 and when I try to create a new login from
the sql server mgmnt studio, I get this error.
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'LOGIN'.
I have logged in as "sa" and the login statement that I am using is:
CREATE LOGIN test WITH PASSWORD = 'beta'
The product version is 8.00.2039.
Anybody knows whats wrong'
Thanks for helping out.
Esha
</pre>
</blockquote>
</body>
</html>
--060001040307010407040902--|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
> >Hi,
> >
> >I am using Sql server 2005 and when I try to create a new login from
> >the sql server mgmnt studio, I get this error.
> >
> >Msg 170, Level 15, State 1, Line 1
> >Line 1: Incorrect syntax near 'LOGIN'.
> >
> >I have logged in as "sa" and the login statement that I am using is:
> >
> >CREATE LOGIN test WITH PASSWORD = 'beta'
> >
> >The product version is 8.00.2039.
> >
> >Anybody knows whats wrong'
> >
> >Thanks for helping out.
> >
> >Esha
> >
> >
> >
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about). I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhyasi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Thanks Mike,
I used sp_addlogin and it worked.
Thanks a lot,
Esha
Mike Hodgson wrote:
> Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new logins
> to a SQL 2000 instance, regardless of which SQL client tool you use, you
> need to use sp_addlogin or sp_grantlogin (depending on whether it's a
> standard SQL login or a trusted login we're talking about). I think the
> GUI in Management Studio ought to take care of this automatically (ie.
> use different commands depending on the server version) but if you want
> to use T-SQL yourself then you need to write the correct statements for
> the SQL version the server is running.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> eshhyasi@.gmail.com wrote:
> >Hi,
> >
> >I am using Sql server 2005 and when I try to create a new login from
> >the sql server mgmnt studio, I get this error.
> >
> >Msg 170, Level 15, State 1, Line 1
> >Line 1: Incorrect syntax near 'LOGIN'.
> >
> >I have logged in as "sa" and the login statement that I am using is:
> >
> >CREATE LOGIN test WITH PASSWORD = 'beta'
> >
> >The product version is 8.00.2039.
> >
> >Anybody knows whats wrong'
> >
> >Thanks for helping out.
> >
> >Esha
> >
> >
> >
> --060001040307010407040902
> Content-Type: text/html; charset=ISO-8859-1
> X-Google-AttachSize: 2029
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> </head>
> <body bgcolor="#ffffff" text="#000000">
> <tt>Yeah, you're trying to run a "CREATE LOGIN" statement on a SQL 2000
> instance by the sounds of it. What's the version of the SQL instance
> (run "SELECT SERVERPROPERTY('ProductVersion')")?<br>
> <br>
> CREATE LOGIN is new to the SQL 2005 dialect of T-SQL. To add new
> logins to a SQL 2000 instance, regardless of which SQL client tool you
> use, you need to use sp_addlogin or sp_grantlogin (depending on whether
> it's a standard SQL login or a trusted login we're talking about). I
> think the GUI in Management Studio ought to take care of this
> automatically (ie. use different commands depending on the server
> version) but if you want to use T-SQL yourself then you need to write
> the correct statements for the SQL version the server is running.<br>
> </tt>
> <div class="moz-signature">
> <title></title>
> <meta http-equiv="Content-Type" content="text/html; ">
> <p><span lang="en-au"><font face="Tahoma" size="2">--<br>
> </font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
> hodgson</font></span></b><span lang="en-au"><br>
> <font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
> </p>
> </div>
> <br>
> <br>
> <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:eshhyasi@.gmail.com">eshhyasi@.gmail.com</a> wrote:
> <blockquote
> cite="mid1151465632.155321.236760@.m73g2000cwd.googlegroups.com"
> type="cite">
> <pre wrap="">Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
> </pre>
> </blockquote>
> </body>
> </html>
> --060001040307010407040902--|||Even you are using Management Studio, you are connected to a SQL Server 2000
instance (according to your product version). You should connect to a SQL
Server 2005 instance to use create login.
Ben Nevarez, MCDBA, OCP
Database Administrator
"eshhyasi@.gmail.com" wrote:
> Hi,
> I am using Sql server 2005 and when I try to create a new login from
> the sql server mgmnt studio, I get this error.
> Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'LOGIN'.
> I have logged in as "sa" and the login statement that I am using is:
> CREATE LOGIN test WITH PASSWORD = 'beta'
> The product version is 8.00.2039.
> Anybody knows whats wrong'
> Thanks for helping out.
> Esha
>

Error converting data type varchar to numeric.

Server: Msg 8114, Level 16, State 5
Error converting data type varchar to numeric.

Hello...
I new in MS SQL.
Given: 2 servers, same SQL statements, same input, same tables, same data types, same triggers.
Problem: One server works fine while the other returns the error above.

I have no idea of the problem why the other is working.IN addition to that, they also have the same MS SQL version.|||Perhaps you could post the relevant code, as well as the table structure? It would be quite impossible to help without these two crucial pieces of information.

Error ConnectionRead (WrapperRead())

Hi
When I ran the ran the backups of my databases I ahve the next error
ConnectionRead (WrapperRead()). [SQLSTATE 01000]
Msg 11, Sev 16: General network error. Check your network documentation.
[SQLSTATE 08S01]
Iâ'm running windows2000 Server Standard Edition, SQL2000 Server Standard
Edition and MDAC2.6 version.
I found the article 827452 but this article
APPLIES TO
â?¢ Microsoft Data Access Components 2.8
â?¢ Microsoft SQL Server 2000 Standard Edition, when used with:
Microsoft Windows Server 2003, Standard Edition
Microsoft Windows 2000 Enterprise Edition
I want to know if this article applies for my case
I run the sproc for backup the databases
Thanks very muchSee http://support.microsoft.com/default.aspx?scid=kb;en-us;827452
Adrian
"Valero" <Valero@.discussions.microsoft.com> wrote in message
news:DEE2A24E-F805-4E56-BDD6-265C6A217912@.microsoft.com...
> Hi
> When I ran the ran the backups of my databases I ahve the next error
> ConnectionRead (WrapperRead()). [SQLSTATE 01000]
> Msg 11, Sev 16: General network error. Check your network documentation.
> [SQLSTATE 08S01]
>
> I'm running windows2000 Server Standard Edition, SQL2000 Server Standard
> Edition and MDAC2.6 version.
> I found the article 827452 but this article
> APPLIES TO
> . Microsoft Data Access Components 2.8
> . Microsoft SQL Server 2000 Standard Edition, when used with:
> Microsoft Windows Server 2003, Standard Edition
> Microsoft Windows 2000 Enterprise Edition
> I want to know if this article applies for my case
> I run the sproc for backup the databases
> Thanks very much
>

Error Code

Server: Msg 8624, Level 16, State 3, Line 1
Internal SQL Server error.
I've looked at several KB articles.
1) http://support.microsoft.com/?kbid=885442
This doesn't describe my problem as the view does not have a delete.
2) http://support.microsoft.com/kb/290817
This does describe my probelm. I already have sp3 installed though.
3) http://support.microsoft.com/?kbid=830466
This also describes my problem. But the KB is old, April 2004, and I
would assume SP3 would have resolved this.
I didn't write the view. Someone from this forum was nice enough to
write it for me. I'm looking through it now and hopefully I can
understand it enough that I can learn from it and attack the problem in
a similar way. But I'm also curious if this might not be a SQL server
setting issue. Thanks.Hi
No, SP3a was build 760. The fix is build 852. Look at installing Build 878,
the highest publicly available hotfix, supported by PSS.
http://support.microsoft.com/defaul...kb;en-us;838166
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Won Lee" <nospam@.nospam.com> wrote in message
news:ObBBUnQIFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Server: Msg 8624, Level 16, State 3, Line 1
> Internal SQL Server error.
> I've looked at several KB articles.
> 1) http://support.microsoft.com/?kbid=885442
> This doesn't describe my problem as the view does not have a delete.
> 2) http://support.microsoft.com/kb/290817
> This does describe my probelm. I already have sp3 installed though.
> 3) http://support.microsoft.com/?kbid=830466
> This also describes my problem. But the KB is old, April 2004, and I
> would assume SP3 would have resolved this.
> I didn't write the view. Someone from this forum was nice enough to
> write it for me. I'm looking through it now and hopefully I can
> understand it enough that I can learn from it and attack the problem in
> a similar way. But I'm also curious if this might not be a SQL server
> setting issue. Thanks.|||Mike Epprecht (SQL MVP) wrote:
> Hi
> No, SP3a was build 760. The fix is build 852. Look at installing Build 878
,
> the highest publicly available hotfix, supported by PSS.
> http://support.microsoft.com/defaul...kb;en-us;838166
>
Applied that hot fix and rebooted 2003 server. No dice. I'm at a loss
for what to do.|||Hi
Log a call with PSS. Looks like you have a live one there.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Won Lee" <nospam@.nospam.com> wrote in message
news:ehNX9BRIFHA.3776@.tk2msftngp13.phx.gbl...
> Mike Epprecht (SQL MVP) wrote:
878,
> Applied that hot fix and rebooted 2003 server. No dice. I'm at a loss
> for what to do.|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Log a call with PSS. Looks like you have a live one there.
>
OK Thanks. Will keep everyone updated.

Error attaching database

I detached a database to copy the files and when I tried to re-attach it
failed with:
/ ****************************************
/
Server: Msg 3624, Level 20, State 1, Line 1
Location: recbase.cpp:1374
Expression: m_nVars > 0
SPID: 55
Process ID: 952
Connection Broken
/ ****************************************
/
I'm pretty sure this is a disk problem. Is there anyway to re-attach this db
in emergency mode? Like you can with a suspect database.
Any help would be appreciated,
DougSIf going back to backup is not an option, then you may want to contact
Microsoft PSS. They maybe able to help you rebuild the transaction log.
However, the database may still be corrupted and depending on the extent of
the corruption, you may or may not be able to repair or extract data out.
Yih-Yoon Lee
On Wed, 24 Mar 2004 11:51:24 -0500, Doug Stiers wrote:

> I detached a database to copy the files and when I tried to re-attach it
> failed with:
> / ****************************************
/
> Server: Msg 3624, Level 20, State 1, Line 1
> Location: recbase.cpp:1374
> Expression: m_nVars > 0
> SPID: 55
> Process ID: 952
> Connection Broken
> / ****************************************
/
> I'm pretty sure this is a disk problem. Is there anyway to re-attach this
db
> in emergency mode? Like you can with a suspect database.
> Any help would be appreciated,
> DougS