Problem with large transactionfile.

Hello
We have a problem with a large transactionfile.
How can I manage this?
I have tried this command:
dbcc shrinkfile (2, truncateonly)
but I get this errormessage:
Cannot shrink log file 2 (UX2LOG1) because all logical log
files are in use.
Can anyone help.
Thanks
Best regards
Carsten Holm.R5Carsten
Have you tried to BACKUP LOG ? (I assume you don't have database set simple
recovery mode)
Also ,DBCC LOGINFO(dbid) command will show what transactions are running?
(status=2 at the end of the list)
"Carsten Holm" <cah@.r5.com> wrote in message
news:1958b01c44d53$dccf1640$a601280a@.phx.gbl...
> Hello
> We have a problem with a large transactionfile.
> How can I manage this?
> I have tried this command:
> dbcc shrinkfile (2, truncateonly)
> but I get this errormessage:
> Cannot shrink log file 2 (UX2LOG1) because all logical log
> files are in use.
> Can anyone help.
> Thanks
> Best regards
> Carsten Holm.R5|||Hi
Thanks for your answer
I have runned the DBCC LOGINFO(dbid) and it returned many
rows, and all is with status 2, exept this:
2 253952 9903669248 0 0 0
2366000000052200021
It is in the middel of the rows.
We have tried a full backup, but the transactionlog was
not deleted.
Thanks.
Best regards
Carsten Holm
>--Original Message--
>Carsten
>Have you tried to BACKUP LOG ? (I assume you don't have
database set simple
>recovery mode)
>Also ,DBCC LOGINFO(dbid) command will show what
transactions are running?
>(status=2 at the end of the list)
>
>"Carsten Holm" <cah@.r5.com> wrote in message
>news:1958b01c44d53$dccf1640$a601280a@.phx.gbl...
>> Hello
>> We have a problem with a large transactionfile.
>> How can I manage this?
>> I have tried this command:
>> dbcc shrinkfile (2, truncateonly)
>> but I get this errormessage:
>> Cannot shrink log file 2 (UX2LOG1) because all logical
log
>> files are in use.
>> Can anyone help.
>> Thanks
>> Best regards
>> Carsten Holm.R5
>
>.
>|||Hi
Full backup of the database does not truncate log file. I suggest you to
backup log file and then to shrink it or to remove.
If you see transactions with status=2 at the end of the list you will need
to perfom dummy INSERTION to move them at the top.
<anonymous@.discussions.microsoft.com> wrote in message
news:19cca01c44d56$53146bf0$a001280a@.phx.gbl...
> Hi
> Thanks for your answer
> I have runned the DBCC LOGINFO(dbid) and it returned many
> rows, and all is with status 2, exept this:
> 2 253952 9903669248 0 0 0
> 2366000000052200021
> It is in the middel of the rows.
> We have tried a full backup, but the transactionlog was
> not deleted.
> Thanks.
> Best regards
> Carsten Holm
> >--Original Message--
> >Carsten
> >Have you tried to BACKUP LOG ? (I assume you don't have
> database set simple
> >recovery mode)
> >Also ,DBCC LOGINFO(dbid) command will show what
> transactions are running?
> >(status=2 at the end of the list)
> >
> >
> >
> >"Carsten Holm" <cah@.r5.com> wrote in message
> >news:1958b01c44d53$dccf1640$a601280a@.phx.gbl...
> >> Hello
> >> We have a problem with a large transactionfile.
> >> How can I manage this?
> >> I have tried this command:
> >> dbcc shrinkfile (2, truncateonly)
> >> but I get this errormessage:
> >> Cannot shrink log file 2 (UX2LOG1) because all logical
> log
> >> files are in use.
> >> Can anyone help.
> >> Thanks
> >> Best regards
> >> Carsten Holm.R5
> >
> >
> >.
> >

problem with INSERT on ExecuteNonQuery

I'm trying to INSERT a new record into a SQL database using the command object. It works if I use an UPDATE, but every time I try an INSERT, I get "ExecuteNonQuery: Connection property has not been initialized". The query has been checked in enterprise manager direct and works fine. Has anybody got any ideas, please?

 Dim cmdSql As New SqlCommand("INSERT INTO table(blah) VALUES ('blah'), ConUsers")
 ConUsers.Open()
 cmdSql.ExecuteNonQuery()
 ConUsers.Close()

What are you using for your connection/connection string? is it exactly the same as the sub that works for the update statement?|||> is it exactly the same as the sub that works for the update statement?

yes, it is. The only thing that changes is the query itself and I've checked that on the database direct. I've even checked that servername/ASPNET has INSERT permission on that table. So I'm a bit stumped.|||Is that code copied and pasted? If so, you have a misplaced double quote. Should be like this:

 Dim cmdSql As New SqlCommand("INSERT INTO table(blah) VALUES ('blah')", ConUsers)

Terri

problem with insert into in scheduled job pleas help ASAP

Hello MSDN

I am using SQL 2005 and trying to INSTERT data in to a table

When I am using my command from SQL query windows it works fine,

INSERT INTO "tbl.FTPuploads" ("FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME")

SELECT "FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME"

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="G:\DATA\EDItoDB";Extended properties=Text')...uploaded#txt

But when I am trying to put that command in to a scheduled job I get this error

Executed as user: GW\fmnlasa. Incorrect syntax near 'tbl.FTPuploads'. [SQLSTATE 42000] (Error 102).The step failed.

I have changed the command to this, I have removed the quotes from the table name.

INSERT INTO tbl.FTPuploads ("FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME")

SELECT "FTPFile_Names", "FTPGS", "FTPST", "FTPJOB", "FTPDN", "FTPSTATUS", "FTPDATE", "FTPTIME"

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="G:\DATA\EDItoDB";Extended properties=Text')...uploaded#txt

And now I get this error

Executed as user: GW\fmnlasa. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).The step failed.

When you execute from the query, it will use your credential to connect the remote server. But the scheduled query might use the ServiceAccount. Check which service account used in the current context & give the required permission on the remote server. Or change the service account context to NT Credential.|||

Bascially the issues is of Security Context under which the Job run. Check these links

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1012759&SiteID=1

http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/44111/Access-to-the-remote-server-is-denied-because-the-current-security

Madhu

|||

Thank you, for such a quick reply

I was using the same account in Job to as I was using in Query window

I am not sure but I think that the problem was in my table and column names

I have created new table with the name tblFTPuploads the old one was tbl.FTPuploads

After that I got erros for may column names so I have changed the first column name to FTPFileNames the old one was FTPfile_name

And the last step I did was to remove the quotes from the table and column names.

So the end script is like this

INSERT INTO tblFTPuploads (FTPFileNames, FTPGS, FTPST, FTPJOB, FTPDN, FTPSTATUS, FTPDATE, FTPTIME)

