Hi,
SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to
access data on another Server running 2000. I get the following error, whe
n I exececute the Stored Procedure in Query Analyzer:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be
set for the connection. This ensures consistent query semantics. Enable the
se options and then reissue your query.
Interestingly, when I issue the identicl select statement within Query Analy
zer, it works fine.
I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
THanks for any help.
JimReview the information in BOL regarding "set ansi_nulls" - pay special
attention to the information about stored procedures. Then review the notes
for "create procedure" - it reiterates the previous point and adds some
additional information. Then - go fix your procedure. Note that using EM
to do this makes the process that much more difficult, since it tends to
hide important details (like this). Instead, use QA and a script to create
the procedure. Of course, you should be using scripts of some sort since
all code for the database (schema, stored procedures, UDFs, triggers, etc)
are as important to the entire system as your application code.
BTW - you want to set ansi_nulls ON, not OFF. Generally, something that is
"set" means it is set "on".|||Generally the error is due to needing to set the properties
when you create the stored procedure. Try recreating your
stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Thu, 21 Sep 2006 14:58:30 -0700, "Jim Fox"
<jim.fox@.emailhdi.com> wrote:
>Hi,
>SQLServer 2000, using an OPENDATASOURCE command within a stored procedure t
o access data on another Server running 2000. I get the following error, wh
en I exececute the Stored Procedure in Query Analyzer:
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to b
e set for the connection. This ensures consistent query semantics. Enable th
ese options and then reissue your query.
>Interestingly, when I issue the identicl select statement within Query Anal
yzer, it works fine.
>I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
>THanks for any help.
>Jim|||Thanks - Much appreciated!
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Review the information in BOL regarding "set ansi_nulls" - pay special
> attention to the information about stored procedures. Then review the
> notes for "create procedure" - it reiterates the previous point and adds
> some additional information. Then - go fix your procedure. Note that
> using EM to do this makes the process that much more difficult, since it
> tends to hide important details (like this). Instead, use QA and a script
> to create the procedure. Of course, you should be using scripts of some
> sort since all code for the database (schema, stored procedures, UDFs,
> triggers, etc) are as important to the entire system as your application
> code.
> BTW - you want to set ansi_nulls ON, not OFF. Generally, something that
> is "set" means it is set "on".
>|||Along these lines, I am calling a trigger that runs an
insert/update/delete on a linked server table, and am running into the
same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
trigger itself does not help out at all...
I've been on the phone with MS all day, and am looking for a fresh
perspective...
My trigger is this:
CREATE TRIGGER opsCompany_Insert ON [dbo].[RM00101]
FOR INSERT
AS
SET ANSI_DEFAULTS ON
-- SET THE DB NAME / DO THIS FOR EACH COMPANY DB --
DECLARE @.CompanyDB CHAR(5)
SELECT @.CompanyDB = (SELECT 'TWO')
-- Set Company Number
DECLARE @.CompanyNumber CHAR(15)
SELECT @.CompanyNumber = (SELECT CUSTNMBR FROM INSERTED)
-- Set Company Name
DECLARE @.CompanyName CHAR(65)
SELECT @.CompanyName = (SELECT CUSTNAME FROM INSERTED)
-- Set Currency ID
DECLARE @.CurrencyID CHAR(15)
SELECT @.CurrencyID = (SELECT CURNCYID FROM INSERTED)
-- Set GovernmentID
DECLARE @.GovernmentID CHAR(25)
SELECT @.GovernmentID = (SELECT TXRGNNUM FROM INSERTED)
-- DEX_ROW_ID
DECLARE @.MstrID INT
SELECT @.MstrID = (SELECT DEX_ROW_ID FROM INSERTED)
-- Push to Cranberry
INSERT INTO SQLSVR.TESTDATA.dbo.Company (GPCompanyID, CompanyNumber,
CompanyName, CurrencyID, GovernmentID, CompanyDB, AddedBy, AddedOn)
VALUES (@.MstrID, @.CompanyNumber, @.CompanyName, @.CurrencyID,
@.GovernmentID, @.CompanyDB, user, getdate())
I can run the trigger fine to a local database, but to the linked
server, I get the same 'Heterogeneous' error... I ahve set it up from
QA with
SET ANSI_NULLS, ANSI_WARNINGS ON
GO
Create Trigger ...
And also setting it within the trigger right after 'AS'
However still no luck...
Any thoughts?
Thanks!
Jim Fox wrote:[vbcol=seagreen]
> Thanks - Much appreciated!
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...|||> Along these lines, I am calling a trigger that runs an
> insert/update/delete on a linked server table, and am running into the
> same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
> trigger itself does not help out at all...
These are connection level settings - for the most part. Stored procedures
have their own wrinkle to this. Ultimately, the issue is the same. You
must use the appropriate connection-level settings for this architecture to
work. Ideally, your client application should be designed to enforce the
appropriate settings. If you can't do that, then the only other option
that I can see is to put your logic for accessing the remote DB into a
procedure. Your procedure must be created with the "sticky" settings that
are needed and can set the others that are needed within the body of the
procedure. I think that approach will work, but I've not investigated all
of the issues to know for certain. Note - your trigger code does not
support mult-row inserts, making the use of a stored procedure much easier
(and as technically flawed the trigger).
Some other alternatives you might want to consider.
* Some form of replication.
* Some form of asynchronous queueing of updates.sql
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Thursday, March 29, 2012
Error: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options
Hi,
SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000. I get the following error, when I exececute the Stored Procedure in Query Analyzer:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.
I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
THanks for any help.
Jim
Review the information in BOL regarding "set ansi_nulls" - pay special
attention to the information about stored procedures. Then review the notes
for "create procedure" - it reiterates the previous point and adds some
additional information. Then - go fix your procedure. Note that using EM
to do this makes the process that much more difficult, since it tends to
hide important details (like this). Instead, use QA and a script to create
the procedure. Of course, you should be using scripts of some sort since
all code for the database (schema, stored procedures, UDFs, triggers, etc)
are as important to the entire system as your application code.
BTW - you want to set ansi_nulls ON, not OFF. Generally, something that is
"set" means it is set "on".
|||Generally the error is due to needing to set the properties
when you create the stored procedure. Try recreating your
stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Thu, 21 Sep 2006 14:58:30 -0700, "Jim Fox"
<jim.fox@.emailhdi.com> wrote:
>Hi,
>SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000. I get the following error, when I exececute the Stored Procedure in Query Analyzer:
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
>Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.
>I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
>THanks for any help.
>Jim
|||Thanks - Much appreciated!
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Review the information in BOL regarding "set ansi_nulls" - pay special
> attention to the information about stored procedures. Then review the
> notes for "create procedure" - it reiterates the previous point and adds
> some additional information. Then - go fix your procedure. Note that
> using EM to do this makes the process that much more difficult, since it
> tends to hide important details (like this). Instead, use QA and a script
> to create the procedure. Of course, you should be using scripts of some
> sort since all code for the database (schema, stored procedures, UDFs,
> triggers, etc) are as important to the entire system as your application
> code.
> BTW - you want to set ansi_nulls ON, not OFF. Generally, something that
> is "set" means it is set "on".
>
|||Along these lines, I am calling a trigger that runs an
insert/update/delete on a linked server table, and am running into the
same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
trigger itself does not help out at all...
I've been on the phone with MS all day, and am looking for a fresh
perspective...
My trigger is this:
CREATE TRIGGER opsCompany_Insert ON [dbo].[RM00101]
FOR INSERT
AS
SET ANSI_DEFAULTS ON
-- SET THE DB NAME / DO THIS FOR EACH COMPANY DB --
DECLARE @.CompanyDB CHAR(5)
SELECT @.CompanyDB = (SELECT 'TWO')
-- Set Company Number
DECLARE @.CompanyNumber CHAR(15)
SELECT @.CompanyNumber = (SELECT CUSTNMBR FROM INSERTED)
-- Set Company Name
DECLARE @.CompanyName CHAR(65)
SELECT @.CompanyName = (SELECT CUSTNAME FROM INSERTED)
-- Set Currency ID
DECLARE @.CurrencyID CHAR(15)
SELECT @.CurrencyID = (SELECT CURNCYID FROM INSERTED)
-- Set GovernmentID
DECLARE @.GovernmentID CHAR(25)
SELECT @.GovernmentID = (SELECT TXRGNNUM FROM INSERTED)
-- DEX_ROW_ID
DECLARE @.MstrID INT
SELECT @.MstrID = (SELECT DEX_ROW_ID FROM INSERTED)
-- Push to Cranberry
INSERT INTO SQLSVR.TESTDATA.dbo.Company (GPCompanyID, CompanyNumber,
CompanyName, CurrencyID, GovernmentID, CompanyDB, AddedBy, AddedOn)
VALUES (@.MstrID, @.CompanyNumber, @.CompanyName, @.CurrencyID,
@.GovernmentID, @.CompanyDB, user, getdate())
I can run the trigger fine to a local database, but to the linked
server, I get the same 'Heterogeneous' error... I ahve set it up from
QA with
SET ANSI_NULLS, ANSI_WARNINGS ON
GO
Create Trigger ...
And also setting it within the trigger right after 'AS'
However still no luck...
Any thoughts?
Thanks!
Jim Fox wrote:[vbcol=seagreen]
> Thanks - Much appreciated!
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
|||> Along these lines, I am calling a trigger that runs an
> insert/update/delete on a linked server table, and am running into the
> same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
> trigger itself does not help out at all...
These are connection level settings - for the most part. Stored procedures
have their own wrinkle to this. Ultimately, the issue is the same. You
must use the appropriate connection-level settings for this architecture to
work. Ideally, your client application should be designed to enforce the
appropriate settings. If you can't do that, then the only other option
that I can see is to put your logic for accessing the remote DB into a
procedure. Your procedure must be created with the "sticky" settings that
are needed and can set the others that are needed within the body of the
procedure. I think that approach will work, but I've not investigated all
of the issues to know for certain. Note - your trigger code does not
support mult-row inserts, making the use of a stored procedure much easier
(and as technically flawed the trigger).
Some other alternatives you might want to consider.
* Some form of replication.
* Some form of asynchronous queueing of updates.
SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000. I get the following error, when I exececute the Stored Procedure in Query Analyzer:
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.
I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
THanks for any help.
Jim
Review the information in BOL regarding "set ansi_nulls" - pay special
attention to the information about stored procedures. Then review the notes
for "create procedure" - it reiterates the previous point and adds some
additional information. Then - go fix your procedure. Note that using EM
to do this makes the process that much more difficult, since it tends to
hide important details (like this). Instead, use QA and a script to create
the procedure. Of course, you should be using scripts of some sort since
all code for the database (schema, stored procedures, UDFs, triggers, etc)
are as important to the entire system as your application code.
BTW - you want to set ansi_nulls ON, not OFF. Generally, something that is
"set" means it is set "on".
|||Generally the error is due to needing to set the properties
when you create the stored procedure. Try recreating your
stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourStoredProc...etc.
-Sue
On Thu, 21 Sep 2006 14:58:30 -0700, "Jim Fox"
<jim.fox@.emailhdi.com> wrote:
>Hi,
>SQLServer 2000, using an OPENDATASOURCE command within a stored procedure to access data on another Server running 2000. I get the following error, when I exececute the Stored Procedure in Query Analyzer:
>Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
>Interestingly, when I issue the identicl select statement within Query Analyzer, it works fine.
>I tried issuing a "Set ANSI_NULLS OFF" etc commands prior, to no avail.
>THanks for any help.
>Jim
|||Thanks - Much appreciated!
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
> Review the information in BOL regarding "set ansi_nulls" - pay special
> attention to the information about stored procedures. Then review the
> notes for "create procedure" - it reiterates the previous point and adds
> some additional information. Then - go fix your procedure. Note that
> using EM to do this makes the process that much more difficult, since it
> tends to hide important details (like this). Instead, use QA and a script
> to create the procedure. Of course, you should be using scripts of some
> sort since all code for the database (schema, stored procedures, UDFs,
> triggers, etc) are as important to the entire system as your application
> code.
> BTW - you want to set ansi_nulls ON, not OFF. Generally, something that
> is "set" means it is set "on".
>
|||Along these lines, I am calling a trigger that runs an
insert/update/delete on a linked server table, and am running into the
same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
trigger itself does not help out at all...
I've been on the phone with MS all day, and am looking for a fresh
perspective...
My trigger is this:
CREATE TRIGGER opsCompany_Insert ON [dbo].[RM00101]
FOR INSERT
AS
SET ANSI_DEFAULTS ON
-- SET THE DB NAME / DO THIS FOR EACH COMPANY DB --
DECLARE @.CompanyDB CHAR(5)
SELECT @.CompanyDB = (SELECT 'TWO')
-- Set Company Number
DECLARE @.CompanyNumber CHAR(15)
SELECT @.CompanyNumber = (SELECT CUSTNMBR FROM INSERTED)
-- Set Company Name
DECLARE @.CompanyName CHAR(65)
SELECT @.CompanyName = (SELECT CUSTNAME FROM INSERTED)
-- Set Currency ID
DECLARE @.CurrencyID CHAR(15)
SELECT @.CurrencyID = (SELECT CURNCYID FROM INSERTED)
-- Set GovernmentID
DECLARE @.GovernmentID CHAR(25)
SELECT @.GovernmentID = (SELECT TXRGNNUM FROM INSERTED)
-- DEX_ROW_ID
DECLARE @.MstrID INT
SELECT @.MstrID = (SELECT DEX_ROW_ID FROM INSERTED)
-- Push to Cranberry
INSERT INTO SQLSVR.TESTDATA.dbo.Company (GPCompanyID, CompanyNumber,
CompanyName, CurrencyID, GovernmentID, CompanyDB, AddedBy, AddedOn)
VALUES (@.MstrID, @.CompanyNumber, @.CompanyName, @.CurrencyID,
@.GovernmentID, @.CompanyDB, user, getdate())
I can run the trigger fine to a local database, but to the linked
server, I get the same 'Heterogeneous' error... I ahve set it up from
QA with
SET ANSI_NULLS, ANSI_WARNINGS ON
GO
Create Trigger ...
And also setting it within the trigger right after 'AS'
However still no luck...
Any thoughts?
Thanks!
Jim Fox wrote:[vbcol=seagreen]
> Thanks - Much appreciated!
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23VbpjNk3GHA.5092@.TK2MSFTNGP04.phx.gbl...
|||> Along these lines, I am calling a trigger that runs an
> insert/update/delete on a linked server table, and am running into the
> same issues. However, setting ANSI_NULLS or ANSI_WARNINGS in the
> trigger itself does not help out at all...
These are connection level settings - for the most part. Stored procedures
have their own wrinkle to this. Ultimately, the issue is the same. You
must use the appropriate connection-level settings for this architecture to
work. Ideally, your client application should be designed to enforce the
appropriate settings. If you can't do that, then the only other option
that I can see is to put your logic for accessing the remote DB into a
procedure. Your procedure must be created with the "sticky" settings that
are needed and can set the others that are needed within the body of the
procedure. I think that approach will work, but I've not investigated all
of the issues to know for certain. Note - your trigger code does not
support mult-row inserts, making the use of a stored procedure much easier
(and as technically flawed the trigger).
Some other alternatives you might want to consider.
* Some form of replication.
* Some form of asynchronous queueing of updates.
Error: Data source name not found and No default driver specified
Hello all,
I have an application which was written in VB6. I used
DSNless connection for the odbc connection to access sql 8.0. The
application works fine on one machine and when I try to run the appl.
on another machine I got this error. Any idea why' Here is my
connection string:
DSNName = "Driver={SQL Server};" & _
"Server=dummy;" & _
"Database=sql_data;" & _
"Uid=crystal"
The user id does not require a password.. Thank you.
SherryThis is a question for a VB group, not a SQL server group, but it sounds
like your DSNName is empty. Are you getting this value from an INI file,
the registry, or is it hardcoded in your app? You will most likely find
that this value is missing, wherever you define it.
Put in a message box that shows DSNName just prior to creating the
connection, and confirm that it is actually populated (I don't think it is).
<sunpalozzi@.gmail.com> wrote in message
news:1138811193.427214.282490@.g43g2000cwa.googlegroups.com...
> Hello all,
> I have an application which was written in VB6. I used
> DSNless connection for the odbc connection to access sql 8.0. The
> application works fine on one machine and when I try to run the appl.
> on another machine I got this error. Any idea why' Here is my
> connection string:
> DSNName = "Driver={SQL Server};" & _
> "Server=dummy;" & _
> "Database=sql_data;" & _
> "Uid=crystal"
> The user id does not require a password.. Thank you.
> Sherry
>|||Maybe the other machine doesn't have MDAC installed?
http://www.aspfaq.com/2057
Once you have the most recent version installed, an OLEDB connection string
will be better in most cases:
ConnectionString = _
"Provider=SQLOLEDB.1; " & _
"Data Source=dummy; " & _
"Initial Catalog=sql_data; " & _
"User ID=crystal; " & _
"Password="
<sunpalozzi@.gmail.com> wrote in message
news:1138811193.427214.282490@.g43g2000cwa.googlegroups.com...
> Hello all,
> I have an application which was written in VB6. I used
> DSNless connection for the odbc connection to access sql 8.0. The
> application works fine on one machine and when I try to run the appl.
> on another machine I got this error. Any idea why' Here is my
> connection string:
> DSNName = "Driver={SQL Server};" & _
> "Server=dummy;" & _
> "Database=sql_data;" & _
> "Uid=crystal"
> The user id does not require a password.. Thank you.
> Sherry
>|||thank you for all replies.. I did post in the vb group as well.. The
wire part is the same application is work on one pc but not the
another... I will try to find out if there a MDAC install on the
problem pc.. thanks.
Sherry
I have an application which was written in VB6. I used
DSNless connection for the odbc connection to access sql 8.0. The
application works fine on one machine and when I try to run the appl.
on another machine I got this error. Any idea why' Here is my
connection string:
DSNName = "Driver={SQL Server};" & _
"Server=dummy;" & _
"Database=sql_data;" & _
"Uid=crystal"
The user id does not require a password.. Thank you.
SherryThis is a question for a VB group, not a SQL server group, but it sounds
like your DSNName is empty. Are you getting this value from an INI file,
the registry, or is it hardcoded in your app? You will most likely find
that this value is missing, wherever you define it.
Put in a message box that shows DSNName just prior to creating the
connection, and confirm that it is actually populated (I don't think it is).
<sunpalozzi@.gmail.com> wrote in message
news:1138811193.427214.282490@.g43g2000cwa.googlegroups.com...
> Hello all,
> I have an application which was written in VB6. I used
> DSNless connection for the odbc connection to access sql 8.0. The
> application works fine on one machine and when I try to run the appl.
> on another machine I got this error. Any idea why' Here is my
> connection string:
> DSNName = "Driver={SQL Server};" & _
> "Server=dummy;" & _
> "Database=sql_data;" & _
> "Uid=crystal"
> The user id does not require a password.. Thank you.
> Sherry
>|||Maybe the other machine doesn't have MDAC installed?
http://www.aspfaq.com/2057
Once you have the most recent version installed, an OLEDB connection string
will be better in most cases:
ConnectionString = _
"Provider=SQLOLEDB.1; " & _
"Data Source=dummy; " & _
"Initial Catalog=sql_data; " & _
"User ID=crystal; " & _
"Password="
<sunpalozzi@.gmail.com> wrote in message
news:1138811193.427214.282490@.g43g2000cwa.googlegroups.com...
> Hello all,
> I have an application which was written in VB6. I used
> DSNless connection for the odbc connection to access sql 8.0. The
> application works fine on one machine and when I try to run the appl.
> on another machine I got this error. Any idea why' Here is my
> connection string:
> DSNName = "Driver={SQL Server};" & _
> "Server=dummy;" & _
> "Database=sql_data;" & _
> "Uid=crystal"
> The user id does not require a password.. Thank you.
> Sherry
>|||thank you for all replies.. I did post in the vb group as well.. The
wire part is the same application is work on one pc but not the
another... I will try to find out if there a MDAC install on the
problem pc.. thanks.
Sherry
Tuesday, March 27, 2012
Error: Column Name "X" appears more than once in the result column list.
Hello,
I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form:
I'll post the error below. Any clues on how to fix this? Thanks in advance!!!
~Derrick
Column name 'Assigned_To' appears more than once in the result column list.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.
Source Error:
Line 1444: }Line 1445: try {Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery();Line 1447: return returnValue;Line 1448: }Thanks for the reply. When I go to my Details.aspx page (the page giving me the error), I click on the Details View Control -> Configure Data Source, Click the Update Tab, and here is the Method Signature:
Update(String Assigned_To, String Request_type, String First_Name, String Last_Name, String Username, Nullable Created_On, String Request_Summary, String Expr1, Int16 Original_Request_Id), returns Int32
Does this help?
|||I was able to resolve this issue. It had to do with the "String Expr1" part of the Update function. I removed any reference to Expr1 from all my data sets, refreshed the schemes in the web forms, and everything worked as it should.
Thanks for anyone who looked into it!
Thursday, March 22, 2012
Error: 5123 CREATE FILE encountered operating system error 5A(Access denied.)
HI ,
This is a problem I encountered when I had to detach a database file (type .mdf):
1) I went to the MS SQL Management Server Studi and detached my database file successfully from a connection called Workhorse.
2) I needed to place the .mdf database file into a zip file in order to put it on a remote server. I did this using Shared Portal. This was also successful
3) However when I tried reattaching the database file, I got this error:
CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "C\Program Files\MSSQL Server\MSSQL\Data\<databasename>.mdf'
Q) The database file and log file (ldf) exist in the correct directory so I don't know what happened. Can any one help?
Thanks much
Tonante
Dear Tonate,
Please, take a look on the following link and might be it will help you :)
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=120536&SiteId=1
|||Hi Tonate,
I got the solution for this. I proud this is my first blog to provide the answer on internet
I got the same error too:
CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "C\Program Files\MSSQL Server\MSSQL\Data\<databasename>.mdf'
the solution is:
set the Operating system permission on that .mdf file to full permission to 'Everyone' in new server.
this will helps me to attach the database in other server..
Have a Great Day..
vino...
|||Well that could be a security issue, its better to give permission to the SQL Server account.
Error: 5123 CREATE FILE encountered operating system error 5A(Access denied.)
HI ,
This is a problem I encountered when I had to detach a database file (type .mdf):
1) I went to the MS SQL Management Server Studi and detached my database file successfully from a connection called Workhorse.
2) I needed to place the .mdf database file into a zip file in order to put it on a remote server. I did this using Shared Portal. This was also successful
3) However when I tried reattaching the database file, I got this error:
CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "C\Program Files\MSSQL Server\MSSQL\Data\<databasename>.mdf'
Q) The database file and log file (ldf) exist in the correct directory so I don't know what happened. Can any one help?
Thanks much
Tonante
Dear Tonate,
Please, take a look on the following link and might be it will help you :)
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=120536&SiteId=1
|||Hi Tonate,
I got the solution for this. I proud this is my first blog to provide the answer on internet
I got the same error too:
CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "C\Program Files\MSSQL Server\MSSQL\Data\<databasename>.mdf'
the solution is:
set the Operating system permission on that .mdf file to full permission to 'Everyone' in new server.
this will helps me to attach the database in other server..
Have a Great Day..
vino...
|||Well that could be a security issue, its better to give permission to the SQL Server account.|||Greetings SQL Server users, I rarely post to forums but think I probably should start doing so. I know this problem was probably solved, but just in case it wasn't, this was my solution to the exact same problem when attempting to do exactly what you tried (detaching, zipping, then re-attaching the database file *.mdf) using SQL Server 2005 with Mgmt Server Studio. First, Stop all SQL services, Secondly, go to the sql folder (default: MSSQL) and right click on it and see if you can change your folder and all subfolder atrributes to NOT read only (unchecked). I noticed that on my server, the log and data files could not be changed. So I immediately went to those files *.ldf and *.mdf and took ownership of both of them (the permissions can easily get wacked). Once I did that, I was able to zip them and move them to safer ground. I then restarted all SQL Services, and was able to re-attach the *.mdf file. If you are like me, you probably log in using a few different usernames and move files around a lot. For some odd reason, our permissions get hosed up from time to time. I hope this helped.Wednesday, March 21, 2012
Error: 18456, Severity: 14, State: 11.
i am new to sql server 2005 express. I am trying to set up xp embedded database. I am trying to allow remote access to a central xp embedded database. i have the database on the sql server machine.
i am using the component database manager toll on a remote machine so that i can connect to the remote xp embedded database. when i try to connect to the db through sql server i get the following error in the log file.
2006-09-12 17:15:10.18 Logon Error: 18456, Severity: 14, State: 11.
2006-09-12 17:15:10.18 Logon Login failed for user 'HOSTNAME\Guest'. [CLIENT: XX.XX.XX.XX]
where HOSTNAME is the hostname of the pc that sql server 2005 is running.
XX.XX.XX.XX is the ip address of the client which is running component database manager tool.
what am i doing wrong here.
YOu will have to disable simple filesharing at the WIndows box, because the user will be otherwise authenticated by the Guest user.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||that did it.
i dont have any clue why it works. but that did it.
thanks a million.
we should call you Mr. Geniusmeyer!!!
|||i have another problem.
i can connect to the sql server 2005 express from one laptop.
however, if i try from another laptop i get the above error.
what is wrong with the second pc.
thanks,
jeff
|||Do you have stored user credentials on that machine ? is the error message the same as the one mentioned in the first post ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
no the first post was state 11. This error is state 16.
im not sure what stored user credentials are.
are you talking about on the db client machine?
|||Stored credentials can be found navigating to the to the Control Panel > User Accounts > Choose the User > manage you network accounts. You might have a fixed stored credential enetered there ?HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
|||Simple file sharing is not at all enables on my machine. I still have this problem when I tried to connect from a sql server 2000 machine to any other version of sql server using linked server. Please advice
Thanks in advance,
Venkat
|||
Hi, have you ever received an answer or solution to this one? I am experiencing the same problem, went through all the same steps. Windows has no info on their website that I could find....
Thanks,
Regards
JD
Error: 18456, Severity: 14, State: 11.
i am new to sql server 2005 express. I am trying to set up xp embedded database. I am trying to allow remote access to a central xp embedded database. i have the database on the sql server machine.
i am using the component database manager toll on a remote machine so that i can connect to the remote xp embedded database. when i try to connect to the db through sql server i get the following error in the log file.
2006-09-12 17:15:10.18 Logon Error: 18456, Severity: 14, State: 11.
2006-09-12 17:15:10.18 Logon Login failed for user 'HOSTNAME\Guest'. [CLIENT: XX.XX.XX.XX]
where HOSTNAME is the hostname of the pc that sql server 2005 is running.
XX.XX.XX.XX is the ip address of the client which is running component database manager tool.
what am i doing wrong here.
YOu will have to disable simple filesharing at the WIndows box, because the user will be otherwise authenticated by the Guest user.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||that did it.
i dont have any clue why it works. but that did it.
thanks a million.
we should call you Mr. Geniusmeyer!!!
|||i have another problem.
i can connect to the sql server 2005 express from one laptop.
however, if i try from another laptop i get the above error.
what is wrong with the second pc.
thanks,
jeff
|||Do you have stored user credentials on that machine ? is the error message the same as the one mentioned in the first post ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
no the first post was state 11. This error is state 16.
im not sure what stored user credentials are.
are you talking about on the db client machine?
|||Stored credentials can be found navigating to the to the Control Panel > User Accounts > Choose the User > manage you network accounts. You might have a fixed stored credential enetered there ?HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
|||Simple file sharing is not at all enables on my machine. I still have this problem when I tried to connect from a sql server 2000 machine to any other version of sql server using linked server. Please advice
Thanks in advance,
Venkat
|||
Hi, have you ever received an answer or solution to this one? I am experiencing the same problem, went through all the same steps. Windows has no info on their website that I could find....
Thanks,
Regards
JD
Error: 18456, Severity: 14, State: 11.
i am new to sql server 2005 express. I am trying to set up xp embedded database. I am trying to allow remote access to a central xp embedded database. i have the database on the sql server machine.
i am using the component database manager toll on a remote machine so that i can connect to the remote xp embedded database. when i try to connect to the db through sql server i get the following error in the log file.
2006-09-12 17:15:10.18 Logon Error: 18456, Severity: 14, State: 11.
2006-09-12 17:15:10.18 Logon Login failed for user 'HOSTNAME\Guest'. [CLIENT: XX.XX.XX.XX]
where HOSTNAME is the hostname of the pc that sql server 2005 is running.
XX.XX.XX.XX is the ip address of the client which is running component database manager tool.
what am i doing wrong here.
YOu will have to disable simple filesharing at the WIndows box, because the user will be otherwise authenticated by the Guest user.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||that did it.
i dont have any clue why it works. but that did it.
thanks a million.
we should call you Mr. Geniusmeyer!!!
|||i have another problem.
i can connect to the sql server 2005 express from one laptop.
however, if i try from another laptop i get the above error.
what is wrong with the second pc.
thanks,
jeff
|||Do you have stored user credentials on that machine ? is the error message the same as the one mentioned in the first post ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
no the first post was state 11. This error is state 16.
im not sure what stored user credentials are.
are you talking about on the db client machine?
|||Stored credentials can be found navigating to the to the Control Panel > User Accounts > Choose the User > manage you network accounts. You might have a fixed stored credential enetered there ?HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
|||Simple file sharing is not at all enables on my machine. I still have this problem when I tried to connect from a sql server 2000 machine to any other version of sql server using linked server. Please advice
Thanks in advance,
Venkat
|||
Hi, have you ever received an answer or solution to this one? I am experiencing the same problem, went through all the same steps. Windows has no info on their website that I could find....
Thanks,
Regards
JD
sqlError: 18456, Severity: 14, State: 11 Valid login but server access failure
Recently, one of my clients began receiving this error. My team gave them sysadmin permissions, but this is terrible practice. I have read into disablying simple file sharing, but I don't even think I have the option to do it. I look in mycomputer > tools > view and don't see any option for this. Besides, the problem just started occuring recently, within the last week. The server is a cluster with veritas clustering and the edition is sql server 2000. Has anybody ever had a problem like this and have a good fix?
Thanks for any help in advance...
-Kyle
Disabling simple file sharing is usually more of something you do to address issues with XP operating system and authentication in workgroup scenarios. What user was the login failing for? Was it a regular domain user or a built in account that is failing? Does the login name show up in the login failure? How are the users granted access - individually, through groups or are they accessing this through an application and authentication is handled by the application?
-Sue
|||Hi Sue,
I'm currently having the same issue.
We have 4 domain AD accounts who access an MS access link through citrix metaframe presentation server.
this links to the actual dbase on another server running SQL Server 2005.
3 of these accounts work fine.
one of them however comes up with the error which i'm reading from the log file viewer
Login failed for user 'Domain\Username'. (CLIENT: ip address)
Error: 18456, Severity:14, State: 11.
the error received when running the appliation is...
Connection Failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'Domain\Username'.
I've checked and found that State: 11 means Valid login but server access failure.
this account is identical to the other 3 (AD and SQL).
any help will be greatly appreciated.
thanks,
luke
|||Well...it's probably not exactly the same. Try dropping the problematic login (and user) from the database users and logins and then recreate it.
-Sue
Error: 18456, Severity: 14, State: 11 Valid login but server access failure
Hi
I am new to SQL server and I have been trying hard to make a client computer to remote connect to a SQL express database on host computer
I have a VB6 application that can connect to SQL server database LOCALLY without problem:
Connection String is:
my_connection.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyMushroom;Data Source=LAPTOP\SQLEXPRESS"
I have followed instruction on enabling remote connection function from this blog:
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
I then try to run the same app from the client computer, it gives me:
Login failed for user 'LAPTOP\Guest'.
After looking up the web for solution, I found that I can test the connection from the HOST computer in this way:
C:\Documents and Settings\kit>sqlcmd -E -S laptop\sqlexpress
1>
2>
The test is successful
Now I run the same command on the CLIENT computer
C:\Documents and Settings\Kit>sqlcmd -E -S laptop\sqlexpress
Msg 18456, Level 14, State 1, Server LAPTOP\SQLEXPRESS, Line 1
Login failed for user 'LAPTOP\Guest'.
Now I can sure that from the client computer it cannot make a connection to it, then I look at the errorLog from my host computer
2006-08-13 21:41:00.34 Logon Error: 18456, Severity: 14, State: 11.
2006-08-13 21:41:00.34 Logon Login failed for user 'LAPTOP\Guest'. [CLIENT: 192.168.0.5]
2006-08-13 21:45:10.64 Logon Error: 18456, Severity: 14, State: 11.
2006-08-13 21:45:10.64 Logon Login failed for user 'LAPTOP\Guest'. [CLIENT: 192.168.0.5]
2006-08-13 21:48:41.80 Logon Error: 18456, Severity: 14, State: 11.
2006-08-13 21:48:41.80 Logon Login failed for user 'LAPTOP\Guest'. [CLIENT: 192.168.0.5]
Now I know it is actually Error: 18456, Severity: 14, State: 11.
From this site : http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
11 and 12 | Valid login but server access failure |
It tells the connection string and SQL Express seem to be set up properly but the server access failed the remote connection
I have previously had SQL Server 2000 installed. I uninstalled SQL 2000 before I install SQL express but somehow the SQL Server Service Manager is still running at startup, and C:\Program Files\Microsoft SQL Server\80 and its files are still exist after uninstallation..... Could this be a problem?
The Knowledge base suggestion on "enabling remote connection" is very simple and I do not understand why it is so difficult to me just to make a remote connection test work..... please, I need your help.
If you want to use Windows Authentication you have to disable the "Simple File and printer sharing" at the "server" (even if its only WIndows XP). With this option enabled, it normally simplifies the logon process as the requestor does not have to give a user name and a password, because he will be logged on with the Guest user.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||I did try to disable the file sharing, and also have firewall disabled. But it still gives me the same error..... 
|||
OK, did you disable that on the "server" (Whereever SQL Server is running on) ?
-Jens.
|||Yes, to make sure file sharing is disabled , I unlick the Simple File Sharing from Folder Option on both computers.
Both computer have file sharing disabled
filewall disabled
But I am still getting the same error.
Anything to do with MSDE 2000 instance? I have uninstalled MSDE2000 before I install SQL expresss.
What is wrong still? 
By the way, I restarted both computer and still got the same error on the client computer while the hosting computer is connecting to the KIT\SQLExpress alright :
C:\Documents and Settings\Kit>sqlcmd -E -S kit\sqlexpress
1>
2>
3>
4>
5>
Here is the current setting of my SQLexpress instance
SQL Server Config. Manager
Protoal: Shared Memory, TCP/IP enabled (Name Pipes and VIA disabled)
SQL server (SQLExpress) and SQL server Browser are running , startmode: automatic
For SQL Server Browser: Log on as : Bulit-in account: Network Service
For SQL Server (SQLExpress): Log on as : Bulit-in account: Network Service
Under Service - General -
Host Name: KIT
Name: SQL Server (SQLExpress)
SQL Native Config. - Client Protocols - Shard Memory 1, TCP/IP 2
SQL Server Surface Area Config.
Service Name: MSSQL$SQLEXPRESS
Display Name: SQL Server (SQLEXPRESS)
Remote COnnection: Local and Remote connection clicked - Using TCP/IP Only clicked
The client computer do not have any SQL express installed, could that be the reason?
Frankly, on the server computer , it can connect to the database on TCP/IP using SQL Management Studio
Here I attached the screen shots
http://www.megaupload.com/?d=JWN349QL
|||
I have a few questions about your setup:
- on the client machine, what account is your application running under?
- when you tried connecting to SQL Express locally, using sqlcmd, what was the account you actually connected as? (you can execute "select suser_name()" to find this out)
Thanks
Laurentiu
Client is running Win XP Professional SP2
Server is running Win XP pro SP2
Client machine is logged in as Admin , without password (so it goes straight into the OS when you start up the machine and bypass the log in screen)
Server machine is also logged in as Admin, with passowrd (log-in: Kit, password: Kit) , TweakUI is setup to log in automatically using the log-in/password pair.
- when you tried connecting to SQL Express locally, using sqlcmd, what was the account you actually connected as? (you can execute "select suser_name()" to find this out) :
C:\Documents and Settings\Kit>sqlcmd -E -S kit\sqlexpress
1> select suser_name()
2> go
--
KIT\Kit
(1 rows affected)
1>
Hope this will give you idea of what has gone wrong with my SQL express setting
Thank you for your response.
|||
On the Server Computer , under Control Panel -> User Accounts , there are 3 accounts: Kit (myself), SQLDebugger and Guest
Guest account is not password protected and is set to off. (I've tried to turn this account on and off but the client computer still give me the same error)
I really havae no idea what I have gone it wrong...
|||
I just realize what you meant by "on the client machine, what account is your application running under?"
It is running under Network Service
|||Locally, you connect as Kit, which seems to have server access. Remotely, you appear to execute as Network Service and when you get on the server machine, you are actually running as Guest, which does not have server access.
If you execute
CREATE LOGIN [Laptop\Guest] FROM WINDOWS
then you will grant server access to the Guest account.
But if you want your application to connect as Kit, you should run it as Kit or it should internally impersonate Kit before connecting to the server.
Thanks
Laurentiu
To Laurentiu and Jens:
Thank you for your great help and now the client can connect to the server SQL express. I have been myself studying the SQL server and exploring how SQL server work. This SQL express is very interesting and I have a lot more to learn about this "toy".
I am very appreciated to your helps.
Kit
Friday, March 9, 2012
Error. My aspx file cant access server to connect to database
Hi,
I am writing my first aspx file to connect to a standalone SQL Server which is separated from the IIS webserver. The code is
<HTML>
<HEAD>
<TITLE>Store Locator</TITLE>
<script runat="server">
Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
if Not IsPostBack Then
Dim DBConn as OleDbConnection
Dim DBCommand As OleDbDataAdapter
Dim DSPageData as New DataSet
DBConn = New OleDbConnection("Provider=sqloledb;" _
& "server=dailyplanet;" _
& "Initial Catalog=JAVSTORE;" _
& "User Id=javtrader;" _
& "Password=rage123;")
DBCommand = New OleDbDataAdapter _
("Select * " _
& "From JAVInventory " _
& "Where ID > ((Select COUNT(ID) " _
& "From JAVInventory ) - 100)" _
& "Order By ID DESC", DBConn)
DBCommand.Fill(DSPageData, _
"RecentJAVs")
' ddlZipCode.DataSource = _
' DSPageData.Tables("RecentJAVs").DefaultView
' ddlZipCode.DataBind()
End If
End Sub
I used Visual .Net and Web Matrix and was able to connect to the sql server with the Data Connection to view the tables. However, when I use IIS or the Visual .Net debugger to run the aspx file, it get the following error.. please help..
Server Error in '/javtrade' Application.
------------------------
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Source Error:
Line 21: & "From JAVInventory ) - 100)" _
Line 22: & "Order By ID DESC", DBConn)
Line 23: DBCommand.Fill(DSPageData, _
Line 24: "RecentJAVs")
Line 25: ' ddlZipCode.DataSource = _
Source File: c:\inetpub\wwwroot\javtrade\management.aspx Line: 23
Stack Trace:
[OleDbException (0x80004005): [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.]
System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
System.Data.OleDb.OleDbConnection.InitializeProvider()
System.Data.OleDb.OleDbConnection.Open()
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +36
ASP.management_aspx.Page_Load(Object Sender, EventArgs E) in c:\inetpub\wwwroot\javtrade\management.aspx:23
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731FYI, when using sql server it is better to use the sql namespace over the oledb. I assume you have a connection string in your web.config file. This can be formed in two ways, one using a trusted connection, or the other being uid=; and pwd=; the login and password for sql respectively. Either version of the connection string must include the server name, which is localhost, 127.0.0.1 if on the localmachine or the actual name or ip of the server.
Error>>"SQL Server does not exist or access denied"
I have re-installed Visual Studio and SQL Server and now i am getting this error for all applications i try to run
Error:
"
SQL Server does not exist or access denied.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. |
Stack Trace:
|
any suggestions?We will need to see your connection string to be able to help you.
|||
tmorton wrote:
We will need to see your connection string to be able to help you.
Connection String:
Session("ResNet_Connection_String") = "workstation id=PC_ID;packet size=4096;user id=USER_ID;data source=SAC063;persist" & _
" security info=True;initial catalog=Jo;password=PASSWORD"
The connection string was working fine before installations. I got a feeling that the application is not able to connect to sql server at all and there is some wee configurations which got to be done.
Other than just trying to connect to the SQL server i was even trying to connect to the local SQL Sserver (MSDE 2000) which gives a slightly different error:
Error:
***************************************************************************************************************************************************************************************************************
Server Error in '/TestApp' Application.
Login failed for user '4NSQ11J\ASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user '4NSQ11J\ASPNET'.
Source Error:
|
Source File:c:\inetpub\wwwroot\TestApp\DbTest_App.aspx.vb Line:181
Stack Trace:
|
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
***************************************************************************************************************************************************************************************************************
Where 'NSQ11J' is my local server as well as computer name.
Tryst
|||
Does the user who is trying to run the query have sufficient access rights on the database/table?
Tryst
Yes DB Owner rights!|||Do you have your <machinename>/ASPNET as the user in the USER accounts ? and make sure this user account has permissions on the tables/stored procs.|||
Do you have your <machinename>/ASPNET as the user in the USER accounts ? and make sure this user account has permissions on the tables/stored procs.
As i mentioned earlier the account i am using has got DBO rights.|||Solution Found:
Ther Alias port had to be set to a specific one to listen to TCP/IP protocols.
Steps to check the Alias settings:
1) go to 'Client Network Utility' in Microsoft SQL Server utlities
2)check if there are any protocols disabled which you going to use in the application
3)click on the tab 'Alias' to add/edit/remove currently Alias servers in the list
Thanks for you guys help anyway!
Cheers
Umer|||Hi UmerTahir,
you solved the problem by doing this, but what exactly was the problem you had with the Server Network Utility?
Tryst
|||
Hi UmerTahir,
you solved the problem by doing this, but what exactly was the problem you had with the Server Network Utility?
Tryst
The problem was with the application and its connection to the SQL server. To access the sql server through TCP/IP protocols some empty port is required which is usualy left to be detected automatically. In my case i had to access through specific port which my company has set for some security issues so that solved the problem.
Umer
Sunday, February 26, 2012
Error with establishing a connection to the server
i cannot access my database as i am keep getting the following error:
"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:shared memory provider, error: 40 - could not open a connection to SQL server)"
when i click on the properties window i cannot see my table nor can i make a new connection as the same error is shown, could you please advice me how i can fix this?
Make sure the SQL Server 2005 service is running.
Are you able to connect to the server through Query Analyzer ?
Visitwww.connectionstrings.com to know how to build a connection string for your scenario.
Error whils accessing Report Manager through browser
Hi friends ,
We are using SQL Server Reporting Services 2005. When I want to access Report Server through browser I am giving IP address as
http://<servername>/ReportServer
this is working fine
but when I want to access Report Manager and when I am giving IP address as
http://<servername>/Reports
I am getting error as followes :
The configuration file contains an element that is not valid. The configuration element is not a configuration file element.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Exception: The configuration file contains an element that is not valid. The configuration element is not a configuration file element.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[Exception: The configuration file contains an element that is not valid. The configuration element is not a configuration file element.] Microsoft.ReportingServices.Diagnostics.RSConfiguration.ThrowInvalidFormat(String element) +41
Microsoft.ReportingServices.Diagnostics.RSConfiguration.ParseDocument() +135
Microsoft.ReportingServices.Diagnostics.RSConfiguration.Load() +32
[ServerConfigurationErrorException: The report server has encountered a configuration error. See the report server log files for more information.]
Microsoft.ReportingServices.Diagnostics.RSConfiguration.Load() +166
Microsoft.ReportingServices.Diagnostics.RSConfiguration.Construct(String configFileName) +62
Microsoft.ReportingServices.Diagnostics.RSConfiguration..ctor(String configFileName, String location) +296
Microsoft.ReportingServices.Diagnostics.RSConfigurationManager..ctor(String configFileName, String configLocation) +134
Microsoft.ReportingServices.UI.Global.get_ConfigurationManager() +84
Microsoft.ReportingServices.UI.Global.get_Configuration() +4
Microsoft.ReportingServices.UI.GlobalApp.Application_AuthenticateRequest(Object sender, EventArgs e) +84
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
Where I am going wrong.
we are now in the stage of deploying and we are getting this kind of errors
can you help me out ?
sandeey
Hi,
seems that you screwed up the configuration files. To see which one is corrupted (your web services seems to work), navigate to C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager or the appropiate path in your installation and validate the XML files by the schema mentioned in the header of the file.
If you have no tools to validate through the schema, you can also take a fresh and working file from antoher instalaltion and look for typos or any things that are different to your damaged file.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Friday, February 17, 2012
Error while importing Text file using Import Export Wizard
Hi all
i have text file where i can import it to excel to access or sql2000 without problem but when i import it using (sql2005 pro) i get this error message during the import
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Success)
- Prepare for Execute (Success)
- Pre-execute (Success)
Messages
Information 0x402090dc: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has started.
(SQL Server Import and Export Wizard)
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "FRDNAME" (25)" failed because truncation occurred, and the truncation row disposition on "output column "FRDNAME" (25)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" on data row 9.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - All_Alarm5_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)
- Copying to [DXB_Data].[dbo].[All_Alarm5] (Stopped)
- Post-execute (Success)
Messages
Information 0x402090dd: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has ended.
(SQL Server Import and Export Wizard)
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)
Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)
- Cleanup (Success)
Messages
Information 0x4004300b: Data Flow Task: "component "Destination - All_Alarm5" (64)" wrote 0 rows.
(SQL Server Import and Export Wizard)
=============================
**** from the error message (Executing) error number 2 and 3 it shows that the error is comming from the (column FRDNAME) and row number 9 ...
this cells contains the following text: ;Ruwais5 B60_Baynounah_R_H_Camp_PH rack1 shelf1 db4g 0;
where the ";" is the delimiter
moreover when i remove this row other problem comes in diffirent location with the following text: ;((AvailabilityStatus,failed ,),(OperationalState,disabled ,));
where the ";" is the delimiter
**** from the error message (Executing) error number 1
there is somthing called ( code page ) this can be modified from the wizard it self and there are many options to choose. i tryed many of them but without solution
i hope that i will find solution for my problem
thank youLooking at the error messages it looks as though there's a truncation going on. What is the length of the value in the FRDNAME column in row 9? If that is longer than the length of the target field - that is the problem.
Import/Export wizard offers you the chance to save your package. Do that - then open it up and look at the metadata of the pipeline. See what the length of the FRDNAME field is.
-Jamie|||dear sir
thank for ur replay
about what u say
Import/Export wizard offers you the chance to save your package
i didnt know how to do it?
is it in the Import/Export wizard then in column maping click in Edit SQL ...
also when u say
then open it up and look at the metadata of the pipeline
i didnt under stand it ..... sorry 
but after long investegation i found the following
i instal SQL2000 and SQL2005 and i create table inside each of them with the
same spacifecation as follows
all the colums with data type: varchar and length: 8000 for both tabels.
i fined somthing very strange 
- when i use the Import/Export wizard of SQL2000
i can import the Text file to the table in SQL2000 and to the table in SQL2005
- when i use the Import/Export wizard of SQL2005
i can't import the same Text file to the table in SQL2000 and also can't import it to the table in SQL2005
- when i check the length of the value in the FRDNAME column in row 9 in the text file i found it 53 and when i reduce the length less than 50 for that value
i found that the Import/Export wizard of SQL2005 works for both table in SQL2000 and to the table in SQL2005
Even both table have length for all colums as 8000 also i change this length to 100, 500, 1000 and other value but the same result !!!!!!!!
|||I'm convinced also that some kind of bug must exist here. I keep getting this error:
The "output column "XXXX" (42)" failed because truncation occurred, and the truncation row disposition on "output column "XXXX" (42)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
This makes no sense. The data in question is nowhere near large enough to cause a truncation. All the columns are properly mapped. All of the rows are properly delimited.
I've tried to make sense of the "Error Output" tab on the OLE DB Destination Editor, but I can't make any sense of it. If I could maybe I could find a way to tell SSIS to ignore whatever fictional truncation errors it is making.
Dan|||OK, time for me to eat some crow.
Long story short, turns out there was a corruption being introduced in the flat file from a zip extraction library. I was having this problem with multiple files, all of which had been extracted in the same way. The corruption did not exist in another copy of the file that I had, which is why I was so convinced that there was nothing wrong with the file. The copy of the file that was testing with BULK INSERT directly had the same corruption.
My apologies to the SQL team at Microsoft for being so convinced there was a bug here. :-)
Dan|||
Is anyone still getting this problem? I get the error when importing a flat file into sql2005 but don't when importing to sql2000 using the exact same file.
Thanks!!
|||I get the same error while trying to import more then 50 characters. Same text file imports fine to sql2000|||I get the same error on all our development and production machines.
I believe that this is a Microsoft SQL Server 2005 *BUG*, yes a bug, because I can import the same data on sql 2000. Because our production environment is already running sql 2005 I had no choice but to leave the dts packages on a sql 2000 machine and simply point the connections to the new sql2005 box and that works 100%.
This will just show that there is a bug in SSIS 2005.
Please help MS! This is a big problem for us!
|||I agree. I have the same problem. I tried to import a basic file with 3 columns ( varchar(50) ) and I get the same error message? The file is ok because the import in SQL Server 2000 works fine.
At first I thought that scandinavian characters are the problem but they are not.
Can anyone help us?
|||I am quite convinced that the there is a bug in Import export wizard.
It looks like that wizard can handle only 50 characters tops in one column.
I resolved the problem by making the bulk insert in sql.
BULK INSERT dbo.[tablename] FROM 'c:\temp\bulkinsertfile.csv' WITH (FIELDTERMINATOR=';',ROWTERMINATOR='\n',CODEPAGE = 'ACP',FIRSTROW=2)
Hopefully this issue is resolved and fixed by Microsoft soon...
|||The flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.
Thanks.
|||Thanks for your answer,
I noticed that the default values for varchar fields are 50. I changed the values in Column Mapping -window but it does not take affect. I get the same error message even if I change the size of the column.
When I changed the values in Advanced page as you told everything works fine ;)
I gues that Column Mapping window does not do the same "trick" as the Advanced page...That suggest types works fine.
There are some problems in SSIS. When import fails and I have to change some parameters (not closing the window first).
I occasionally get the error messages and I have to start all over again. I can't say now what I exactly did.
import SQL generate documents and received these errors. I have
had to add the extra step of first importing them into a spreadsheet
and then importing into SQL Server 2005. If anyone knows of a
patch, I would love to know.|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||
hi jaypee,
I have just tried using the bulk insert as follows
BULK INSERT dbo.[table]
FROM 'Y:\data.csv'
with (FIELDTERMINATOR=',',ROWTERMINATOR='\n',CODEPAGE='ACP',FIRSTROW=2)
and I ge the following error for all the Datetime columns in the file.
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (EnteredDateTime).
any ideas?
Error while importing Text file using Import Export Wizard
Hi all
i have text file where i can import it to excel to access or sql2000 without problem but when i import it using (sql2005 pro) i get this error message during the import
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Success)
- Prepare for Execute (Success)
- Pre-execute (Success)
Messages
Information 0x402090dc: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has started.
(SQL Server Import and Export Wizard)
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "FRDNAME" (25)" failed because truncation occurred, and the truncation row disposition on "output column "FRDNAME" (25)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" on data row 9.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - All_Alarm5_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)
- Copying to [DXB_Data].[dbo].[All_Alarm5] (Stopped)
- Post-execute (Success)
Messages
Information 0x402090dd: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has ended.
(SQL Server Import and Export Wizard)
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)
Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)
- Cleanup (Success)
Messages
Information 0x4004300b: Data Flow Task: "component "Destination - All_Alarm5" (64)" wrote 0 rows.
(SQL Server Import and Export Wizard)
=============================
**** from the error message (Executing) error number 2 and 3 it shows that the error is comming from the (column FRDNAME) and row number 9 ...
this cells contains the following text: ;Ruwais5 B60_Baynounah_R_H_Camp_PH rack1 shelf1 db4g 0;
where the ";" is the delimiter
moreover when i remove this row other problem comes in diffirent location with the following text: ;((AvailabilityStatus,failed ,),(OperationalState,disabled ,));
where the ";" is the delimiter
**** from the error message (Executing) error number 1
there is somthing called ( code page ) this can be modified from the wizard it self and there are many options to choose. i tryed many of them but without solution
i hope that i will find solution for my problem
thank you
Looking at the error messages it looks as though there's a truncation going on. What is the length of the value in the FRDNAME column in row 9? If that is longer than the length of the target field - that is the problem.
Import/Export wizard offers you the chance to save your package. Do that - then open it up and look at the metadata of the pipeline. See what the length of the FRDNAME field is.
-Jamie|||dear sir
thank for ur replay
about what u say
Import/Export wizard offers you the chance to save your package
i didnt know how to do it?
is it in the Import/Export wizard then in column maping click in Edit SQL ...
also when u say
then open it up and look at the metadata of the pipeline
i didnt under stand it ..... sorry
but after long investegation i found the following
i instal SQL2000 and SQL2005 and i create table inside each of them with the
same spacifecation as follows
all the colums with data type: varchar and length: 8000 for both tabels.
i fined somthing very strange
- when i use the Import/Export wizard of SQL2000
i can import the Text file to the table in SQL2000 and to the table in SQL2005
- when i use the Import/Export wizard of SQL2005
i can't import the same Text file to the table in SQL2000 and also can't import it to the table in SQL2005
- when i check the length of the value in the FRDNAME column in row 9 in the text file i found it 53 and when i reduce the length less than 50 for that value
i found that the Import/Export wizard of SQL2005 works for both table in SQL2000 and to the table in SQL2005
Even both table have length for all colums as 8000 also i change this length to 100, 500, 1000 and other value but the same result !!!!!!!!
|||I'm convinced also that some kind of bug must exist here. I keep getting this error:
The "output column "XXXX" (42)" failed because truncation occurred, and the truncation row disposition on "output column "XXXX" (42)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
This makes no sense. The data in question is nowhere near large enough to cause a truncation. All the columns are properly mapped. All of the rows are properly delimited.
I've tried to make sense of the "Error Output" tab on the OLE DB Destination Editor, but I can't make any sense of it. If I could maybe I could find a way to tell SSIS to ignore whatever fictional truncation errors it is making.
Dan
|||OK, time for me to eat some crow.
Long story short, turns out there was a corruption being introduced in the flat file from a zip extraction library. I was having this problem with multiple files, all of which had been extracted in the same way. The corruption did not exist in another copy of the file that I had, which is why I was so convinced that there was nothing wrong with the file. The copy of the file that was testing with BULK INSERT directly had the same corruption.
My apologies to the SQL team at Microsoft for being so convinced there was a bug here. :-)
Dan
|||
Is anyone still getting this problem? I get the error when importing a flat file into sql2005 but don't when importing to sql2000 using the exact same file.
Thanks!!
|||I get the same error while trying to import more then 50 characters. Same text file imports fine to sql2000|||I get the same error on all our development and production machines.
I believe that this is a Microsoft SQL Server 2005 *BUG*, yes a bug, because I can import the same data on sql 2000. Because our production environment is already running sql 2005 I had no choice but to leave the dts packages on a sql 2000 machine and simply point the connections to the new sql2005 box and that works 100%.
This will just show that there is a bug in SSIS 2005.
Please help MS! This is a big problem for us!
|||I agree. I have the same problem. I tried to import a basic file with 3 columns ( varchar(50) ) and I get the same error message? The file is ok because the import in SQL Server 2000 works fine.
At first I thought that scandinavian characters are the problem but they are not.
Can anyone help us?
|||I am quite convinced that the there is a bug in Import export wizard.
It looks like that wizard can handle only 50 characters tops in one column.
I resolved the problem by making the bulk insert in sql.
BULK INSERT dbo.[tablename] FROM 'c:\temp\bulkinsertfile.csv' WITH (FIELDTERMINATOR=';',ROWTERMINATOR='\n',CODEPAGE = 'ACP',FIRSTROW=2)
Hopefully this issue is resolved and fixed by Microsoft soon...
|||The flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.
Thanks.
|||Thanks for your answer,
I noticed that the default values for varchar fields are 50. I changed the values in Column Mapping -window but it does not take affect. I get the same error message even if I change the size of the column.
When I changed the values in Advanced page as you told everything works fine ;)
I gues that Column Mapping window does not do the same "trick" as the Advanced page...That suggest types works fine.
There are some problems in SSIS. When import fails and I have to change some parameters (not closing the window first).
I occasionally get the error messages and I have to start all over again. I can't say now what I exactly did.
|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||
hi jaypee,
I have just tried using the bulk insert as follows
BULK INSERT dbo.[table]
FROM 'Y:\data.csv'
with (FIELDTERMINATOR=',',ROWTERMINATOR='\n',CODEPAGE='ACP',FIRSTROW=2)
and I ge the following error for all the Datetime columns in the file.
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (EnteredDateTime).
any ideas?
Error while importing Text file using Import Export Wizard
Hi all
i have text file where i can import it to excel to access or sql2000 without problem but when i import it using (sql2005 pro) i get this error message during the import
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Success)
- Prepare for Execute (Success)
- Pre-execute (Success)
Messages
Information 0x402090dc: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has started.
(SQL Server Import and Export Wizard)
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "FRDNAME" (25)" failed because truncation occurred, and the truncation row disposition on "output column "FRDNAME" (25)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" on data row 9.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - All_Alarm5_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)
- Copying to [DXB_Data].[dbo].[All_Alarm5] (Stopped)
- Post-execute (Success)
Messages
Information 0x402090dd: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has ended.
(SQL Server Import and Export Wizard)
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)
Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)
- Cleanup (Success)
Messages
Information 0x4004300b: Data Flow Task: "component "Destination - All_Alarm5" (64)" wrote 0 rows.
(SQL Server Import and Export Wizard)
=============================
**** from the error message (Executing) error number 2 and 3 it shows that the error is comming from the (column FRDNAME) and row number 9 ...
this cells contains the following text: ;Ruwais5 B60_Baynounah_R_H_Camp_PH rack1 shelf1 db4g 0;
where the ";" is the delimiter
moreover when i remove this row other problem comes in diffirent location with the following text: ;((AvailabilityStatus,failed ,),(OperationalState,disabled ,));
where the ";" is the delimiter
**** from the error message (Executing) error number 1
there is somthing called ( code page ) this can be modified from the wizard it self and there are many options to choose. i tryed many of them but without solution
i hope that i will find solution for my problem
thank you
Looking at the error messages it looks as though there's a truncation going on. What is the length of the value in the FRDNAME column in row 9? If that is longer than the length of the target field - that is the problem.
Import/Export wizard offers you the chance to save your package. Do that - then open it up and look at the metadata of the pipeline. See what the length of the FRDNAME field is.
-Jamie|||dear sir
thank for ur replay
about what u say
Import/Export wizard offers you the chance to save your package
i didnt know how to do it?
is it in the Import/Export wizard then in column maping click in Edit SQL ...
also when u say
then open it up and look at the metadata of the pipeline
i didnt under stand it ..... sorry
but after long investegation i found the following
i instal SQL2000 and SQL2005 and i create table inside each of them with the
same spacifecation as follows
all the colums with data type: varchar and length: 8000 for both tabels.
i fined somthing very strange
- when i use the Import/Export wizard of SQL2000
i can import the Text file to the table in SQL2000 and to the table in SQL2005
- when i use the Import/Export wizard of SQL2005
i can't import the same Text file to the table in SQL2000 and also can't import it to the table in SQL2005
- when i check the length of the value in the FRDNAME column in row 9 in the text file i found it 53 and when i reduce the length less than 50 for that value
i found that the Import/Export wizard of SQL2005 works for both table in SQL2000 and to the table in SQL2005
Even both table have length for all colums as 8000 also i change this length to 100, 500, 1000 and other value but the same result !!!!!!!!
|||I'm convinced also that some kind of bug must exist here. I keep getting this error:
The "output column "XXXX" (42)" failed because truncation occurred, and the truncation row disposition on "output column "XXXX" (42)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
This makes no sense. The data in question is nowhere near large enough to cause a truncation. All the columns are properly mapped. All of the rows are properly delimited.
I've tried to make sense of the "Error Output" tab on the OLE DB Destination Editor, but I can't make any sense of it. If I could maybe I could find a way to tell SSIS to ignore whatever fictional truncation errors it is making.
Dan
|||OK, time for me to eat some crow.
Long story short, turns out there was a corruption being introduced in the flat file from a zip extraction library. I was having this problem with multiple files, all of which had been extracted in the same way. The corruption did not exist in another copy of the file that I had, which is why I was so convinced that there was nothing wrong with the file. The copy of the file that was testing with BULK INSERT directly had the same corruption.
My apologies to the SQL team at Microsoft for being so convinced there was a bug here. :-)
Dan
|||
Is anyone still getting this problem? I get the error when importing a flat file into sql2005 but don't when importing to sql2000 using the exact same file.
Thanks!!
|||I get the same error while trying to import more then 50 characters. Same text file imports fine to sql2000|||I get the same error on all our development and production machines.
I believe that this is a Microsoft SQL Server 2005 *BUG*, yes a bug, because I can import the same data on sql 2000. Because our production environment is already running sql 2005 I had no choice but to leave the dts packages on a sql 2000 machine and simply point the connections to the new sql2005 box and that works 100%.
This will just show that there is a bug in SSIS 2005.
Please help MS! This is a big problem for us!
|||I agree. I have the same problem. I tried to import a basic file with 3 columns ( varchar(50) ) and I get the same error message? The file is ok because the import in SQL Server 2000 works fine.
At first I thought that scandinavian characters are the problem but they are not.
Can anyone help us?
|||I am quite convinced that the there is a bug in Import export wizard.
It looks like that wizard can handle only 50 characters tops in one column.
I resolved the problem by making the bulk insert in sql.
BULK INSERT dbo.[tablename] FROM 'c:\temp\bulkinsertfile.csv' WITH (FIELDTERMINATOR=';',ROWTERMINATOR='\n',CODEPAGE = 'ACP',FIRSTROW=2)
Hopefully this issue is resolved and fixed by Microsoft soon...
|||The flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.
Thanks.
|||Thanks for your answer,
I noticed that the default values for varchar fields are 50. I changed the values in Column Mapping -window but it does not take affect. I get the same error message even if I change the size of the column.
When I changed the values in Advanced page as you told everything works fine ;)
I gues that Column Mapping window does not do the same "trick" as the Advanced page...That suggest types works fine.
There are some problems in SSIS. When import fails and I have to change some parameters (not closing the window first).
I occasionally get the error messages and I have to start all over again. I can't say now what I exactly did.
|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||
hi jaypee,
I have just tried using the bulk insert as follows
BULK INSERT dbo.[table]
FROM 'Y:\data.csv'
with (FIELDTERMINATOR=',',ROWTERMINATOR='\n',CODEPAGE='ACP',FIRSTROW=2)
and I ge the following error for all the Datetime columns in the file.
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (EnteredDateTime).
any ideas?
Error while importing Text file using Import Export Wizard
Hi all
i have text file where i can import it to excel to access or sql2000 without problem but when i import it using (sql2005 pro) i get this error message during the import
Operation stopped...
- Initializing Data Flow Task (Success)
- Initializing Connections (Success)
- Setting SQL Command (Success)
- Setting Source Connection (Success)
- Setting Destination Connection (Success)
- Validating (Success)
- Prepare for Execute (Success)
- Pre-execute (Success)
Messages
Information 0x402090dc: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has started.
(SQL Server Import and Export Wizard)
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "FRDNAME" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task: The "output column "FRDNAME" (25)" failed because truncation occurred, and the truncation row disposition on "output column "FRDNAME" (25)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task: An error occurred while processing file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" on data row 9.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - All_Alarm5_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)
Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)
- Copying to [DXB_Data].[dbo].[All_Alarm5] (Stopped)
- Post-execute (Success)
Messages
Information 0x402090dd: Data Flow Task: The processing of file "C:\Documents and Settings\Wail\Desktop\All_Alarm5.txt" has ended.
(SQL Server Import and Export Wizard)
Information 0x402090df: Data Flow Task: The final commit for the data insertion has started.
(SQL Server Import and Export Wizard)
Information 0x402090e0: Data Flow Task: The final commit for the data insertion has ended.
(SQL Server Import and Export Wizard)
- Cleanup (Success)
Messages
Information 0x4004300b: Data Flow Task: "component "Destination - All_Alarm5" (64)" wrote 0 rows.
(SQL Server Import and Export Wizard)
=============================
**** from the error message (Executing) error number 2 and 3 it shows that the error is comming from the (column FRDNAME) and row number 9 ...
this cells contains the following text: ;Ruwais5 B60_Baynounah_R_H_Camp_PH rack1 shelf1 db4g 0;
where the ";" is the delimiter
moreover when i remove this row other problem comes in diffirent location with the following text: ;((AvailabilityStatus,failed ,),(OperationalState,disabled ,));
where the ";" is the delimiter
**** from the error message (Executing) error number 1
there is somthing called ( code page ) this can be modified from the wizard it self and there are many options to choose. i tryed many of them but without solution
i hope that i will find solution for my problem
thank youLooking at the error messages it looks as though there's a truncation going on. What is the length of the value in the FRDNAME column in row 9? If that is longer than the length of the target field - that is the problem.
Import/Export wizard offers you the chance to save your package. Do that - then open it up and look at the metadata of the pipeline. See what the length of the FRDNAME field is.
-Jamie|||dear sir
thank for ur replay
about what u say
Import/Export wizard offers you the chance to save your package
i didnt know how to do it?
is it in the Import/Export wizard then in column maping click in Edit SQL ...
also when u say
then open it up and look at the metadata of the pipeline
i didnt under stand it ..... sorry
but after long investegation i found the following
i instal SQL2000 and SQL2005 and i create table inside each of them with the
same spacifecation as follows
all the colums with data type: varchar and length: 8000 for both tabels.
i fined somthing very strange
- when i use the Import/Export wizard of SQL2000
i can import the Text file to the table in SQL2000 and to the table in SQL2005
- when i use the Import/Export wizard of SQL2005
i can't import the same Text file to the table in SQL2000 and also can't import it to the table in SQL2005
- when i check the length of the value in the FRDNAME column in row 9 in the text file i found it 53 and when i reduce the length less than 50 for that value
i found that the Import/Export wizard of SQL2005 works for both table in SQL2000 and to the table in SQL2005
Even both table have length for all colums as 8000 also i change this length to 100, 500, 1000 and other value but the same result !!!!!!!!
|||I'm convinced also that some kind of bug must exist here. I keep getting this error:
The "output column "XXXX" (42)" failed because truncation occurred, and the truncation row disposition on "output column "XXXX" (42)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
This makes no sense. The data in question is nowhere near large enough to cause a truncation. All the columns are properly mapped. All of the rows are properly delimited.
I've tried to make sense of the "Error Output" tab on the OLE DB Destination Editor, but I can't make any sense of it. If I could maybe I could find a way to tell SSIS to ignore whatever fictional truncation errors it is making.
Dan|||OK, time for me to eat some crow.
Long story short, turns out there was a corruption being introduced in the flat file from a zip extraction library. I was having this problem with multiple files, all of which had been extracted in the same way. The corruption did not exist in another copy of the file that I had, which is why I was so convinced that there was nothing wrong with the file. The copy of the file that was testing with BULK INSERT directly had the same corruption.
My apologies to the SQL team at Microsoft for being so convinced there was a bug here. :-)
Dan|||
Is anyone still getting this problem? I get the error when importing a flat file into sql2005 but don't when importing to sql2000 using the exact same file.
Thanks!!
|||I get the same error while trying to import more then 50 characters. Same text file imports fine to sql2000|||I get the same error on all our development and production machines.
I believe that this is a Microsoft SQL Server 2005 *BUG*, yes a bug, because I can import the same data on sql 2000. Because our production environment is already running sql 2005 I had no choice but to leave the dts packages on a sql 2000 machine and simply point the connections to the new sql2005 box and that works 100%.
This will just show that there is a bug in SSIS 2005.
Please help MS! This is a big problem for us!
|||I agree. I have the same problem. I tried to import a basic file with 3 columns ( varchar(50) ) and I get the same error message? The file is ok because the import in SQL Server 2000 works fine.
At first I thought that scandinavian characters are the problem but they are not.
Can anyone help us?
|||I am quite convinced that the there is a bug in Import export wizard.
It looks like that wizard can handle only 50 characters tops in one column.
I resolved the problem by making the bulk insert in sql.
BULK INSERT dbo.[tablename] FROM 'c:\temp\bulkinsertfile.csv' WITH (FIELDTERMINATOR=';',ROWTERMINATOR='\n',CODEPAGE = 'ACP',FIRSTROW=2)
Hopefully this issue is resolved and fixed by Microsoft soon...
|||The flat file defaults initially all columns to characters with length of 50. You can change this by going to the “Advanced” page of the Flat File connection page and change the length manually, or you can click on "Suggest Types..." to get suggested column metadata attributes based on sampling a certain number of rows from the file.
Thanks.
|||Thanks for your answer,
I noticed that the default values for varchar fields are 50. I changed the values in Column Mapping -window but it does not take affect. I get the same error message even if I change the size of the column.
When I changed the values in Advanced page as you told everything works fine ;)
I gues that Column Mapping window does not do the same "trick" as the Advanced page...That suggest types works fine.
There are some problems in SSIS. When import fails and I have to change some parameters (not closing the window first).
I occasionally get the error messages and I have to start all over again. I can't say now what I exactly did.
import SQL generate documents and received these errors. I have
had to add the extra step of first importing them into a spreadsheet
and then importing into SQL Server 2005. If anyone knows of a
patch, I would love to know.|||I have been getting the same error trying to export an XML file into a SQL SERVER 2005 database.|||
hi jaypee,
I have just tried using the bulk insert as follows
BULK INSERT dbo.[table]
FROM 'Y:\data.csv'
with (FIELDTERMINATOR=',',ROWTERMINATOR='\n',CODEPAGE='ACP',FIRSTROW=2)
and I ge the following error for all the Datetime columns in the file.
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 15 (EnteredDateTime).
any ideas?
Subscribe to:
Posts (Atom)