Showing posts with label sqlserver. Show all posts
Showing posts with label sqlserver. Show all posts

Thursday, March 29, 2012

Can't Connect

Win 2000, sqlServer 2000 Personal edition
Trying ot connect using .asp Vb Script
1. Sql server set to use Windows Authentication
2. Log onto Windows 2000 as Jim Bunton {no password)
3 Server 'Study' running ok
4. sql query analyser
i. "SELECT User_Name > dbo
ii. [using database NorthWind]
"SELECT * FROM Customers" > gives expected result
5. VB script
** BEGIN CODE connect.asp ***
<%
@. LANGUAGE="VBSCRIPT"
%>
<%
Option explicit
response.expires = 0
%>
<!-- #include file="adovbs.inc" -->
<html>
<head>
<title></title>
</head>
<body>
<%
dim Cn, CnStr
Set Cn = Server.CreateObject("ADODB.Connection")
Cn.ConnectionTimeOut = 1
Cn.provider="sqloledb"
response.write "Conn Provider is " & Cn.provider
CnStr="Data Source=Study;Initial Catalog=Norhwind;User
Id=sa;Password=;"
' Tried all sorts of user id's and paswords
'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id = 'Jim
Bunton';Password=;"
'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id
=dbo;Password=;"
'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id =
JimBunton;Password=;"
'CnStr="Data Source='Study';Initial Catalogue='Norhwind';User Id
='Study\Jim Bunton';Password=;"
%>
<p>--</p>
<%
response.write "Cnstr = " & CnStr
'ERROR ALWAYS OCCURS ON NEXT LINEon the next line
Cn.open CnStr
%>
<p>--</p>
<%
response.write "The End - ran ok"
%>
</body>
</html>
**** END CODE *****
? what to do?
? am I missing something 'obvious' out ?
Jim Bunton
If you are using windows authentication (also known as a
trusted connection) to connect, you don't supply a user
name, password in the connection string. Instead, you
indicate this by using Integrated Security=SSPI
in your connection string.
You also want to make sure the database is correct. In your
examples, you want to make sure you use Northwind (you have
Norwind listed).
You can find sample connection strings using the SQL Server
OLE DB provider at:
http://www.carlprothman.net/Default...erForSQLServer
-Sue
On Mon, 19 Sep 2005 11:44:58 GMT, "Jim Bunton"
<jBunton@.BlueYonder.co.uk> wrote:

>Win 2000, sqlServer 2000 Personal edition
>Trying ot connect using .asp Vb Script
>1. Sql server set to use Windows Authentication
>2. Log onto Windows 2000 as Jim Bunton {no password)
>3 Server 'Study' running ok
>4. sql query analyser
> i. "SELECT User_Name > dbo
> ii. [using database NorthWind]
> "SELECT * FROM Customers" > gives expected result
>5. VB script
>** BEGIN CODE connect.asp ***
><%
> @. LANGUAGE="VBSCRIPT"
>%>
><%
> Option explicit
> response.expires = 0
>%>
><!-- #include file="adovbs.inc" -->
><html>
><head>
><title></title>
></head>
><body>
><%
> dim Cn, CnStr
> Set Cn = Server.CreateObject("ADODB.Connection")
> Cn.ConnectionTimeOut = 1
> Cn.provider="sqloledb"
> response.write "Conn Provider is " & Cn.provider
> CnStr="Data Source=Study;Initial Catalog=Norhwind;User
>Id=sa;Password=;"
> ' Tried all sorts of user id's and paswords
> 'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id = 'Jim
>Bunton';Password=;"
> 'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id
>=dbo;Password=;"
> 'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id =
>JimBunton;Password=;"
> 'CnStr="Data Source='Study';Initial Catalogue='Norhwind';User Id
>='Study\Jim Bunton';Password=;"
>%>
><p>--</p>
><%
> response.write "Cnstr = " & CnStr
> 'ERROR ALWAYS OCCURS ON NEXT LINEon the next line
> Cn.open CnStr
>%>
><p>--</p>
><%
> response.write "The End - ran ok"
>%>
></body>
></html>
>**** END CODE *****
>? what to do?
>? am I missing something 'obvious' out ?
|||Thanks for your clear response Sue.
Unfortunately I still have a problem:
The following works:-
<%
dim Cn, SqlStr,rsCustomers
Set Cn=Server.CreateObject("ADODB.Connection")
Cn.Provider = "sqloledb"
Cn.Properties("Data Source") = "Study\StudySQLServer"
Cn.Properties("Initial Catalog") = "NorthWind"
Cn.Properties("Connect Timeout") = "3"
Cn.Properties("User ID") = "sa"
Cn.Properties("Password") = "mypassword"
Cn.open
. . . code which displays Customer info
The folowing does NOT:-
<%
dim Cn, CnStr, SqlStr,rsCustomers
Set Cn=Server.CreateObject("ADODB.Connection")
CnStr="Provider=sqloledb;" & _
"Data Source=Study\StudySqlServer;" & _
"Initial Catalog=NorthWind;" & _
"Integrated Security=SSPI"
response.write "cnStr = " & cnStr
' response.end
Cn.open CnStr
It fails at Cn.open CnStr
Error Type:
(0x80040E4D)
/MyWebs/Connect_wAuth.asp, line 15
response.write "cnStr = " & cnStr gives:-
cnStr = Provider=sqloledb;Data Source=Study\StudySqlServer;Initial Catalog=NorthWind;Integrated Security=SSPI
In Query Analyser (login using Windows Authentification)
select user_Name() >> dbo
select * from Customers >> a list of customers
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:5tuui1l8ku9a5fl6r704knccn7jfun1kf4@.4ax.com...
> If you are using windows authentication (also known as a
> trusted connection) to connect, you don't supply a user
> name, password in the connection string. Instead, you
> indicate this by using Integrated Security=SSPI
> in your connection string.
> You also want to make sure the database is correct. In your
> examples, you want to make sure you use Northwind (you have
> Norwind listed).
> You can find sample connection strings using the SQL Server
> OLE DB provider at:
> http://www.carlprothman.net/Default...erForSQLServer
> -Sue
> On Mon, 19 Sep 2005 11:44:58 GMT, "Jim Bunton"
> <jBunton@.BlueYonder.co.uk> wrote:
>
|||Then it's likely the authentication settings on the IIS side
of it. Try adding the IUSR account as a login in SQL Server
with the appropriate permissions as needed for your code.
Also, turned on failed login audits in SQL Server and
restart the instance. That will help you track down what
login is failing.
-Sue
On Tue, 20 Sep 2005 10:17:50 GMT, "Jim Bunton"
<jBunton@.BlueYonder.co.uk> wrote:
[vbcol=seagreen]
>Thanks for your clear response Sue.
>Unfortunately I still have a problem:
>The following works:-
><%
>dim Cn, SqlStr,rsCustomers
> Set Cn=Server.CreateObject("ADODB.Connection")
> Cn.Provider = "sqloledb"
> Cn.Properties("Data Source") = "Study\StudySQLServer"
> Cn.Properties("Initial Catalog") = "NorthWind"
> Cn.Properties("Connect Timeout") = "3"
> Cn.Properties("User ID") = "sa"
> Cn.Properties("Password") = "mypassword"
> Cn.open
> . . . code which displays Customer info
>The folowing does NOT:-
><%
>dim Cn, CnStr, SqlStr,rsCustomers
> Set Cn=Server.CreateObject("ADODB.Connection")
> CnStr="Provider=sqloledb;" & _
> "Data Source=Study\StudySqlServer;" & _
> "Initial Catalog=NorthWind;" & _
> "Integrated Security=SSPI"
> response.write "cnStr = " & cnStr
> ' response.end
> Cn.open CnStr
>It fails at Cn.open CnStr
>Error Type:
>(0x80040E4D)
>/MyWebs/Connect_wAuth.asp, line 15
>response.write "cnStr = " & cnStr gives:-
>cnStr = Provider=sqloledb;Data Source=Study\StudySqlServer;Initial Catalog=NorthWind;Integrated Security=SSPI
>In Query Analyser (login using Windows Authentification)
>select user_Name() >> dbo
>select * from Customers >> a list of customers
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:5tuui1l8ku9a5fl6r704knccn7jfun1kf4@.4ax.com...

