Cant get LIKE to work

I have the below procedure that will not work- I must be losing my mind, this is not that difficult - mental roadblock for me.

Using SQL Server 2000 to create SP being called by ASP.Net with C# code behind

stored procedure only returns if input exactly matches L_Name

PROCEDURE newdawn.LinkNameLIKESearch
@.L_Name nvarchar(100)
AS
SELECT [L_Name], [L_ID], [C_ID], [L_Enabled], [L_Rank], [L_URL]
FROM tblContractorLinkInfo
WHERE L_Name LIKE @.L_Name
RETURN

I tried: WHERE L_Name LIKE ' % L_Name % ' no luck. What am I missing?

Thank you

try it like this:

LIKE '%' + @.L_Name + '%'

|||

Ever have a day when you really fell dumb - I am having way too many of them.

Thank you.

Can't find tool

Based on this message referenced in the link below I can't find the file
"FiletoTable.exe" anywhere online. Does anyone know how I can get this file?
"You can also use FiletoTable.exe, a command line utility, to load a trace
file
to a database table so that data in the trace file can be analyzed..."
www.microsoft.com/technet/ prodtechnol/sql/2000/maintain/sqlc2.mspx
Thanks in advance,
Arnold
I don't know about that tool but if you have SQL 2000 you can use:
Select * FROM ::fn_trace_gettable('C:\Traces\TestTrace.trc', default)
See BOL for more details.
Andrew J. Kelly SQL MVP
"Arnold" <Arnold@.discussions.microsoft.com> wrote in message
news:51EF4662-3922-448D-8E46-FCF9F88EDECD@.microsoft.com...
> Based on this message referenced in the link below I can't find the file
> "FiletoTable.exe" anywhere online. Does anyone know how I can get this
> file?
>
> "You can also use FiletoTable.exe, a command line utility, to load a trace
> file
> to a database table so that data in the trace file can be analyzed..."
> www.microsoft.com/technet/ prodtechnol/sql/2000/maintain/sqlc2.mspx
> Thanks in advance,
> Arnold

Cant find my UDF

Sometimes the simplest things are the most difficult... I'm creating a
UDF as below, then executing it but all I get is that the object does not exist. I must be missing something very basic here...

CREATE function dbo.GetColumnLength(@.vcTableName varchar(50), @.vcColumnName varchar(50)) returns smallint
as
begin
declare @.intLength as smallint
select @.intLength=sysC.prec from syscolumns sysC, sysobjects sysO
where sysC.Id = sysO.Id AND sysO.xtype ='U' And
sysO.Name = @.vcTableName AND
sysC.Name = @.vcColumnName
return @.intLength
End
GO
select top 2 * from player, dbo.GetColumnLength('playerdetails','email')Odd. Can you see the UDF in Enterprise Manager?|||select top 2 * from player, dbo.GetColumnLength('playerdetails','email')

Check the above statement.
I think it should be ...
select top 2 *,dbo.GetColumnLength('playerdetails','email') from player

And yes it just working fine ...
Joydeep|||Odd.

Odd?

Didn't you just answer this interview question a short while ago?

It's not retuning a table so it can't be in the FROM clause, and in any event, what you're doing doesn't make much sense.

Just what is it you are trying to accomplish?|||What I meant was, "Odd" that I didn't notice he had the function in his WHERE clause... :)

Still odd that he would recieve an error stating that the object doesn't exist...

Maybe what he wants is this:
select top 2 *, dbo.GetColumnLength('playerdetails','email') from player|||Thanks... I know I was tired at work today, but this mistake was just too much ;-)|||The code I posted was just an example, it's not the final UDF. It was simply just the only UDF code I had at hand.

Just as a principle, I should be able to have a UDF call in the table list, shouldn't I ?|||Yes, if the UDF returns a table rather than a scalar result.

Can't find log-shipping option

In the text on the link (part 2 - below) - I cannot find the "Transaction
Log Shipping" option Is this an SP4 option only for SQL Server 2000?
http://msdn2.microsoft.com/en-gb/library/ms189071.aspx
Right-click the database you want to use as your primary database in the log
shipping configuration, and then click Properties.
Under Select a page, click Transaction Log Shipping.
Clear the Enable this as a primary database in a log shipping configuration
check box.
Regards,
Jamie
I may be asking the wrong question - Is there a wizard that assists in the
creation of log shipping or is it done strictly with a DTS package?
Regards,
Jamie
"thejamie" wrote:

> In the text on the link (part 2 - below) - I cannot find the "Transaction
> Log Shipping" option Is this an SP4 option only for SQL Server 2000?
> http://msdn2.microsoft.com/en-gb/library/ms189071.aspx
> ----
> Right-click the database you want to use as your primary database in the log
> shipping configuration, and then click Properties.
> Under Select a page, click Transaction Log Shipping.
> Clear the Enable this as a primary database in a log shipping configuration
> check box.
> --
> Regards,
> Jamie
|||Jamie,
the original MSDN2 link you supplied applies to SQL Server 2005, but as you
are tlaking about DTS you must be using SQL Server 2000. In your edition of
sql server, you'll set this up using a maintenance plan. In the first screen
there's a checkbox at the bottom for log shipping.
Rgds,
Paul Ibison
|||Actually, it looks from the description you are giving me that the SQL Server
2000 Standard edition does not support log shipping. We are running both
but are not currently in a position to migrate to 2005.
Regards,
Jamie
"Paul Ibison" wrote:

> Jamie,
> the original MSDN2 link you supplied applies to SQL Server 2005, but as you
> are tlaking about DTS you must be using SQL Server 2000. In your edition of
> sql server, you'll set this up using a maintenance plan. In the first screen
> there's a checkbox at the bottom for log shipping.
> Rgds,
> Paul Ibison
>
>
|||Paul,
I can see from looking at various articles on the web that log shipping is
possible without the enterprise version. Are there sample scripts available
for this (SQL Server 2000)?
Regards,
Jamie
"Paul Ibison" wrote:

> Jamie,
> the original MSDN2 link you supplied applies to SQL Server 2005, but as you
> are tlaking about DTS you must be using SQL Server 2000. In your edition of
> sql server, you'll set this up using a maintenance plan. In the first screen
> there's a checkbox at the bottom for log shipping.
> Rgds,
> Paul Ibison
>
>
|||Jamie - these scripts are in the Backoffice Resource Kit, and are also on
some sites (eg
http://www.sql-server-performance.com/sql_server_log_shipping.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I also see that we have not turned on our backup logs option. Is there an
impact on the server if I turn it on?
Regards,
Jamie
"Paul Ibison" wrote:

> Jamie - these scripts are in the Backoffice Resource Kit, and are also on
> some sites (eg
> http://www.sql-server-performance.com/sql_server_log_shipping.asp).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||This is pretty much standard on a production server. If you only have simple
recovery mode, the DR plan will rely on complete backups only and will
therefore expose you to a large potential data loss. Much better to use FULL
and regular log backups. There will be an impact on the production server as
the log backup will need to access the disk, but this is unavoidable to have
a decent DR plan, and if the backups begin to take too long on the disk, you
can use LiteSpeed
(http://www.quest.com/litespeed_for_sql_server/overview.aspx) or equivalent.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
After turning on the option to save the transaction logs, in six short
hours, there was an accumulation of over 10gig of data. I suspect that given
the absense of a wizard, my better choice is the litespeed or a similar tool.
Is there a reason you chose litespeed - there are a couple of other products
reviewed in this month's sql mag... I suspect that I have more to manage
than I can handle at this point.
Regards,
Jamie
"Paul Ibison" wrote:

> This is pretty much standard on a production server. If you only have simple
> recovery mode, the DR plan will rely on complete backups only and will
> therefore expose you to a large potential data loss. Much better to use FULL
> and regular log backups. There will be an impact on the production server as
> the log backup will need to access the disk, but this is unavoidable to have
> a decent DR plan, and if the backups begin to take too long on the disk, you
> can use LiteSpeed
> (http://www.quest.com/litespeed_for_sql_server/overview.aspx) or equivalent.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||Jamie,
I looked at a couple and simply went for the market leader on the assumption
that they were less likely to go bust
If you start using this type of tool, the log-shipping will have to be
hand-crafted as SQL Server won't recognise the backup files. This sounds
like a pain, but in reality log shipping is very simple technology and can
easily be created via a series of your own jobs. Using SQLLiteSpeed in this
way is supported as it has extended stored procedures to do the backup and
the restore.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Can't create trigger

I have a WH table with three primary key: PID, SchYears, Sem.
and WHID is one field of it.
I use the code below to create a trigger but fail with message:
"The multi-part identifier "Inserted.PID" could not be bound."
How can I do that?
-----
create trigger trigWH on WH
AFTER INSERT
AS
UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
GOUPDATE WH SET WHID=1
FROM inserted -- you were missing this...
WHERE
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>|||To complete it
UPDATE WH SET WHID=1
FROM inserted INNER JOIN WH ON
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>

Can't create trigger

I have a WH table with three primary key: PID, SchYears, Sem.
and WHID is one field of it.
I use the code below to create a trigger but fail with message:
"The multi-part identifier "Inserted.PID" could not be bound."
How can I do that?
-----
create trigger trigWH on WH
AFTER INSERT
AS
UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
GO
UPDATE WH SET WHID=1
FROM inserted -- you were missing this...
WHERE
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>
|||To complete it
UPDATE WH SET WHID=1
FROM inserted INNER JOIN WH ON
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> -----
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>

Can't create trigger

I have a WH table with three primary key: PID, SchYears, Sem.
and WHID is one field of it.
I use the code below to create a trigger but fail with message:
"The multi-part identifier "Inserted.PID" could not be bound."
How can I do that?
----
--
create trigger trigWH on WH
AFTER INSERT
AS
UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
GOUPDATE WH SET WHID=1
FROM inserted -- you were missing this...
WHERE
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> ----
--
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>|||To complete it
UPDATE WH SET WHID=1
FROM inserted INNER JOIN WH ON
WH.PID=Inserted.PID
and
W.GradeID=Inserted.GradeID
and
WH.Sem=Inserted.Sem
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OV$Aie9tFHA.3932@.TK2MSFTNGP15.phx.gbl...
>I have a WH table with three primary key: PID, SchYears, Sem.
> and WHID is one field of it.
> I use the code below to create a trigger but fail with message:
> "The multi-part identifier "Inserted.PID" could not be bound."
> How can I do that?
> ----
--
> create trigger trigWH on WH
> AFTER INSERT
> AS
> UPDATE WH SET WHID=1 WHERE WH.PID=Inserted.PID and
> WH.GradeID=Inserted.GradeID and WH.Sem=Inserted.Sem
> GO
>

Can't create Primary Key??

Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREAT
E UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
Rob
IT guy!Assuming your PK is compreised of columns Col1 and Col2, run the following:
select
Col1
, Col2
, count (*)
from
MyTable
group by
Col1
, Col2
having
count (*) > 1
This will reveal the duplicates.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREAT
E UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
Rob
IT guy!|||Assuming your PK is comprised of columns Col1 and Col2, run the following:
select
Col1
, Col2
, count (*)
from
MyTable
group by
Col1
, Col2
having
count (*) > 1
This will reveal the duplicates.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREAT
E UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
Rob
IT guy!|||Tom,
I try running the query but it times out. The table is quite large.
"Tom Moreau" wrote:

> Assuming your PK is comprised of columns Col1 and Col2, run the following:
> select
> Col1
> , Col2
> , count (*)
> from
> MyTable
> group by
> Col1
> , Col2
> having
> count (*) > 1
> This will reveal the duplicates.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
> Hi Guy's
> I've migrated an access DB of ours over to SQL 2000 and when I try to set
> the primary key I get the error below. I've checked for and deleted all of
> the other indexes so i'm not sure why it says a duplicate was found. I hav
e
> some other tables in the Database which I was able to set a primary key on
> but this table chokes each time. Help I'm stuck!
> 'Master' table
> - Unable to create index 'PK_Master'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CRE
ATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'type 6c, len 9'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not creat
e constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> --
> Rob
> IT guy!
>|||> I try running the query but it times out.
Where did you try running it? I suggest usng Query Analyzer, not Enterprise
Manager. You might also try
...
FROM MyTable WITH (NOLOCK)
...
http://www.aspfaq.com/
(Reverse address to reply.)|||Rob,
Try
select
Col1
, Col2
, count (*)
from
MyTable
where Col1 = 'type 6c, len 9'
group by
Col1
, Col2
having
count (*) > 1
This assumes that Col1 is the initial column of PK_master, the primary
key you are trying to set, since this value was provided in the error
message.
Steve Kass
Drew University
Rob wrote:
[vbcol=seagreen]
>Tom,
>I try running the query but it times out. The table is quite large.
>"Tom Moreau" wrote:
>
>|||You probably have no indexes on the table yet. Run it through QA and let it
finish.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:1960D77C-C7C8-40C7-B927-6FC1360C26DC@.microsoft.com...
Tom,
I try running the query but it times out. The table is quite large.
"Tom Moreau" wrote:

> Assuming your PK is comprised of columns Col1 and Col2, run the following:
> select
> Col1
> , Col2
> , count (*)
> from
> MyTable
> group by
> Col1
> , Col2
> having
> count (*) > 1
> This will reveal the duplicates.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
> Hi Guy's
> I've migrated an access DB of ours over to SQL 2000 and when I try to set
> the primary key I get the error below. I've checked for and deleted all of
> the other indexes so i'm not sure why it says a duplicate was found. I
have
> some other tables in the Database which I was able to set a primary key on
> but this table chokes each time. Help I'm stuck!
> 'Master' table
> - Unable to create index 'PK_Master'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CRE
ATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'type 6c, len 9'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create[/vbcol
]
constraint.[vbcol=seagreen]
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> --
> Rob
> IT guy!
>

Can't create Primary Key??

Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
Rob
IT guy!
Assuming your PK is compreised of columns Col1 and Col2, run the following:
select
Col1
, Col2
, count (*)
from
MyTable
group by
Col1
, Col2
having
count (*) > 1
This will reveal the duplicates.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
Rob
IT guy!
|||Assuming your PK is comprised of columns Col1 and Col2, run the following:
select
Col1
, Col2
, count (*)
from
MyTable
group by
Col1
, Col2
having
count (*) > 1
This will reveal the duplicates.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
Rob
IT guy!
|||Tom,
I try running the query but it times out. The table is quite large.
"Tom Moreau" wrote:

> Assuming your PK is comprised of columns Col1 and Col2, run the following:
> select
> Col1
> , Col2
> , count (*)
> from
> MyTable
> group by
> Col1
> , Col2
> having
> count (*) > 1
> This will reveal the duplicates.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
> Hi Guy's
> I've migrated an access DB of ours over to SQL 2000 and when I try to set
> the primary key I get the error below. I've checked for and deleted all of
> the other indexes so i'm not sure why it says a duplicate was found. I have
> some other tables in the Database which I was able to set a primary key on
> but this table chokes each time. Help I'm stuck!
> 'Master' table
> - Unable to create index 'PK_Master'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'type 6c, len 9'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> --
> Rob
> IT guy!
>
|||> I try running the query but it times out.
Where did you try running it? I suggest usng Query Analyzer, not Enterprise
Manager. You might also try
...
FROM MyTable WITH (NOLOCK)
...
http://www.aspfaq.com/
(Reverse address to reply.)
|||Rob,
Try
select
Col1
, Col2
, count (*)
from
MyTable
where Col1 = 'type 6c, len 9'
group by
Col1
, Col2
having
count (*) > 1
This assumes that Col1 is the initial column of PK_master, the primary
key you are trying to set, since this value was provided in the error
message.
Steve Kass
Drew University
Rob wrote:
[vbcol=seagreen]
>Tom,
>I try running the query but it times out. The table is quite large.
>"Tom Moreau" wrote:
>
|||You probably have no indexes on the table yet. Run it through QA and let it
finish.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:1960D77C-C7C8-40C7-B927-6FC1360C26DC@.microsoft.com...
Tom,
I try running the query but it times out. The table is quite large.
"Tom Moreau" wrote:

> Assuming your PK is comprised of columns Col1 and Col2, run the following:
> select
> Col1
> , Col2
> , count (*)
> from
> MyTable
> group by
> Col1
> , Col2
> having
> count (*) > 1
> This will reveal the duplicates.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
> Hi Guy's
> I've migrated an access DB of ours over to SQL 2000 and when I try to set
> the primary key I get the error below. I've checked for and deleted all of
> the other indexes so i'm not sure why it says a duplicate was found. I
have
> some other tables in the Database which I was able to set a primary key on
> but this table chokes each time. Help I'm stuck!
> 'Master' table
> - Unable to create index 'PK_Master'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'type 6c, len 9'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> --
> Rob
> IT guy!
>

Can't create Primary Key??

Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
--
Rob
IT guy!Assuming your PK is compreised of columns Col1 and Col2, run the following:
select
Col1
, Col2
, count (*)
from
MyTable
group by
Col1
, Col2
having
count (*) > 1
This will reveal the duplicates.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
--
Rob
IT guy!|||Assuming your PK is comprised of columns Col1 and Col2, run the following:
select
Col1
, Col2
, count (*)
from
MyTable
group by
Col1
, Col2
having
count (*) > 1
This will reveal the duplicates.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
Hi Guy's
I've migrated an access DB of ours over to SQL 2000 and when I try to set
the primary key I get the error below. I've checked for and deleted all of
the other indexes so i'm not sure why it says a duplicate was found. I have
some other tables in the Database which I was able to set a primary key on
but this table chokes each time. Help I'm stuck!
'Master' table
- Unable to create index 'PK_Master'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
INDEX terminated because a duplicate key was found for index ID 1. Most
significant primary key is 'type 6c, len 9'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
--
Rob
IT guy!|||Tom,
I try running the query but it times out. The table is quite large.
"Tom Moreau" wrote:
> Assuming your PK is comprised of columns Col1 and Col2, run the following:
> select
> Col1
> , Col2
> , count (*)
> from
> MyTable
> group by
> Col1
> , Col2
> having
> count (*) > 1
> This will reveal the duplicates.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
> Hi Guy's
> I've migrated an access DB of ours over to SQL 2000 and when I try to set
> the primary key I get the error below. I've checked for and deleted all of
> the other indexes so i'm not sure why it says a duplicate was found. I have
> some other tables in the Database which I was able to set a primary key on
> but this table chokes each time. Help I'm stuck!
> 'Master' table
> - Unable to create index 'PK_Master'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'type 6c, len 9'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> --
> Rob
> IT guy!
>|||> I try running the query but it times out.
Where did you try running it? I suggest usng Query Analyzer, not Enterprise
Manager. You might also try
...
FROM MyTable WITH (NOLOCK)
...
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Rob,
Try
select
Col1
, Col2
, count (*)
from
MyTable
where Col1 = 'type 6c, len 9'
group by
Col1
, Col2
having
count (*) > 1
This assumes that Col1 is the initial column of PK_master, the primary
key you are trying to set, since this value was provided in the error
message.
Steve Kass
Drew University
Rob wrote:
>Tom,
>I try running the query but it times out. The table is quite large.
>"Tom Moreau" wrote:
>
>>Assuming your PK is comprised of columns Col1 and Col2, run the following:
>>select
>> Col1
>>, Col2
>>, count (*)
>>from
>> MyTable
>>group by
>> Col1
>>, Col2
>>having
>> count (*) > 1
>>This will reveal the duplicates.
>>--
>>Tom
>>---
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>>"Rob" <Rob@.discussions.microsoft.com> wrote in message
>>news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
>>Hi Guy's
>>I've migrated an access DB of ours over to SQL 2000 and when I try to set
>>the primary key I get the error below. I've checked for and deleted all of
>>the other indexes so i'm not sure why it says a duplicate was found. I have
>>some other tables in the Database which I was able to set a primary key on
>>but this table chokes each time. Help I'm stuck!
>>'Master' table
>>- Unable to create index 'PK_Master'.
>>ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
>>INDEX terminated because a duplicate key was found for index ID 1. Most
>>significant primary key is 'type 6c, len 9'.
>>[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint.
>>See previous errors.
>>[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
>>terminated.
>>--
>>Rob
>>IT guy!
>>
>>|||You probably have no indexes on the table yet. Run it through QA and let it
finish.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:1960D77C-C7C8-40C7-B927-6FC1360C26DC@.microsoft.com...
Tom,
I try running the query but it times out. The table is quite large.
"Tom Moreau" wrote:
> Assuming your PK is comprised of columns Col1 and Col2, run the following:
> select
> Col1
> , Col2
> , count (*)
> from
> MyTable
> group by
> Col1
> , Col2
> having
> count (*) > 1
> This will reveal the duplicates.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:AA6A9681-B871-4E70-BF9B-6B5A10B74DCE@.microsoft.com...
> Hi Guy's
> I've migrated an access DB of ours over to SQL 2000 and when I try to set
> the primary key I get the error below. I've checked for and deleted all of
> the other indexes so i'm not sure why it says a duplicate was found. I
have
> some other tables in the Database which I was able to set a primary key on
> but this table chokes each time. Help I'm stuck!
> 'Master' table
> - Unable to create index 'PK_Master'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
> INDEX terminated because a duplicate key was found for index ID 1. Most
> significant primary key is 'type 6c, len 9'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint.
> See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> --
> Rob
> IT guy!
>

Can't create a backup job in SQL2005

I'm trying to create a simple backup job using the maintenance wizard in SQL
2005 and get the error below, anyone know what I'm doing wrong? (of course
the "For help click" link goes nowhere as usual)
Thanks
Maintenance Plan Wizard Progress
- Creating maintenance plan "MaintenancePlan" (Error)
Messages
Create maintenance plan failed.
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?Prod...ep&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?Prod...34&LinkId=20476
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "MaintenancePlan" (Stopped)Make sure both SQL Agent and SSIS services are running.
Andrew J. Kelly SQL MVP
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
> I'm trying to create a simple backup job using the maintenance wizard in
> SQL 2005 and get the error below, anyone know what I'm doing wrong? (of
> course the "For help click" link goes nowhere as usual)
> Thanks
> Maintenance Plan Wizard Progress
> - Creating maintenance plan "MaintenancePlan" (Error)
> Messages
> Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...ep&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...34&LinkId=20476
> - Adding tasks to the maintenance plan (Stopped)
> - Adding scheduling options (Stopped)
> - Adding reporting options (Stopped)
> - Saving maintenance plan "MaintenancePlan" (Stopped)
>|||Very weird, everything looked OK, but I completely un-installed SQL2005 and
re-installed it and everything works now, no differences in the two installs
that I know of.
Anyway, problem fixed now - thanks for the help
Peter Lawton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
> Make sure both SQL Agent and SSIS services are running.
> --
> Andrew J. Kelly SQL MVP
>
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>|||I spoke too soon, I'm getting exactly the same issue on a second server I've
just upgraded from SQL 2000 to SQL 2005
If I try to create a new maintenance job or migrate any of the legace
maintenace jobs it fails with the error:-
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
Error Number: 14234
Severity: 16
State: 1
Procedure: sp_verify_subsystem
Line Number: 21
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
> Very weird, everything looked OK, but I completely un-installed SQL2005
> and re-installed it and everything works now, no differences in the two
> installs that I know of.
> Anyway, problem fixed now - thanks for the help
> Peter Lawton
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
>|||OK, I've found the problem
Maintenance plans need "SQL Integration Services" installed to work.
Peter Lawton
"Peter Lawton" <devnull@.dummydomain.com> wrote in message
news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>I spoke too soon, I'm getting exactly the same issue on a second server
>I've just upgraded from SQL 2000 to SQL 2005
> If I try to create a new maintenance job or migrate any of the legace
> maintenace jobs it fails with the error:-
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
> Error Number: 14234
> Severity: 16
> State: 1
> Procedure: sp_verify_subsystem
> Line Number: 21
>
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
>|||Yes as I stated SQL Server Integration Services or "SSIS" for short must be
running. But by learning the hard way you won't have that problem again<g>.
Andrew J. Kelly SQL MVP
"Peter Lawton" <devnull@.dummydomain.com> wrote in message
news:uc2mfciBGHA.628@.TK2MSFTNGP14.phx.gbl...
> OK, I've found the problem
> Maintenance plans need "SQL Integration Services" installed to work.
> Peter Lawton
> "Peter Lawton" <devnull@.dummydomain.com> wrote in message
> news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>|||Yes, you're very right
I expect it's a mistake a lot of people will make though as it's not at all
obvious why an optional component is needed for basic tasks, and the error
message isn't at all helpful.
Peter Lawton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OuI9JcjBGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Yes as I stated SQL Server Integration Services or "SSIS" for short must
> be running. But by learning the hard way you won't have that problem
> again<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Peter Lawton" <devnull@.dummydomain.com> wrote in message
> news:uc2mfciBGHA.628@.TK2MSFTNGP14.phx.gbl...
>

Can't create a backup job in SQL2005

I'm trying to create a simple backup job using the maintenance wizard in SQL
2005 and get the error below, anyone know what I'm doing wrong? (of course
the "For help click" link goes nowhere as usual)
Thanks
Maintenance Plan Wizard Progress
- Creating maintenance plan "MaintenancePlan" (Error)
Messages
Create maintenance plan failed.
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?ProdN...p&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?ProdN...4&LinkId=20476
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "MaintenancePlan" (Stopped)
Make sure both SQL Agent and SSIS services are running.
Andrew J. Kelly SQL MVP
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
> I'm trying to create a simple backup job using the maintenance wizard in
> SQL 2005 and get the error below, anyone know what I'm doing wrong? (of
> course the "For help click" link goes nowhere as usual)
> Thanks
> Maintenance Plan Wizard Progress
> - Creating maintenance plan "MaintenancePlan" (Error)
> Messages
> Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...p&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...4&LinkId=20476
> - Adding tasks to the maintenance plan (Stopped)
> - Adding scheduling options (Stopped)
> - Adding reporting options (Stopped)
> - Saving maintenance plan "MaintenancePlan" (Stopped)
>
|||Very weird, everything looked OK, but I completely un-installed SQL2005 and
re-installed it and everything works now, no differences in the two installs
that I know of.
Anyway, problem fixed now - thanks for the help
Peter Lawton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
> Make sure both SQL Agent and SSIS services are running.
> --
> Andrew J. Kelly SQL MVP
>
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>
|||I spoke too soon, I'm getting exactly the same issue on a second server I've
just upgraded from SQL 2000 to SQL 2005
If I try to create a new maintenance job or migrate any of the legace
maintenace jobs it fails with the error:-
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
Error Number: 14234
Severity: 16
State: 1
Procedure: sp_verify_subsystem
Line Number: 21
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
> Very weird, everything looked OK, but I completely un-installed SQL2005
> and re-installed it and everything works now, no differences in the two
> installs that I know of.
> Anyway, problem fixed now - thanks for the help
> Peter Lawton
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
>
|||OK, I've found the problem
Maintenance plans need "SQL Integration Services" installed to work.
Peter Lawton
"Peter Lawton" <devnull@.dummydomain.com> wrote in message
news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>I spoke too soon, I'm getting exactly the same issue on a second server
>I've just upgraded from SQL 2000 to SQL 2005
> If I try to create a new maintenance job or migrate any of the legace
> maintenace jobs it fails with the error:-
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
> Error Number: 14234
> Severity: 16
> State: 1
> Procedure: sp_verify_subsystem
> Line Number: 21
>
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
>
|||Yes as I stated SQL Server Integration Services or "SSIS" for short must be
running. But by learning the hard way you won't have that problem again<g>.
Andrew J. Kelly SQL MVP
"Peter Lawton" <devnull@.dummydomain.com> wrote in message
news:uc2mfciBGHA.628@.TK2MSFTNGP14.phx.gbl...
> OK, I've found the problem
> Maintenance plans need "SQL Integration Services" installed to work.
> Peter Lawton
> "Peter Lawton" <devnull@.dummydomain.com> wrote in message
> news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>
|||Yes, you're very right
I expect it's a mistake a lot of people will make though as it's not at all
obvious why an optional component is needed for basic tasks, and the error
message isn't at all helpful.
Peter Lawton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OuI9JcjBGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Yes as I stated SQL Server Integration Services or "SSIS" for short must
> be running. But by learning the hard way you won't have that problem
> again<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Peter Lawton" <devnull@.dummydomain.com> wrote in message
> news:uc2mfciBGHA.628@.TK2MSFTNGP14.phx.gbl...
>

Can't create a backup job in SQL2005

I'm trying to create a simple backup job using the maintenance wizard in SQL
2005 and get the error below, anyone know what I'm doing wrong? (of course
the "For help click" link goes nowhere as usual)
Thanks
Maintenance Plan Wizard Progress
- Creating maintenance plan "MaintenancePlan" (Error)
Messages
Create maintenance plan failed.
--
ADDITIONAL INFORMATION:
Create failed for JobStep 'Subplan'.
(Microsoft.SqlServer.MaintenancePlanTasks)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
- Adding tasks to the maintenance plan (Stopped)
- Adding scheduling options (Stopped)
- Adding reporting options (Stopped)
- Saving maintenance plan "MaintenancePlan" (Stopped)Make sure both SQL Agent and SSIS services are running.
--
Andrew J. Kelly SQL MVP
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
> I'm trying to create a simple backup job using the maintenance wizard in
> SQL 2005 and get the error below, anyone know what I'm doing wrong? (of
> course the "For help click" link goes nowhere as usual)
> Thanks
> Maintenance Plan Wizard Progress
> - Creating maintenance plan "MaintenancePlan" (Error)
> Messages
> Create maintenance plan failed.
> --
> ADDITIONAL INFORMATION:
> Create failed for JobStep 'Subplan'.
> (Microsoft.SqlServer.MaintenancePlanTasks)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
> - Adding tasks to the maintenance plan (Stopped)
> - Adding scheduling options (Stopped)
> - Adding reporting options (Stopped)
> - Saving maintenance plan "MaintenancePlan" (Stopped)
>|||Very weird, everything looked OK, but I completely un-installed SQL2005 and
re-installed it and everything works now, no differences in the two installs
that I know of.
Anyway, problem fixed now - thanks for the help
Peter Lawton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
> Make sure both SQL Agent and SSIS services are running.
> --
> Andrew J. Kelly SQL MVP
>
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> I'm trying to create a simple backup job using the maintenance wizard in
>> SQL 2005 and get the error below, anyone know what I'm doing wrong? (of
>> course the "For help click" link goes nowhere as usual)
>> Thanks
>> Maintenance Plan Wizard Progress
>> - Creating maintenance plan "MaintenancePlan" (Error)
>> Messages
>> Create maintenance plan failed.
>> --
>> ADDITIONAL INFORMATION:
>> Create failed for JobStep 'Subplan'.
>> (Microsoft.SqlServer.MaintenancePlanTasks)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
>> --
>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>> - Adding tasks to the maintenance plan (Stopped)
>> - Adding scheduling options (Stopped)
>> - Adding reporting options (Stopped)
>> - Saving maintenance plan "MaintenancePlan" (Stopped)
>|||I spoke too soon, I'm getting exactly the same issue on a second server I've
just upgraded from SQL 2000 to SQL 2005
If I try to create a new maintenance job or migrate any of the legace
maintenace jobs it fails with the error:-
The specified '@.subsystem' is invalid (valid values are returned by
sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
Error Number: 14234
Severity: 16
State: 1
Procedure: sp_verify_subsystem
Line Number: 21
"Peter Lawton" <devnull@.fakedomain.com> wrote in message
news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
> Very weird, everything looked OK, but I completely un-installed SQL2005
> and re-installed it and everything works now, no differences in the two
> installs that I know of.
> Anyway, problem fixed now - thanks for the help
> Peter Lawton
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
>> Make sure both SQL Agent and SSIS services are running.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
>> news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> I'm trying to create a simple backup job using the maintenance wizard in
>> SQL 2005 and get the error below, anyone know what I'm doing wrong? (of
>> course the "For help click" link goes nowhere as usual)
>> Thanks
>> Maintenance Plan Wizard Progress
>> - Creating maintenance plan "MaintenancePlan" (Error)
>> Messages
>> Create maintenance plan failed.
>> --
>> ADDITIONAL INFORMATION:
>> Create failed for JobStep 'Subplan'.
>> (Microsoft.SqlServer.MaintenancePlanTasks)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
>> --
>> An exception occurred while executing a Transact-SQL statement or batch.
>> (Microsoft.SqlServer.ConnectionInfo)
>> --
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>> - Adding tasks to the maintenance plan (Stopped)
>> - Adding scheduling options (Stopped)
>> - Adding reporting options (Stopped)
>> - Saving maintenance plan "MaintenancePlan" (Stopped)
>>
>|||OK, I've found the problem :)
Maintenance plans need "SQL Integration Services" installed to work.
Peter Lawton
"Peter Lawton" <devnull@.dummydomain.com> wrote in message
news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>I spoke too soon, I'm getting exactly the same issue on a second server
>I've just upgraded from SQL 2000 to SQL 2005
> If I try to create a new maintenance job or migrate any of the legace
> maintenace jobs it fails with the error:-
> The specified '@.subsystem' is invalid (valid values are returned by
> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
> Error Number: 14234
> Severity: 16
> State: 1
> Procedure: sp_verify_subsystem
> Line Number: 21
>
> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
> news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Very weird, everything looked OK, but I completely un-installed SQL2005
>> and re-installed it and everything works now, no differences in the two
>> installs that I know of.
>> Anyway, problem fixed now - thanks for the help
>> Peter Lawton
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
>> Make sure both SQL Agent and SSIS services are running.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
>> news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> I'm trying to create a simple backup job using the maintenance wizard
>> in SQL 2005 and get the error below, anyone know what I'm doing wrong?
>> (of course the "For help click" link goes nowhere as usual)
>> Thanks
>> Maintenance Plan Wizard Progress
>> - Creating maintenance plan "MaintenancePlan" (Error)
>> Messages
>> Create maintenance plan failed.
>> --
>> ADDITIONAL INFORMATION:
>> Create failed for JobStep 'Subplan'.
>> (Microsoft.SqlServer.MaintenancePlanTasks)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
>> --
>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>> - Adding tasks to the maintenance plan (Stopped)
>> - Adding scheduling options (Stopped)
>> - Adding reporting options (Stopped)
>> - Saving maintenance plan "MaintenancePlan" (Stopped)
>>
>>
>|||Yes as I stated SQL Server Integration Services or "SSIS" for short must be
running. But by learning the hard way you won't have that problem again<g>.
--
Andrew J. Kelly SQL MVP
"Peter Lawton" <devnull@.dummydomain.com> wrote in message
news:uc2mfciBGHA.628@.TK2MSFTNGP14.phx.gbl...
> OK, I've found the problem :)
> Maintenance plans need "SQL Integration Services" installed to work.
> Peter Lawton
> "Peter Lawton" <devnull@.dummydomain.com> wrote in message
> news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>>I spoke too soon, I'm getting exactly the same issue on a second server
>>I've just upgraded from SQL 2000 to SQL 2005
>> If I try to create a new maintenance job or migrate any of the legace
>> maintenace jobs it fails with the error:-
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
>> Error Number: 14234
>> Severity: 16
>> State: 1
>> Procedure: sp_verify_subsystem
>> Line Number: 21
>>
>> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
>> news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Very weird, everything looked OK, but I completely un-installed SQL2005
>> and re-installed it and everything works now, no differences in the two
>> installs that I know of.
>> Anyway, problem fixed now - thanks for the help
>> Peter Lawton
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
>> Make sure both SQL Agent and SSIS services are running.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
>> news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> I'm trying to create a simple backup job using the maintenance wizard
>> in SQL 2005 and get the error below, anyone know what I'm doing wrong?
>> (of course the "For help click" link goes nowhere as usual)
>> Thanks
>> Maintenance Plan Wizard Progress
>> - Creating maintenance plan "MaintenancePlan" (Error)
>> Messages
>> Create maintenance plan failed.
>> --
>> ADDITIONAL INFORMATION:
>> Create failed for JobStep 'Subplan'.
>> (Microsoft.SqlServer.MaintenancePlanTasks)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
>> --
>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>> - Adding tasks to the maintenance plan (Stopped)
>> - Adding scheduling options (Stopped)
>> - Adding reporting options (Stopped)
>> - Saving maintenance plan "MaintenancePlan" (Stopped)
>>
>>
>>
>|||Yes, you're very right :)
I expect it's a mistake a lot of people will make though as it's not at all
obvious why an optional component is needed for basic tasks, and the error
message isn't at all helpful.
Peter Lawton
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OuI9JcjBGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Yes as I stated SQL Server Integration Services or "SSIS" for short must
> be running. But by learning the hard way you won't have that problem
> again<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Peter Lawton" <devnull@.dummydomain.com> wrote in message
> news:uc2mfciBGHA.628@.TK2MSFTNGP14.phx.gbl...
>> OK, I've found the problem :)
>> Maintenance plans need "SQL Integration Services" installed to work.
>> Peter Lawton
>> "Peter Lawton" <devnull@.dummydomain.com> wrote in message
>> news:%23b2pOPiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
>>I spoke too soon, I'm getting exactly the same issue on a second server
>>I've just upgraded from SQL 2000 to SQL 2005
>> If I try to create a new maintenance job or migrate any of the legace
>> maintenace jobs it fails with the error:-
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (.Net SqlClient Data Provider)
>> Error Number: 14234
>> Severity: 16
>> State: 1
>> Procedure: sp_verify_subsystem
>> Line Number: 21
>>
>> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
>> news:%23MAERcaBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Very weird, everything looked OK, but I completely un-installed SQL2005
>> and re-installed it and everything works now, no differences in the two
>> installs that I know of.
>> Anyway, problem fixed now - thanks for the help
>> Peter Lawton
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJKecXZBGHA.3396@.tk2msftngp13.phx.gbl...
>> Make sure both SQL Agent and SSIS services are running.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Peter Lawton" <devnull@.fakedomain.com> wrote in message
>> news:u%23%23KJOZBGHA.3984@.TK2MSFTNGP14.phx.gbl...
>> I'm trying to create a simple backup job using the maintenance wizard
>> in SQL 2005 and get the error below, anyone know what I'm doing
>> wrong? (of course the "For help click" link goes nowhere as usual)
>> Thanks
>> Maintenance Plan Wizard Progress
>> - Creating maintenance plan "MaintenancePlan" (Error)
>> Messages
>> Create maintenance plan failed.
>> --
>> ADDITIONAL INFORMATION:
>> Create failed for JobStep 'Subplan'.
>> (Microsoft.SqlServer.MaintenancePlanTasks)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+JobStep&LinkId=20476
>> --
>> An exception occurred while executing a Transact-SQL statement or
>> batch. (Microsoft.SqlServer.ConnectionInfo)
>> --
>> The specified '@.subsystem' is invalid (valid values are returned by
>> sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error: 14234)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=14234&LinkId=20476
>> - Adding tasks to the maintenance plan (Stopped)
>> - Adding scheduling options (Stopped)
>> - Adding reporting options (Stopped)
>> - Saving maintenance plan "MaintenancePlan" (Stopped)
>>
>>
>>
>>
>

Cant connect to SQL Server 2000

I can't connect my jsp file to SQL Server 2000. Below is my code and the error result that i got.. Please help me to solve it..I have installed the driver and write them in the classpath..

<%@.page import="java.sql.*"%>
<%
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
try {
// Open Database connection
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://sansan:1433;databasename= coffeepassion","sa","sa");

// Query Database (all queries use the same connection)
String sql = "SELECT EmpID, EmpPassword FROM EmpLogin";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("sql");

// Print header and each record returned
out.println("List of items in the database:");
while(rs.next())
out.println(rs.getString("EmpID") + " " +rs.getString("EmpPassword"));
// Finished using the database instances
rs.close();
stmt.close();
con.close();
}

catch (Exception e) {
out.println(e.toString()); // Error message to display
}
%>

Error Result:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot open database requested in login ' coffeepassion'. Login fails.I don't have a lot of experience with jdbc, but here goes nothing:

1. Should there be a space in between "database=" and "coffeepassion"?
2. Can you make a connection from the web server to the database server using a .udl file (make a file, call it anything.udl, start it and use another driver type to connect)?\
3. Are you sure you have the right password?

One other thought; please consider using a different account to connect to SQL server. Once you've started down the development path using the sa account, it gets very difficult to make the decision to change to using a different account. Using sa leaves you exposed to all sorts of mischief.

Regards,

hmscott

I can't connect my jsp file to SQL Server 2000. Below is my code and the error result that i got.. Please help me to solve it..I have installed the driver and write them in the classpath..

<%@.page import="java.sql.*"%>
<%
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
try {
// Open Database connection
Connection con = DriverManager.getConnection("jdbc:microsoft:sqlserver://sansan:1433;databasename= coffeepassion","sa","sa");

// Query Database (all queries use the same connection)
String sql = "SELECT EmpID, EmpPassword FROM EmpLogin";
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("sql");

// Print header and each record returned
out.println("List of items in the database:");
while(rs.next())
out.println(rs.getString("EmpID") + " " +rs.getString("EmpPassword"));
// Finished using the database instances
rs.close();
stmt.close();
con.close();
}

catch (Exception e) {
out.println(e.toString()); // Error message to display
}
%>

Error Result:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot open database requested in login ' coffeepassion'. Login fails.|||dear hmscott,

i've remove the space in the 'databasename= coffeepassion'..and get another error:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.

Can't connect to server

I reinstall sql server develope edition. Then I try to connect the server from another computer, it shows below infomation. But in server property, it allows remote connection. I don't know whether it was caused by reinstall. Before reinstall, I can connect to the server, but it's edtion is group version. And edition for client is also group version. Any one can help me? Thank you very much!

TITLE: Connect to Server

Cannot connect to *.*.*.*.

ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

BUTTONS:

OK

Hi,

Have you checked if the Named Pipe and TCP/IP are enabled? Ensure firewall rules are in proper manner!!1

Also refer NET DDE and NET DDE DSDM and SQL Browser Service are started in Control Panel -> Administrative Tools-> Services

Hemantgiri S. Goswami

|||Now I'm reinstalling again. If issue still exists after that, I'll take your suggestion. Thank you!|||After reinstall, issue has been solved.|||

Hi,

Thanks for the information , nice to know that your issue has been resolve.

Hemantgiri S. Goswami

cant connect to Oracle

Hi all,

Being that so far I didn't get an answer to my below problem on the
Oracle newsgroup, I figured it couldn't hurt to try here.

While at my current job I've been working with mostly SQL Server, now
I need to connect to our Oracle Financials running on HP-UX. The
Oracle DBA set me up as a user and installed the Oracle client on my
box and I have the tnsnames file, but I still can't establish a
connection to the database from my development environment... I'm
getting an error message "ORA-12224 TNS:no listener".

At a previous company I also had Visual Studio and was able to connect
to the Oracle databases from it... I want to do the same thing here,
plus I need to set the Oracle database up as a linked server with my
SQL Servers so I can make distributed transactions. (No heart
attacks please, I'm going to create a couple of tables on the Oracle
side and the Oracle DBA will put triggers on them so that whenever I
insert to them he grabs the data and feeds it to the canned processes
in OF.)

The Oracle DBA gave me a little utility with a window where I can type
freehand SQL and get results but not nicely, so I know the connection
isn't impossible, but I need to be able to address the Oracle database
programmatically. I thought the problem might be related to the fact
that I don't see the unix box in Network, although I can ping it
either by name or IP address... the Oracle guy said for me to see it
in Network he'd have to install something I forget the name of.
(Something like NFS?) Someone suggested I put an entry in the hosts
file, which I did, to no avail... although I think it's in the wrong
place, it's in WINNT\System32\Drivers\etc. The Oracle guy says he
thinks the problem is that the MS OLE DB Provider for Oracle doesn't
know to look in the tnsnames file, but I find that difficult to
believe. He said maybe somewhere I need to enter a "path" to it...
(I vaguely remember something about setting paths back in the DOS
days...)

So anyway, can any of you tell me what to do?

Thanks very much in advance. :)Ellen K. wrote:
> Hi all,
> Being that so far I didn't get an answer to my below problem on the
> Oracle newsgroup, I figured it couldn't hurt to try here.
> While at my current job I've been working with mostly SQL Server, now
> I need to connect to our Oracle Financials running on HP-UX. The
> Oracle DBA set me up as a user and installed the Oracle client on my
> box and I have the tnsnames file, but I still can't establish a
> connection to the database from my development environment... I'm
> getting an error message "ORA-12224 TNS:no listener".
> At a previous company I also had Visual Studio and was able to connect
> to the Oracle databases from it... I want to do the same thing here,
> plus I need to set the Oracle database up as a linked server with my
> SQL Servers so I can make distributed transactions. (No heart
> attacks please, I'm going to create a couple of tables on the Oracle
> side and the Oracle DBA will put triggers on them so that whenever I
> insert to them he grabs the data and feeds it to the canned processes
> in OF.)
> The Oracle DBA gave me a little utility with a window where I can type
> freehand SQL and get results but not nicely, so I know the connection
> isn't impossible, but I need to be able to address the Oracle database
> programmatically. I thought the problem might be related to the fact
> that I don't see the unix box in Network, although I can ping it
> either by name or IP address... the Oracle guy said for me to see it
> in Network he'd have to install something I forget the name of.
> (Something like NFS?) Someone suggested I put an entry in the hosts
> file, which I did, to no avail... although I think it's in the wrong
> place, it's in WINNT\System32\Drivers\etc. The Oracle guy says he
> thinks the problem is that the MS OLE DB Provider for Oracle doesn't
> know to look in the tnsnames file, but I find that difficult to
> believe. He said maybe somewhere I need to enter a "path" to it...
> (I vaguely remember something about setting paths back in the DOS
> days...)
> So anyway, can any of you tell me what to do?
> Thanks very much in advance. :)

