Error, when alter table

Hi,
on a SQL Server 200 installed on a Win2003 Std. Server, we get an error in a
ny Databse when trying to 'alter table'
The error says something like 'Cursor not vaild' or in German 'Ungueltiger C
ursorstatus'
Any ideas?
Thanks in advanceDid you try running an explicit ALTER TABLE statement in Query Analyzer,
instead of making changes using Enterprise Manager?
http://www.aspfaq.com/2515
http://www.aspfaq.com/
(Reverse address to reply.)
"DOKOM@.community.nospam" <DOKOM@.community.nospam@.discussions.microsoft.com>
wrote in message news:A51F71F0-A5DD-43B9-B849-B3B26759C929@.microsoft.com...
> Hi,
> on a SQL Server 200 installed on a Win2003 Std. Server, we get an error in
any Databse when trying to 'alter table'
> The error says something like 'Cursor not vaild' or in German 'Ungueltiger
Cursorstatus'
> Any ideas?
> Thanks in advance|||Hi again,
your suggestions work fine with the QueryAnalyzer. But beside that, we are f
acing the problem, that some asp-Scripts do not work when performing a 'rs.U
pdate'
They show error: (80040e23) 'Cursor operation conflict'
The Scripts worked fine before.
"Aaron [SQL Server MVP]" wrote:

> Did you try running an explicit ALTER TABLE statement in Query Analyzer,
> instead of making changes using Enterprise Manager?
> http://www.aspfaq.com/2515
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "DOKOM@.community.nospam" <DOKOM@.community.nospam@.discussions.microsoft.com
> wrote in message news:A51F71F0-A5DD-43B9-B849-B3B26759C929@.microsoft.com..
.
> any Databse when trying to 'alter table'
> Cursorstatus'
>
>|||> some asp-Scripts do not work when performing a 'rs.Update'
Call a stored procedure or use an UPDATE statement to perform an update.
Don't use a heavy, lock-intensive recordset object...
http://www.aspfaq.com/2191
http://www.aspfaq.com/
(Reverse address to reply.)|||That might be true, but the Scripts workes on our 'old' Server.
The only thing we want is the scripts running in the 'new' environment.
We will not change everything because of some bad SQL-Patches.
If it is the only chance I'd prefer installing a new instance of the SQL-Ser
ver without the above mentioned Patch.
"Aaron [SQL Server MVP]" wrote:

> Call a stored procedure or use an UPDATE statement to perform an update.
> Don't use a heavy, lock-intensive recordset object...
> http://www.aspfaq.com/2191
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||That might be true, but the Scripts workes on our 'old' Server.
The only thing we want is the scripts running in the 'new' environment.
We will not change everything because of some bad SQL-Patches.
If it is the only chance I'd prefer installing a new instance of the SQL-Ser
ver without the above mentioned Patch.
"Aaron [SQL Server MVP]" wrote:

> Call a stored procedure or use an UPDATE statement to perform an update.
> Don't use a heavy, lock-intensive recordset object...
> http://www.aspfaq.com/2191
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||> If it is the only chance I'd prefer installing a new instance of the
SQL-Server without the above mentioned Patch.
Did you actually read the whole article I referenced? Here it is again,
just in case:
http://www.aspfaq.com/2515
http://www.aspfaq.com/
(Reverse address to reply.)

Error, when alter table

Hi,
on a SQL Server 200 installed on a Win2003 Std. Server, we get an error in any Databse when trying to 'alter table'
The error says something like 'Cursor not vaild' or in German 'Ungueltiger Cursorstatus'
Any ideas?
Thanks in advanceDid you try running an explicit ALTER TABLE statement in Query Analyzer,
instead of making changes using Enterprise Manager?
http://www.aspfaq.com/2515
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DOKOM@.community.nospam" <DOKOM@.community.nospam@.discussions.microsoft.com>
wrote in message news:A51F71F0-A5DD-43B9-B849-B3B26759C929@.microsoft.com...
> Hi,
> on a SQL Server 200 installed on a Win2003 Std. Server, we get an error in
any Databse when trying to 'alter table'
> The error says something like 'Cursor not vaild' or in German 'Ungueltiger
Cursorstatus'
> Any ideas?
> Thanks in advance|||> some asp-Scripts do not work when performing a 'rs.Update'
Call a stored procedure or use an UPDATE statement to perform an update.
Don't use a heavy, lock-intensive recordset object...
http://www.aspfaq.com/2191
--
http://www.aspfaq.com/
(Reverse address to reply.)|||> If it is the only chance I'd prefer installing a new instance of the
SQL-Server without the above mentioned Patch.
Did you actually read the whole article I referenced? Here it is again,
just in case:
http://www.aspfaq.com/2515
--
http://www.aspfaq.com/
(Reverse address to reply.)