Can't Connect

Win 2000, sqlServer 2000 Personal edition
Trying ot connect using .asp Vb Script
1. Sql server set to use Windows Authentication
2. Log onto Windows 2000 as Jim Bunton {no password)
3 Server 'Study' running ok
4. sql query analyser
i. "SELECT User_Name > dbo
ii. [using database NorthWind]
"SELECT * FROM Customers" > gives expected result
5. VB script
** BEGIN CODE connect.asp ***
<%
@. LANGUAGE="VBSCRIPT"
%>
<%
Option explicit
response.expires = 0
%>
<!-- #include file="adovbs.inc" -->
<html>
<head>
<title></title>
</head>
<body>
<%
dim Cn, CnStr
Set Cn = Server.CreateObject("ADODB.Connection")
Cn.ConnectionTimeOut = 1
Cn.provider="sqloledb"
response.write "Conn Provider is " & Cn.provider
CnStr="Data Source=Study;Initial Catalog=Norhwind;User
Id=sa;Password=;"
' Tried all sorts of user id's and paswords
'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id = 'Jim
Bunton';Password=;"
'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id
=dbo;Password=;"
'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id =
JimBunton;Password=;"
'CnStr="Data Source='Study';Initial Catalogue='Norhwind';User Id
='Study\Jim Bunton';Password=;"
%>
<p>--</p>
<%
response.write "Cnstr = " & CnStr
'ERROR ALWAYS OCCURS ON NEXT LINEon the next line
Cn.open CnStr
%>
<p>--</p>
<%
response.write "The End - ran ok"
%>
</body>
</html>
**** END CODE *****
? what to do'
' am I missing something 'obvious' out ?
Jim BuntonIf you are using windows authentication (also known as a
trusted connection) to connect, you don't supply a user
name, password in the connection string. Instead, you
indicate this by using Integrated Security=SSPI
in your connection string.
You also want to make sure the database is correct. In your
examples, you want to make sure you use Northwind (you have
Norwind listed).
You can find sample connection strings using the SQL Server
OLE DB provider at:
http://www.carlprothman.net/Default...derForSQLServer
-Sue
On Mon, 19 Sep 2005 11:44:58 GMT, "Jim Bunton"
<jBunton@.BlueYonder.co.uk> wrote:

>Win 2000, sqlServer 2000 Personal edition
>Trying ot connect using .asp Vb Script
>1. Sql server set to use Windows Authentication
>2. Log onto Windows 2000 as Jim Bunton {no password)
>3 Server 'Study' running ok
>4. sql query analyser
> i. "SELECT User_Name > dbo
> ii. [using database NorthWind]
> "SELECT * FROM Customers" > gives expected result
>5. VB script
>** BEGIN CODE connect.asp ***
><%
> @. LANGUAGE="VBSCRIPT"
>%>
><%
> Option explicit
> response.expires = 0
>%>
><!-- #include file="adovbs.inc" -->
><html>
><head>
><title></title>
></head>
><body>
><%
> dim Cn, CnStr
> Set Cn = Server.CreateObject("ADODB.Connection")
> Cn.ConnectionTimeOut = 1
> Cn.provider="sqloledb"
> response.write "Conn Provider is " & Cn.provider
> CnStr="Data Source=Study;Initial Catalog=Norhwind;User
>Id=sa;Password=;"
> ' Tried all sorts of user id's and paswords
> 'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id = 'Jim
>Bunton';Password=;"
> 'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id
>=dbo;Password=;"
> 'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id =
>JimBunton;Password=;"
> 'CnStr="Data Source='Study';Initial Catalogue='Norhwind';User Id
>='Study\Jim Bunton';Password=;"
>%>
><p>--</p>
><%
> response.write "Cnstr = " & CnStr
> 'ERROR ALWAYS OCCURS ON NEXT LINEon the next line
> Cn.open CnStr
>%>
><p>--</p>
><%
> response.write "The End - ran ok"
>%>
></body>
></html>
>**** END CODE *****
>? what to do'
>' am I missing something 'obvious' out ?|||Thanks for your clear response Sue.
Unfortunately I still have a problem:
The following works:-
<%
dim Cn, SqlStr,rsCustomers
Set Cn=Server.CreateObject("ADODB.Connection")
Cn.Provider = "sqloledb"
Cn.Properties("Data Source") = "Study\StudySQLServer"
Cn.Properties("Initial Catalog") = "NorthWind"
Cn.Properties("Connect Timeout") = "3"
Cn.Properties("User ID") = "sa"
Cn.Properties("Password") = "mypassword"
Cn.open
. . . code which displays Customer info
The folowing does NOT:-
<%
dim Cn, CnStr, SqlStr,rsCustomers
Set Cn=Server.CreateObject("ADODB.Connection")
CnStr="Provider=sqloledb;" & _
"Data Source=Study\StudySqlServer;" & _
"Initial Catalog=NorthWind;" & _
"Integrated Security=SSPI"
response.write "cnStr = " & cnStr
' response.end
Cn.open CnStr
It fails at Cn.open CnStr
Error Type:
(0x80040E4D)
/MyWebs/Connect_wAuth.asp, line 15
response.write "cnStr = " & cnStr gives:-
cnStr = Provider=sqloledb;Data Source=Study\StudySqlServer;Initial Catalog=N
orthWind;Integrated Security=SSPI
In Query Analyser (login using Windows Authentification)
select user_Name() >> dbo
select * from Customers >> a list of customers
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:5tuui1l8ku9a5fl6r704knccn7jfun1
kf4@.4ax.com...
> If you are using windows authentication (also known as a
> trusted connection) to connect, you don't supply a user
> name, password in the connection string. Instead, you
> indicate this by using Integrated Security=SSPI
> in your connection string.
> You also want to make sure the database is correct. In your
> examples, you want to make sure you use Northwind (you have
> Norwind listed).
> You can find sample connection strings using the SQL Server
> OLE DB provider at:
> [url]http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer[/url
]
>
> -Sue
>
> On Mon, 19 Sep 2005 11:44:58 GMT, "Jim Bunton"
> <jBunton@.BlueYonder.co.uk> wrote:
>
>|||Then it's likely the authentication settings on the IIS side
of it. Try adding the IUSR account as a login in SQL Server
with the appropriate permissions as needed for your code.
Also, turned on failed login audits in SQL Server and
restart the instance. That will help you track down what
login is failing.
-Sue
On Tue, 20 Sep 2005 10:17:50 GMT, "Jim Bunton"
<jBunton@.BlueYonder.co.uk> wrote:
[vbcol=seagreen]
>Thanks for your clear response Sue.
>Unfortunately I still have a problem:
>The following works:-
><%
>dim Cn, SqlStr,rsCustomers
> Set Cn=Server.CreateObject("ADODB.Connection")
> Cn.Provider = "sqloledb"
> Cn.Properties("Data Source") = "Study\StudySQLServer"
> Cn.Properties("Initial Catalog") = "NorthWind"
> Cn.Properties("Connect Timeout") = "3"
> Cn.Properties("User ID") = "sa"
> Cn.Properties("Password") = "mypassword"
> Cn.open
> . . . code which displays Customer info
>The folowing does NOT:-
><%
>dim Cn, CnStr, SqlStr,rsCustomers
> Set Cn=Server.CreateObject("ADODB.Connection")
> CnStr="Provider=sqloledb;" & _
> "Data Source=Study\StudySqlServer;" & _
> "Initial Catalog=NorthWind;" & _
> "Integrated Security=SSPI"
> response.write "cnStr = " & cnStr
> ' response.end
> Cn.open CnStr
>It fails at Cn.open CnStr
>Error Type:
>(0x80040E4D)
>/MyWebs/Connect_wAuth.asp, line 15
>response.write "cnStr = " & cnStr gives:-
>cnStr = Provider=sqloledb;Data Source=Study\StudySqlServer;Initial Catalog=
NorthWind;Integrated Security=SSPI
>In Query Analyser (login using Windows Authentification)
>select user_Name() >> dbo
>select * from Customers >> a list of customers
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:5tuui1l8ku9a5f
l6r704knccn7jfun1kf4@.4ax.com...sql