SELECT FTPFileNames, FTPGS, FTPST, FTPJOB, FTPDN, FTPSTATUS, FTPDATE, FTPTIME

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="G:\DATA\EDItoDB";Extended properties=Text')...uploaded#txt

And it is working now no problems.

Thanks again for a quick reply

Best regards,

Artavazd ASLANYAN

Network Administrator

problem with insert command

Hi,
i try to insert values into a sql server table like this (code-behind):
comm1 = Textbox1.Text
comm2=Textbox2.Text
......
comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...)
values(comm1, comm2, ...)"
i get the error:
The name "field1l" is not permitted in this context. Valid expressions are
constants, constant expressions, and (in some contexts) variables. Column
names are not permitted.
Can someone give me the right syntax?
Thanks in advance.
Dan
Dan wrote:
> Hi,
> i try to insert values into a sql server table like this (code-behind):
> comm1 = Textbox1.Text
> comm2=Textbox2.Text
> .....
> comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...)
> values(comm1, comm2, ...)"
> i get the error:
> The name "field1l" is not permitted in this context. Valid expressions are
> constants, constant expressions, and (in some contexts) variables. Column
> names are not permitted.
> Can someone give me the right syntax?
> Thanks in advance.
> Dan
The database is not aware of the variables that you use in your code.
Put parameters in the query, and add the values as parameter objects to
the command object.
Gran Andersson
_____
http://www.guffa.com
|||"Dan" <d@.er.df> wrote in message
news:eF%23nAdXwHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Hi,
> i try to insert values into a sql server table like this (code-behind):
> comm1 = Textbox1.Text
> comm2=Textbox2.Text
> .....
> comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...)
> values(comm1, comm2, ...)"
> i get the error:
> The name "field1l" is not permitted in this context. Valid expressions are
> constants, constant expressions, and (in some contexts) variables. Column
> names are not permitted.
> Can someone give me the right syntax?
> Thanks in advance.
> Dan
>
>
Your SQL statement is in quotes so the comm1, comm2 are sent as those values
to the server which has no idea what you want to do.
Use parameters to pass the values of the fields. Something like "insert
into mytable (field1,field2) values (@.field1, @.field2)
Then in your code create the parameters, add them to the SQLCommand and then
execute.
Hope this gets you going in the right direction.
Lloyd Sheen
|||On Sun, 8 Jul 2007 17:47:11 +0200, "Dan" <d@.er.df> wrote:

>Hi,
>i try to insert values into a sql server table like this (code-behind):
>comm1 = Textbox1.Text
>comm2=Textbox2.Text
>.....
>comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...)
>values(comm1, comm2, ...)"
>i get the error:
>The name "field1l" is not permitted in this context. Valid expressions are
>constants, constant expressions, and (in some contexts) variables. Column
>names are not permitted.
>Can someone give me the right syntax?
>Thanks in advance.
>Dan
>
You need to read up on parameterized queries ... then you'll know how
to safely pass user entered variables to database queries
http://bytes.thinkersroom.com
|||Of course, thanks ..
"Lloyd Sheen" <a@.b.c> schreef in bericht
news:DADD47DE-3006-4C63-8087-F368EFA9EFFF@.microsoft.com...
> "Dan" <d@.er.df> wrote in message
> news:eF%23nAdXwHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Your SQL statement is in quotes so the comm1, comm2 are sent as those
> values to the server which has no idea what you want to do.
> Use parameters to pass the values of the fields. Something like "insert
> into mytable (field1,field2) values (@.field1, @.field2)
> Then in your code create the parameters, add them to the SQLCommand and
> then execute.
> Hope this gets you going in the right direction.
> Lloyd Sheen

problem with insert command

Hi,
i try to insert values into a sql server table like this (code-behind):
comm1 = Textbox1.Text
comm2=Textbox2.Text
.....
comd.CommandText = "insert into [dbo].[mytable] (field1, field2,...
)
values(comm1, comm2, ...)"
i get the error:
The name "field1l" is not permitted in this context. Valid expressions are
constants, constant expressions, and (in some contexts) variables. Column
names are not permitted.
Can someone give me the right syntax?
Thanks in advance.
DanDan wrote:
> Hi,
> i try to insert values into a sql server table like this (code-behind):
> comm1 = Textbox1.Text
> comm2=Textbox2.Text
> .....
> comd.CommandText = "insert into [dbo].[mytable] (field1, field2,.
.)
> values(comm1, comm2, ...)"
> i get the error:
> The name "field1l" is not permitted in this context. Valid expressions are
> constants, constant expressions, and (in some contexts) variables. Column
> names are not permitted.
> Can someone give me the right syntax?
> Thanks in advance.
> Dan
The database is not aware of the variables that you use in your code.
Put parameters in the query, and add the values as parameter objects to
the command object.
Gran Andersson
_____
http://www.guffa.com|||"Dan" <d@.er.df> wrote in message
news:eF%23nAdXwHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Hi,
> i try to insert values into a sql server table like this (code-behind):
> comm1 = Textbox1.Text
> comm2=Textbox2.Text
> .....
> comd.CommandText = "insert into [dbo].[mytable] (field1, field2,.
.)
> values(comm1, comm2, ...)"
> i get the error:
> The name "field1l" is not permitted in this context. Valid expressions are
> constants, constant expressions, and (in some contexts) variables. Column
> names are not permitted.
> Can someone give me the right syntax?
> Thanks in advance.
> Dan
>
>
Your SQL statement is in quotes so the comm1, comm2 are sent as those values
to the server which has no idea what you want to do.
Use parameters to pass the values of the fields. Something like "insert
into mytable (field1,field2) values (@.field1, @.field2)
Then in your code create the parameters, add them to the SQLCommand and then
execute.
Hope this gets you going in the right direction.
Lloyd Sheen|||On Sun, 8 Jul 2007 17:47:11 +0200, "Dan" <d@.er.df> wrote:

>Hi,
>i try to insert values into a sql server table like this (code-behind):
>comm1 = Textbox1.Text
>comm2=Textbox2.Text
>.....
>comd.CommandText = "insert into [dbo].[mytable] (field1, field2,..
.)
>values(comm1, comm2, ...)"
>i get the error:
>The name "field1l" is not permitted in this context. Valid expressions are
>constants, constant expressions, and (in some contexts) variables. Column
>names are not permitted.
>Can someone give me the right syntax?
>Thanks in advance.
>Dan
>
You need to read up on parameterized queries ... then you'll know how
to safely pass user entered variables to database queries
http://bytes.thinkersroom.com|||Of course, thanks ..
"Lloyd Sheen" <a@.b.c> schreef in bericht
news:DADD47DE-3006-4C63-8087-F368EFA9EFFF@.microsoft.com...
> "Dan" <d@.er.df> wrote in message
> news:eF%23nAdXwHHA.4736@.TK2MSFTNGP05.phx.gbl...
> Your SQL statement is in quotes so the comm1, comm2 are sent as those
> values to the server which has no idea what you want to do.
> Use parameters to pass the values of the fields. Something like "insert
> into mytable (field1,field2) values (@.field1, @.field2)
> Then in your code create the parameters, add them to the SQLCommand and
> then execute.
> Hope this gets you going in the right direction.
> Lloyd Sheensql

