Question about restore replication database

Hi Guys,
I got a trouble that I tried to restore a database that has a replication
created on and I got an error after restore. In log reader agent and
distribution agent became red color and error message like "Could not execute
sp_repldone/sp_replcounters.." Sorry I cannot remember error message
exactly, and I am unable to restart the agent. I guess the reason might be
that I didn't restore distributiuon because I didn't backup this database. Do
I need to restore distribution databse when I restore publication database?
thanks.
Ideally your publication and distribution database would be in lock step by
using the sync with backup option.
Otherwise you should use sp_replrestart to get around problems like this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:63B4A13A-87EC-48B3-8867-EBD74B228A24@.microsoft.com...
> Hi Guys,
> I got a trouble that I tried to restore a database that has a replication
> created on and I got an error after restore. In log reader agent and
> distribution agent became red color and error message like "Could not
> execute
> sp_repldone/sp_replcounters.." Sorry I cannot remember error message
> exactly, and I am unable to restart the agent. I guess the reason might be
> that I didn't restore distributiuon because I didn't backup this database.
> Do
> I need to restore distribution databse when I restore publication
> database?
> thanks.
>
|||What is "sync with backup option"? Also, I looked in BOL and could not find
sp_replrestart. I am interested in this because we are seeting up a new
merge publication and am interested in how it affects backup/restore.
Thanks.
David
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23GOSOVNOHHA.780@.TK2MSFTNGP03.phx.gbl...
> Ideally your publication and distribution database would be in lock step
> by using the sync with backup option.
> Otherwise you should use sp_replrestart to get around problems like this.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Iter" <Iter@.discussions.microsoft.com> wrote in message
> news:63B4A13A-87EC-48B3-8867-EBD74B228A24@.microsoft.com...
>
|||This is for transactional replication and not merge replication.
for more info on sync with backup consult -
http://msdn2.microsoft.com/en-us/library/aa237094(SQL.80).aspx
for more info repl_restart consult
http://msdn2.microsoft.com/en-us/library/ms174390.aspx
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%234Q6oLPOHHA.2232@.TK2MSFTNGP02.phx.gbl...
> What is "sync with backup option"? Also, I looked in BOL and could not
> find sp_replrestart. I am interested in this because we are seeting up a
> new merge publication and am interested in how it affects backup/restore.
> Thanks.
> David
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23GOSOVNOHHA.780@.TK2MSFTNGP03.phx.gbl...
>

Question about relationships - "cycles or multiple cascade paths" error

I have 2 tables:
Table: UserTable
Field: Id (pk)
Field: FullName
Table: MarriageTable
Field: MarriageId (pk)
Field: Husband
Field: Wife
The MarriageTable.Husband and MarriageTable.Wife fields BOTH have to be
valid users from the UserTable.
In SQL Server I'm creating a relationship diagram. I (1) dragged Id from
UserTable to MarriageTable.Husband and (2) dragged Id from UserTable to
MarriageTable.Wife. But, the Diagram has trouble saving the Wife
relationship. Any ideas? Am I doing this correctly?
Here's the error:
'UserTable' table saved successfully
'MarriageTable' table
- Unable to create relationship 'FK_MarriageTable_UserTable1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing
FOREIGN KEY constraint 'FK_MarriageTable_UserTable1' on table
'MarriageTable' may cause cycles or multiple cascade paths. Specify ON
DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY
constraints.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
Thanks.
On Wed, 29 Dec 2004 10:01:20 -0500, VB Programmer wrote:

>I have 2 tables:
>Table: UserTable
>Field: Id (pk)
>Field: FullName
>Table: MarriageTable
>Field: MarriageId (pk)
>Field: Husband
>Field: Wife
>The MarriageTable.Husband and MarriageTable.Wife fields BOTH have to be
>valid users from the UserTable.
>In SQL Server I'm creating a relationship diagram. I (1) dragged Id from
>UserTable to MarriageTable.Husband and (2) dragged Id from UserTable to
>MarriageTable.Wife. But, the Diagram has trouble saving the Wife
>relationship. Any ideas? Am I doing this correctly?
Hi VB Programmer,
Simple point and click interfaces can be great if you don't know the nitty
gritty details and don't want to know them. However, when dealing with a
complex DBMS such as SQL Server, it's best to do know the nitty gritty
details. You should get used to defining all your tables and constraints
in pure SQL.
CREATE TABLE Users (UserID int NOT NULL,
FullName varchar(60) NOT NULL,
PRIMARY KEY (UserID)
)
CREATE TABLE Marriages (MarriageID int NOT NULL,
Husband int NOT NULL,
Wife int NOT NULL,
PRIMARY KEY (MarriageID),
UNIQUE (Husband, Wife),
FOREIGN KEY (Husband)
REFERENCES Users
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (Wife)
REFERENCES Users
ON DELETE CASCADE
ON UPDATE CASCADE
)
Run this in Query Analyzer and you'll be 100% sure that you didn't
accidentally create a foreign key "in the wrong direction".
Unfortunately, you'll still get an error. This is a limitation in SQL
Server 2000. If you use the cascades option on foreign key constraints,
SQL Server demands that there never be more than one cascading path from
any table to any other table. You'll have to modify at least one of your
foreign key constraints to do ON DELETE NO ACTION ON and ON UPDATE NO
ACTION (the default options). You'll also have to use another solution if
your applications requires cascading behaviour. Possible solutions are to
encapsulate all logic in a stored procedure or to use a trigger.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Question about Querying by "for xml auto" and retriving xml by Str

The following function works fine for 2 years but recently it crashes severa
l
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End FunctionI don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:

> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>sql

Question about Querying by "for xml auto" and retriving xml by Str

The following function works fine for 2 years but recently it crashes several
times
and the error message is "Object reference not set to an instance of an
object."
The SQL query works fine in SQL server 2000. OS is Windows server 2003, and
this
function is used in ASP.Net project.
Can somebody figure it out? Your help is highly appreciated!
Public Function get_cart_number() As String
Dim cmd As New Command()
Dim conn As New Connection()
Dim strmIn As New Stream()
Dim strmOut As New Stream()
Dim SQLxml As String
Dim xml As New XmlDocument()
Dim strTemp As String
' Open a connection to the SQL Server.
conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
database=someDB;")
cmd.ActiveConnection = conn
'Build the command string in the form of an XML template
SQLxml = "<root
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
SQLxml = SQLxml & "select distinct cart_number from Cart for xml auto"
SQLxml = SQLxml & "</sql:query></root>"
' Set the command dialect to XML.
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
' Open the command stream and write our template to it.
strmIn.Open()
strmIn.WriteText(SQLxml)
strmIn.Position = 0
cmd.CommandStream = strmIn
' Execute the command, open the return stream, and read the result.
strmOut.Open()
strmOut.LineSeparator = adCRLF
cmd.Properties("Output Stream").Value = strmOut
cmd.Execute(, , adExecuteStream)
strmOut.Position = 0
xml.LoadXml(strmOut.ReadText)
Dim cart As XmlNode
For Each cart In xml.SelectSingleNode("root").ChildNodes
strTemp = strTemp & "<cart>" & cart.Attributes(0).Value.ToString
& "</cart>"
Next
strmIn.Close()
strmOut.Close()
Return (strTemp)
End Function
I don't know what line that this is failing on, but if I had to guess, I
think it would be in this area:

> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString & "</cart>"
> Next
You probably have some NULL values and therefore aren't bringing back the
attribute value that you are trying to retrive here:
"cart.Attributes(0).Value". I would check the results of the query first.
Jay Nathan
http://www.jaynathan.com/blog
"Ruopian" <Ruopian@.discussions.microsoft.com> wrote in message
news:35A167A6-1ECB-485D-A916-2B1A7690DA4C@.microsoft.com...
> The following function works fine for 2 years but recently it crashes
several
> times
> and the error message is "Object reference not set to an instance of an
> object."
> The SQL query works fine in SQL server 2000. OS is Windows server 2003,
and
> this
> function is used in ASP.Net project.
> Can somebody figure it out? Your help is highly appreciated!
> Public Function get_cart_number() As String
> Dim cmd As New Command()
> Dim conn As New Connection()
> Dim strmIn As New Stream()
> Dim strmOut As New Stream()
> Dim SQLxml As String
> Dim xml As New XmlDocument()
> Dim strTemp As String
> ' Open a connection to the SQL Server.
> conn.Open("Provider=SQLOLEDB; server=someServer; uid=uid; pwd=pwd;
> database=someDB;")
> cmd.ActiveConnection = conn
> 'Build the command string in the form of an XML template
> SQLxml = "<root
> xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>"
> SQLxml = SQLxml & "select distinct cart_number from Cart for xml
auto"
> SQLxml = SQLxml & "</sql:query></root>"
> ' Set the command dialect to XML.
> cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
> ' Open the command stream and write our template to it.
> strmIn.Open()
> strmIn.WriteText(SQLxml)
> strmIn.Position = 0
> cmd.CommandStream = strmIn
> ' Execute the command, open the return stream, and read the
result.
> strmOut.Open()
> strmOut.LineSeparator = adCRLF
> cmd.Properties("Output Stream").Value = strmOut
> cmd.Execute(, , adExecuteStream)
> strmOut.Position = 0
> xml.LoadXml(strmOut.ReadText)
> Dim cart As XmlNode
> For Each cart In xml.SelectSingleNode("root").ChildNodes
> strTemp = strTemp & "<cart>" &
cart.Attributes(0).Value.ToString
> & "</cart>"
> Next
> strmIn.Close()
> strmOut.Close()
> Return (strTemp)
> End Function
>
>

Question about EXECUTE AS USER

Can anyone tell me why I'll receive a error via following steps? Thanks in advance!

1. Create a database “TESTDB” and a table “Table1”

2. Create a login “TestLogin”, which is db_owner roles of both msdb and TESTDB

3. Create a DML trigger for Table1 by following script

CREATE TRIGGER TRG1

ON Table1

FOR INSERT, UPDATE, DELETE

AS

SELECT * FROM msdb..sysjobs

GO

4. Open SSMS, login as TestLogin, execute following statement

SELECT * FROM msdb..sysjobs

It will succeed to select data in msdb..sysjobs

5. Open another SSMS, login as sa, execute following statement

use TEST

execute as user='TestLogin'

select * From msdb..sysjobs

I receive an error about permission to select data from sysjobs, why?

Add the EXECUTE AS to the end of the Query.

SELECT *

FROM msdb..sysjobs

EXECUTE AS user='TestLogin'

|||

The reason why “SELECT * FROM msdb..sysjobs”fails under an impersonated context (EXECUTE AS USER) is because the impersonation mechanism you are calling is (by default) bound only to the current database (TESTDB), but you are trying to access data from a different DB (msdb).

As Arnie suggested, one potential solution may be use the current execution context to gether the information from msdb, and after that impersonate, but it would really depend on what you are trying to accomplish on this task.

I would recommend the following topics from BOL:

· Understanding Context Switching (http://msdn2.microsoft.com/en-us/library/ms191296.aspx)

· Extending Database Impersonation by Using EXECUTE AS (http://msdn2.microsoft.com/en-us/library/ms188304.aspx )

My guess s that the trigger you are trying to create is intended to have a controlled escalation of the privileges of the invoker in order to gather information from msdb and accomplish some task, correct?

If this is the case, I would suggest evaluating using digital signatures for this task. I have an example in my blog that probably may help you to get started (not exactly the same scenario, but I hope it will be useful):
http://blogs.msdn.com/raulga/archive/2006/10/30/using-a-digital-signature-as-a-secondary-identity-to-replace-cross-database-ownership-chaining.aspx

If you have further questions, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Question about error handler

In store procedure, when a statement has error and Severity is high, so eror
is occured and stop the stored procedure and exit the sp. So I cannot use
@.@.error to catch the error. Is there any way that I can proceed the sp and
don't let it stoped so that I can use @.@.error to catch the error and make th
e
sp much nicely. Thanks.I am afraid that the answer is "no". You will have to capture the error at
the client side. See if this helps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Iter" wrote:

> In store procedure, when a statement has error and Severity is high, so er
or
> is occured and stop the stored procedure and exit the sp. So I cannot use
> @.@.error to catch the error. Is there any way that I can proceed the sp and
> don't let it stoped so that I can use @.@.error to catch the error and make
the
> sp much nicely. Thanks.
>

Question about by Import oracle data and field is null

Hi, I've a question about importing Oracle data and some fields are null. I get an error 'Conversion failed because the data value overflowed the specified type'. When i look in preview query result, via OLE db Source editor > Preview, this field contains '<value too big to display>'.

What do i do wrong? Can somebody help me?

Thanks in advance

Olaf

It may be helpful to have a few more details. Which version of SQL Server and Oracle are you using? And which method and tool are you using to import data?|||

Hello Buck,

I work with SQL server 2005 and Oracle 7.

Olaf

|||Olaf - SQL Server has several methods to export and import data, as does Oracle. For most data cleansing issues, the simplest way to eliminate data format differences is to export the data from Oracle to a text file and then import that to SQL Server. If you're looking for something that is simpler, such as using SQL Server Integration Services (SSIS) then you'll need to check the data types on both systems to ensure that they match up. You can also "push" the data from Oracle using their export tools. Whether you pull the data from Oracle using SSIS or push it from Oracle, both servers need to have the latest client drivers for each other.|||Hi Buck,
What is the problem. I have a made a SSIS, in VS2005, with an ole db source, settings to the oracle db, and an ole db destination, settings to sql 2005 db. When i look in preview

query result, via OLE db Source editor > Preview, afield

contains '<value too big to display>'. This field is a datetime oracle field and it's empty. How do i check if it's empty? And what do this field then get for an value so that i don't get problem with importing it in to SQL?

Thanks in advance

Olaf
|||

As I mentioned, you'll need the latest drivers on your SQL Server System for Oracle. Also, you can check the data you're about to import using the OPENQUERY statement in SQL Server. Look in Books Online for more information on how to use that statement.

question about "System.Data.SqlClient.SqlException"

I'm trying to retrieve an image from my ms sql server 2005, and i'm using VS2005...however, i have the following error during the compilation process

Code in webform2.aspx.vb:

Partial Class webform2
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class

System.Data.SqlClient.SqlException was unhandled by user code
Class=15
ErrorCode=-2146232060
LineNumber=1
Message="Incorrect syntax near '='."
Number=102
Procedure=""
Server="DCPRJ007\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at webform2.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\WebSites\WebSite7\webform2.aspx.vb:line 10
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Are you sure you are getting value in the Request.QueryString("id") ? If not, your select statement will have an incorrect syntax and so is the exception. keep a break point and debug to find out.

Thanks

|||

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class

i found that...this line is highlighted during debugging, what is problem with this statement?

error message is : Incorrect syntax near '='.

thx a lot!!

|||

Are you sure, you have some value in Request.QueryString("id") ?

Thanks

|||

gaze:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim connstr As String = "Data Source=DCPRJ007\SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True"
Dim cnn As New Data.SqlClient.SqlConnection(connstr)
Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)
cnn.Open()
Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
Dim bindata() As Byte = dr.GetValue(1)
Response.BinaryWrite(bindata)
End Sub
End Class

i found that...this line is highlighted during debugging, what is problem with this statement?

error message is : Incorrect syntax near '='.

thx a lot!!

Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn)

try

Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id='" & Request.QueryString("id") & "'", cnn)

means ... use single cote before and after the your id.

If you think this post helped you marked as read.

Question about "Distribution clean up: distribution" Job

Can the manner in which you remove a publication effect the ability of this
job to run?
I had been experiencing errors with this job. The error message was as
follows:
"Executed as user: <SQLServerAgentDomainAccount>. Could not remove directory
'P:\ReplData\unc\YKCLNSE_CLGWAREHOUSEPROD_STAGINGD AILYADT\20061221142453\'.
Check the security context of xp_cmdshell and close other processes that may
be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed."
I run my SQL Server Agent process with an account that does not have
sysadmin rights. After reading a few other posts with this same error, I
experimented with running the job as a user with sysadmin rights.
Unfortunately, I still had the same error.
Then I went into the job step and copied the command it was attempting to
run. This is the command:
"EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
@.max_distretention = 72"
I pasted this command into a query window and attempted to run it under my
own login, which is a member of the sysadmin role and is also a member of the
Local Administrators group. It still could not run.
The folders the command was trying to delete were used for Publications that
no longer existed. So I went ahead and deleted the folders manually. Now
the command ran successfully.
I am thinking something went wrong during the process of removing the
publications and subscriptions. Anyone have any idea what might have
happened?
Is this SQL 2005? If so you need to enable xp_cmdshell.
If it is SQL 2000 you need to ensure that no one else is accessing this
directory.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:6E882295-B0F5-4FA8-A3ED-731CFB672615@.microsoft.com...
> Can the manner in which you remove a publication effect the ability of
> this
> job to run?
> I had been experiencing errors with this job. The error message was as
> follows:
> "Executed as user: <SQLServerAgentDomainAccount>. Could not remove
> directory
> 'P:\ReplData\unc\YKCLNSE_CLGWAREHOUSEPROD_STAGINGD AILYADT\20061221142453\'.
> Check the security context of xp_cmdshell and close other processes that
> may
> be accessing the directory. [SQLSTATE 42000] (Error 20015). The step
> failed."
> I run my SQL Server Agent process with an account that does not have
> sysadmin rights. After reading a few other posts with this same error, I
> experimented with running the job as a user with sysadmin rights.
> Unfortunately, I still had the same error.
> Then I went into the job step and copied the command it was attempting to
> run. This is the command:
> "EXEC dbo.sp_MSdistribution_cleanup @.min_distretention = 0,
> @.max_distretention = 72"
> I pasted this command into a query window and attempted to run it under my
> own login, which is a member of the sysadmin role and is also a member of
> the
> Local Administrators group. It still could not run.
> The folders the command was trying to delete were used for Publications
> that
> no longer existed. So I went ahead and deleted the folders manually. Now
> the command ran successfully.
> I am thinking something went wrong during the process of removing the
> publications and subscriptions. Anyone have any idea what might have
> happened?
|||It is SQL Server 2005 and xp_cmdshell was already enabled.
"Hilary Cotter" wrote:

> Is this SQL 2005? If so you need to enable xp_cmdshell.
> If it is SQL 2000 you need to ensure that no one else is accessing this
> directory.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:6E882295-B0F5-4FA8-A3ED-731CFB672615@.microsoft.com...
>
>
|||Hi Ken,
I am guessing that the SQLServerAgent service does not have sufficient
rights to remove folders under P:\ReplData. As such, you may want to try
right-clicking the folder in Windows Explorer and grant the SQLServerAgent
service account Full Control rights on it.
-Raymond
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:4F98FEE4-6C0D-4274-A6C4-8384EADB82A9@.microsoft.com...[vbcol=seagreen]
> It is SQL Server 2005 and xp_cmdshell was already enabled.
> "Hilary Cotter" wrote:
|||You're right. Originally, the Agent service account had not been granted
access to the Snapshot folder (P:\ReplData). However, I was still receiving
the error even after I granted the service account "Full Control" over the
share.
"Raymond Mak [MSFT]" wrote:

> Hi Ken,
> I am guessing that the SQLServerAgent service does not have sufficient
> rights to remove folders under P:\ReplData. As such, you may want to try
> right-clicking the folder in Windows Explorer and grant the SQLServerAgent
> service account Full Control rights on it.
> -Raymond
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:4F98FEE4-6C0D-4274-A6C4-8384EADB82A9@.microsoft.com...
>
>
|||I am guessing (again) that the SQL Server Agent service account is not what
xp_cmdshell running under in the cleanup scenario, another suspect will be
the SQL Server service account. In any case, you should be able to find out
exactly which security account is doing the delete using the filemon.exe
tool from sysinternals.com.
"Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
news:6B416C84-EBDE-469A-AA39-0E94DD2F2DE7@.microsoft.com...[vbcol=seagreen]
> You're right. Originally, the Agent service account had not been granted
> access to the Snapshot folder (P:\ReplData). However, I was still
> receiving
> the error even after I granted the service account "Full Control" over the
> share.
> "Raymond Mak [MSFT]" wrote:
|||You were right. I granted the SQL Server Service Account "Full Control" over
the Snapshot Folder and the job has been running successfully ever since.
Thanks for your help.
"Raymond Mak [MSFT]" wrote:

> I am guessing (again) that the SQL Server Agent service account is not what
> xp_cmdshell running under in the cleanup scenario, another suspect will be
> the SQL Server service account. In any case, you should be able to find out
> exactly which security account is doing the delete using the filemon.exe
> tool from sysinternals.com.
> "Ken Powers" <KenPowers@.discussions.microsoft.com> wrote in message
> news:6B416C84-EBDE-469A-AA39-0E94DD2F2DE7@.microsoft.com...
>
>

question

Hi,
I'm getting error in my sql server log.
is it possible to find out a table name in db with this problem
Could not find the index entry for RID '16303130303034333933393938158000200'
in index page (1:19016095), index ID 17, database 'PR...'
thanks
dt
Rather than trying to translate it, I would recommend running DBCC CHECKDB
to determine whether you've got database corruption issues. If CHECKDB
doesn't indicate errors, I would open a case with Microsoft SQL support
(http://support.microsoft.com).
Thanks,
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Darius T." <dta@.takas.lt> wrote in message
news:ePo11aZDFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm getting error in my sql server log.
> is it possible to find out a table name in db with this problem
> Could not find the index entry for RID
> '16303130303034333933393938158000200' in index page (1:19016095), index ID
> 17, database 'PR...'
> thanks
> dt
>

question

I have my sql error log filled with "starting up database
XPTO" but that database is fine (not suspect or
anything).. i thought it would be the users in the
database but the users that have permissions are all ok...
does anyone know why this is happening?
You probably have autoclose for the database turned on.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"cs" <anonymous@.discussions.microsoft.com> wrote in message news:763c01c430e0$31d55a90$a101280a@.phx.gbl...
> I have my sql error log filled with "starting up database
> XPTO" but that database is fine (not suspect or
> anything).. i thought it would be the users in the
> database but the users that have permissions are all ok...
> does anyone know why this is happening?