Cant connect

Win 2000, sqlServer 2000 Personal edition

Trying ot connect using .asp Vb Script

1. Sql server set to use Windows Authentication
2. Log onto Windows 2000 as Jim Bunton {no password)
3 Server 'Study' running ok
4. sql query analyser
i. "SELECT User_Name > dbo
ii. [using database NorthWind]
"SELECT * FROM Customers" > gives expected result

5. VB script
** BEGIN CODE connect.asp ***
<%
@. LANGUAGE="VBSCRIPT"
%
<%
Option explicit
response.expires = 0
%
<!-- #include file="adovbs.inc" --
<html>
<head>
<title></title>
</head>
<body>
<%
dim Cn, CnStr
Set Cn = Server.CreateObject("ADODB.Connection")
Cn.ConnectionTimeOut = 1
Cn.provider="sqloledb"
response.write "Conn Provider is " & Cn.provider
CnStr="Data Source=Study;Initial Catalog=Norhwind;User
Id=sa;Password=;"
' Tried all sorts of user id's and paswords
'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id =
'Jim Bunton';Password=;"
'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id
=dbo;Password=;"
'CnStr="Data Source=Study;Initial Catalog=Norhwind;User Id =
JimBunton;Password=;"
'CnStr="Data Source='Study';Initial Catalogue='Norhwind';User Id
='Study\Jim Bunton';Password=;"
%>
<p>----</p>
<%
response.write "Cnstr = " & CnStr
'ERROR ALWAYS OCCURS ON NEXT LINE
Cn.open CnStr
'Error Type:
'(0x80004005)
'Unspecified error
'/myweb/mywebs/connect.asp, line 35
%>
<p>----</p>
<%
response.write "The End - ran ok"
%
</body
</html
**** END CODE *****

? what to do??
?? am I missing something 'obvious' out ?

--

Jim BuntonJim Bunton (jBunton@.BlueYonder.co.uk) writes:
> Win 2000, sqlServer 2000 Personal edition
> Trying ot connect using .asp Vb Script
> 1. Sql server set to use Windows Authentication
> 2. Log onto Windows 2000 as Jim Bunton {no password)

You're a brave man!

> 'CnStr="Data Source='Study';Initial Catalogue='Norhwind';User Id
>='Study\Jim Bunton';Password=;"

That is not likely to be correct. User Id and Password is for SQL
authentication, and SQL logins usually don't have \ in them. To login
with Windows authentication, specify "Integrated Security=SSPI", or
Trusted_connect=Yes.

And spell Northwind correctly. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Tuesday, March 27, 2012

Can't Call Stored Procedure in SqlFunction

There is a SqlFunction in my SQL Server Project:

[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]
public static SqlBoolean GetRelation(SqlGuid ID)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM MemberRelation WHERE ID='" + ID + "'", conn);
//SqlCommand cmd = new SqlCommand("EXEC GetMemberRelation '" + ID + "'", conn);
/*SqlCommand cmd = new SqlCommand("GetMemberRelation", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@.ID", SqlDbType.UniqueIdentifier));
cmd.Parameters["@.ID"].Value = ID;*/

using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
//...
sdr.Close();
}
conn.Close();
}
}

When I try to call a stored procedure by using one of the two marked code segments instead of concatenating string in GetRelation, I can deploy the project to my SQL server without problem. But when I call GetRelation from Query Analyzer, I get an error: "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function."

What does the error message mean? How could I correct it? Thanks for answering.

The problem is resolved. Just remove the default "SET NOCOUNT ON;" added by SQL 2005 while creating stored procedure, then the SqlFunction will work.|||

Does anyone know of another way to fix this? We have alot of stored procedures with "SET NOCOUNT ON" and I would really rather not have to make that change unless we have to.

Thanks,
JD

|||Hi JD,

Bleh... I can't see good way. :) You could make a normal connection instead of using the context connection, but you'll have to deploy your assembly as external-access.

Cheers,
-Isaac

Can't Call Stored Procedure in SqlFunction

There is a SqlFunction in my SQL Server Project:

[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]
public static SqlBoolean GetRelation(SqlGuid ID)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM MemberRelation WHERE ID='" + ID + "'", conn);
//SqlCommand cmd = new SqlCommand("EXEC GetMemberRelation '" + ID + "'", conn);
/*SqlCommand cmd = new SqlCommand("GetMemberRelation", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@.ID", SqlDbType.UniqueIdentifier));
cmd.Parameters["@.ID"].Value = ID;*/

using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
//...
sdr.Close();
}
conn.Close();
}
}

When I try to call a stored procedure by using one of the two marked code segments instead of concatenating string in GetRelation, I can deploy the project to my SQL server without problem. But when I call GetRelation from Query Analyzer, I get an error: "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function."

What does the error message mean? How could I correct it? Thanks for answering.

The problem is resolved. Just remove the default "SET NOCOUNT ON;" added by SQL 2005 while creating stored procedure, then the SqlFunction will work.|||

Does anyone know of another way to fix this? We have alot of stored procedures with "SET NOCOUNT ON" and I would really rather not have to make that change unless we have to.

Thanks,
JD

|||Hi JD,

Bleh... I can't see good way. :) You could make a normal connection instead of using the context connection, but you'll have to deploy your assembly as external-access.

Cheers,
-Isaacsql

Can't backup systemstate when SQLServer running?

Any ideas why I can't backup the systemstate for a DC when SQLServer
(2000) is running? I'm confused why SQLServer is even included as part
of the systemstate?
This is the specific error:
"MSDEWriter" has reported an error 0x800423f4. This is part of System
State. The backup cannot continue.
Event ID: 6008
Sqllib error: Failed to create VDS object. hr = 0x80770005.
- Code: SQLVFRZC 00000255
- Call: SQLVFRZC 00000176
- PID: 00004164 - TID: 00004444
- CMD: C:\WINDOWS\System32\vssvc.exe
- User: NTAUTHORITY\SYSTEM - Sid: S-1-5-18try this:
http://support.microsoft.com/?id=828481
http://support.microsoft.com/kb/912414
Tom
"Steve" <steverino.smith@.gmail.com> wrote in message
news:1171056337.693146.104080@.j27g2000cwj.googlegroups.com...
> Any ideas why I can't backup the systemstate for a DC when SQLServer
> (2000) is running? I'm confused why SQLServer is even included as part
> of the systemstate?
> This is the specific error:
> "MSDEWriter" has reported an error 0x800423f4. This is part of System
> State. The backup cannot continue.
> Event ID: 6008
> Sqllib error: Failed to create VDS object. hr = 0x80770005.
> - Code: SQLVFRZC 00000255
> - Call: SQLVFRZC 00000176
> - PID: 00004164 - TID: 00004444
> - CMD: C:\WINDOWS\System32\vssvc.exe
> - User: NTAUTHORITY\SYSTEM - Sid: S-1-5-18
>sql

Can't backup systemstate when SQLServer running?