Why would you think anyone could answer your question anywhere?

What version of Oracle?
What hardware platform and operating is it running on?
What operating system are you running?
Can you ping the Oracle server?
An ORA-12224 could have been investigated at www.google.com with ease

And you would have found that the problem is that you have not properly
configured the environment variables oracle_home, oracle_sid and/or the
files sqlnet.ora and tnsnames.ora

Did you read the installation and configuration documentation or did you
just stick the CD into the drive? My guess is that you can't even ping
the server.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||Oracle 8.0something, it is on HP-UX (mentioned in my original post),
client is W2K (Windows something was obvious from the fact that I
mentioned wanting to access Oracle from Visual Studio), yes I can ping
the Oracle server either by name or IP address (also mentioned in my
original post). What I found on google didn't help.

Configuring the environment etc ain't my responsibility, we have an
Oracle DBA... also mentioned in my original post.

And I never touched the CD, the Oracle DBA did it.

So anyway, not only your guess, but most of your allegations, are
wrong.

The Oracle DBA made some modifications to the tnsnames file and did
some other stuff and now 12224 went away and we have 12514 claiming it
can't find RDBMS80.

This is beyond ridiculous, I can set up a user on any of my SQL Server
boxes in less than a minute, including defining all their permissions.

On Thu, 13 May 2004 20:01:32 -0700, Daniel Morgan
<damorgan@.x.washington.edu> wrote:

>Why would you think anyone could answer your question anywhere?
>What version of Oracle?
>What hardware platform and operating is it running on?
>What operating system are you running?
>Can you ping the Oracle server?
>An ORA-12224 could have been investigated at www.google.com with ease
>And you would have found that the problem is that you have not properly
>configured the environment variables oracle_home, oracle_sid and/or the
>files sqlnet.ora and tnsnames.ora
>Did you read the installation and configuration documentation or did you
>just stick the CD into the drive? My guess is that you can't even ping
>the server.|||>The Oracle DBA gave me a little utility with a window where I can type
>freehand SQL and get results but not nicely

So it's working fine, right? The Oracle tools are talking to the Oracle
database.

>I still can't establish a
>connection to the database from my development environment

Try asking in a development newsgroup - your oracle is running fine, it;s
your development tool that is not happy.

>This is beyond ridiculous, I can set up a user on any of my SQL Server
>boxes in less than a minute, including defining all their permissions.

Yes, I have heard the MS-ACCESS is even simpler! Try getting your finance
department to switch to that and your life will be much easier.

Certainly much easier than reading book, or researching the problem with
your development environment.

JON

P.S. It's probably in your connect string, most likely not matching up
exactly with what is defined in the tnsnames file. Add a 2nd entry tnsnames
that is a clone of the first, but with the domain name after it, i.e. just
dbname, not dbname.server.facility.org. Who knows what weird string your
development environment is appending to the connect string.