Error, when alter table

Hi,
on a SQL Server 200 installed on a Win2003 Std. Server, we get an error in any Databse when trying to 'alter table'
The error says something like 'Cursor not vaild' or in German 'Ungueltiger Cursorstatus'
Any ideas?
Thanks in advance
Did you try running an explicit ALTER TABLE statement in Query Analyzer,
instead of making changes using Enterprise Manager?
http://www.aspfaq.com/2515
http://www.aspfaq.com/
(Reverse address to reply.)
"DOKOM@.community.nospam" <DOKOM@.community.nospam@.discussions.microsoft.com>
wrote in message news:A51F71F0-A5DD-43B9-B849-B3B26759C929@.microsoft.com...
> Hi,
> on a SQL Server 200 installed on a Win2003 Std. Server, we get an error in
any Databse when trying to 'alter table'
> The error says something like 'Cursor not vaild' or in German 'Ungueltiger
Cursorstatus'
> Any ideas?
> Thanks in advance
|||Hi again,
your suggestions work fine with the QueryAnalyzer. But beside that, we are facing the problem, that some asp-Scripts do not work when performing a 'rs.Update'
They show error: (80040e23) 'Cursor operation conflict'
The Scripts worked fine before.
"Aaron [SQL Server MVP]" wrote:

> Did you try running an explicit ALTER TABLE statement in Query Analyzer,
> instead of making changes using Enterprise Manager?
> http://www.aspfaq.com/2515
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "DOKOM@.community.nospam" <DOKOM@.community.nospam@.discussions.microsoft.com>
> wrote in message news:A51F71F0-A5DD-43B9-B849-B3B26759C929@.microsoft.com...
> any Databse when trying to 'alter table'
> Cursorstatus'
>
>
|||> some asp-Scripts do not work when performing a 'rs.Update'
Call a stored procedure or use an UPDATE statement to perform an update.
Don't use a heavy, lock-intensive recordset object...
http://www.aspfaq.com/2191
http://www.aspfaq.com/
(Reverse address to reply.)
|||That might be true, but the Scripts workes on our 'old' Server.
The only thing we want is the scripts running in the 'new' environment.
We will not change everything because of some bad SQL-Patches.
If it is the only chance I'd prefer installing a new instance of the SQL-Server without the above mentioned Patch.
"Aaron [SQL Server MVP]" wrote:

> Call a stored procedure or use an UPDATE statement to perform an update.
> Don't use a heavy, lock-intensive recordset object...
> http://www.aspfaq.com/2191
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||That might be true, but the Scripts workes on our 'old' Server.
The only thing we want is the scripts running in the 'new' environment.
We will not change everything because of some bad SQL-Patches.
If it is the only chance I'd prefer installing a new instance of the SQL-Server without the above mentioned Patch.
"Aaron [SQL Server MVP]" wrote:

> Call a stored procedure or use an UPDATE statement to perform an update.
> Don't use a heavy, lock-intensive recordset object...
> http://www.aspfaq.com/2191
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||> If it is the only chance I'd prefer installing a new instance of the
SQL-Server without the above mentioned Patch.
Did you actually read the whole article I referenced? Here it is again,
just in case:
http://www.aspfaq.com/2515
http://www.aspfaq.com/
(Reverse address to reply.)

Error with linked server after failover

Hi all,

The following query that uses a linked server is giving me the error message below after I initiate a failover (ALTER DATABASE Northwind SET PARTNER FAILOVER).I have SQL Server 2005 SP2.I think that without the service pack there is another error too.

The query is run from a database other than northwind of course.

select * from DualLink.northwind.dbo.Test1

Please note that:

without a failover itworks perfectly

it always work if I try to run it a second time - only the first time it fails.

it fails the first time for each of the open connections. A new connection that was open after the failover will work fine.

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

The linked server is defined as

EXEC master.

dbo.sp_addlinkedserver

@.server = N'DualLink',

@.srvproduct=N'',

@.provider=N'SQLOLEDB',

@.catalog=N'northwind',

@.provstr=N'Server=(local);FailoverPartner=MyIPAddr;'