Any ideas why I can't backup the systemstate for a DC when SQLServer
(2000) is running? I'm confused why SQLServer is even included as part
of the systemstate?
This is the specific error:
"MSDEWriter" has reported an error 0x800423f4. This is part of System
State. The backup cannot continue.
Event ID: 6008
Sqllib error: Failed to create VDS object. hr = 0x80770005.
- Code: SQLVFRZC 00000255
- Call: SQLVFRZC 00000176
- PID: 00004164 - TID: 00004444
- CMD: C:\WINDOWS\System32\vssvc.exe
- User: NTAUTHORITY\SYSTEM - Sid: S-1-5-18try this:
http://support.microsoft.com/?id=828481
http://support.microsoft.com/kb/912414
Tom
"Steve" <steverino.smith@.gmail.com> wrote in message
news:1171056337.693146.104080@.j27g2000cwj.googlegroups.com...
> Any ideas why I can't backup the systemstate for a DC when SQLServer
> (2000) is running? I'm confused why SQLServer is even included as part
> of the systemstate?
> This is the specific error:
> "MSDEWriter" has reported an error 0x800423f4. This is part of System
> State. The backup cannot continue.
> Event ID: 6008
> Sqllib error: Failed to create VDS object. hr = 0x80770005.
> - Code: SQLVFRZC 00000255
> - Call: SQLVFRZC 00000176
> - PID: 00004164 - TID: 00004444
> - CMD: C:\WINDOWS\System32\vssvc.exe
> - User: NTAUTHORITY\SYSTEM - Sid: S-1-5-18
>

Sunday, March 25, 2012

can't alter function

In sqlserver 2000 I have a UDF which works fine but I want to make a
change to it. When I do an ALTER FUNCTION ... I get an error saying
that I can't alter the function because it is referenced by an object.
Is there any way around this? I reference the UDF in over 100 tables,
do I have to go to each table, remove the all references alter the
function then edit each 100 tables again? How clumsy can it be?

BarryBarry wrote:

Quote:

Originally Posted by

In sqlserver 2000 I have a UDF which works fine but I want to make a
change to it. When I do an ALTER FUNCTION ... I get an error saying
that I can't alter the function because it is referenced by an object.
Is there any way around this? I reference the UDF in over 100 tables,
do I have to go to each table, remove the all references alter the
function then edit each 100 tables again? How clumsy can it be?


Yep, that's it. You can generate a script for the drop/recreate of the
objects that reference the function, using the information from the
system tables. What kind of objects are we talking about
(defaults/check constraints/computed columns) ?

Razvan|||Defaults.

What would a script to look like to do this?

In Oracle I would ALTER TABLE XXX
Modify ( Column default null)

then write a script to fill in XXX from owner_tab_columns where column
exists

Thanks
Barry

Razvan Socol wrote:

Quote:

Originally Posted by

Barry wrote:

Quote:

Originally Posted by

In sqlserver 2000 I have a UDF which works fine but I want to make a
change to it. When I do an ALTER FUNCTION ... I get an error saying
that I can't alter the function because it is referenced by an object.
Is there any way around this? I reference the UDF in over 100 tables,
do I have to go to each table, remove the all references alter the
function then edit each 100 tables again? How clumsy can it be?


>
Yep, that's it. You can generate a script for the drop/recreate of the
objects that reference the function, using the information from the
system tables. What kind of objects are we talking about
(defaults/check constraints/computed columns) ?
>
Razvan

|||How does one reference a UDF in a table?

Jim|||I'm assigning it as a default The return from the function is my
default. I use it for User_id's

jim_geiss...@.countrywide.com wrote:

Quote:

Originally Posted by

How does one reference a UDF in a table?
>
Jim

|||Barry (bgt0990@.optonline.net) writes:

Quote:

Originally Posted by

What would a script to look like to do this?
>
In Oracle I would ALTER TABLE XXX
Modify ( Column default null)
>
then write a script to fill in XXX from owner_tab_columns where column
exists


ALTER TABLE tbl DROP CONSTRAINT <nameofconstraint>

Here is a query that will generate all necessary DROP commands:

SELECT 'ALTER TABLE ' + o.name + ' ALTER COLUMN ' + c.name +
' DROP CONSTRAINT ' + oc.name
FROM sysdepends d
JOIN sysobjects ofn ON d.depid = ofn.id
JOIN sysobjects oc ON d.id = oc.id
JOIN sysobjects o ON o.id = oc.parent_obj
JOIN syscolumns c ON o.id = c.id
AND c.cdefault = oc.id
WHERE ofn.name = '<yourfunction>'

You can also modify it to regenerate the command to restore the default.
Run that modified query, before you execute the result of the above. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi, Barry

Here is how you can generate a script to drop/recreate the defaults
that depend on a given function:

SELECT 'ALTER TABLE '+QUOTENAME(OBJECT_NAME(parent_obj))
+' DROP CONSTRAINT '+QUOTENAME(name)
FROM sysobjects WHERE xtype='D' AND id IN (
SELECT id FROM sysdepends
WHERE depid=OBJECT_ID('YourFunction')
)

SELECT 'ALTER TABLE '+QUOTENAME(OBJECT_NAME(parent_obj))
+' ADD CONSTRAINT '+QUOTENAME(o.name)
+' DEFAULT '+x.text
+' FOR '+QUOTENAME(c.name)
FROM sysobjects o INNER JOIN syscomments x ON o.id=x.id
INNER JOIN syscolumns c ON c.cdefault=o.id
WHERE o.xtype='D' AND o.id IN (
SELECT id FROM sysdepends
WHERE depid=OBJECT_ID('YourFunction')
)

There is a limitation regarding the size of the definition of the
default (x.text in the above query): if it's more than 4000 characters,
the above query won't work (because there would be multiple rows in
syscomments for the same id); but I'm sure nobody would create a
default with a definition longer than 100 characters to invoke a UDF,
so that should not be a problem.

Razvan

Thursday, March 22, 2012

Can't access SqlServer 2005 using JDBC

Hi;

I can access SqlServer 2000 fine, but not SqlServer 2005. My client is on Windows XP and SqlServer 2005 (dev version) is on Windows 2003. When I run I get:

Error: net.windward.datasource.DataSourceException: JdbcDataSource could not ope
n jdbc:sqlserver://T1-Databases;databaseName=AdventureWorks;integratedSecurity=t
rue;
net.windward.datasource.DataSourceException: net.windward.datasource.DataSourceE
xception: JdbcDataSource could not open jdbc:sqlserver://T1-Databases;databaseNa
me=AdventureWorks;integratedSecurity=true;
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1030)
at net.windward.xmlreport.RunReport.main(RunReport.java:165)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid integratedSe
curity property value:true
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(U
nknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Sour
ce)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1024)
... 1 more
Exception in thread "main" net.windward.datasource.DataSourceException: net.wind
ward.datasource.DataSourceException: JdbcDataSource could not open jdbc:sqlserve
r://T1-Databases;databaseName=AdventureWorks;integratedSecurity=true;
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1030)
at net.windward.xmlreport.RunReport.main(RunReport.java:165)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid integratedSe
curity property value:true
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(U
nknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Sour
ce)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1024)
... 1 more

Any ideas?

thanks - dave

Are you using Microsoft SQL Server 2005 JDBC Driver version 1.1?

What platform (OS and version) does your JDBC client run on?

|||

I have similar error:

java.lang.NoClassDefFoundError
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

I do have the JDBC driver version 1.1.

Cocoon 2.0

Tomcat 4.x

|||

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid integratedSe
curity property value:true

It appears as though it has something to do with integratedSecurity. I think the connection string is case sensitive, you did spell it IntegratedSecurity="True" right? The exception thrown seems to be targetted to integratedSecurity specifically. Also, are you authenticating in mixed mode or windows only? Did you configure the server on install correctly to be able to authenticate the kind of connection you are trying to make?