"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
news:adr4a0dleii63sl2up0f7rkf9ctgbt20vc@.4ax.com...
> Hi all,
> Being that so far I didn't get an answer to my below problem on the
> Oracle newsgroup, I figured it couldn't hurt to try here.
> While at my current job I've been working with mostly SQL Server, now
> I need to connect to our Oracle Financials running on HP-UX. The
> Oracle DBA set me up as a user and installed the Oracle client on my
> box and I have the tnsnames file, but I still can't establish a
> connection to the database from my development environment... I'm
> getting an error message "ORA-12224 TNS:no listener".
> At a previous company I also had Visual Studio and was able to connect
> to the Oracle databases from it... I want to do the same thing here,
> plus I need to set the Oracle database up as a linked server with my
> SQL Servers so I can make distributed transactions. (No heart
> attacks please, I'm going to create a couple of tables on the Oracle
> side and the Oracle DBA will put triggers on them so that whenever I
> insert to them he grabs the data and feeds it to the canned processes
> in OF.)
> The Oracle DBA gave me a little utility with a window where I can type
> freehand SQL and get results but not nicely, so I know the connection
> isn't impossible, but I need to be able to address the Oracle database
> programmatically. I thought the problem might be related to the fact
> that I don't see the unix box in Network, although I can ping it
> either by name or IP address... the Oracle guy said for me to see it
> in Network he'd have to install something I forget the name of.
> (Something like NFS?) Someone suggested I put an entry in the hosts
> file, which I did, to no avail... although I think it's in the wrong
> place, it's in WINNT\System32\Drivers\etc. The Oracle guy says he
> thinks the problem is that the MS OLE DB Provider for Oracle doesn't
> know to look in the tnsnames file, but I find that difficult to
> believe. He said maybe somewhere I need to enter a "path" to it...
> (I vaguely remember something about setting paths back in the DOS
> days...)
> So anyway, can any of you tell me what to do?
> Thanks very much in advance. :)|||Considering that I had zero problems connecting to MULTIPLE Oracle
databases from VisualStudio at my previous job, I think it's safe to
say the problem is not with VisualStudio.