problem with executing an sqlcommand

when i push a button my datagrid doesn't show up. please help this is my code. I only try to execute one of the sql command and when i push the button it just redirects me to my homepage.

Private Sub btnbid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbid.Click

Dim conn As New SqlConnection((Application("SQL_Connection_String")))

conn.Open()

Dim cmdbid As New SqlCommand("insert into Bids (CustID,ItemCode,BidAmount) values (@.CustID,@.ItemCode,@.BidAmount)", conn)

cmdbid.Parameters.Add("@.CustID", SqlDbType.Int, 4)
cmdbid.Parameters("@.CustID").Value = Session("CustID")
cmdbid.Parameters.Add("@.ItemCode", SqlDbType.Int, 4)
cmdbid.Parameters("@.ItemCode").Value = CInt(Request.QueryString("Id"))
cmdbid.Parameters.Add("@.BidAmount", SqlDbType.Decimal, 9)
cmdbid.Parameters("@.BidAmount").Value = CDec(txtbidamount.Text)

Dim cmdhighbid As New SqlCommand("update items set Highestbid=@.BidAmount,HighestBidder=@.bidder where ItemCode=@.ItemCode and Highestbid<@.BidAmount", conn)

cmdhighbid.Parameters.Add("@.BidAmount", SqlDbType.Decimal, 9)
cmdhighbid.Parameters("@.BidAmount").Value = CDec(txtbidamount.Text)
cmdhighbid.Parameters.Add("@.bidder", SqlDbType.Int, 4)
cmdhighbid.Parameters("@.bidder").Value = Session("CustID")
cmdhighbid.Parameters.Add("@.ItemCode", SqlDbType.Int, 4)
cmdhighbid.Parameters("@.ItemCode").Value = Request.QueryString("Id")

Try
cmdbid.ExecuteNonQuery()

lblbidstatus.Text = "Bid Inserted Successfully!! Good Luck!!!"
Catch ex As Exception

lblbidstatus.Text = ex.Message
End Try

btnbid.Enabled = False
conn.Close()
End Subare you binding the datagrid within if not ispostback loop in page_load.. ?

hth|||i don't have anything in the page load only the bindgrid() proc.|||because you are prbly losing the datagrid during postbck. try this

sub page_load(...)
if not ispostback() then
 bindgrid()
end if
end sub

hth|||no that's not the problem. when i push the button it redirects me to the homepage with no reason and does not do any changes i do with the sql command|||i dont see anything in your code to redirect to another page. are you sure you are calling the right event ?

Problem with EXEC command

I am try to populate a table based on a variable (it holds the table name).
I have managed to create the table using the EXEC command but am having
problems populating it with the following command. I receive "Error 156:
Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a solution
.
Thanks.
BEGIN
EXEC ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
CONVERT(DATETIME, @.EndDate, 102) + ')')
END
However the following works fine into a regular table.
BEGIN
INSERT INTO SubsList (SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = @.PubCode) AND (DateEntered >= CONVERT(DATETIME,
@.StartDate, 102)) AND (DateEntered <= CONVERT(DATETIME, @.EndDate, 102))
ENDHi pete
try this:
BEGIN
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
CONVERT(DATETIME, @.EndDate, 102) + ')')
END
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:

> I am try to populate a table based on a variable (it holds the table name)
.
> I have managed to create the table using the EXEC command but am having
> problems populating it with the following command. I receive "Error 156:
> Incorrect syntax near the keyword 'CONVERT'". Could anyone offer a soluti
on.
> Thanks.
> BEGIN
> EXEC ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
> CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
> CONVERT(DATETIME, @.EndDate, 102) + ')')
> END
>
> However the following works fine into a regular table.
> BEGIN
> INSERT INTO SubsList (SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = @.PubCode) AND (DateEntered >= CONVERT(DATETIME,
> @.StartDate, 102)) AND (DateEntered <= CONVERT(DATETIME, @.EndDate, 102))
> END|||Thanks Chandra but still no joy.
Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
"Chandra" wrote:
> Hi pete
> try this:
> BEGIN
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' +
> CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
> CONVERT(DATETIME, @.EndDate, 102) + ')')
> END
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||Hi pete
try now:
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
CONVERT(DATETIME,'+ @.EndDate +', 102)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Thanks Chandra but still no joy.
> Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
>
> "Chandra" wrote:
>|||Hi pete
just observed and found that, "(" was missing before "DateEntered"
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Thanks Chandra but still no joy.
> Now getting "Error 170: Line 125: Incorrect syntax near 'INSERT INTO'"
>
> "Chandra" wrote:
>|||'Fraid not. Same error.
"Chandra" wrote:
> Hi pete
> try now:
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
> CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
> CONVERT(DATETIME,'+ @.EndDate +', 102)
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||my mistake:
EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
CONVERT(DATETIME,'+ @.EndDate +', 102)'
i missed a ' at the end.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> 'Fraid not. Same error.
> "Chandra" wrote:
>|||Nope :-(
"Chandra" wrote:
> Noe then,
> this should work
> EXECUTE sp_executesql ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >=
> CONVERT(DATETIME, ' + @.StartDate + ', 102) AND DateEntered <=
> CONVERT(DATETIME,'+ @.EndDate +', 102)')
> too many quotes and brackets, these are confusing..
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Pete" wrote:
>|||Why don't you try printing your query instead of executing it?
Then you'll see the error yourself. With what you're doing
now, you make a change, ask the parse "is this right yet?", and
listen to the parser's particular complaint, over and over.
If you've never looked at the query yourself, are you going to
assume it's correct once the parser stops complaining about it?
Once you get it right, then you can execute it instead of print
it.
Steve Kass
Drew University
Pete wrote:
>Getting closer..
>The following gives "Syntax error converting datetime from character string
"
>SET @.SqlString = N'INSERT INTO ' + @.NewSubsList + ' (SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND (DateEntered >= ' +
>CONVERT(DATETIME, @.StartDate, 102) + ') AND (DateEntered <= ' +
>CONVERT(DATETIME, @.EndDate, 102) + ')'
>EXECUTE sp_executesql @.SqlString
>
>"Pete" wrote:
>
>|||hi
or do one thing,
first create the string that you want to execute.
just see if it clear in the syntax. and then pass it to sp_executesql
does this make sence.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Pete" wrote:
> Nope :-(
> "Chandra" wrote:
>

Problem with dropping tables when set showplan_all is on

I used the set showplan_all on command to get some statistics; I can't use a server trace and I don't like the graphical thingummy.

I ran a sproc that contained three drop table commands; SQL Server refused to drop those tables when set showplan_all was on. I turned it off and it dropped them fine; why can't I drop tables with set showplan_all on?I used the set showplan_all on command to get some statistics; I can't use a server trace and I don't like the graphical thingummy.

I ran a sproc that contained three drop table commands; SQL Server refused to drop those tables when set showplan_all was on. I turned it off and it dropped them fine; why can't I drop tables with set showplan_all on?


From BOL,

The setting of SET SHOWPLAN_ALL is set at execute or run time and not at parse time.
When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it, and Transact-SQL statements are not executed. After this option is set ON, information about all subsequent Transact-SQL statements are returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_ALL is ON, SQL Server returns an error message from a subsequent SELECT statement involving that same table; the specified table does not exist. Therefore, subsequent references to this table fail. When SET SHOWPLAN_ALL is OFF, SQL Server executes the statements without generating a report.|||From BOL,

The setting of SET SHOWPLAN_ALL is set at execute or run time and not at parse time.
When SET SHOWPLAN_ALL is ON, SQL Server returns execution information for each statement without executing it, and Transact-SQL statements are not executed. After this option is set ON, information about all subsequent Transact-SQL statements are returned until the option is set OFF. For example, if a CREATE TABLE statement is executed while SET SHOWPLAN_ALL is ON, SQL Server returns an error message from a subsequent SELECT statement involving that same table; the specified table does not exist. Therefore, subsequent references to this table fail. When SET SHOWPLAN_ALL is OFF, SQL Server executes the statements without generating a report.

Thanks; the DBAs figured it out. It didn't say anything about that in Books Online so I was completely stumped. :cool:|||Thanks; the DBAs figured it out. It didn't say anything about that in Books Online so I was completely stumped. :cool:

What?:shocked: I think mallier very rightly quoted the segment that provides the answer of your problem.
I suggest you to go through it again...:cool:

Problem with Delete On Linked Server

I am getting time-out errors when I try to perform a simple delete on a
linked server. The command is:

Delete From MyTable Where PKID=12345

I have tried executing this command directly from Query Analyzer, but it
just times out. However, when I run it from QA against the server itself
(rather than from my local server against a linked server), it executes
immediately. Similarly, if I run the same SQL command through an ODBC linked
table in an Access 2000 MDB file (tweaking the syntax slightly), it also
executes immediately. Only if I run it from SQL 7 as a stored procedure or a
QA command against a linked server does it time-out.

I have no problems inserting rows using any of the three methods noted
above. It is only with deleting rows through the linked server that it times
out.

Thanks for any assistance.

NeilCorrection: The syntax used against the linked server is the four-art
syntax:

Delete From svr.db.dbo.MyTable Where PKID=12345

Also, the same problem occurs with updates as with deletes (but not
inserts).

Thanks,

Neil

"Neil" <nospam@.nospam.net> wrote in message
news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthli nk.net...
>I am getting time-out errors when I try to perform a simple delete on a
>linked server. The command is:
> Delete From MyTable Where PKID=12345
> I have tried executing this command directly from Query Analyzer, but it
> just times out. However, when I run it from QA against the server itself
> (rather than from my local server against a linked server), it executes
> immediately. Similarly, if I run the same SQL command through an ODBC
> linked table in an Access 2000 MDB file (tweaking the syntax slightly), it
> also executes immediately. Only if I run it from SQL 7 as a stored
> procedure or a QA command against a linked server does it time-out.
> I have no problems inserting rows using any of the three methods noted
> above. It is only with deleting rows through the linked server that it
> times out.
> Thanks for any assistance.
> Neil|||I ran a profiler trace while inserting and then deleting a row from the
table on the linked server. I added all events to the trace. However, for
both the insert and delete, it only showed a single SQL:BatchCompleted
event -- except that after the line for the delete command, there was a
second, blank, line in the trace. The two lines for the delete didn't show
in the profiler until the batch was canceled. Below are the lines from
Profiler. Any assistance is appreciated.

Thanks,

Neil

Event Class Text Application Name NT User Name SQL User Name CPU Reads
Writes Duration Connection ID SPID Start Time
+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
02:23:56.827

"Neil" <nospam@.nospam.net> wrote in message
news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthli nk.net...
>I am getting time-out errors when I try to perform a simple delete on a
>linked server. The command is:
> Delete From MyTable Where PKID=12345
> I have tried executing this command directly from Query Analyzer, but it
> just times out. However, when I run it from QA against the server itself
> (rather than from my local server against a linked server), it executes
> immediately. Similarly, if I run the same SQL command through an ODBC
> linked table in an Access 2000 MDB file (tweaking the syntax slightly), it
> also executes immediately. Only if I run it from SQL 7 as a stored
> procedure or a QA command against a linked server does it time-out.
> I have no problems inserting rows using any of the three methods noted
> above. It is only with deleting rows through the linked server that it
> times out.
> Thanks for any assistance.
> Neil|||Which box were you running the trace against? Try running it
against the destination (the linked server) instead of the
server that you are executing the command from.
Another thing to see if it works - try executing the
statement using OpenQuery instead of the 4 part name and see
if that makes a difference.

-Sue

On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@.nospam.net>
wrote:

>I ran a profiler trace while inserting and then deleting a row from the
>table on the linked server. I added all events to the trace. However, for
>both the insert and delete, it only showed a single SQL:BatchCompleted
>event -- except that after the line for the delete command, there was a
>second, blank, line in the trace. The two lines for the delete didn't show
>in the profiler until the batch was canceled. Below are the lines from
>Profiler. Any assistance is appreciated.
>Thanks,
>Neil
>Event Class Text Application Name NT User Name SQL User Name CPU Reads
>Writes Duration Connection ID SPID Start Time
>+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
>(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
>+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
>MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
> SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
>02:23:56.827
>
>"Neil" <nospam@.nospam.net> wrote in message
>news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthli nk.net...
>>I am getting time-out errors when I try to perform a simple delete on a
>>linked server. The command is:
>>
>> Delete From MyTable Where PKID=12345
>>
>> I have tried executing this command directly from Query Analyzer, but it
>> just times out. However, when I run it from QA against the server itself
>> (rather than from my local server against a linked server), it executes
>> immediately. Similarly, if I run the same SQL command through an ODBC
>> linked table in an Access 2000 MDB file (tweaking the syntax slightly), it
>> also executes immediately. Only if I run it from SQL 7 as a stored
>> procedure or a QA command against a linked server does it time-out.
>>
>> I have no problems inserting rows using any of the three methods noted
>> above. It is only with deleting rows through the linked server that it
>> times out.
>>
>> Thanks for any assistance.
>>
>> Neil
>|||Couldn't run a trace against the linked server, as I don't have permissions
to execute the trace sp. Sent a note to the web admin to give me
permissions.

In the meantime, I used OpenQuery as you suggested. This time at least I got
an error message (yea!), though somewhat cryptic:

"Could not process object 'Delete From images Where ImageID=99986'. The
OLE DB provider 'SQLOLEDB' indicates that the object has no columns."

Any ideas about what that means?

Thanks!

Neil

"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bh9m62hksvsaa8fdoob0sphsd32jgqc36h@.4ax.com...
> Which box were you running the trace against? Try running it
> against the destination (the linked server) instead of the
> server that you are executing the command from.
> Another thing to see if it works - try executing the
> statement using OpenQuery instead of the 4 part name and see
> if that makes a difference.
> -Sue
> On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@.nospam.net>
> wrote:
>>I ran a profiler trace while inserting and then deleting a row from the
>>table on the linked server. I added all events to the trace. However, for
>>both the insert and delete, it only showed a single SQL:BatchCompleted
>>event -- except that after the line for the delete command, there was a
>>second, blank, line in the trace. The two lines for the delete didn't show
>>in the profiler until the batch was canceled. Below are the lines from
>>Profiler. Any assistance is appreciated.
>>
>>Thanks,
>>
>>Neil
>>
>>Event Class Text Application Name NT User Name SQL User Name CPU Reads
>>Writes Duration Connection ID SPID Start Time
>>+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
>>(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
>>+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
>>MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
>> SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
>>02:23:56.827
>>
>>
>>
>>"Neil" <nospam@.nospam.net> wrote in message
>>news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthli nk.net...
>>>I am getting time-out errors when I try to perform a simple delete on a
>>>linked server. The command is:
>>>
>>> Delete From MyTable Where PKID=12345
>>>
>>> I have tried executing this command directly from Query Analyzer, but it
>>> just times out. However, when I run it from QA against the server itself
>>> (rather than from my local server against a linked server), it executes
>>> immediately. Similarly, if I run the same SQL command through an ODBC
>>> linked table in an Access 2000 MDB file (tweaking the syntax slightly),
>>> it
>>> also executes immediately. Only if I run it from SQL 7 as a stored
>>> procedure or a QA command against a linked server does it time-out.
>>>
>>> I have no problems inserting rows using any of the three methods noted
>>> above. It is only with deleting rows through the linked server that it
>>> times out.
>>>
>>> Thanks for any assistance.
>>>
>>> Neil
>>>
>|||OK, I found out that the error I got originally (per other message) was due
to the fact that Delete doesn't return rows, as OpenQuery is looking for.
So, per http://support.microsoft.com/defaul...b;en-us;Q270119, I
changed it to:

Delete OPENQUERY(abcweb, 'Select ImageID From images Where
ImageID=99987')

and it worked! Would still be good to find out why the original method
wouldn't work for deletes or updates, but did work for inserts. But at least
this works.

Thanks!

Neil

"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bh9m62hksvsaa8fdoob0sphsd32jgqc36h@.4ax.com...
> Which box were you running the trace against? Try running it
> against the destination (the linked server) instead of the
> server that you are executing the command from.
> Another thing to see if it works - try executing the
> statement using OpenQuery instead of the 4 part name and see
> if that makes a difference.
> -Sue
> On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@.nospam.net>
> wrote:
>>I ran a profiler trace while inserting and then deleting a row from the
>>table on the linked server. I added all events to the trace. However, for
>>both the insert and delete, it only showed a single SQL:BatchCompleted
>>event -- except that after the line for the delete command, there was a
>>second, blank, line in the trace. The two lines for the delete didn't show
>>in the profiler until the batch was canceled. Below are the lines from
>>Profiler. Any assistance is appreciated.
>>
>>Thanks,
>>
>>Neil
>>
>>Event Class Text Application Name NT User Name SQL User Name CPU Reads
>>Writes Duration Connection ID SPID Start Time
>>+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
>>(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
>>+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
>>MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
>> SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
>>02:23:56.827
>>
>>
>>
>>"Neil" <nospam@.nospam.net> wrote in message
>>news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthli nk.net...
>>>I am getting time-out errors when I try to perform a simple delete on a
>>>linked server. The command is:
>>>
>>> Delete From MyTable Where PKID=12345
>>>
>>> I have tried executing this command directly from Query Analyzer, but it
>>> just times out. However, when I run it from QA against the server itself
>>> (rather than from my local server against a linked server), it executes
>>> immediately. Similarly, if I run the same SQL command through an ODBC
>>> linked table in an Access 2000 MDB file (tweaking the syntax slightly),
>>> it
>>> also executes immediately. Only if I run it from SQL 7 as a stored
>>> procedure or a QA command against a linked server does it time-out.
>>>
>>> I have no problems inserting rows using any of the three methods noted
>>> above. It is only with deleting rows through the linked server that it
>>> times out.
>>>
>>> Thanks for any assistance.
>>>
>>> Neil
>>>
>

Problem with Delete On Linked Server

I am getting time-out errors when I try to perform a simple delete on a
linked server. The command is:
Delete From MyTable Where PKID=12345
I have tried executing this command directly from Query Analyzer, but it
just times out. However, when I run it from QA against the server itself
(rather than from my local server against a linked server), it executes
immediately. Similarly, if I run the same SQL command through an ODBC linked
table in an Access 2000 MDB file (tweaking the syntax slightly), it also
executes immediately. Only if I run it from SQL 7 as a stored procedure or a
QA command against a linked server does it time-out.
I have no problems inserting rows using any of the three methods noted
above. It is only with deleting rows through the linked server that it times
out.
Thanks for any assistance.
NeilCorrection: The syntax used against the linked server is the four-art
syntax:
Delete From svr.db.dbo.MyTable Where PKID=12345
Also, the same problem occurs with updates as with deletes (but not
inserts).
Thanks,
Neil
"Neil" <nospam@.nospam.net> wrote in message
news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthlink.net...
>I am getting time-out errors when I try to perform a simple delete on a
>linked server. The command is:
> Delete From MyTable Where PKID=12345
> I have tried executing this command directly from Query Analyzer, but it
> just times out. However, when I run it from QA against the server itself
> (rather than from my local server against a linked server), it executes
> immediately. Similarly, if I run the same SQL command through an ODBC
> linked table in an Access 2000 MDB file (tweaking the syntax slightly), it
> also executes immediately. Only if I run it from SQL 7 as a stored
> procedure or a QA command against a linked server does it time-out.
> I have no problems inserting rows using any of the three methods noted
> above. It is only with deleting rows through the linked server that it
> times out.
> Thanks for any assistance.
> Neil
>|||I ran a profiler trace while inserting and then deleting a row from the
table on the linked server. I added all events to the trace. However, for
both the insert and delete, it only showed a single SQL:BatchCompleted
event -- except that after the line for the delete command, there was a
second, blank, line in the trace. The two lines for the delete didn't show
in the profiler until the batch was canceled. Below are the lines from
Profiler. Any assistance is appreciated.
Thanks,
Neil
Event Class Text Application Name NT User Name SQL User Name CPU Reads
Writes Duration Connection ID SPID Start Time
+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
02:23:56.827
"Neil" <nospam@.nospam.net> wrote in message
news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthlink.net...
>I am getting time-out errors when I try to perform a simple delete on a
>linked server. The command is:
> Delete From MyTable Where PKID=12345
> I have tried executing this command directly from Query Analyzer, but it
> just times out. However, when I run it from QA against the server itself
> (rather than from my local server against a linked server), it executes
> immediately. Similarly, if I run the same SQL command through an ODBC
> linked table in an Access 2000 MDB file (tweaking the syntax slightly), it
> also executes immediately. Only if I run it from SQL 7 as a stored
> procedure or a QA command against a linked server does it time-out.
> I have no problems inserting rows using any of the three methods noted
> above. It is only with deleting rows through the linked server that it
> times out.
> Thanks for any assistance.
> Neil
>|||Which box were you running the trace against? Try running it
against the destination (the linked server) instead of the
server that you are executing the command from.
Another thing to see if it works - try executing the
statement using OpenQuery instead of the 4 part name and see
if that makes a difference.
-Sue
On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@.nospam.net>
wrote:

>I ran a profiler trace while inserting and then deleting a row from the
>table on the linked server. I added all events to the trace. However, for
>both the insert and delete, it only showed a single SQL:BatchCompleted
>event -- except that after the line for the delete command, there was a
>second, blank, line in the trace. The two lines for the delete didn't show
>in the profiler until the batch was canceled. Below are the lines from
>Profiler. Any assistance is appreciated.
>Thanks,
>Neil
>Event Class Text Application Name NT User Name SQL User Name CPU Reads
>Writes Duration Connection ID SPID Start Time
>+SQL:BatchCompleted insert into abcweb.abc.dbo.images (ImageID) values
>(99986) MS SQL Query Analyzer neil sa 0 13 0 640 5264 12 02:23:31.450
>+SQL:BatchCompleted Delete From abcweb.abc.dbo.images Where ImageID=99986
>MS SQL Query Analyzer neil sa 0 20 1 14440 5264 12 02:23:42.387
> SQL:BatchCompleted MS SQL Query Analyzer neil sa 0 0 0 0 5264 12
>02:23:56.827
>
>"Neil" <nospam@.nospam.net> wrote in message
>news:_Jqag.5464$u4.5402@.newsread1.news.pas.earthlink.net...
>|||Couldn't run a trace against the linked server, as I don't have permissions
to execute the trace sp. Sent a note to the web admin to give me
permissions.
In the meantime, I used OpenQuery as you suggested. This time at least I got
an error message (yea!), though somewhat cryptic:
"Could not process object 'Delete From images Where ImageID=99986'. The
OLE DB provider 'SQLOLEDB' indicates that the object has no columns."
Any ideas about what that means?
Thanks!
Neil
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bh9m62hksvsaa8fdoob0sphsd32jgqc36h@.
4ax.com...
> Which box were you running the trace against? Try running it
> against the destination (the linked server) instead of the
> server that you are executing the command from.
> Another thing to see if it works - try executing the
> statement using OpenQuery instead of the 4 part name and see
> if that makes a difference.
> -Sue
> On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@.nospam.net>
> wrote:
>
>|||OK, I found out that the error I got originally (per other message) was due
to the fact that Delete doesn't return rows, as OpenQuery is looking for.
So, per http://support.microsoft.com/defaul...;en-us;Q270119, I
changed it to:
Delete OPENQUERY(abcweb, 'Select ImageID From images Where
ImageID=99987')
and it worked! Would still be good to find out why the original method
wouldn't work for deletes or updates, but did work for inserts. But at least
this works.
Thanks!
Neil
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:bh9m62hksvsaa8fdoob0sphsd32jgqc36h@.
4ax.com...
> Which box were you running the trace against? Try running it
> against the destination (the linked server) instead of the
> server that you are executing the command from.
> Another thing to see if it works - try executing the
> statement using OpenQuery instead of the 4 part name and see
> if that makes a difference.
> -Sue
> On Wed, 17 May 2006 06:23:30 GMT, "Neil" <nospam@.nospam.net>
> wrote:
>
>

Problem with DBO after restore

Hi,
I support an application and part of it has an archive function. It takes
backups of a main database (using the SQL backup command) and saves them to
be restored at a later date to an archive database on the same SQL Server
instance for reporting on past events. All of this has worked fine until
recently. The user that we do this under is a SQL Server login and until
recently was a sysadmin. Based on customer requests we have lowered the
permissions on this user to be DBO on the databases it needs to backup and
restore to and also a member of the dbcreator fixed server role. Everything
still works fine except in one case. When the user moves everything to a ne
w
SQL Server. When this happens and they try to restore one of the backups to
the archive database, after it restores our user is no longer the DBO and ha
s
no access to the database. I originally thought this was because the SIDs
were different between the two machines so I used the sp_help_revlogin
procedure to create the user on the new SQL Server with the SID from the old
machine. This worked because I do not see our user as an orphan when runnin
g
sp_change_users_login but unfortunately this did not fix the problem. After
the restore it is still not DBO. I can give our user access again by runnin
g
sp_Changedbowner after the database restore but that isn't practical for
customers especially since it requires sysadmin authority and the need for
our user to not be a sysadmin was what prompted this change in the first
place. Thanks in advance for any help.
WayneWayne
Well , it is well known issue
Please search on internet for "sp_help_revlogin script" and read the
explanation
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:51F9F2CC-E47F-45F0-B9D6-76B21F1E3E02@.microsoft.com...
> Hi,
> I support an application and part of it has an archive function. It takes
> backups of a main database (using the SQL backup command) and saves them
> to
> be restored at a later date to an archive database on the same SQL Server
> instance for reporting on past events. All of this has worked fine until
> recently. The user that we do this under is a SQL Server login and until
> recently was a sysadmin. Based on customer requests we have lowered the
> permissions on this user to be DBO on the databases it needs to backup and
> restore to and also a member of the dbcreator fixed server role.
> Everything
> still works fine except in one case. When the user moves everything to a
> new
> SQL Server. When this happens and they try to restore one of the backups
> to
> the archive database, after it restores our user is no longer the DBO and
> has
> no access to the database. I originally thought this was because the SIDs
> were different between the two machines so I used the sp_help_revlogin
> procedure to create the user on the new SQL Server with the SID from the
> old
> machine. This worked because I do not see our user as an orphan when
> running
> sp_change_users_login but unfortunately this did not fix the problem.
> After
> the restore it is still not DBO. I can give our user access again by
> running
> sp_Changedbowner after the database restore but that isn't practical for
> customers especially since it requires sysadmin authority and the need for
> our user to not be a sysadmin was what prompted this change in the first
> place. Thanks in advance for any help.
> Wayne|||Thanks Uri,
Ok, just wanted to confirm that is how it is and that I wasn't missing
something.
Wayne
"Uri Dimant" wrote:

> Wayne
> Well , it is well known issue
> Please search on internet for "sp_help_revlogin script" and read the
> explanation
>
>
>
>
> "Wayne" <Wayne@.discussions.microsoft.com> wrote in message
> news:51F9F2CC-E47F-45F0-B9D6-76B21F1E3E02@.microsoft.com...
>
>|||The SQL 2000 SP4 version of sp_changedbowner proc has a hard-coded check for
sysadmin role membership. A possible work-around (unsupported) is to create
a special version of this system proc, changing the role check from
'sysadmin' to 'dbcreator' (see script below). I don't know if this is
palatable in your case but I can't think of another solution.
Backup/restore really isn't intended for routine application functionality
and even 'dbcreator' is a very powerful role.
-- create a system proc (unsupported) --
sp_configure 'allow', 1
GO
RECONFIGURE WITH OVERRIDE
GO
--*** create new sp_changedbowner_dbcreator here ***
GO
EXEC sp_MS_marksystemobject 'sp_changedbowner_dbcreator'
GO
sp_configure 'allow', 0
GO
RECONFIGURE
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:51F9F2CC-E47F-45F0-B9D6-76B21F1E3E02@.microsoft.com...
> Hi,
> I support an application and part of it has an archive function. It takes
> backups of a main database (using the SQL backup command) and saves them
> to
> be restored at a later date to an archive database on the same SQL Server
> instance for reporting on past events. All of this has worked fine until
> recently. The user that we do this under is a SQL Server login and until
> recently was a sysadmin. Based on customer requests we have lowered the
> permissions on this user to be DBO on the databases it needs to backup and
> restore to and also a member of the dbcreator fixed server role.
> Everything
> still works fine except in one case. When the user moves everything to a
> new
> SQL Server. When this happens and they try to restore one of the backups
> to
> the archive database, after it restores our user is no longer the DBO and
> has
> no access to the database. I originally thought this was because the SIDs
> were different between the two machines so I used the sp_help_revlogin
> procedure to create the user on the new SQL Server with the SID from the
> old
> machine. This worked because I do not see our user as an orphan when
> running
> sp_change_users_login but unfortunately this did not fix the problem.
> After
> the restore it is still not DBO. I can give our user access again by
> running
> sp_Changedbowner after the database restore but that isn't practical for
> customers especially since it requires sysadmin authority and the need for
> our user to not be a sysadmin was what prompted this change in the first
> place. Thanks in advance for any help.
> Wayne

Problem with DBO after restore

Hi,
I support an application and part of it has an archive function. It takes
backups of a main database (using the SQL backup command) and saves them to
be restored at a later date to an archive database on the same SQL Server
instance for reporting on past events. All of this has worked fine until
recently. The user that we do this under is a SQL Server login and until
recently was a sysadmin. Based on customer requests we have lowered the
permissions on this user to be DBO on the databases it needs to backup and
restore to and also a member of the dbcreator fixed server role. Everything
still works fine except in one case. When the user moves everything to a new
SQL Server. When this happens and they try to restore one of the backups to
the archive database, after it restores our user is no longer the DBO and has
no access to the database. I originally thought this was because the SIDs
were different between the two machines so I used the sp_help_revlogin
procedure to create the user on the new SQL Server with the SID from the old
machine. This worked because I do not see our user as an orphan when running
sp_change_users_login but unfortunately this did not fix the problem. After
the restore it is still not DBO. I can give our user access again by running
sp_Changedbowner after the database restore but that isn't practical for
customers especially since it requires sysadmin authority and the need for
our user to not be a sysadmin was what prompted this change in the first
place. Thanks in advance for any help.
WayneWayne
Well , it is well known issue
Please search on internet for "sp_help_revlogin script" and read the
explanation
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:51F9F2CC-E47F-45F0-B9D6-76B21F1E3E02@.microsoft.com...
> Hi,
> I support an application and part of it has an archive function. It takes
> backups of a main database (using the SQL backup command) and saves them
> to
> be restored at a later date to an archive database on the same SQL Server
> instance for reporting on past events. All of this has worked fine until
> recently. The user that we do this under is a SQL Server login and until
> recently was a sysadmin. Based on customer requests we have lowered the
> permissions on this user to be DBO on the databases it needs to backup and
> restore to and also a member of the dbcreator fixed server role.
> Everything
> still works fine except in one case. When the user moves everything to a
> new
> SQL Server. When this happens and they try to restore one of the backups
> to
> the archive database, after it restores our user is no longer the DBO and
> has
> no access to the database. I originally thought this was because the SIDs
> were different between the two machines so I used the sp_help_revlogin
> procedure to create the user on the new SQL Server with the SID from the
> old
> machine. This worked because I do not see our user as an orphan when
> running
> sp_change_users_login but unfortunately this did not fix the problem.
> After
> the restore it is still not DBO. I can give our user access again by
> running
> sp_Changedbowner after the database restore but that isn't practical for
> customers especially since it requires sysadmin authority and the need for
> our user to not be a sysadmin was what prompted this change in the first
> place. Thanks in advance for any help.
> Wayne|||Thanks Uri,
Ok, just wanted to confirm that is how it is and that I wasn't missing
something.
Wayne
"Uri Dimant" wrote:
> Wayne
> Well , it is well known issue
> Please search on internet for "sp_help_revlogin script" and read the
> explanation
>
>
>
>
> "Wayne" <Wayne@.discussions.microsoft.com> wrote in message
> news:51F9F2CC-E47F-45F0-B9D6-76B21F1E3E02@.microsoft.com...
> > Hi,
> > I support an application and part of it has an archive function. It takes
> > backups of a main database (using the SQL backup command) and saves them
> > to
> > be restored at a later date to an archive database on the same SQL Server
> > instance for reporting on past events. All of this has worked fine until
> > recently. The user that we do this under is a SQL Server login and until
> > recently was a sysadmin. Based on customer requests we have lowered the
> > permissions on this user to be DBO on the databases it needs to backup and
> > restore to and also a member of the dbcreator fixed server role.
> > Everything
> > still works fine except in one case. When the user moves everything to a
> > new
> > SQL Server. When this happens and they try to restore one of the backups
> > to
> > the archive database, after it restores our user is no longer the DBO and
> > has
> > no access to the database. I originally thought this was because the SIDs
> > were different between the two machines so I used the sp_help_revlogin
> > procedure to create the user on the new SQL Server with the SID from the
> > old
> > machine. This worked because I do not see our user as an orphan when
> > running
> > sp_change_users_login but unfortunately this did not fix the problem.
> > After
> > the restore it is still not DBO. I can give our user access again by
> > running
> > sp_Changedbowner after the database restore but that isn't practical for
> > customers especially since it requires sysadmin authority and the need for
> > our user to not be a sysadmin was what prompted this change in the first
> > place. Thanks in advance for any help.
> >
> > Wayne
>
>|||The SQL 2000 SP4 version of sp_changedbowner proc has a hard-coded check for
sysadmin role membership. A possible work-around (unsupported) is to create
a special version of this system proc, changing the role check from
'sysadmin' to 'dbcreator' (see script below). I don't know if this is
palatable in your case but I can't think of another solution.
Backup/restore really isn't intended for routine application functionality
and even 'dbcreator' is a very powerful role.
-- create a system proc (unsupported) --
sp_configure 'allow', 1
GO
RECONFIGURE WITH OVERRIDE
GO
--*** create new sp_changedbowner_dbcreator here ***
GO
EXEC sp_MS_marksystemobject 'sp_changedbowner_dbcreator'
GO
sp_configure 'allow', 0
GO
RECONFIGURE
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:51F9F2CC-E47F-45F0-B9D6-76B21F1E3E02@.microsoft.com...
> Hi,
> I support an application and part of it has an archive function. It takes
> backups of a main database (using the SQL backup command) and saves them
> to
> be restored at a later date to an archive database on the same SQL Server
> instance for reporting on past events. All of this has worked fine until
> recently. The user that we do this under is a SQL Server login and until
> recently was a sysadmin. Based on customer requests we have lowered the
> permissions on this user to be DBO on the databases it needs to backup and
> restore to and also a member of the dbcreator fixed server role.
> Everything
> still works fine except in one case. When the user moves everything to a
> new
> SQL Server. When this happens and they try to restore one of the backups
> to
> the archive database, after it restores our user is no longer the DBO and
> has
> no access to the database. I originally thought this was because the SIDs
> were different between the two machines so I used the sp_help_revlogin
> procedure to create the user on the new SQL Server with the SID from the
> old
> machine. This worked because I do not see our user as an orphan when
> running
> sp_change_users_login but unfortunately this did not fix the problem.
> After
> the restore it is still not DBO. I can give our user access again by
> running
> sp_Changedbowner after the database restore but that isn't practical for
> customers especially since it requires sysadmin authority and the need for
> our user to not be a sysadmin was what prompted this change in the first
> place. Thanks in advance for any help.
> Wayne

problem with datetime value as parameter value

Hi all,
I created a stored proc that has input datetime parameters (begindate and enddate), I tried the command: exec storedproc '20060320' in the query command part in Microsoft SQL Server Management Studio and it works but when I try to create a report dataset using the stored proc and execute it using value 20060320 it does not work. I even try using '20060320' and "20060320" as the value and it did not work also. I received the following error:
TITLE: Microsoft Report Designer

An error occurred while executing the query.
Failed to convert parameter value from a String to a DateTime.

ADDITIONAL INFORMATION:
Failed to convert parameter value from a String to a DateTime. (System.Data)

String was not recognized as a valid DateTime. (mscorlib)

BUTTONS:
OK

Anyone have any idea on how can I solve it or go about it? Thanks in advance.
Daren
Try using the format: "mm/dd/yyyy", so it would be: 03/20/2006.|||Thanks Deepak,
This solved my problem.
Daren

Problem with Dates and OpenXML command

My data is being updated correctly for all fields except a date field.
The program is crashing when a date is encountered with OpenXML. Is
there a way to convert the date within SQL to handle this...I have tried
the CONVERT statement below, but it does little
UPDATE EducationSheetSchool
SET
EducationSheetSchool.Address= XMLEducationSheetSchool.Address,
EducationSheetSchool.[Name]= XMLEducationSheetSchool.[Name],
EducationSheetSchool.LEA= XMLEducationSheetSchool.LEA,
EducationSheetSchool.Tel= XMLEducationSheetSchool.Tel,
EducationSheetSchool.Email= XMLEducationSheetSchool.Email,
EducationSheetSchool.Start=CONVERT(varchar(30),XML EducationSheetSchool.S
tart, 126),
*** Sent via Developersdex http://www.codecomments.com ***
Can you show a sample of the XML and the OPENXML syntax you're using?
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
"Paul Barnett" <barnettpaul@.gmail.com> wrote in message
news:eCtAwfM4FHA.1188@.TK2MSFTNGP12.phx.gbl...
>
> My data is being updated correctly for all fields except a date field.
> The program is crashing when a date is encountered with OpenXML. Is
> there a way to convert the date within SQL to handle this...I have tried
> the CONVERT statement below, but it does little
>
> UPDATE EducationSheetSchool
> SET
> EducationSheetSchool.Address= XMLEducationSheetSchool.Address,
> EducationSheetSchool.[Name]= XMLEducationSheetSchool.[Name],
> EducationSheetSchool.LEA= XMLEducationSheetSchool.LEA,
> EducationSheetSchool.Tel= XMLEducationSheetSchool.Tel,
> EducationSheetSchool.Email= XMLEducationSheetSchool.Email,
> EducationSheetSchool.Start=CONVERT(varchar(30),XML EducationSheetSchool.S
> tart, 126),
> *** Sent via Developersdex http://www.codecomments.com ***

Problem With Dataset()

Hello all

i m facing a problem "There is already an open DataReader associated with this Command which must be closed first."

what is this alll the another page like thispage is currently working properly

so what is this problem

pleasse help me

ashwani

hi Friend,

do check your code you might be using same datareader without closing it before .

|||

Hi,

There is already an open DataReader associated with this Command which must be closed first.

From the error message you provided, it seems that you are using DataReader in your application, right?

Generally, a datareader represents an open connection to the database. If you want to run 2 readers simultaneously, you need to use two separate connections.

Thanks.