Hope we can figure this one out together,

John

|||

Have you tried enabling the TCP/IP Protocol through the SQL Server Configuration Manager? I seem to recall it being turned off by default in SQL 2005. Enable it and restart the SQL Service.

Good luck,

-Tom

|||You can run SQL Profiler to check if your application is indeed trying to establish a connection or not.|||It looks like you are using the 1.0 dirver which did not support integrated auth property.sql

Can't access SqlServer 2005 using JDBC

Hi;

I can access SqlServer 2000 fine, but not SqlServer 2005. My client is on Windows XP and SqlServer 2005 (dev version) is on Windows 2003. When I run I get:

Error: net.windward.datasource.DataSourceException: JdbcDataSource could not ope
n jdbc:sqlserver://T1-Databases;databaseName=AdventureWorks;integratedSecurity=t
rue;
net.windward.datasource.DataSourceException: net.windward.datasource.DataSourceE
xception: JdbcDataSource could not open jdbc:sqlserver://T1-Databases;databaseNa
me=AdventureWorks;integratedSecurity=true;
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1030)
at net.windward.xmlreport.RunReport.main(RunReport.java:165)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid integratedSe
curity property value:true
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(U
nknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Sour
ce)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1024)
... 1 more
Exception in thread "main" net.windward.datasource.DataSourceException: net.wind
ward.datasource.DataSourceException: JdbcDataSource could not open jdbc:sqlserve
r://T1-Databases;databaseName=AdventureWorks;integratedSecurity=true;
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1030)
at net.windward.xmlreport.RunReport.main(RunReport.java:165)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid integratedSe
curity property value:true
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(U
nknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Sour
ce)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1024)
... 1 more

Any ideas?

thanks - dave

Are you using Microsoft SQL Server 2005 JDBC Driver version 1.1?

What platform (OS and version) does your JDBC client run on?

|||

I have similar error:

java.lang.NoClassDefFoundError
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

I do have the JDBC driver version 1.1.

Cocoon 2.0

Tomcat 4.x

|||

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid integratedSe
curity property value:true

It appears as though it has something to do with integratedSecurity. I think the connection string is case sensitive, you did spell it IntegratedSecurity="True" right? The exception thrown seems to be targetted to integratedSecurity specifically. Also, are you authenticating in mixed mode or windows only? Did you configure the server on install correctly to be able to authenticate the kind of connection you are trying to make?

Hope we can figure this one out together,

John

|||

Have you tried enabling the TCP/IP Protocol through the SQL Server Configuration Manager? I seem to recall it being turned off by default in SQL 2005. Enable it and restart the SQL Service.

Good luck,

-Tom

|||You can run SQL Profiler to check if your application is indeed trying to establish a connection or not.|||It looks like you are using the 1.0 dirver which did not support integrated auth property.

Can't access SqlServer 2005 using JDBC

Hi;

I can access SqlServer 2000 fine, but not SqlServer 2005. My client is on Windows XP and SqlServer 2005 (dev version) is on Windows 2003. When I run I get:

Error: net.windward.datasource.DataSourceException: JdbcDataSource could not ope
n jdbc:sqlserver://T1-Databases;databaseName=AdventureWorks;integratedSecurity=t
rue;
net.windward.datasource.DataSourceException: net.windward.datasource.DataSourceE
xception: JdbcDataSource could not open jdbc:sqlserver://T1-Databases;databaseNa
me=AdventureWorks;integratedSecurity=true;
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1030)
at net.windward.xmlreport.RunReport.main(RunReport.java:165)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid integratedSe
curity property value:true
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(U
nknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Sour
ce)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1024)
... 1 more
Exception in thread "main" net.windward.datasource.DataSourceException: net.wind
ward.datasource.DataSourceException: JdbcDataSource could not open jdbc:sqlserve
r://T1-Databases;databaseName=AdventureWorks;integratedSecurity=true;
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1030)
at net.windward.xmlreport.RunReport.main(RunReport.java:165)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid integratedSe
curity property value:true
at
com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(U
nknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Sour
ce)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at net.windward.datasource.jdbc.JdbcDataSource.<init>(JdbcDataSource.jav
a:1024)
... 1 more

Any ideas?

thanks - dave

Are you using Microsoft SQL Server 2005 JDBC Driver version 1.1?

What platform (OS and version) does your JDBC client run on?

|||

I have similar error:

java.lang.NoClassDefFoundError
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)

I do have the JDBC driver version 1.1.

Cocoon 2.0

Tomcat 4.x

|||

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid integratedSe
curity property value:true

It appears as though it has something to do with integratedSecurity. I think the connection string is case sensitive, you did spell it IntegratedSecurity="True" right? The exception thrown seems to be targetted to integratedSecurity specifically. Also, are you authenticating in mixed mode or windows only? Did you configure the server on install correctly to be able to authenticate the kind of connection you are trying to make?

Hope we can figure this one out together,

John

|||

Have you tried enabling the TCP/IP Protocol through the SQL Server Configuration Manager? I seem to recall it being turned off by default in SQL 2005. Enable it and restart the SQL Service.

Good luck,

-Tom

|||You can run SQL Profiler to check if your application is indeed trying to establish a connection or not.|||It looks like you are using the 1.0 dirver which did not support integrated auth property.

Tuesday, March 20, 2012

can't access server

Hi, I'm having problem with a workstation connecting to the Server's SQL
Server. The workstation can access the server's hard drive via share and
TCP/IP seems to be working. But the workstation just can't access the
server.
how to tack it down? Thanks.
Hi
What is the error message?
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/
"js" <js@.someone.com> wrote in message
news:uzlVHEDLFHA.3628@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm having problem with a workstation connecting to the Server's SQL
> Server. The workstation can access the server's hard drive via share and
> TCP/IP seems to be working. But the workstation just can't access the
> server.
> how to tack it down? Thanks.
>
|||Thanks Mike,
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eD0%23SeHLFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Hi
> What is the error message?
Error: SQL server does not exist or access denied.
It works local, but not trying to connect from another computer.
And I found there are two instance of sql server.
how to check it out? thanks.
sql

can't access server

Hi, I'm having problem with a workstation connecting to the Server's SQL
Server. The workstation can access the server's hard drive via share and
TCP/IP seems to be working. But the workstation just can't access the
server.
how to tack it down? Thanks.Hi
What is the error message?
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/
"js" <js@.someone.com> wrote in message
news:uzlVHEDLFHA.3628@.TK2MSFTNGP10.phx.gbl...
> Hi, I'm having problem with a workstation connecting to the Server's SQL
> Server. The workstation can access the server's hard drive via share and
> TCP/IP seems to be working. But the workstation just can't access the
> server.
> how to tack it down? Thanks.
>|||Thanks Mike,
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eD0%23SeHLFHA.2716@.TK2MSFTNGP15.phx.gbl...
> Hi
> What is the error message?
Error: SQL server does not exist or access denied.
It works local, but not trying to connect from another computer.
And I found there are two instance of sql server.
how to check it out? thanks.

Can't access merge agent properties

Hi,
I'm having a problem trying to access merge agent's properties. I have
a SqlServer publication that synchronizes with diferent PDA devices.
These devices register themselfes as subscribers to synchronize with
the database and create a merge agent for each of them.
The problem is that I can't access the agent's properties. If I go to
Replication Monitor -> Agents -> Merge Agents in sql server's
administrative console, right click one of the agent (e.g subscription
name 21F:-460) and then select "Agent Properties..." i get a login
screen "Connect to Sql Server: 21F" and asks for user & password.
What is this account ? I can't understand how this works, could
somebody explain it please ? Is it possible to access merge agent
properties ?
Any help would be appreciated,
thx in advance.
ori
That's the problem with the ActiveX controls used by replication, you can't view the properties in EM.
I normally don't get this error message however when I right click on the agents.
To administer these activeX agents you need to make changes in your code, not through EM.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ORi" wrote:

> Hi,
> I'm having a problem trying to access merge agent's properties. I have
> a SqlServer publication that synchronizes with diferent PDA devices.
> These devices register themselfes as subscribers to synchronize with
> the database and create a merge agent for each of them.
> The problem is that I can't access the agent's properties. If I go to
> Replication Monitor -> Agents -> Merge Agents in sql server's
> administrative console, right click one of the agent (e.g subscription
> name 21F:-460) and then select "Agent Properties..." i get a login
> screen "Connect to Sql Server: 21F" and asks for user & password.
> What is this account ? I can't understand how this works, could
> somebody explain it please ? Is it possible to access merge agent
> properties ?
> Any help would be appreciated,
> thx in advance.
> ori
>

Thursday, March 8, 2012

Cannot update identity column

I have just taken over a sqlserver 2k database using C# (not to mention I
don't know the language). I have a column in one of the tables that is an
autonumber as well as a primary key. Here is the problem, the autonumber
was created to display the current year and a number. Example, 2004001,
2004002, 2004003, etc. Now that the year has changed, the column is still
generating numbers for 2004, when I try to update the field and change a
record for example: Update ABC set rptnum = '2005001' where rptnum =
'2004257' I get 'Cannot update identity column 'rptnum''. Not only do I
need to change the value to reflect 2005001, I also need the next record
enter to automaticly get the next number ex. 2005002, 2005003, etc.
Thanks in advance,vickie wrote:
> I have just taken over a sqlserver 2k database using C# (not to
> mention I don't know the language). I have a column in one of the
> tables that is an autonumber as well as a primary key. Here is the
> problem, the autonumber was created to display the current year and a
> number. Example, 2004001, 2004002, 2004003, etc. Now that the year
> has changed, the column is still generating numbers for 2004, when I
> try to update the field and change a record for example: Update ABC
> set rptnum = '2005001' where rptnum = '2004257' I get 'Cannot update
> identity column 'rptnum''. Not only do I need to change the value to
> reflect 2005001, I also need the next record enter to automaticly get
> the next number ex. 2005002, 2005003, etc. Thanks in advance,
AutoNumber sounds like an Access term. SQL Server uses IDENTITY
attribute on integer-type columns. THe first thing you should know about
identity columns is that they are not really designed to be intelligent
keys. There are usually used as a surrogate key, where the internal
value is meaningless.
Your inherited application is using them as intelligent keys, which I
don't think is a really good idea. Identity columns cannot be updated
directly. This is by design to prevent chaning values which SQL Server
is trying to make sure are unique.
You can update an identity by using the SET IDENTITY_INSERT ON / OFF
function before the update. In any case, you'll need to reseed the
identity value before you do this to get it to start pulling numbers
from your desired range.
You can reseed the identity using the DBCC CHECKIDENT function. See BOL
for more information on the actual parameters for the call. Reseed
first, leaving enough range for you to make the updates to the incorrect
identity values and then update the old rows. And then reconsider the
use of an identity on this table. Maybe an identity column for the PK
and a data/time column for dating would be a better idea.
David Gugick
Imceda Software
www.imceda.com|||By default, identity inserts are prohibited. However, by calling
SET IDENTITY_INSERT <table> ON/OFF
you can alter this behaviour.
So you may turn identity inserts on, insert a single new record and turn
it off again.
/Jo
vickie wrote:

> I have just taken over a sqlserver 2k database using C# (not to mention I
> don't know the language). I have a column in one of the tables that is an
> autonumber as well as a primary key. Here is the problem, the autonumber
> was created to display the current year and a number. Example, 2004001,
> 2004002, 2004003, etc. Now that the year has changed, the column is still
> generating numbers for 2004, when I try to update the field and change a
> record for example: Update ABC set rptnum = '2005001' where rptnum =
> '2004257' I get 'Cannot update identity column 'rptnum''. Not only do I
> need to change the value to reflect 2005001, I also need the next record
> enter to automaticly get the next number ex. 2005002, 2005003, etc.
> Thanks in advance,
>|||Hopefully, the person who designed this table got fired - or sent on a
course to learn something about SQL Server! ;-)
Using IDENTITY (the proper term for an "autonumber" column). as a
"meaningful" key is very wrong. I suggest that your best bet is to add
a DATETIME column (if you don't have one already) to represent the date
and then ignore the IDENTITY values. Set a default of the current date
on the DATETIME column. That will likely require some application
changes but the alternative of trying to control the IDENTITY value is
a very messy kludge.
The short term fix is to use DBCC CHECKIDENT to change the identity
seed value:
DBCC CHECKIDENT (YourTable, RESEED, 2005001)
Then delete and re-insert the rows that have the wrong ID (You cannot
UPDATE an IDENTITY value).
If you do resort to the kludge rather than fix the table properly then
you will obviously have the same problem again next year.
Hope this helps.
David Portas
SQL Server MVP
--|||First the preaching. The identity property of a column is not good for what
you are doing with it. It is only an effective tool for building a
surrogate key. It is not meant to be modified, so it makes a bad value to
show to users. Reconsider how you are doing this, and just make the column
an integer that you insert values into. I also assume that you probably
don't want the chance of getting gaps in the sequence either, which is
common with identities.
As for what you actually asked: You cannot (as the error says) update
identity values. You will have to recreate your table, insert the existing
values (can't be more than a thousand rows, apparently.) You can change
the seed:
dbcc checkident(<tablename>,reseed,20050000)
Here is a test script to show you it in action.
create table testIdentity
(
id int identity (2004001,1),
value varchar(10)
)
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
select * from testIdentity
/*
id value
-- --
2004001 stuff
2004002 stuff
2004003 stuff
2004004 stuff
*/
dbcc checkident(testIdentity,reseed,2005000)
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
insert into testIdentity
values ('stuff')
select * from testIdentity
/*
id value
-- --
2004001 stuff
2004002 stuff
2004003 stuff
2004004 stuff
2005001 stuff
2005002 stuff
2005003 stuff
2005004 stuff
*/
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"vickie" <victoria.wheaton@.ngc.com> wrote in message
news:c6e431cc1937df694405d2d97d6d6c6e@.lo
calhost.talkaboutsoftware.com...
>I have just taken over a sqlserver 2k database using C# (not to mention I
> don't know the language). I have a column in one of the tables that is an
> autonumber as well as a primary key. Here is the problem, the autonumber
> was created to display the current year and a number. Example, 2004001,
> 2004002, 2004003, etc. Now that the year has changed, the column is still
> generating numbers for 2004, when I try to update the field and change a
> record for example: Update ABC set rptnum = '2005001' where rptnum =
> '2004257' I get 'Cannot update identity column 'rptnum''. Not only do I
> need to change the value to reflect 2005001, I also need the next record
> enter to automaticly get the next number ex. 2005002, 2005003, etc.
> Thanks in advance,
>|||Yeah, but the question was can you UPDATE the identity value. Which you
cannot, even with this setting set.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Jo Siffert" <jo.siffert@.gmx.net> wrote in message
news:eEb4AYtCFHA.1296@.TK2MSFTNGP10.phx.gbl...
> By default, identity inserts are prohibited. However, by calling
> SET IDENTITY_INSERT <table> ON/OFF
> you can alter this behaviour.
> So you may turn identity inserts on, insert a single new record and turn
> it off again.
> /Jo
> vickie wrote:
>|||>> You can update an identity by using the SET IDENTITY_INSERT ON / OFF
Are you sure?
Identity columns are non-updateable. The only way one could alter the
identity column value is to delete and insert the same data within a single
transaction. But then, any existing references on this column could all be
messed up.
Anith|||Anith Sen wrote:
> Are you sure?
> Identity columns are non-updateable. The only way one could alter the
> identity column value is to delete and insert the same data within a
> single transaction. But then, any existing references on this column
> could all be messed up.
Oops. Talking about updates and inserts before coffee is a bad idea.
David Gugick
Imceda Software
www.imceda.com|||On 4 Feb 2005 08:33:38 -0800, David Portas wrote:
(snip)
>If you do resort to the kludge rather than fix the table properly then
>you will obviously have the same problem again next year.
Or a much bigger problem if the company fares well and more than 999 new
rows are inserted during this year......
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The fifth labor of Hercules was to clean the stables of King Augeas in
a single day. The Augean stables held thousands of animals and were
over a mile long. This story has a happy ending for three reasons: (1)
Hercules solved the problem in a clever way (2) Hercules got one tenth
of the cattle for his work (3) At the end of the story of the Labors of
Hercules, he got to kill the bastard that gave him this job.
You have all of the horse manure and probably cannot kill the guy who
screwed this up. Bummer.
Rows are not records; fields are not columns; tables are not files.
More importantly, autonumbers (IDENTITY, GUID, ROWID, et al) are not
keys by definition. They are exposed physical locators and have
nothing to do the logicl data model.
You need to start over and move the data to a new schema. If you
business logic is to use the 1950's IBM magnetic tape label codes, then
write a procedure to build them.
I also hope that your business rules are not to grow beyond 1000
transactions per year -- plan stagnation and failure rather than
letting it happen.

Wednesday, March 7, 2012

Cannot start the Reporting Service

I am currently working on a windows 2003 server with SQL2000 (developers edition). My Sqlserver and Report Server are on two different machines.

This was working yesterday but frustratingly not this morning.

Everytime I try to start the Reporting Server service, it gives me the following message:

"The Report Server service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.

Is there a way around this? - any help would be greatly appreciated .

Anyone?|||If you are still seeing this issue...I found a solution for a similiar error (a different service, but the same error). Had to clear out the Application log in Event Viewer. See if that helps.|||Possibly best not to delete the contents of the event logs, as this may provide a pointer towards solving your problem. Open the eventlogs, then start the service, find the error that is logged for it. If you post that here it may give more of an idea.
|||

I'm facing a very similar problem. I've installed, uninstalled and reinstalled SQL server 2005 Developer Edition twice now. In each case Report Server can't be started. I've looked in the logs and the pertinent errors seem to be the following:

ReportingServicesService!library!4!6/16/2006-15:28:33:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'E1705\ASPNET' is not recognized., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'E1705\ASPNET' is not recognized.
ReportingServicesService!servicecontroller!4!6/16/2006-15:28:33:: e ERROR: Exception caught starting RPC server: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'E1705\ASPNET' is not recognized.

In the other log file is the following error:

ReportingServicesService!library!8!6/16/2006-15:28:36:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportServerDatabaseException: The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights., ;
Info:

Looking at the time stamp, I believe the later is just a result of the UnknownUserNameException:

SQL Server works fine, I can work with the database in the Management Studio and access them through ASP.NET without a problem (or at least I could before I uninstalled a couple of times!!)

.Net framework 2.0 is installed. I have a Gig of RAM, plenty of hard disk and XP Prof Ed SP 2.

I'm at a total loss on how to get it to work on this machine. Any help would be greatly appreciated!!

Bob O'Dell

|||

The problem seems to be that the default ASP.NET user is not present. Are you able to find ASPNET user on your local machine E1705? Re-registering asp.net might help.

aspnet_regiis.exe -i -enable

|||

ASPNET user did appear as a user and I tried different permissions to no avail. What finally worked was to uninstall Report Services (actually I uninstalled ALL of SQL server) and then reinstalled the .NET framework. After that I reinstalled SQL Server and Report Services. Everything seemed to work after that. I'm not sure what was wrong, but I'm off and running now!

Thanks for the help

|||

James Wu - MSFT wrote:

The problem seems to be that the default ASP.NET user is not present. Are you able to find ASPNET user on your local machine E1705? Re-registering asp.net might help.

aspnet_regiis.exe -i -enable

This was the 'trick' I was looking for. Now I'm able to start the Reporting Service. Many thanks!!

Cannot start the Reporting Service

I am currently working on a windows 2003 server with SQL2000 (developers edition). My Sqlserver and Report Server are on two different machines.

This was working yesterday but frustratingly not this morning.

Everytime I try to start the Reporting Server service, it gives me the following message:

"The Report Server service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.

Is there a way around this? - any help would be greatly appreciated .

Anyone?|||If you are still seeing this issue...I found a solution for a similiar error (a different service, but the same error). Had to clear out the Application log in Event Viewer. See if that helps.|||Possibly best not to delete the contents of the event logs, as this may provide a pointer towards solving your problem. Open the eventlogs, then start the service, find the error that is logged for it. If you post that here it may give more of an idea.
|||

I'm facing a very similar problem. I've installed, uninstalled and reinstalled SQL server 2005 Developer Edition twice now. In each case Report Server can't be started. I've looked in the logs and the pertinent errors seem to be the following:

ReportingServicesService!library!4!6/16/2006-15:28:33:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'E1705\ASPNET' is not recognized., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'E1705\ASPNET' is not recognized.
ReportingServicesService!servicecontroller!4!6/16/2006-15:28:33:: e ERROR: Exception caught starting RPC server: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'E1705\ASPNET' is not recognized.

In the other log file is the following error:

ReportingServicesService!library!8!6/16/2006-15:28:36:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportServerDatabaseException: The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights., ;
Info:

Looking at the time stamp, I believe the later is just a result of the UnknownUserNameException:

SQL Server works fine, I can work with the database in the Management Studio and access them through ASP.NET without a problem (or at least I could before I uninstalled a couple of times!!)

.Net framework 2.0 is installed. I have a Gig of RAM, plenty of hard disk and XP Prof Ed SP 2.

I'm at a total loss on how to get it to work on this machine. Any help would be greatly appreciated!!

Bob O'Dell

|||

The problem seems to be that the default ASP.NET user is not present. Are you able to find ASPNET user on your local machine E1705? Re-registering asp.net might help.

aspnet_regiis.exe -i -enable

|||

ASPNET user did appear as a user and I tried different permissions to no avail. What finally worked was to uninstall Report Services (actually I uninstalled ALL of SQL server) and then reinstalled the .NET framework. After that I reinstalled SQL Server and Report Services. Everything seemed to work after that. I'm not sure what was wrong, but I'm off and running now!

Thanks for the help

|||

James Wu - MSFT wrote:

The problem seems to be that the default ASP.NET user is not present. Are you able to find ASPNET user on your local machine E1705? Re-registering asp.net might help.

aspnet_regiis.exe -i -enable

This was the 'trick' I was looking for. Now I'm able to start the Reporting Service. Many thanks!!

Cannot start the Reporting Service

I am currently working on a windows 2003 server with SQL2000 (developers edition). My Sqlserver and Report Server are on two different machines.

This was working yesterday but frustratingly not this morning.

Everytime I try to start the Reporting Server service, it gives me the following message:

"The Report Server service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.

Is there a way around this? - any help would be greatly appreciated .

Anyone?|||If you are still seeing this issue...I found a solution for a similiar error (a different service, but the same error). Had to clear out the Application log in Event Viewer. See if that helps.|||Possibly best not to delete the contents of the event logs, as this may provide a pointer towards solving your problem. Open the eventlogs, then start the service, find the error that is logged for it. If you post that here it may give more of an idea.
|||

I'm facing a very similar problem. I've installed, uninstalled and reinstalled SQL server 2005 Developer Edition twice now. In each case Report Server can't be started. I've looked in the logs and the pertinent errors seem to be the following:

ReportingServicesService!library!4!6/16/2006-15:28:33:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'E1705\ASPNET' is not recognized., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'E1705\ASPNET' is not recognized.
ReportingServicesService!servicecontroller!4!6/16/2006-15:28:33:: e ERROR: Exception caught starting RPC server: Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'E1705\ASPNET' is not recognized.

In the other log file is the following error:

ReportingServicesService!library!8!6/16/2006-15:28:36:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportServerDatabaseException: The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights., ;
Info:

Looking at the time stamp, I believe the later is just a result of the UnknownUserNameException:

SQL Server works fine, I can work with the database in the Management Studio and access them through ASP.NET without a problem (or at least I could before I uninstalled a couple of times!!)

.Net framework 2.0 is installed. I have a Gig of RAM, plenty of hard disk and XP Prof Ed SP 2.

I'm at a total loss on how to get it to work on this machine. Any help would be greatly appreciated!!

Bob O'Dell

|||

The problem seems to be that the default ASP.NET user is not present. Are you able to find ASPNET user on your local machine E1705? Re-registering asp.net might help.

aspnet_regiis.exe -i -enable

|||

ASPNET user did appear as a user and I tried different permissions to no avail. What finally worked was to uninstall Report Services (actually I uninstalled ALL of SQL server) and then reinstalled the .NET framework. After that I reinstalled SQL Server and Report Services. Everything seemed to work after that. I'm not sure what was wrong, but I'm off and running now!

Thanks for the help

|||

James Wu - MSFT wrote:

The problem seems to be that the default ASP.NET user is not present. Are you able to find ASPNET user on your local machine E1705? Re-registering asp.net might help.

aspnet_regiis.exe -i -enable

This was the 'trick' I was looking for. Now I'm able to start the Reporting Service. Many thanks!!

cannot start sqlserver agent - urgent

I cannot start the sqlserver agent neither manually or automatically and I
get this message in the event log:
SQLServerAgent could not be started (reason: Unable to connect to server
'(local)'; SQLServerAgent cannot start).
Can anyone please help? Thanks.
Is your local SQL Server instance running? Can you connect to your SQL
instance using a different tool (like Query Analyser or osql.exe) and
the same login (be it a SQL login or a trusted connection) that your
instance of SQLAgent is configured to use? Is SQLAgent configured to
use a trusted connection or a standard SQL login?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
00allen_iverson wrote:

>I cannot start the sqlserver agent neither manually or automatically and I
>get this message in the event log:
>SQLServerAgent could not be started (reason: Unable to connect to server
>'(local)'; SQLServerAgent cannot start).
>Can anyone please help? Thanks.
>
>
|||How can I find out the SQLagent configuration of trust connection or standard SQL login? And how can I get help from this. Thanks.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u0rF7QZYFHA.4060@.TK2MSFTNGP10.phx.gbl...
Is your local SQL Server instance running? Can you connect to your SQL instance using a different tool (like Query Analyser or osql.exe) and the same login (be it a SQL login or a trusted connection) that your instance of SQLAgent is configured to use? Is SQLAgent configured to use a trusted connection or a standard SQL login?
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
00allen_iverson wrote:
I cannot start the sqlserver agent neither manually or automatically and I
get this message in the event log:
SQLServerAgent could not be started (reason: Unable to connect to server
'(local)'; SQLServerAgent cannot start).
Can anyone please help? Thanks.
|||Open SQL Enterprise Manager. Expand the branches to expose your local
instance. Left-click on the local instance; this will establish a
client connection (SQLEM being the client) to your server. If that is
successful then your SQL server is running and accepting client connections.
Expand the + next to the server name. Expand the + next to Management.
Right-click on SQL Server Agent and select the property menu option.
This will bring up the configuration dialog box for SQL Agent.
Click on the last tab (Connection). This will tell you how SQLAgent is
attempting to connect to the SQL server instance.
(I'm sure this basic GUI config stuff is described in SQL Books
Online...like here for instance:
http://msdn.microsoft.com/library/de...omate_01et.asp)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
00allen_iverson wrote:
[vbcol=seagreen]
> How can I find out the SQLagent configuration of trust connection or
> standard SQL login? And how can I get help from this. Thanks.
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:u0rF7QZYFHA.4060@.TK2MSFTNGP10.phx.gbl...
> Is your local SQL Server instance running? Can you connect to
> your SQL instance using a different tool (like Query Analyser or
> osql.exe) and the same login (be it a SQL login or a trusted
> connection) that your instance of SQLAgent is configured to use?
> Is SQLAgent configured to use a trusted connection or a standard
> SQL login?
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> 00allen_iverson wrote:

cannot start sqlserver agent - urgent

I cannot start the sqlserver agent neither manually or automatically and I
get this message in the event log:
SQLServerAgent could not be started (reason: Unable to connect to server
'(local)'; SQLServerAgent cannot start).
Can anyone please help? Thanks.Is your local SQL Server instance running? Can you connect to your SQL
instance using a different tool (like Query Analyser or osql.exe) and
the same login (be it a SQL login or a trusted connection) that your
instance of SQLAgent is configured to use? Is SQLAgent configured to
use a trusted connection or a standard SQL login?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
00allen_iverson wrote:

>I cannot start the sqlserver agent neither manually or automatically and I
>get this message in the event log:
>SQLServerAgent could not be started (reason: Unable to connect to server
>'(local)'; SQLServerAgent cannot start).
>Can anyone please help? Thanks.
>
>|||How can I find out the SQLagent configuration of trust connection or standar
d SQL login? And how can I get help from this. Thanks.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:u0r
F7QZYFHA.4060@.TK2MSFTNGP10.phx.gbl...
Is your local SQL Server instance running? Can you connect to your SQL inst
ance using a different tool (like Query Analyser or osql.exe) and the same l
ogin (be it a SQL login or a trusted connection) that your instance of SQLAg
ent is configured to use? Is SQLAgent configured to use a trusted connectio
n or a standard SQL login?
mike hodgson | database administrator | mallesons stephen jaques
T +61 (2) 9296 3668 | F +61 (2) 9296 3885 | M +61 (408) 675 907
E mailto:mike.hodgson@.mallesons.nospam.com | W http://www.mallesons.com
00allen_iverson wrote:
I cannot start the sqlserver agent neither manually or automatically and I
get this message in the event log:
SQLServerAgent could not be started (reason: Unable to connect to server
'(local)'; SQLServerAgent cannot start).
Can anyone please help? Thanks.|||Open SQL Enterprise Manager. Expand the branches to expose your local
instance. Left-click on the local instance; this will establish a
client connection (SQLEM being the client) to your server. If that is
successful then your SQL server is running and accepting client connections.
Expand the + next to the server name. Expand the + next to Management.
Right-click on SQL Server Agent and select the property menu option.
This will bring up the configuration dialog box for SQL Agent.
Click on the last tab (Connection). This will tell you how SQLAgent is
attempting to connect to the SQL server instance.
(I'm sure this basic GUI config stuff is described in SQL Books
Online...like here for instance:
ate_01et.asp" target="_blank">http://msdn.microsoft.com/library/d...>
ate_01et.asp)
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
00allen_iverson wrote:
[vbcol=seagreen]
> How can I find out the SQLagent configuration of trust connection or
> standard SQL login? And how can I get help from this. Thanks.
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in message
> news:u0rF7QZYFHA.4060@.TK2MSFTNGP10.phx.gbl...
> Is your local SQL Server instance running? Can you connect to
> your SQL instance using a different tool (like Query Analyser or
> osql.exe) and the same login (be it a SQL login or a trusted
> connection) that your instance of SQLAgent is configured to use?
> Is SQLAgent configured to use a trusted connection or a standard
> SQL login?
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W*
> http://www.mallesons.com
>
> 00allen_iverson wrote:
>