I just want to insert true value in a bit field
does anybody know why this is not working
Function Main()
DTSDestination("Active") = True
Main = DTSTransformStat_OK
End Function
Also tryed this
Function Main()
DTSDestination("Active") = 1
Main = DTSTransformStat_OK
End Function
the error is "unspecified error"
Please does somebody know what the problem isMay check this KBA (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q196/9/59.asp&NoWebContent=1) reference.
Showing posts with label bit. Show all posts
Showing posts with label bit. Show all posts
Wednesday, March 28, 2012
problem with bit datatype conversion
Hi All,
I have create following table and inserted few records
Code Snippet
USE
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_position](
[part] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[price] [money] NOT NULL,
[opt] [bit] NOT NULL
) ON [PRIMARY]
I am executing following procedure to update the table.
Code Snippet
USE
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PROC1] ( @.part varchar(20), @.PRICE MONEY, @.OPT BIT )
AS
BEGIN
declare @.sSQL as nvarchar(max);
SET @.sSQL = 'update tbl_position set opt=@.OPT,price=@.PRICE WHERE part = '+ @.part;
exec sp_executesql @.sSQL
END
ex:
Code Snippet
exec PROC1 1,2.50,0but I am getting following error
Code Snippet
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@.OPT".
I am struck up with this query .
Please Help me regarding this issue.
Regards
Gomaz
Code Snippet
CREATE PROCEDURE [dbo].[PROC1] ( @.part varchar(20), @.PRICE MONEY, @.OPT BIT )
AS
BEGIN
declare @.sSQL as nvarchar(max);
SET @.sSQL = 'update tbl_position set opt=' +cast(@.OPT as char(1) )+',price='+@.PRICE+' WHERE part = '+ @.part;
exec sp_executesql @.sSQL
END
sp_executesql execute query in different bacth. As you couldn't use opt=@.OPT
Friday, March 23, 2012
Problem with ADO
Hello,
First of all, excuse me for my very poor english : I just "speak" a bit of
english...
We recently have a problem with some PCs and ADO : we can connect our
programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC,
but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
(SP2). Our programs written in C# or VB.Net which use ADO.Net work perfectly
with the databases. But our programs written in "old" VB6 can't connect to
the same
database on the same computer (*) It's not the case for all the PCs, just
for a small minority... For these minority, the problem concerns third
partie products too... And with XP SP2, whe can't try to reinstall MDAC,
because Windows rejects the reinstall...
Any idea ?
Thanx in advance !!!
(*) We are sure it isn't a problem of connect string, you can forget this
reason ;-)Try to use the OLE DB native provider for SQL Server. In this case the MDAC
stack is not required. See SNAC or "SQL Server Native Client" in online help
. It's designed to access SQL Server 2005 instances and all of their new fea
tures.
I would also use the Component Checker tool (downloadable from here). It wou
ld also help to review this.
Clearly, the reason some computers can connect and others cannot is a differ
ence in the systems... ;) The Component Checker can show the differences in
the myriad of MDAC layered components installed. This problem is (partially)
alleviated with the SNAC provider.
I discuss these issues in my book "ADO.NET Examples and Best Practices".
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"News" <news@.invalid> wrote in message news:466d1e93$0$28887$426a34cc@.news.free.fr...seagreen">
> Hello,
>
> First of all, excuse me for my very poor english : I just "speak" a bit of
> english...
>
> We recently have a problem with some PCs and ADO : we can connect our
> programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC
,
> but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
> (SP2). Our programs written in C# or VB.Net which use ADO.Net work perfect
ly
> with the databases. But our programs written in "old" VB6 can't connect to
> the same
> database on the same computer (*) It's not the case for all the PCs, just
> for a small minority... For these minority, the problem concerns third
> partie products too... And with XP SP2, whe can't try to reinstall MDAC,
> because Windows rejects the reinstall...
>
> Any idea ?
>
> Thanx in advance !!!
>
> (*) We are sure it isn't a problem of connect string, you can forget this
> reason ;-)
>
>
>|||It works with SNAC !
Thank you for your help !
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> a crit dans le message
de news: eVugpLErHHA.5032@.TK2MSFTNGP02.phx.gbl...
Try to use the OLE DB native provider for SQL Server. In this case the MDAC
stack is not required. See SNAC or "SQL Server Native Client" in online help
. It's designed to access SQL Server 2005 instances and all of their new fea
tures.
I would also use the Component Checker tool (downloadable from here). It wou
ld also help to review this.
Clearly, the reason some computers can connect and others cannot is a differ
ence in the systems... ;) The Component Checker can show the differences in
the myriad of MDAC layered components installed. This problem is (partially)
alleviated with the SNAC provider.
I discuss these issues in my book "ADO.NET Examples and Best Practices".
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"News" <news@.invalid> wrote in message news:466d1e93$0$28887$426a34cc@.news.free.fr...seagreen">
> Hello,
>
> First of all, excuse me for my very poor english : I just "speak" a bit of
> english...
>
> We recently have a problem with some PCs and ADO : we can connect our
> programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC
,
> but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
> (SP2). Our programs written in C# or VB.Net which use ADO.Net work perfect
ly
> with the databases. But our programs written in "old" VB6 can't connect to
> the same
> database on the same computer (*) It's not the case for all the PCs, just
> for a small minority... For these minority, the problem concerns third
> partie products too... And with XP SP2, whe can't try to reinstall MDAC,
> because Windows rejects the reinstall...
>
> Any idea ?
>
> Thanx in advance !!!
>
> (*) We are sure it isn't a problem of connect string, you can forget this
> reason ;-)
>
>
>|||Thanks for letting us know. I'll send you a bill. ;)
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"News" <news@.invalid> wrote in message news:466e8f95$0$14995$426a34cc@.news.f
ree.fr...
It works with SNAC !
Thank you for your help !
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> a crit dans le message
de news: eVugpLErHHA.5032@.TK2MSFTNGP02.phx.gbl...
Try to use the OLE DB native provider for SQL Server. In this case the MDAC
stack is not required. See SNAC or "SQL Server Native Client" in online help
. It's designed to access SQL Server 2005 instances and all of their new fea
tures.
I would also use the Component Checker tool (downloadable from here). It wou
ld also help to review this.
Clearly, the reason some computers can connect and others cannot is a differ
ence in the systems... ;) The Component Checker can show the differences in
the myriad of MDAC layered components installed. This problem is (partially)
alleviated with the SNAC provider.
I discuss these issues in my book "ADO.NET Examples and Best Practices".
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"News" <news@.invalid> wrote in message news:466d1e93$0$28887$426a34cc@.news.free.fr...seagreen">
> Hello,
>
> First of all, excuse me for my very poor english : I just "speak" a bit of
> english...
>
> We recently have a problem with some PCs and ADO : we can connect our
> programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC
,
> but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
> (SP2). Our programs written in C# or VB.Net which use ADO.Net work perfect
ly
> with the databases. But our programs written in "old" VB6 can't connect to
> the same
> database on the same computer (*) It's not the case for all the PCs, just
> for a small minority... For these minority, the problem concerns third
> partie products too... And with XP SP2, whe can't try to reinstall MDAC,
> because Windows rejects the reinstall...
>
> Any idea ?
>
> Thanx in advance !!!
>
> (*) We are sure it isn't a problem of connect string, you can forget this
> reason ;-)
>
>
>
First of all, excuse me for my very poor english : I just "speak" a bit of
english...
We recently have a problem with some PCs and ADO : we can connect our
programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC,
but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
(SP2). Our programs written in C# or VB.Net which use ADO.Net work perfectly
with the databases. But our programs written in "old" VB6 can't connect to
the same
database on the same computer (*) It's not the case for all the PCs, just
for a small minority... For these minority, the problem concerns third
partie products too... And with XP SP2, whe can't try to reinstall MDAC,
because Windows rejects the reinstall...
Any idea ?
Thanx in advance !!!
(*) We are sure it isn't a problem of connect string, you can forget this
reason ;-)Try to use the OLE DB native provider for SQL Server. In this case the MDAC
stack is not required. See SNAC or "SQL Server Native Client" in online help
. It's designed to access SQL Server 2005 instances and all of their new fea
tures.
I would also use the Component Checker tool (downloadable from here). It wou
ld also help to review this.
Clearly, the reason some computers can connect and others cannot is a differ
ence in the systems... ;) The Component Checker can show the differences in
the myriad of MDAC layered components installed. This problem is (partially)
alleviated with the SNAC provider.
I discuss these issues in my book "ADO.NET Examples and Best Practices".
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"News" <news@.invalid> wrote in message news:466d1e93$0$28887$426a34cc@.news.free.fr...seagreen">
> Hello,
>
> First of all, excuse me for my very poor english : I just "speak" a bit of
> english...
>
> We recently have a problem with some PCs and ADO : we can connect our
> programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC
,
> but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
> (SP2). Our programs written in C# or VB.Net which use ADO.Net work perfect
ly
> with the databases. But our programs written in "old" VB6 can't connect to
> the same
> database on the same computer (*) It's not the case for all the PCs, just
> for a small minority... For these minority, the problem concerns third
> partie products too... And with XP SP2, whe can't try to reinstall MDAC,
> because Windows rejects the reinstall...
>
> Any idea ?
>
> Thanx in advance !!!
>
> (*) We are sure it isn't a problem of connect string, you can forget this
> reason ;-)
>
>
>|||It works with SNAC !
Thank you for your help !
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> a crit dans le message
de news: eVugpLErHHA.5032@.TK2MSFTNGP02.phx.gbl...
Try to use the OLE DB native provider for SQL Server. In this case the MDAC
stack is not required. See SNAC or "SQL Server Native Client" in online help
. It's designed to access SQL Server 2005 instances and all of their new fea
tures.
I would also use the Component Checker tool (downloadable from here). It wou
ld also help to review this.
Clearly, the reason some computers can connect and others cannot is a differ
ence in the systems... ;) The Component Checker can show the differences in
the myriad of MDAC layered components installed. This problem is (partially)
alleviated with the SNAC provider.
I discuss these issues in my book "ADO.NET Examples and Best Practices".
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"News" <news@.invalid> wrote in message news:466d1e93$0$28887$426a34cc@.news.free.fr...seagreen">
> Hello,
>
> First of all, excuse me for my very poor english : I just "speak" a bit of
> english...
>
> We recently have a problem with some PCs and ADO : we can connect our
> programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC
,
> but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
> (SP2). Our programs written in C# or VB.Net which use ADO.Net work perfect
ly
> with the databases. But our programs written in "old" VB6 can't connect to
> the same
> database on the same computer (*) It's not the case for all the PCs, just
> for a small minority... For these minority, the problem concerns third
> partie products too... And with XP SP2, whe can't try to reinstall MDAC,
> because Windows rejects the reinstall...
>
> Any idea ?
>
> Thanx in advance !!!
>
> (*) We are sure it isn't a problem of connect string, you can forget this
> reason ;-)
>
>
>|||Thanks for letting us know. I'll send you a bill. ;)
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"News" <news@.invalid> wrote in message news:466e8f95$0$14995$426a34cc@.news.f
ree.fr...
It works with SNAC !
Thank you for your help !
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> a crit dans le message
de news: eVugpLErHHA.5032@.TK2MSFTNGP02.phx.gbl...
Try to use the OLE DB native provider for SQL Server. In this case the MDAC
stack is not required. See SNAC or "SQL Server Native Client" in online help
. It's designed to access SQL Server 2005 instances and all of their new fea
tures.
I would also use the Component Checker tool (downloadable from here). It wou
ld also help to review this.
Clearly, the reason some computers can connect and others cannot is a differ
ence in the systems... ;) The Component Checker can show the differences in
the myriad of MDAC layered components installed. This problem is (partially)
alleviated with the SNAC provider.
I discuss these issues in my book "ADO.NET Examples and Best Practices".
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"News" <news@.invalid> wrote in message news:466d1e93$0$28887$426a34cc@.news.free.fr...seagreen">
> Hello,
>
> First of all, excuse me for my very poor english : I just "speak" a bit of
> english...
>
> We recently have a problem with some PCs and ADO : we can connect our
> programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC
,
> but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
> (SP2). Our programs written in C# or VB.Net which use ADO.Net work perfect
ly
> with the databases. But our programs written in "old" VB6 can't connect to
> the same
> database on the same computer (*) It's not the case for all the PCs, just
> for a small minority... For these minority, the problem concerns third
> partie products too... And with XP SP2, whe can't try to reinstall MDAC,
> because Windows rejects the reinstall...
>
> Any idea ?
>
> Thanx in advance !!!
>
> (*) We are sure it isn't a problem of connect string, you can forget this
> reason ;-)
>
>
>
Problem with ADO
Hello,
First of all, excuse me for my very poor english : I just "speak" a bit of
english...
We recently have a problem with some PCs and ADO : we can connect our
programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC,
but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
(SP2). Our programs written in C# or VB.Net which use ADO.Net work perfectly
with the databases. But our programs written in "old" VB6 can't connect to
the same
database on the same computer (*) It's not the case for all the PCs, just
for a small minority... For these minority, the problem concerns third
partie products too... And with XP SP2, whe can't try to reinstall MDAC,
because Windows rejects the reinstall...
Any idea ?
Thanx in advance !!!
(*) We are sure it isn't a problem of connect string, you can forget this
reason ;-)
Try to use the OLE DB native provider for SQL Server. In this case the MDAC stack is not required. See SNAC or "SQL Server Native Client" in online help. It's designed to access SQL Server 2005 instances and all of their new features.
I would also use the Component Checker tool (downloadable from here). It would also help to review this.
Clearly, the reason some computers can connect and others cannot is a difference in the systems... ;) The Component Checker can show the differences in the myriad of MDAC layered components installed. This problem is (partially) alleviated with the SNAC provider.
I discuss these issues in my book "ADO.NET Examples and Best Practices".
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"News" <news@.invalid> wrote in message news:466d1e93$0$28887$426a34cc@.news.free.fr...
> Hello,
> First of all, excuse me for my very poor english : I just "speak" a bit of
> english...
> We recently have a problem with some PCs and ADO : we can connect our
> programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC,
> but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
> (SP2). Our programs written in C# or VB.Net which use ADO.Net work perfectly
> with the databases. But our programs written in "old" VB6 can't connect to
> the same
> database on the same computer (*) It's not the case for all the PCs, just
> for a small minority... For these minority, the problem concerns third
> partie products too... And with XP SP2, whe can't try to reinstall MDAC,
> because Windows rejects the reinstall...
> Any idea ?
> Thanx in advance !!!
> (*) We are sure it isn't a problem of connect string, you can forget this
> reason ;-)
>
>
|||It works with SNAC !
Thank you for your help !
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> a crit dans le message de news: eVugpLErHHA.5032@.TK2MSFTNGP02.phx.gbl...
Try to use the OLE DB native provider for SQL Server. In this case the MDAC stack is not required. See SNAC or "SQL Server Native Client" in online help. It's designed to access SQL Server 2005 instances and all of their new features.
I would also use the Component Checker tool (downloadable from here). It would also help to review this.
Clearly, the reason some computers can connect and others cannot is a difference in the systems... ;) The Component Checker can show the differences in the myriad of MDAC layered components installed. This problem is (partially) alleviated with the SNAC provider.
I discuss these issues in my book "ADO.NET Examples and Best Practices".
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"News" <news@.invalid> wrote in message news:466d1e93$0$28887$426a34cc@.news.free.fr...
> Hello,
> First of all, excuse me for my very poor english : I just "speak" a bit of
> english...
> We recently have a problem with some PCs and ADO : we can connect our
> programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC,
> but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
> (SP2). Our programs written in C# or VB.Net which use ADO.Net work perfectly
> with the databases. But our programs written in "old" VB6 can't connect to
> the same
> database on the same computer (*) It's not the case for all the PCs, just
> for a small minority... For these minority, the problem concerns third
> partie products too... And with XP SP2, whe can't try to reinstall MDAC,
> because Windows rejects the reinstall...
> Any idea ?
> Thanx in advance !!!
> (*) We are sure it isn't a problem of connect string, you can forget this
> reason ;-)
>
>
|||Thanks for letting us know. I'll send you a bill. ;)
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"News" <news@.invalid> wrote in message news:466e8f95$0$14995$426a34cc@.news.free.fr...
It works with SNAC !
Thank you for your help !
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> a crit dans le message de news: eVugpLErHHA.5032@.TK2MSFTNGP02.phx.gbl...
Try to use the OLE DB native provider for SQL Server. In this case the MDAC stack is not required. See SNAC or "SQL Server Native Client" in online help. It's designed to access SQL Server 2005 instances and all of their new features.
I would also use the Component Checker tool (downloadable from here). It would also help to review this.
Clearly, the reason some computers can connect and others cannot is a difference in the systems... ;) The Component Checker can show the differences in the myriad of MDAC layered components installed. This problem is (partially) alleviated with the SNAC provider.
I discuss these issues in my book "ADO.NET Examples and Best Practices".
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"News" <news@.invalid> wrote in message news:466d1e93$0$28887$426a34cc@.news.free.fr...
> Hello,
> First of all, excuse me for my very poor english : I just "speak" a bit of
> english...
> We recently have a problem with some PCs and ADO : we can connect our
> programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC,
> but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
> (SP2). Our programs written in C# or VB.Net which use ADO.Net work perfectly
> with the databases. But our programs written in "old" VB6 can't connect to
> the same
> database on the same computer (*) It's not the case for all the PCs, just
> for a small minority... For these minority, the problem concerns third
> partie products too... And with XP SP2, whe can't try to reinstall MDAC,
> because Windows rejects the reinstall...
> Any idea ?
> Thanx in advance !!!
> (*) We are sure it isn't a problem of connect string, you can forget this
> reason ;-)
>
>
First of all, excuse me for my very poor english : I just "speak" a bit of
english...
We recently have a problem with some PCs and ADO : we can connect our
programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC,
but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
(SP2). Our programs written in C# or VB.Net which use ADO.Net work perfectly
with the databases. But our programs written in "old" VB6 can't connect to
the same
database on the same computer (*) It's not the case for all the PCs, just
for a small minority... For these minority, the problem concerns third
partie products too... And with XP SP2, whe can't try to reinstall MDAC,
because Windows rejects the reinstall...
Any idea ?
Thanx in advance !!!
(*) We are sure it isn't a problem of connect string, you can forget this
reason ;-)
Try to use the OLE DB native provider for SQL Server. In this case the MDAC stack is not required. See SNAC or "SQL Server Native Client" in online help. It's designed to access SQL Server 2005 instances and all of their new features.
I would also use the Component Checker tool (downloadable from here). It would also help to review this.
Clearly, the reason some computers can connect and others cannot is a difference in the systems... ;) The Component Checker can show the differences in the myriad of MDAC layered components installed. This problem is (partially) alleviated with the SNAC provider.
I discuss these issues in my book "ADO.NET Examples and Best Practices".
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"News" <news@.invalid> wrote in message news:466d1e93$0$28887$426a34cc@.news.free.fr...
> Hello,
> First of all, excuse me for my very poor english : I just "speak" a bit of
> english...
> We recently have a problem with some PCs and ADO : we can connect our
> programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC,
> but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
> (SP2). Our programs written in C# or VB.Net which use ADO.Net work perfectly
> with the databases. But our programs written in "old" VB6 can't connect to
> the same
> database on the same computer (*) It's not the case for all the PCs, just
> for a small minority... For these minority, the problem concerns third
> partie products too... And with XP SP2, whe can't try to reinstall MDAC,
> because Windows rejects the reinstall...
> Any idea ?
> Thanx in advance !!!
> (*) We are sure it isn't a problem of connect string, you can forget this
> reason ;-)
>
>
|||It works with SNAC !
Thank you for your help !
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> a crit dans le message de news: eVugpLErHHA.5032@.TK2MSFTNGP02.phx.gbl...
Try to use the OLE DB native provider for SQL Server. In this case the MDAC stack is not required. See SNAC or "SQL Server Native Client" in online help. It's designed to access SQL Server 2005 instances and all of their new features.
I would also use the Component Checker tool (downloadable from here). It would also help to review this.
Clearly, the reason some computers can connect and others cannot is a difference in the systems... ;) The Component Checker can show the differences in the myriad of MDAC layered components installed. This problem is (partially) alleviated with the SNAC provider.
I discuss these issues in my book "ADO.NET Examples and Best Practices".
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"News" <news@.invalid> wrote in message news:466d1e93$0$28887$426a34cc@.news.free.fr...
> Hello,
> First of all, excuse me for my very poor english : I just "speak" a bit of
> english...
> We recently have a problem with some PCs and ADO : we can connect our
> programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC,
> but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
> (SP2). Our programs written in C# or VB.Net which use ADO.Net work perfectly
> with the databases. But our programs written in "old" VB6 can't connect to
> the same
> database on the same computer (*) It's not the case for all the PCs, just
> for a small minority... For these minority, the problem concerns third
> partie products too... And with XP SP2, whe can't try to reinstall MDAC,
> because Windows rejects the reinstall...
> Any idea ?
> Thanx in advance !!!
> (*) We are sure it isn't a problem of connect string, you can forget this
> reason ;-)
>
>
|||Thanks for letting us know. I'll send you a bill. ;)
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"News" <news@.invalid> wrote in message news:466e8f95$0$14995$426a34cc@.news.free.fr...
It works with SNAC !
Thank you for your help !
"William (Bill) Vaughn" <billvaRemoveThis@.betav.com> a crit dans le message de news: eVugpLErHHA.5032@.TK2MSFTNGP02.phx.gbl...
Try to use the OLE DB native provider for SQL Server. In this case the MDAC stack is not required. See SNAC or "SQL Server Native Client" in online help. It's designed to access SQL Server 2005 instances and all of their new features.
I would also use the Component Checker tool (downloadable from here). It would also help to review this.
Clearly, the reason some computers can connect and others cannot is a difference in the systems... ;) The Component Checker can show the differences in the myriad of MDAC layered components installed. This problem is (partially) alleviated with the SNAC provider.
I discuss these issues in my book "ADO.NET Examples and Best Practices".
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"News" <news@.invalid> wrote in message news:466d1e93$0$28887$426a34cc@.news.free.fr...
> Hello,
> First of all, excuse me for my very poor english : I just "speak" a bit of
> english...
> We recently have a problem with some PCs and ADO : we can connect our
> programs to databases (SqlServer 2000 or Express 2005) via ADO.Net or ODBC,
> but it doesn't work via ADO !? PCs are under Windows 2000 or Windows XP
> (SP2). Our programs written in C# or VB.Net which use ADO.Net work perfectly
> with the databases. But our programs written in "old" VB6 can't connect to
> the same
> database on the same computer (*) It's not the case for all the PCs, just
> for a small minority... For these minority, the problem concerns third
> partie products too... And with XP SP2, whe can't try to reinstall MDAC,
> because Windows rejects the reinstall...
> Any idea ?
> Thanx in advance !!!
> (*) We are sure it isn't a problem of connect string, you can forget this
> reason ;-)
>
>
Wednesday, March 7, 2012
problem w/ sp_change_users_login on 64bit SQL Server 2005?
Here's the situation: I just restored a full backup of a SQL Server 2K
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen
> How can it work on one and not the other?
Because these are different instances and might not have the same logins.
> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>
|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen
> How can it work on one and not the other?
Because these are different instances and might not have the same logins.
> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>
|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
problem w/ sp_change_users_login on 64bit SQL Server 2005?
Here's the situation: I just restored a full backup of a SQL Server 2K
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen> How can it work on one and not the other?
Because these are different instances and might not have the same logins.
> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen> How can it work on one and not the other?
Because these are different instances and might not have the same logins.
> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo
problem w/ sp_change_users_login on 64bit SQL Server 2005?
Here's the situation: I just restored a full backup of a SQL Server 2K
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen> How can it work on one and not the other?
Because these are different instances and might not have the same logins.
> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo
database to a brand new server running 64 bit Windows 2003 Server running 64
bit SQL Server 2005 Standard Edition. I also restored this same database to
your basic, normal Win2003 Server running SQL Server 2005 Std Edition.
I fire up Management Studio and connect to both servers. I run the
following in a new query window on the standard Win2003/SQL 2005 server
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
and this runs just fine. However, when I copy/paste this query to a new
query window on the 64bit server with 64 bit SQL, I get the following error:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
An invalid parameter or option was specified for procedure
'sys.sp_change_users_login'.
So, at this point, I am completely stumped. How can it work on one and not
the other? Line 207? Is there something about 64 bit SQL Server 2005 that
would prevent this from working?
Any suggestions would be greatly appreciated.
-- Margo Noreen> How can it work on one and not the other?
Because these are different instances and might not have the same logins.
> Line 207?
Here's the excerpt from the proc text::
if @.Password IS Null
begin
line 207 --> raiserror(15600,-1,-1,'sys.sp_change_users_login')
deallocate ms_crs_110_Users
return (1)
end
So it looks like a new standard security login needs to be created but you
have not specified a password. You need to either create the login manually
or specify the password parameter to the proc.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"M Noreen" <noreen@.newsgroups.nospam> wrote in message
news:%23DM2pzPWGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Here's the situation: I just restored a full backup of a SQL Server 2K
> database to a brand new server running 64 bit Windows 2003 Server running
> 64 bit SQL Server 2005 Standard Edition. I also restored this same
> database to your basic, normal Win2003 Server running SQL Server 2005 Std
> Edition.
> I fire up Management Studio and connect to both servers. I run the
> following in a new query window on the standard Win2003/SQL 2005 server
> exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
>
> and this runs just fine. However, when I copy/paste this query to a new
> query window on the 64bit server with 64 bit SQL, I get the following
> error:
>
> Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 207
> An invalid parameter or option was specified for procedure
> 'sys.sp_change_users_login'.
>
> So, at this point, I am completely stumped. How can it work on one and
> not the other? Line 207? Is there something about 64 bit SQL Server 2005
> that would prevent this from working?
> Any suggestions would be greatly appreciated.
> -- Margo Noreen
>|||Hi Margo,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. This issue is not related to your 64 bit SQL
Server. This issue is caused by the orphaned users in your database.
If you map a dtabase user to a login and then you deleted the login, the
user in your database will not be delete automatically but it will become a
orphaned user. No login will mapped to this user.
Once a orphaned user appeared in your database, if you want to use the
following statement:
exec sp_change_users_login 'Auto_Fix', 'myLoginNameHere'
since there is not any Login in your sql server, this stored procedure will
try to create a new login , but you did not specify any password in the
statement, so a 15600 error will raise.
To resolve this issue , please use the following statement.
exec sp_change_users_login 'Auto_Fix', 'LoginNameHere',null, 'YourPassword'
For more information, please follow this Books online help article:
sp_change_users_login
http://msdn2.microsoft.com/en-us/library/ms174378.aspx
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo|||Thanks for the feedback.
I guess I was surprised I had to supply the password parameter one instance
and not the other. I had performed the exact same steps on both servers
(that is, take a complete backup of my db, copy to a local hard drive, open
up SSMS and restore the database to the server, open up a query window and
issue the sp_Change_users_login 'Auto_Fix' stored proc to create and wire up
the orphaned user/login).
I *think* the difference/issue was that the 64bit server resides in an AD
child domain where a password enforcement policy was in place, whereas the
other 32bit server did not have this same situation.
So, I added the password parameter and got a new error message"
"Password validation failed. The password does not meet Windows policy
requirements because it is too short."
So I ended up using SSMS to create the login manually, and made sure I
unchecked the Enforce Password policy option. I suppose I could create a
script that did the a "create login" followed by a an "alter login"
statement, but I don't the change_users_login stored proc will support the
"CHECK_POLICY" parameter...
All - in - all, kind of difficult for a pretty common scenario, but at least
I can I learned something!
Thanks again.
-- Margo
Problem Viewing Reports on Server
Hello, we're having a bit of an issue getting Report Server to display reports. We've published via the web-interface and Visual Studio. After we publish, it gives an error and it looks as if it's trying to find the data source that we used to create the report. We've changed the datasource names to match that of the local machine too. Here's the error...
Any help would be greatly appreciated. Thank you!
What is the connection string and which data provider are you using? It looks like the data provider can't handle the connection string.|||Problem solved....we were using an invalid connection string. For some reason it was working on one server but not the other. Just tried a different string on both of them and it works great now.Monday, February 20, 2012
Problem using dates in XMLtoFLFF Transformation
I am having a bit of trouble transforming a XML (xs:date) type to the DBDATE in my FLFF. I believe the problem is because the SSIS DF (Metadeta) converts xs:date to DT_DBTIMESTAMP which becomes 01/01/2005 00:00:00 -- appending the time. So when I try to transform in my fixed length flat file, it throws a truncation error when the length is set to 10. (which is the length of date)
Is it possible to (A) use some other xs format that changes DF Metadata to only use date and not DT_DBTIMESTAMP, or (B) is there a field type in the destination FLFF Connection that will allow a length of 10 without throwing a truncation error?The only output column date type supported by the XmlSrc is DT_DBTIMESTAMP. You could try changing the output column type to a string in the advanced editor, and set it to the length you want, and set the truncation disposition to ignore truncations.
Or, You could place a Data Conversion transform on the output of the XmlSrc, and either convert the DT_DBTIMESTAMP column to DT_DBDATE (which does not include the time), or actually convert the DT_DBTIMESTAMP to a string of the desired length and set the truncation disposition to ignore truncations.
Let me know if this solves your problem, or you need a different solution.
Mark|||Thanks Mark the feedback. Yep, I ended up implementing the first solution and works ok. The second solution is a good one as well but seems a bit too much, we will just enforce no date_type validations for now. Do you (or anyone else) know if this ability will ever be natively incorporated into SSIS?|||Do you mean, will it be possible to map the xml input to a date type other than just DT_DBTIMESTAMP directly in the XmlSrc adapter? If so, I know of no plans for that, but it would be great if you could open a DCR bug to that effect.
Subscribe to:
Posts (Atom)