EXEC master.dbo.sp_addlinkedsrvlogin

@.rmtsrvname = 'DualLink',

@.useself = false,

@.locallogin = 'sa',

@.rmtuser = 'sa',

@.rmtpassword = 'MyPwd'

Thanks,

Avi

I am having the same problem except I don't have a linked server.

I am running SQL Server 2005 sp1 and Windows 2003 using clustering.

In addition to your symptoms I have noticed the following:

1. The error occurs when I make a query through Server Manager and when using a java app that queries a database using an ASP page. However, if I bring up our website as the first request after failover it works fine.

2. If I failover from Server1 to Server2 and don't make any requests while it is on Server2 and then failover back to Server1 I do not get the error. If I make any requests while it is on Server2 then the first request I make on Server1 after failing over will cause the error.

If you have solved the problem I would like to know how. If I find a solution I will post it here.

Blake

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' = (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' => (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>
> > Ok, I previously had a thread about "Column name as variable".
> > Here's a proc I wrote, and it compiles ok:
> > alter PROCEDURE rptRRTP_By_Date_Range
> > -- Add the parameters for the stored procedure here
> > @.Update_Field as sysname,
> > @.start_date as datetime,
> > @.end_date as datetime,
> > @.contr_Stat as varchar(2),
> > @.seq as int
> > AS
> > declare @.sql_stat as varchar(255)
> > BEGIN
> > set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' => > (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> > ',' + @.end_date + ')
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
> > where contract_status = ' + @.contr_stat + ' group by sequence_no)
> > where sequence = ' + @.seq
> > Execute (@.sql_stat)
> > END
> > GO|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegroups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>> You can't pass a function in as a parameter. Try:
>> DECLARE @.dt DATETIME;
>> SET @.dt = GETDATE();
>> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
> Ok, did that, but see error below.
>
>
>> --
>> Aaron Bertrand
>> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
>> "d.s." <nodamnspa...@.yahoo.com> wrote in message
>> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>>
>> > Ok, I previously had a thread about "Column name as variable".
>> > Here's a proc I wrote, and it compiles ok:
>> > alter PROCEDURE rptRRTP_By_Date_Range
>> > -- Add the parameters for the stored procedure here
>> > @.Update_Field as sysname,
>> > @.start_date as datetime,
>> > @.end_date as datetime,
>> > @.contr_Stat as varchar(2),
>> > @.seq as int
>> > AS
>> > declare @.sql_stat as varchar(255)
>> > BEGIN
>> > set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =>> > (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
>> > ',' + @.end_date + ')
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>> > where contract_status = ' + @.contr_stat + ' group by sequence_no)
>> > where sequence = ' + @.seq
>> > Execute (@.sql_stat)
>> > END
>> > GO
>|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name string.
> You could try declaring your parameters as type varchar, but you have to be
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

error while running a simple sp.

Hi,

alter procedure sp_migrate_sg_oracle_vendoraddress1

as

insert into oracle..ps_vendor_address

SELECT a.AddressSeq as address_seq_num,a.addressLine1 as address1,

a.AddressLine2 as address2, a.City , a.State, a.Country, a.Postcode,

a.AddressLine3 as address3,a.AddressLine4 as address4, a.County,

a.Email as emailid, cast(a.effectivedate as char(10)) as effdt,a.Status as eff_status,

v.txtvendorname1 as name1,txtvendorname2 as name2,num1=null,

num2=null,housetype=null,addr_field1=null,addr_field2=null,addr_field3=null,

geo_code=null,in_city_limit='N',v.txtSetID as setid,v.txtVendorID as vendorid

FROM sf_stagingoutbound..AP_Address as a

left outer join vw_ap_vendorssubmitted v

on v.EFOLDERID = a.EFOLDERID

when iam executing this iam repeatedly getting the following error.

Msg 241, Level 16, State 1, Procedure sp_migrate_sg_oracle_vendoraddress1, Line 3

Conversion failed when converting datetime from character string.

pls help.

thanks and Regards,

sg

your cast is wrong, take a look at this

declare @.effectivedate datetime
select @.effectivedate = getdate()

select @.effectivedate,cast(@.effectivedate as char(10)) as effdt, convert(char(8),@.effectivedate,112) as effdt2

if you run this you will see that your cast returns Jan 30 200

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

try this instead

select convert(varchar,getdate(), 106) -- 30 Jan 2007
select convert(varchar,getdate(), 107) -- Jan 30, 2007