Question

Does anyone know exactly what this error message means?
"Cannot create a worktable row larger than allowable maximum. Resubmit your
query with the ROBUST PLAN hint."
I get this error when trying to display SQL query results in my browser but
when I run the same query using Query Analyser, I don't get this error.
Is this ODBC related or pure SQL?
Thanks.Just search Books Online for "ROBUST PLAN" and you will find what optimizer hint you need to put in
the query. Basically, the optimizer selected a plan where temp storage resulted in a row size over
page size. The hint tell the optimizer to not do that.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Johann Castueras" <johann.castueras@.bigpond.com> wrote in message
news:%23YJw%23WXiDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Does anyone know exactly what this error message means?
> "Cannot create a worktable row larger than allowable maximum. Resubmit your
> query with the ROBUST PLAN hint."
> I get this error when trying to display SQL query results in my browser but
> when I run the same query using Query Analyser, I don't get this error.
> Is this ODBC related or pure SQL?
> Thanks.
>|||What is the statement you are using ?
ROBUST PLAN -
Forces the query optimizer to attempt a plan that works for the maximum
potential row size, possibly at the expense of performance. When the query
is processed, intermediate tables and operators may need to store and
process rows that are wider than any of the input rows. The rows may be so
wide that, in some cases, the particular operator cannot process the row. If
this happens, SQL Server produces an error during query execution. By using
ROBUST PLAN, you instruct the query optimizer not to consider any query
plans that may encounter this problem.
Also
http://support.microsoft.com/default.aspx?scid=kb;en-us;280138
What version and SP level of SQL Server are you running?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Johann Castueras" <johann.castueras@.bigpond.com> wrote in message
news:%23YJw%23WXiDHA.2320@.TK2MSFTNGP12.phx.gbl...
> Does anyone know exactly what this error message means?
> "Cannot create a worktable row larger than allowable maximum. Resubmit
your
> query with the ROBUST PLAN hint."
> I get this error when trying to display SQL query results in my browser
but
> when I run the same query using Query Analyser, I don't get this error.
> Is this ODBC related or pure SQL?
> Thanks.
>