The Oracle DBA modified the tnsnames file and the result was that the
listener is now found, but not RDBMS80. I will pass on your
suggestion about the additional entry.

On Fri, 14 May 2004 18:39:23 GMT, "Onlynews"
<jonkimball@.email.uophx.edu> wrote:

>>The Oracle DBA gave me a little utility with a window where I can type
>>freehand SQL and get results but not nicely
>So it's working fine, right? The Oracle tools are talking to the Oracle
>database.
>>I still can't establish a
>>connection to the database from my development environment
>Try asking in a development newsgroup - your oracle is running fine, it;s
>your development tool that is not happy.
>>This is beyond ridiculous, I can set up a user on any of my SQL Server
>>boxes in less than a minute, including defining all their permissions.
>Yes, I have heard the MS-ACCESS is even simpler! Try getting your finance
>department to switch to that and your life will be much easier.
>Certainly much easier than reading book, or researching the problem with
>your development environment.
>JON
>P.S. It's probably in your connect string, most likely not matching up
>exactly with what is defined in the tnsnames file. Add a 2nd entry tnsnames
>that is a clone of the first, but with the domain name after it, i.e. just
>dbname, not dbname.server.facility.org. Who knows what weird string your
>development environment is appending to the connect string.
>
>"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
>news:adr4a0dleii63sl2up0f7rkf9ctgbt20vc@.4ax.com...
>> Hi all,
>>
>> Being that so far I didn't get an answer to my below problem on the
>> Oracle newsgroup, I figured it couldn't hurt to try here.
>>
>> While at my current job I've been working with mostly SQL Server, now
>> I need to connect to our Oracle Financials running on HP-UX. The
>> Oracle DBA set me up as a user and installed the Oracle client on my
>> box and I have the tnsnames file, but I still can't establish a
>> connection to the database from my development environment... I'm
>> getting an error message "ORA-12224 TNS:no listener".
>>
>> At a previous company I also had Visual Studio and was able to connect
>> to the Oracle databases from it... I want to do the same thing here,
>> plus I need to set the Oracle database up as a linked server with my
>> SQL Servers so I can make distributed transactions. (No heart
>> attacks please, I'm going to create a couple of tables on the Oracle
>> side and the Oracle DBA will put triggers on them so that whenever I
>> insert to them he grabs the data and feeds it to the canned processes
>> in OF.)
>>
>> The Oracle DBA gave me a little utility with a window where I can type
>> freehand SQL and get results but not nicely, so I know the connection
>> isn't impossible, but I need to be able to address the Oracle database
>> programmatically. I thought the problem might be related to the fact
>> that I don't see the unix box in Network, although I can ping it
>> either by name or IP address... the Oracle guy said for me to see it
>> in Network he'd have to install something I forget the name of.
>> (Something like NFS?) Someone suggested I put an entry in the hosts
>> file, which I did, to no avail... although I think it's in the wrong
>> place, it's in WINNT\System32\Drivers\etc. The Oracle guy says he
>> thinks the problem is that the MS OLE DB Provider for Oracle doesn't
>> know to look in the tnsnames file, but I find that difficult to
>> believe. He said maybe somewhere I need to enter a "path" to it...
>> (I vaguely remember something about setting paths back in the DOS
>> days...)
>>
>> So anyway, can any of you tell me what to do?
>>
>> Thanks very much in advance. :)