question

Hi,
I'm getting error in my sql server log.
is it possible to find out a table name in db with this problem
Could not find the index entry for RID '16303130303034333933393938158000200'
in index page (1:19016095), index ID 17, database 'PR...'
thanks
dtRather than trying to translate it, I would recommend running DBCC CHECKDB
to determine whether you've got database corruption issues. If CHECKDB
doesn't indicate errors, I would open a case with Microsoft SQL support
(http://support.microsoft.com).
Thanks,
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Darius T." <dta@.takas.lt> wrote in message
news:ePo11aZDFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm getting error in my sql server log.
> is it possible to find out a table name in db with this problem
> Could not find the index entry for RID
> '16303130303034333933393938158000200' in index page (1:19016095), index ID
> 17, database 'PR...'
> thanks
> dt
>

question

I have my sql error log filled with "starting up database
XPTO" but that database is fine (not suspect or
anything).. i thought it would be the users in the
database but the users that have permissions are all ok...
does anyone know why this is happening?You probably have autoclose for the database turned on.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"cs" <anonymous@.discussions.microsoft.com> wrote in message news:763c01c430e0$31d55a90$a101280a@.phx.gbl...
> I have my sql error log filled with "starting up database
> XPTO" but that database is fine (not suspect or
> anything).. i thought it would be the users in the
> database but the users that have permissions are all ok...
> does anyone know why this is happening?

question

Can anyone tell me why this gives me the following error:
declare @.hstname varchar (50)
set @.hstname = 'XPTO'
Select * from openquery (@.hstname,'select * from
master..sysdatabases')
Error:
Incorrect syntax near "@.hstname"From Books Online:
"OPENQUERY does not accept variables for its arguments."
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
":)" <anonymous@.discussions.microsoft.com> wrote in message news:33cc01c4291b$82c179b0$a301280a@.phx.gbl...
> Can anyone tell me why this gives me the following error:
> declare @.hstname varchar (50)
> set @.hstname = 'XPTO'
> Select * from openquery (@.hstname,'select * from
> master..sysdatabases')
> Error:
> Incorrect syntax near "@.hstname"

question

Hi,
I'm getting error in my sql server log.
is it possible to find out a table name in db with this problem
Could not find the index entry for RID '16303130303034333933393938158000200'
in index page (1:19016095), index ID 17, database 'PR...'
thanks
dtRather than trying to translate it, I would recommend running DBCC CHECKDB
to determine whether you've got database corruption issues. If CHECKDB
doesn't indicate errors, I would open a case with Microsoft SQL support
(http://support.microsoft.com).
Thanks,
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Darius T." <dta@.takas.lt> wrote in message
news:ePo11aZDFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I'm getting error in my sql server log.
> is it possible to find out a table name in db with this problem
> Could not find the index entry for RID
> '16303130303034333933393938158000200' in index page (1:19016095), index ID
> 17, database 'PR...'
> thanks
> dt
>

question

I have my sql error log filled with "starting up database
XPTO" but that database is fine (not suspect or
anything).. i thought it would be the users in the
database but the users that have permissions are all ok...
does anyone know why this is happening?You probably have autoclose for the database turned on.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"cs" <anonymous@.discussions.microsoft.com> wrote in message news:763c01c430e0$31d55a90$a1012
80a@.phx.gbl...
> I have my sql error log filled with "starting up database
> XPTO" but that database is fine (not suspect or
> anything).. i thought it would be the users in the
> database but the users that have permissions are all ok...
> does anyone know why this is happening?