Error executing User Defined function on another server

Hi All,
Facing a problem while executing a user defined function (UDF) which resides
on another sql server ( we have access on this sql server to execute the
UDF). This UDF accepts a varchar parameter with max length 8. When we
execute the function as
Select * from
SERVERNAME.DBName.dbo. fn_GetConsulteesDataForReport('00118179'
) it throws an
error as given below.
Server: Msg 170, Level 15, State 31, Line 1
Line 2: Incorrect syntax near '('.
Please let me know what is the issue in my SQL statement and how we can
resolve this.
Thanks in advance.
SuhasThere's a workaround for this hm... feature. Use the OPENQUERY function:
select *
from openquery(SERVERNAME, '
select *
from
DBName.dbo. fn_GetConsulteesDataForReport('00118179'
)
')
ML
http://milambda.blogspot.com/|||Hi ML,
Thank you for giving this suggestion. But please let me know whether open
query does accept variable as an input to the function ? I mean, can I
execute the same as given below.
declare @.empid varchar(8)
select @.empid = '00118179'
select *
from openquery(SERVERNAME, '
select *
from
DBName.dbo.fn_GetConsulteesDataForReport(@.empid)
')
Becuase we need to dynamically change this employee ID.
Thank you.
Suhas
"ML" wrote:

> There's a workaround for this hm... feature. Use the OPENQUERY function:
> select *
> from openquery(SERVERNAME, '
> select *
> from
> DBName.dbo. fn_GetConsulteesDataForReport('00118179'
)
> ')
>
> ML
> --
> http://milambda.blogspot.com/|||For actual production use I'd suggest designing a local UDF, that references
tables in the database on the linked server. This way you can call the
function directly, and still access the data remotely.
ML
http://milambda.blogspot.com/|||Sorry, I forgot to mention, that OPENQUERY does not accept parameters or
variables. Read more here:
http://msdn.microsoft.com/library/d...br />
5xix.asp
ML
http://milambda.blogspot.com/

error enabling AWE to access >2GB of memory

hi,

I am having a problem enabling this, I am getting the error message:

Alter failed. (Microsoft.SqlServer.Smo)

Aditional information:

An exception occurred while executing a Transact-SQl Statement or batch
(Microsoft.SqlServer.ConnectionInfo)

Address Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install (Microsoft SQL Server, Error: 5845)

-- end of error.

Prior to this, I opened up gpedit.msc, and associated the Administrator on the "lock pages in memory" policy.

I rebooted the server thinking that this might require it, but it didn't. I still cannot access memory .2Gb.

Any ideas?

cheers.hi,

anyone who can help?

thanks

Error during restore operation

Hi all,
I installed SQL server 2000 on my machine first.
Then i just installed SQL server 2005 tools on above it to get
management studio access.
Now i am trying to restore a database on my machine from backup which
i took from server running SQL server 2005.
i am getting the error-
Too many backup devices specified for backup or restore; only 64 are
allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
Server, Error:3205)
Does anybody know how do i go about to resolve it.
Thanks a lot.
I did some Google which suggests you get this error if you try to restore a 2005 backup to 2000.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<nitinluthra@.gmail.com> wrote in message
news:c1f4f806-807c-4f03-9757-43b9401d89b8@.a5g2000prg.googlegroups.com...
> Hi all,
> I installed SQL server 2000 on my machine first.
> Then i just installed SQL server 2005 tools on above it to get
> management studio access.
> Now i am trying to restore a database on my machine from backup which
> i took from server running SQL server 2005.
> i am getting the error-
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
> Server, Error:3205)
> Does anybody know how do i go about to resolve it.
> Thanks a lot.
|||I reproduced the error, that's exactly an error message you get when you try
to restore a backup that is taken from a SQL Server 2005 instance.
Ekrem nsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzMom43nIHA.3652@.TK2MSFTNGP03.phx.gbl...
>I did some Google which suggests you get this error if you try to restore a
>2005 backup to 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <nitinluthra@.gmail.com> wrote in message
> news:c1f4f806-807c-4f03-9757-43b9401d89b8@.a5g2000prg.googlegroups.com...
>
sql

Error during restore operation

Hi all,
I installed SQL server 2000 on my machine first.
Then i just installed SQL server 2005 tools on above it to get
management studio access.
Now i am trying to restore a database on my machine from backup which
i took from server running SQL server 2005.
i am getting the error-
Too many backup devices specified for backup or restore; only 64 are
allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
Server, Error:3205)
Does anybody know how do i go about to resolve it.
Thanks a lot.I did some Google which suggests you get this error if you try to restore a 2005 backup to 2000.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<nitinluthra@.gmail.com> wrote in message
news:c1f4f806-807c-4f03-9757-43b9401d89b8@.a5g2000prg.googlegroups.com...
> Hi all,
> I installed SQL server 2000 on my machine first.
> Then i just installed SQL server 2005 tools on above it to get
> management studio access.
> Now i am trying to restore a database on my machine from backup which
> i took from server running SQL server 2005.
> i am getting the error-
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
> Server, Error:3205)
> Does anybody know how do i go about to resolve it.
> Thanks a lot.|||I reproduced the error, that's exactly an error message you get when you try
to restore a backup that is taken from a SQL Server 2005 instance.
--
Ekrem Önsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OzMom43nIHA.3652@.TK2MSFTNGP03.phx.gbl...
>I did some Google which suggests you get this error if you try to restore a
>2005 backup to 2000.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <nitinluthra@.gmail.com> wrote in message
> news:c1f4f806-807c-4f03-9757-43b9401d89b8@.a5g2000prg.googlegroups.com...
>> Hi all,
>> I installed SQL server 2000 on my machine first.
>> Then i just installed SQL server 2005 tools on above it to get
>> management studio access.
>> Now i am trying to restore a database on my machine from backup which
>> i took from server running SQL server 2005.
>> i am getting the error-
>> Too many backup devices specified for backup or restore; only 64 are
>> allowed. RESTORE HEADER ONLY is terminating abnormally.(Microsoft SQL
>> Server, Error:3205)
>> Does anybody know how do i go about to resolve it.
>> Thanks a lot.
>

Error during Removing the DB access to BUILTIN\Administrators

Hi all,
I want to remove the Access of Particular DB to BUILTIN\Administrators -
Windows Group.
But when am trying to remove am getting the error message " The Database
Owner Cannot be Dropped ".
please give me the solution for this...
Thanks,
HerbertSuggest changing the database owner to someone else and then try.
There is a SP for this :- sp_changedbowner
HTH. Ryan
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:5AD1859D-F02F-4E0B-966E-DF1B8011DA15@.microsoft.com...
> Hi all,
> I want to remove the Access of Particular DB to BUILTIN\Administrators -
> Windows Group.
> But when am trying to remove am getting the error message " The Database
> Owner Cannot be Dropped ".
> please give me the solution for this...
> Thanks,
> Herbert|||Hi Ryan,
thanks for your immediate reply
By using that SP i have changed the Owner of DB. But still same error is
coming.
In the Users list of that DB, after changing the Owner also name is showing
as dbo and loginname is changed to new name which i gave in sp_changedbowner
Procedure.
please suggest me what to do...
Thanks,
Herbert
"Ryan" wrote:

> Suggest changing the database owner to someone else and then try.
> There is a SP for this :- sp_changedbowner
> --
> HTH. Ryan
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:5AD1859D-F02F-4E0B-966E-DF1B8011DA15@.microsoft.com...
>
>|||Before removing it you need to ensure that none of the SQL Services are
using the local admin account
There are a number of related support articles :-
http://support.microsoft.com/defaul...kb;EN-US;237604
http://support.microsoft.com/defaul...kb;EN-US;295034
http://support.microsoft.com/defaul...b;EN-US;Q263712
HTH. Ryan
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:494309B1-DF63-41AB-8F0D-CE4A0DACC564@.microsoft.com...
> Hi Ryan,
> thanks for your immediate reply
> By using that SP i have changed the Owner of DB. But still same error is
> coming.
> In the Users list of that DB, after changing the Owner also name is
> showing
> as dbo and loginname is changed to new name which i gave in
> sp_changedbowner
> Procedure.
> please suggest me what to do...
> Thanks,
> Herbert
>
> "Ryan" wrote:
>|||SQLServerCentral did this piece on the topic ...
http://www.sqlservercentral.com/col...fallstoavoi.asp
HTH. Ryan
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eVI0fZBHGHA.2912@.tk2msftngp13.phx.gbl...
> Before removing it you need to ensure that none of the SQL Services are
> using the local admin account
> There are a number of related support articles :-
> http://support.microsoft.com/defaul...kb;EN-US;237604
> http://support.microsoft.com/defaul...kb;EN-US;295034
> http://support.microsoft.com/defaul...b;EN-US;Q263712
>
> --
> HTH. Ryan
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:494309B1-DF63-41AB-8F0D-CE4A0DACC564@.microsoft.com...
>

Error during configuring distribution wizard

TITLE: Configure Distribution Wizard

An error occurred configuring SQL Server Agent.

ADDITIONAL INFORMATION:

SQL Server blocked access to procedure 'dbo.sp_set_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)

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

How do I solve this?

Thanks,

Frank

System Engineer

I think the solution is pretty clear inside the error message. Did you have problems following the instructions?|||

The worst part, as with many of the error messages, the link in the error takes you to a "sorry no information is available about this problem". Don't know what broke in that process, but I have seen that exact page come up several times while checking alerts in MOM. I hope it is something that will be fixed soon because it seems pretty sad that a link in internal help provides "No Help".

Allen

|||

Please execute

The following shows the available options

USE master;

GO

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE;

EXEC sp_configure;

The following enables the Agent XPs

EXEC sp_configure 'Agent XPs', '1';

|||

Hi,

Has anybody get the solution as I am getting the same error while installing sqlserver 2005 express on my window xp.

pls see below

TITLE: Microsoft SQL Server 2005 Setup

SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Cannot add functional unit 'sp_sqlagent_get_startup_info' to component 'Agent XPs'. This unit has been already registered with the component.. Refer to the server error logs and Setup logs for detailed error information.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40sysdbupg.sql%40107%40x3bff

BUTTONS:

&Retry
Cancel

Thanks

|||

SQL Server 2005 Express does not have a SQL Server agent. Nor can it be configured as a distributor.

Exactly how are you attempting to configure it as a distributor?

From http://msdn2.microsoft.com/en-us/library/ms151819.aspx

SQL Server Express cannot serve as a Publisher or Distributor. However, merge replication allows changes to be replicated in both directions between a Publisher and Subscriber.

|||

try this :

Change the system date to cureent day

Error during configuring distribution wizard

TITLE: Configure Distribution Wizard

An error occurred configuring SQL Server Agent.

ADDITIONAL INFORMATION:

SQL Server blocked access to procedure 'dbo.sp_set_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)

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

How do I solve this?

Thanks,

Frank

System Engineer

I think the solution is pretty clear inside the error message. Did you have problems following the instructions?|||

The worst part, as with many of the error messages, the link in the error takes you to a "sorry no information is available about this problem". Don't know what broke in that process, but I have seen that exact page come up several times while checking alerts in MOM. I hope it is something that will be fixed soon because it seems pretty sad that a link in internal help provides "No Help".

Allen

|||

Please execute

The following shows the available options

USE master;

GO

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE;

EXEC sp_configure;

The following enables the Agent XPs

EXEC sp_configure 'Agent XPs', '1';

|||

Hi,

Has anybody get the solution as I am getting the same error while installing sqlserver 2005 express on my window xp.

pls see below

TITLE: Microsoft SQL Server 2005 Setup

SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Cannot add functional unit 'sp_sqlagent_get_startup_info' to component 'Agent XPs'. This unit has been already registered with the component.. Refer to the server error logs and Setup logs for detailed error information.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40sysdbupg.sql%40107%40x3bff

BUTTONS:

&Retry
Cancel

Thanks

|||

SQL Server 2005 Express does not have a SQL Server agent. Nor can it be configured as a distributor.

Exactly how are you attempting to configure it as a distributor?

From http://msdn2.microsoft.com/en-us/library/ms151819.aspx

SQL Server Express cannot serve as a Publisher or Distributor. However, merge replication allows changes to be replicated in both directions between a Publisher and Subscriber.

|||

try this :

Change the system date to cureent day

sql

Error during configuring distribution wizard

TITLE: Configure Distribution Wizard

An error occurred configuring SQL Server Agent.

ADDITIONAL INFORMATION:

SQL Server blocked access to procedure 'dbo.sp_set_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)

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

How do I solve this?

Thanks,

Frank

System Engineer

I think the solution is pretty clear inside the error message. Did you have problems following the instructions?|||

The worst part, as with many of the error messages, the link in the error takes you to a "sorry no information is available about this problem". Don't know what broke in that process, but I have seen that exact page come up several times while checking alerts in MOM. I hope it is something that will be fixed soon because it seems pretty sad that a link in internal help provides "No Help".

Allen

|||

Please execute

The following shows the available options

USE master;

GO

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE;

EXEC sp_configure;

The following enables the Agent XPs

EXEC sp_configure 'Agent XPs', '1';

|||

Hi,

Has anybody get the solution as I am getting the same error while installing sqlserver 2005 express on my window xp.

pls see below

TITLE: Microsoft SQL Server 2005 Setup

SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Cannot add functional unit 'sp_sqlagent_get_startup_info' to component 'Agent XPs'. This unit has been already registered with the component.. Refer to the server error logs and Setup logs for detailed error information.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40sysdbupg.sql%40107%40x3bff

BUTTONS:

&Retry
Cancel

Thanks

|||

SQL Server 2005 Express does not have a SQL Server agent. Nor can it be configured as a distributor.

Exactly how are you attempting to configure it as a distributor?

From http://msdn2.microsoft.com/en-us/library/ms151819.aspx

SQL Server Express cannot serve as a Publisher or Distributor. However, merge replication allows changes to be replicated in both directions between a Publisher and Subscriber.

|||

try this :

Change the system date to cureent day

Error during configuring distribution wizard

TITLE: Configure Distribution Wizard

An error occurred configuring SQL Server Agent.

ADDITIONAL INFORMATION:

SQL Server blocked access to procedure 'dbo.sp_set_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)

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

How do I solve this?

Thanks,

Frank

System Engineer

I think the solution is pretty clear inside the error message. Did you have problems following the instructions?|||

The worst part, as with many of the error messages, the link in the error takes you to a "sorry no information is available about this problem". Don't know what broke in that process, but I have seen that exact page come up several times while checking alerts in MOM. I hope it is something that will be fixed soon because it seems pretty sad that a link in internal help provides "No Help".

Allen

|||

Please execute

The following shows the available options

USE master;

GO

EXEC sp_configure 'show advanced option', '1';

RECONFIGURE;

EXEC sp_configure;

The following enables the Agent XPs

EXEC sp_configure 'Agent XPs', '1';

|||

Hi,

Has anybody get the solution as I am getting the same error while installing sqlserver 2005 express on my window xp.

pls see below

TITLE: Microsoft SQL Server 2005 Setup

SQL Server Setup failed to execute a command for server configuration. The error was [Microsoft][SQL Native Client][SQL Server]Cannot add functional unit 'sp_sqlagent_get_startup_info' to component 'Agent XPs'. This unit has been already registered with the component.. Refer to the server error logs and Setup logs for detailed error information.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=setup.rll&EvtID=29521&EvtType=lib%5codbc_statement.h%40Do_sqlScript%40OdbcStatement%3a%3aexecute_batch%40sysdbupg.sql%40107%40x3bff

BUTTONS:

&Retry
Cancel

Thanks

|||

SQL Server 2005 Express does not have a SQL Server agent. Nor can it be configured as a distributor.

Exactly how are you attempting to configure it as a distributor?

From http://msdn2.microsoft.com/en-us/library/ms151819.aspx

SQL Server Express cannot serve as a Publisher or Distributor. However, merge replication allows changes to be replicated in both directions between a Publisher and Subscriber.

|||

try this :

Change the system date to cureent day

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Hi

I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.

[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Please help.

Thanks in advance,
Ramzee

According to BOL (http://msdn2.microsoft.com/en-us/library/ms175970.aspx) the types can be converted, so that should work. Have you identified the values that fail? Using the error output is a very easy way to capture this data. My best guess for now is the values are out of range for the SQL datetime type. A derived column transformation could be used to massage these values depending on requirements.|||

Thanks Darren!

I used the script component to identify the wrong date entries. Now it works!

Thanks once again.

|||

I am having the same error. A simple select from Query Analyzer

Select * from HCPROD..HC.UNIT U

Even if I specifically reference a number column I get the same error

Select BLOCK_ID from HCPROD..HC.UNIT U

The way I see it is that there is a column, probably the DATE_MODIFIED column, somewhere in the database that has a date & time value that the driver cannot process. Why the error occurs even if I do not reference the column is a mystery.

I am using Microsoft OLE DB Provider for Oracle

Note that I have insured that every date field in the table is in 20th or 21st century

|||Some more information. We are using Oracle version 10g. I wonder if there is an updated driver from Microsoft? We searched and could not find one ourselves.|||SELECT TOP 10 * FROM [ORACLE_LOOKUP]..[SchemaName].[TableName] AlaisName

Error converting data type DBTYPE_DBTIMESTAMP to datetime

SELECT * FROM
OPENQUERY (
ORACLE_LOOKUP,
'SELECT TO_CHAR(DateColumnName) FROM TableName')

The following workaround was foud on the internet

A workaround would be to use the OPENQUERY function in SQL Server. This
function allows you to execute a pass-through query on the linked server.
By issuing a pass-through query, you can then take advantage of Oracle's
built-in functions to convert the date to a character data type or NULL.
Examples of both are as follows:

In the following example, the column "hiredate" is converted to a string in
the format of MM-DD-YYYY.

SELECT *
FROM OPENQUERY(, 'SELECT empno,
TO_CHAR(hiredate,''MM-DD-YYYY'') FROM scott.emp')

In this example we use a combination of two Oracle built-in functions,
DECODE and GREATEST to convert any hiredate that is earlier than 01/01/1753
(SQL Server's lower bound) to NULL.

SELECT *
FROM OPENQUERY(,'SELECT DECODE (hiredate,
GREATEST(hiredate, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), hiredate, NULL)
FROM scott.emp')

In case you are not familiar with Oracle built-ins, here is a description of
the two used in the query above.

DECODE is similar to a case statement in SQL Server. Its syntax is as
follows :

DECODE(, , , , ,...,
)

The is compared to each value. If there is a match,
the corresponding is returned. Otherwise, the value is
returned.

GREATEST returns the greatest value in the list of expressions.

GREATEST(, , ...)
Reply With Quote

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Hi

I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.

[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Please help.

Thanks in advance,
Ramzee

According to BOL (http://msdn2.microsoft.com/en-us/library/ms175970.aspx) the types can be converted, so that should work. Have you identified the values that fail? Using the error output is a very easy way to capture this data. My best guess for now is the values are out of range for the SQL datetime type. A derived column transformation could be used to massage these values depending on requirements.|||

Thanks Darren!

I used the script component to identify the wrong date entries. Now it works!

Thanks once again.

|||

I am having the same error. A simple select from Query Analyzer

Select * from HCPROD..HC.UNIT U

Even if I specifically reference a number column I get the same error

Select BLOCK_ID from HCPROD..HC.UNIT U

The way I see it is that there is a column, probably the DATE_MODIFIED column, somewhere in the database that has a date & time value that the driver cannot process. Why the error occurs even if I do not reference the column is a mystery.

I am using Microsoft OLE DB Provider for Oracle

Note that I have insured that every date field in the table is in 20th or 21st century

|||Some more information. We are using Oracle version 10g. I wonder if there is an updated driver from Microsoft? We searched and could not find one ourselves.|||SELECT TOP 10 * FROM [ORACLE_LOOKUP]..[SchemaName].[TableName] AlaisName

Error converting data type DBTYPE_DBTIMESTAMP to datetime

SELECT * FROM
OPENQUERY (
ORACLE_LOOKUP,
'SELECT TO_CHAR(DateColumnName) FROM TableName')

The following workaround was foud on the internet

A workaround would be to use the OPENQUERY function in SQL Server. This
function allows you to execute a pass-through query on the linked server.
By issuing a pass-through query, you can then take advantage of Oracle's
built-in functions to convert the date to a character data type or NULL.
Examples of both are as follows:

In the following example, the column "hiredate" is converted to a string in
the format of MM-DD-YYYY.

SELECT *
FROM OPENQUERY(, 'SELECT empno,
TO_CHAR(hiredate,''MM-DD-YYYY'') FROM scott.emp')

In this example we use a combination of two Oracle built-in functions,
DECODE and GREATEST to convert any hiredate that is earlier than 01/01/1753
(SQL Server's lower bound) to NULL.

SELECT *
FROM OPENQUERY(,'SELECT DECODE (hiredate,
GREATEST(hiredate, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), hiredate, NULL)
FROM scott.emp')

In case you are not familiar with Oracle built-ins, here is a description of
the two used in the query above.

DECODE is similar to a case statement in SQL Server. Its syntax is as
follows :

DECODE(, , , , ,...,
)

The is compared to each value. If there is a match,
the corresponding is returned. Otherwise, the value is
returned.

GREATEST returns the greatest value in the list of expressions.

GREATEST(, , ...)
Reply With Quote

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Hi

I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.

[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Please help.

Thanks in advance,
Ramzee

According to BOL (http://msdn2.microsoft.com/en-us/library/ms175970.aspx) the types can be converted, so that should work. Have you identified the values that fail? Using the error output is a very easy way to capture this data. My best guess for now is the values are out of range for the SQL datetime type. A derived column transformation could be used to massage these values depending on requirements.|||

Thanks Darren!

I used the script component to identify the wrong date entries. Now it works!

Thanks once again.

|||

I am having the same error. A simple select from Query Analyzer

Select * from HCPROD..HC.UNIT U

Even if I specifically reference a number column I get the same error

Select BLOCK_ID from HCPROD..HC.UNIT U

The way I see it is that there is a column, probably the DATE_MODIFIED column, somewhere in the database that has a date & time value that the driver cannot process. Why the error occurs even if I do not reference the column is a mystery.

I am using Microsoft OLE DB Provider for Oracle

Note that I have insured that every date field in the table is in 20th or 21st century

|||Some more information. We are using Oracle version 10g. I wonder if there is an updated driver from Microsoft? We searched and could not find one ourselves.|||SELECT TOP 10 * FROM [ORACLE_LOOKUP]..[SchemaName].[TableName] AlaisName

Error converting data type DBTYPE_DBTIMESTAMP to datetime

SELECT * FROM
OPENQUERY (
ORACLE_LOOKUP,
'SELECT TO_CHAR(DateColumnName) FROM TableName')

The following workaround was foud on the internet

A workaround would be to use the OPENQUERY function in SQL Server. This
function allows you to execute a pass-through query on the linked server.
By issuing a pass-through query, you can then take advantage of Oracle's
built-in functions to convert the date to a character data type or NULL.
Examples of both are as follows:

In the following example, the column "hiredate" is converted to a string in
the format of MM-DD-YYYY.

SELECT *
FROM OPENQUERY(, 'SELECT empno,
TO_CHAR(hiredate,''MM-DD-YYYY'') FROM scott.emp')

In this example we use a combination of two Oracle built-in functions,
DECODE and GREATEST to convert any hiredate that is earlier than 01/01/1753
(SQL Server's lower bound) to NULL.

SELECT *
FROM OPENQUERY(,'SELECT DECODE (hiredate,
GREATEST(hiredate, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), hiredate, NULL)
FROM scott.emp')

In case you are not familiar with Oracle built-ins, here is a description of
the two used in the query above.

DECODE is similar to a case statement in SQL Server. Its syntax is as
follows :

DECODE(, , , , ,...,
)

The is compared to each value. If there is a match,
the corresponding is returned. Otherwise, the value is
returned.

GREATEST returns the greatest value in the list of expressions.

GREATEST(, , ...)
Reply With Quote

Error connection to MSDE

Hi all,
I have the Windows 2003 Server in my company and the MSDE SP3 install with
an instance for a program. But the program can't access the database, I have
tried to create a new data source to SQL Server in ODBC and it tells me SQL
Server Error: 11004 (Connection failed) and SQL Server Error: 6 (Specified
SQL server not found).
The MSDE is running with the name "SERVER\INSTANCE". I have enabled the
protocols in SVRNETCN.
Does anyone know what to do?
Thanks
Hi,
From the SQL server MSDE installed machine can you try to connect to SQL
server using both authentication using OSQL.
OSQL -Sservername -Usa -Ppassword
The aove command is suppose to go to a SQL Prompt. If it does then your SQL
server is working fine.If it is not look into sql serevr logs and find the
rason why sql server is down.
If your first stpe passed then, From command prompt execute a PING from your
client PC to MSDE server.
If it fail "Request time out" then it is a network issue.
Thanks
Hari
MCDBA
"Pedro" <pedronazario@.timetech.pt> wrote in message
news:4065ce47$0$3527$a729d347@.news.telepac.pt...
> Hi all,
>
> I have the Windows 2003 Server in my company and the MSDE SP3 install with
> an instance for a program. But the program can't access the database, I
have
> tried to create a new data source to SQL Server in ODBC and it tells me
SQL
> Server Error: 11004 (Connection failed) and SQL Server Error: 6 (Specified
> SQL server not found).
>
> The MSDE is running with the name "SERVER\INSTANCE". I have enabled the
> protocols in SVRNETCN.
>
> Does anyone know what to do?
>
> Thanks
>
|||By default only shared memory is enabled when you install MSDE. If the
connection is being made from a remote machine or uisng TCP/IP or named
pipes to connect locally the connection will fail. You can use svrnetcn.exe
(SQL Server Network Utiltiy) to enable TCP/IP and/or named pipes.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Error connection to MSDE

Hi all,
I have the Windows 2003 Server in my company and the MSDE SP3 install with
an instance for a program. But the program can't access the database, I have
tried to create a new data source to SQL Server in ODBC and it tells me SQL
Server Error: 11004 (Connection failed) and SQL Server Error: 6 (Specified
SQL server not found).
The MSDE is running with the name "SERVER\INSTANCE". I have enabled the
protocols in SVRNETCN.
Does anyone know what to do?
ThanksHi,
From the SQL server MSDE installed machine can you try to connect to SQL
server using both authentication using OSQL.
OSQL -Sservername -Usa -Ppassword
The aove command is suppose to go to a SQL Prompt. If it does then your SQL
server is working fine.If it is not look into sql serevr logs and find the
rason why sql server is down.
If your first stpe passed then, From command prompt execute a PING from your
client PC to MSDE server.
If it fail "Request time out" then it is a network issue.
Thanks
Hari
MCDBA
"Pedro" <pedronazario@.timetech.pt> wrote in message
news:4065ce47$0$3527$a729d347@.news.telepac.pt...
> Hi all,
>
> I have the Windows 2003 Server in my company and the MSDE SP3 install with
> an instance for a program. But the program can't access the database, I
have
> tried to create a new data source to SQL Server in ODBC and it tells me
SQL
> Server Error: 11004 (Connection failed) and SQL Server Error: 6 (Specified
> SQL server not found).
>
> The MSDE is running with the name "SERVER\INSTANCE". I have enabled the
> protocols in SVRNETCN.
>
> Does anyone know what to do?
>
> Thanks
>|||By default only shared memory is enabled when you install MSDE. If the
connection is being made from a remote machine or uisng TCP/IP or named
pipes to connect locally the connection will fail. You can use svrnetcn.exe
(SQL Server Network Utiltiy) to enable TCP/IP and/or named pipes.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Error connecting to SQL Server 2005 Express from Access

I am trying to connect to SQL Server 2005 Express from Access using a
DSN-less connection string, but I keep encountering errors. What happens is
that I have a login form with 2 text boxes for users to enter their username
and password. When they have entered them, they click OK and Access tries to
connect using the connection string below.
After waiting a while I get an error, however the SQL Server Login box
appears and I am able to login using my sa login. So, since I know I can
connect to SQL Server, I think the problem must be with my connection string:
stConnect = "ODBC;DRIVER={SQL Native Client}" _
& ";Trusted_Connection=no" _
& ";SERVER=CHRIS\SQLEXPRESS" _
& ";Address=10.0.0.17,1433" _
& ";Network=DBMSSOCN" _
& ";DATABASE=authorDB 2005_11_11SQL" _
& ";UID=" & stUID _
& ";PWD=" & stPWD & ";"
These are the error messages I get when I try to connect:
Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]TCP Provider: No connection could be made
because the target machine actively refused it.
Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]An error has occurred while establishing a
connection to the server. 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.
Connection failed:
SQLState: 'S1T00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login timeout expired
I should mention that I was able to use a similar connection string to
connect to an MSDE instance with no problems, so if need be I can just use
MSDE. Still, I would prefer to use SQL Server Express since it doesn't have
a workload governor. Also, this is on my local machine, so there shouldn't
be any network issues.
I would appreciate any assistance that anyone can offer.
Thanks,
Chris
By default SQL Express does not accept remote connections over TCP or Named
Pipes, you need to enable that explicitly.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
news:4FAF0093-8C11-4030-8E4C-8621DE179732@.microsoft.com...
>I am trying to connect to SQL Server 2005 Express from Access using a
> DSN-less connection string, but I keep encountering errors. What happens
> is
> that I have a login form with 2 text boxes for users to enter their
> username
> and password. When they have entered them, they click OK and Access tries
> to
> connect using the connection string below.
> After waiting a while I get an error, however the SQL Server Login box
> appears and I am able to login using my sa login. So, since I know I can
> connect to SQL Server, I think the problem must be with my connection
> string:
> stConnect = "ODBC;DRIVER={SQL Native Client}" _
> & ";Trusted_Connection=no" _
> & ";SERVER=CHRIS\SQLEXPRESS" _
> & ";Address=10.0.0.17,1433" _
> & ";Network=DBMSSOCN" _
> & ";DATABASE=authorDB 2005_11_11SQL" _
> & ";UID=" & stUID _
> & ";PWD=" & stPWD & ";"
> These are the error messages I get when I try to connect:
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 10061
> [Microsoft][SQL Native Client]TCP Provider: No connection could be made
> because the target machine actively refused it.
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 10061
> [Microsoft][SQL Native Client]An error has occurred while establishing a
> connection to the server. 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.
> Connection failed:
> SQLState: 'S1T00'
> SQL Server Error: 0
> [Microsoft][SQL Native Client]Login timeout expired
>
> I should mention that I was able to use a similar connection string to
> connect to an MSDE instance with no problems, so if need be I can just use
> MSDE. Still, I would prefer to use SQL Server Express since it doesn't
> have
> a workload governor. Also, this is on my local machine, so there
> shouldn't
> be any network issues.
> I would appreciate any assistance that anyone can offer.
> Thanks,
> Chris
|||Gert, I know that. I started the Browser and enabled TCP/IP. Like I said, I
can connect using the sa login, and techinically it's not a remote connection
as it's on a local machine. Thanks for the response, but I already checked
and that isn't the problem.
-Chris
"Gert E.R. Drapers" wrote:

> By default SQL Express does not accept remote connections over TCP or Named
> Pipes, you need to enable that explicitly.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
> news:4FAF0093-8C11-4030-8E4C-8621DE179732@.microsoft.com...
>
>
|||I figured it out. I just needed to change the default port for TCP/IP since
MSDE was using port 1433.
-Chris
"Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
news:BD09DFBD-66E6-411D-AA16-2A89D9672C1B@.microsoft.com...[vbcol=seagreen]
> Gert, I know that. I started the Browser and enabled TCP/IP. Like I
> said, I
> can connect using the sa login, and techinically it's not a remote
> connection
> as it's on a local machine. Thanks for the response, but I already
> checked
> and that isn't the problem.
> -Chris
> "Gert E.R. Drapers" wrote:

Error connecting to SQL Server 2005 Express from Access

I am trying to connect to SQL Server 2005 Express from Access using a
DSN-less connection string, but I keep encountering errors. What happens is
that I have a login form with 2 text boxes for users to enter their username
and password. When they have entered them, they click OK and Access tries to
connect using the connection string below.
After waiting a while I get an error, however the SQL Server Login box
appears and I am able to login using my sa login. So, since I know I can
connect to SQL Server, I think the problem must be with my connection string:
stConnect = "ODBC;DRIVER={SQL Native Client}" _
& ";Trusted_Connection=no" _
& ";SERVER=CHRIS\SQLEXPRESS" _
& ";Address=10.0.0.17,1433" _
& ";Network=DBMSSOCN" _
& ";DATABASE=authorDB 2005_11_11SQL" _
& ";UID=" & stUID _
& ";PWD=" & stPWD & ";"
These are the error messages I get when I try to connect:
Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]TCP Provider: No connection could be made
because the target machine actively refused it.
Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]An error has occurred while establishing a
connection to the server. 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.
Connection failed:
SQLState: 'S1T00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login timeout expired
I should mention that I was able to use a similar connection string to
connect to an MSDE instance with no problems, so if need be I can just use
MSDE. Still, I would prefer to use SQL Server Express since it doesn't have
a workload governor. Also, this is on my local machine, so there shouldn't
be any network issues.
I would appreciate any assistance that anyone can offer.
Thanks,
Chris
By default SQL Express does not accept remote connections over TCP or Named
Pipes, you need to enable that explicitly.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
news:4FAF0093-8C11-4030-8E4C-8621DE179732@.microsoft.com...
>I am trying to connect to SQL Server 2005 Express from Access using a
> DSN-less connection string, but I keep encountering errors. What happens
> is
> that I have a login form with 2 text boxes for users to enter their
> username
> and password. When they have entered them, they click OK and Access tries
> to
> connect using the connection string below.
> After waiting a while I get an error, however the SQL Server Login box
> appears and I am able to login using my sa login. So, since I know I can
> connect to SQL Server, I think the problem must be with my connection
> string:
> stConnect = "ODBC;DRIVER={SQL Native Client}" _
> & ";Trusted_Connection=no" _
> & ";SERVER=CHRIS\SQLEXPRESS" _
> & ";Address=10.0.0.17,1433" _
> & ";Network=DBMSSOCN" _
> & ";DATABASE=authorDB 2005_11_11SQL" _
> & ";UID=" & stUID _
> & ";PWD=" & stPWD & ";"
> These are the error messages I get when I try to connect:
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 10061
> [Microsoft][SQL Native Client]TCP Provider: No connection could be made
> because the target machine actively refused it.
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 10061
> [Microsoft][SQL Native Client]An error has occurred while establishing a
> connection to the server. 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.
> Connection failed:
> SQLState: 'S1T00'
> SQL Server Error: 0
> [Microsoft][SQL Native Client]Login timeout expired
>
> I should mention that I was able to use a similar connection string to
> connect to an MSDE instance with no problems, so if need be I can just use
> MSDE. Still, I would prefer to use SQL Server Express since it doesn't
> have
> a workload governor. Also, this is on my local machine, so there
> shouldn't
> be any network issues.
> I would appreciate any assistance that anyone can offer.
> Thanks,
> Chris
|||Gert, I know that. I started the Browser and enabled TCP/IP. Like I said, I
can connect using the sa login, and techinically it's not a remote connection
as it's on a local machine. Thanks for the response, but I already checked
and that isn't the problem.
-Chris
"Gert E.R. Drapers" wrote:

> By default SQL Express does not accept remote connections over TCP or Named
> Pipes, you need to enable that explicitly.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
> news:4FAF0093-8C11-4030-8E4C-8621DE179732@.microsoft.com...
>
>
|||I figured it out. I just needed to change the default port for TCP/IP since
MSDE was using port 1433.
-Chris
"Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
news:BD09DFBD-66E6-411D-AA16-2A89D9672C1B@.microsoft.com...[vbcol=seagreen]
> Gert, I know that. I started the Browser and enabled TCP/IP. Like I
> said, I
> can connect using the sa login, and techinically it's not a remote
> connection
> as it's on a local machine. Thanks for the response, but I already
> checked
> and that isn't the problem.
> -Chris
> "Gert E.R. Drapers" wrote:

Error connecting to SQL Server 2005 Express from Access

I am trying to connect to SQL Server 2005 Express from Access using a
DSN-less connection string, but I keep encountering errors. What happens is
that I have a login form with 2 text boxes for users to enter their username
and password. When they have entered them, they click OK and Access tries t
o
connect using the connection string below.
After waiting a while I get an error, however the SQL Server Login box
appears and I am able to login using my sa login. So, since I know I can
connect to SQL Server, I think the problem must be with my connection string
:
stConnect = "ODBC;DRIVER={SQL Native Client}" _
& ";Trusted_Connection=no" _
& ";SERVER=CHRIS\SQLEXPRESS" _
& ";Address=10.0.0.17,1433" _
& ";Network=DBMSSOCN" _
& ";DATABASE=authorDB 2005_11_11SQL" _
& ";UID=" & stUID _
& ";PWD=" & stPWD & ";"
These are the error messages I get when I try to connect:
Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]TCP Provider: No connection could be m
ade
because the target machine actively refused it.
Connection failed:
SQLState: '08001'
SQL Server Error: 10061
[Microsoft][SQL Native Client]An error has occurred while establishi
ng a
connection to the server. When connecting to SQL Server 2005, this failure
may be caused by the fact that under the default settings SQL Server does no
t
allow remote connections.
Connection failed:
SQLState: 'S1T00'
SQL Server Error: 0
[Microsoft][SQL Native Client]Login timeout expired
I should mention that I was able to use a similar connection string to
connect to an MSDE instance with no problems, so if need be I can just use
MSDE. Still, I would prefer to use SQL Server Express since it doesn't have
a workload governor. Also, this is on my local machine, so there shouldn't
be any network issues.
I would appreciate any assistance that anyone can offer.
Thanks,
ChrisBy default SQL Express does not accept remote connections over TCP or Named
Pipes, you need to enable that explicitly.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
news:4FAF0093-8C11-4030-8E4C-8621DE179732@.microsoft.com...
>I am trying to connect to SQL Server 2005 Express from Access using a
> DSN-less connection string, but I keep encountering errors. What happens
> is
> that I have a login form with 2 text boxes for users to enter their
> username
> and password. When they have entered them, they click OK and Access tries
> to
> connect using the connection string below.
> After waiting a while I get an error, however the SQL Server Login box
> appears and I am able to login using my sa login. So, since I know I can
> connect to SQL Server, I think the problem must be with my connection
> string:
> stConnect = "ODBC;DRIVER={SQL Native Client}" _
> & ";Trusted_Connection=no" _
> & ";SERVER=CHRIS\SQLEXPRESS" _
> & ";Address=10.0.0.17,1433" _
> & ";Network=DBMSSOCN" _
> & ";DATABASE=authorDB 2005_11_11SQL" _
> & ";UID=" & stUID _
> & ";PWD=" & stPWD & ";"
> These are the error messages I get when I try to connect:
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 10061
> [Microsoft][SQL Native Client]TCP Provider: No connection could be
made
> because the target machine actively refused it.
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 10061
> [Microsoft][SQL Native Client]An error has occurred while establis
hing a
> connection to the server. 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.
> Connection failed:
> SQLState: 'S1T00'
> SQL Server Error: 0
> [Microsoft][SQL Native Client]Login timeout expired
>
> I should mention that I was able to use a similar connection string to
> connect to an MSDE instance with no problems, so if need be I can just use
> MSDE. Still, I would prefer to use SQL Server Express since it doesn't
> have
> a workload governor. Also, this is on my local machine, so there
> shouldn't
> be any network issues.
> I would appreciate any assistance that anyone can offer.
> Thanks,
> Chris|||Gert, I know that. I started the Browser and enabled TCP/IP. Like I said,
I
can connect using the sa login, and techinically it's not a remote connectio
n
as it's on a local machine. Thanks for the response, but I already checked
and that isn't the problem.
-Chris
"Gert E.R. Drapers" wrote:

> By default SQL Express does not accept remote connections over TCP or Name
d
> Pipes, you need to enable that explicitly.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in messag
e
> news:4FAF0093-8C11-4030-8E4C-8621DE179732@.microsoft.com...
>
>|||I figured it out. I just needed to change the default port for TCP/IP since
MSDE was using port 1433.
-Chris
"Chris Burnette" <ChrisBurnette@.discussions.microsoft.com> wrote in message
news:BD09DFBD-66E6-411D-AA16-2A89D9672C1B@.microsoft.com...[vbcol=seagreen]
> Gert, I know that. I started the Browser and enabled TCP/IP. Like I
> said, I
> can connect using the sa login, and techinically it's not a remote
> connection
> as it's on a local machine. Thanks for the response, but I already
> checked
> and that isn't the problem.
> -Chris
> "Gert E.R. Drapers" wrote:
>

Error connecting to OLEDB for DB2

I'm attempting to configure a new Microsoft OLE DB Provider for DB2 connection using the provided Data Access Tool and Wizard. I'm configured for DB2\MVS TCP/IP and have my Catalog and other defaults set. When I go to connect I get the following:

Could not connect to data source 'New Data Source':
An internal network library error has occurred. A network level conversational protocol error has occurred. SQLSTATE: HY000, SQLCODE: -343

The port I've configured is what is used when we use the IBM OLE DB and ODBC drivers. Where can I look to see what the problem is?

Thanks,

Mike

I'm moving your thread to the Data Access forum as you'll have a greater chance of having an OLEDB question answered.|||

Hi,

some OLEDB providers have the functionality for logging implemented which can be turned on with a flag on the client side, this should give you more information about the error you ar egetting. The Provider flags are very specific so you should look in the documentation of the used provider.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Error Code

Error messages:
Source: mscorlib
Target Site: Void WinIOError(Int32, System.String)
Message: Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\ReplData\unc\Servername_ADVENTUREWORKS_ADVWORKSPRODUCTTRANS\20070625161637\' is denied.
Stack: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.Directory.InternalCreateDirectory(String fullPath, String path, DirectorySecurity dirSecurity)
at System.IO.Directory.CreateDirectory(String path, DirectorySecurity directorySecurity)
at Microsoft.SqlServer.Replication.Utilities.CreateDirectoryWithExtendedErrorInformation(String directory)
at Microsoft.SqlServer.Replication.Snapshot.SnapshotProvider.CreateSnapshotFolders()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: mscorlib, Error number: 0)

I totally followed by tutorials of replication, repldata is new created folder

sharing for windowns accounts like shown below

repl_distribution read

repl_merge read

repl_snapshot Full control

the settting for these accounts same with security setting

any idea about this error?

many thanks

Can you confirm that xp_cmdshell is enabled? Also can you confirm that the account your snapshot agent is running under (either the windows impersonation account, of the SQL Server agent account on your publisher) has write, and list files and folders rights on the publisher?

Error Calling SQL Stored Procedure from Access

I am getting an error when I try to call a stored procedure from Access. The
error is as follows: "[Microsoft][OBDC SQL Driver][SQL Server] Invalid
length parameter passed to the substring function."
It has been working fine for months until yesterday, with no code changes
being done. I thought maybe teh data is the problem, but if I run the stored
procedure from the SQL Query Analyzer on the server, I receive no error
message. It seems to be something between Access and SQL.
What could be up? Below is the code that lies behind a form button that
calls the stored procedure. Help!
Dim objConn As ADODB.Connection
Dim objCmd As New ADODB.Command
' Open a connection without using a Data Source Name (DSN)
Set objConn = New ADODB.Connection
objConn.ConnectionString = "Driver=SQL Server;Server=TRADB1;" & _
"Database=AllData;UID=user;PWD=password;"
objConn.open
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "WorkOrderTransferImport"
objCmd.CommandType = adCmdStoredProc
objCmd.Parameters.Refresh
objCmd.Execute
' clean up
objConn.Close
Set objConn = Nothing
Thanks,
JoeI think the problem is not located within the ADO Code rather than the SQL
Code, your calling something like a stringfunction which expects paramters
like len, e.g. LEFT(Text,Lenght). That expression evaluates not right and
that s the problem whats all about. If the problem appears now, without code
changing, a special row was inserted meanwhile which causes the error. As
far as you didnt post your SP i cant guess what the problem in the SP is,
just post it, we will help you.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Joe Williams" <joe@.anywhere.com> schrieb im Newsbeitrag
news:ur0LCgDQFHA.2736@.TK2MSFTNGP09.phx.gbl...
>I am getting an error when I try to call a stored procedure from Access.
>The error is as follows: "[Microsoft][OBDC SQL Driver][SQL Server] Invalid
>length parameter passed to the substring function."
> It has been working fine for months until yesterday, with no code changes
> being done. I thought maybe teh data is the problem, but if I run the
> stored procedure from the SQL Query Analyzer on the server, I receive no
> error message. It seems to be something between Access and SQL.
> What could be up? Below is the code that lies behind a form button that
> calls the stored procedure. Help!
> Dim objConn As ADODB.Connection
> Dim objCmd As New ADODB.Command
> ' Open a connection without using a Data Source Name (DSN)
> Set objConn = New ADODB.Connection
> objConn.ConnectionString = "Driver=SQL Server;Server=TRADB1;" & _
> "Database=AllData;UID=user;PWD=password;"
> objConn.open
> Set objCmd.ActiveConnection = objConn
> objCmd.CommandText = "WorkOrderTransferImport"
> objCmd.CommandType = adCmdStoredProc
> objCmd.Parameters.Refresh
> objCmd.Execute
> ' clean up
> objConn.Close
> Set objConn = Nothing
>
> Thanks,
> Joe
>
>|||Hi
Data issue.
In your SP or trigger on the table the SP works with, there is a substring
function that is failing. Possible a string that is shorter than expected (o
r
null).
Regards
Mike
Regards
Mike
"Joe Williams" wrote:

> I am getting an error when I try to call a stored procedure from Access. T
he
> error is as follows: "[Microsoft][OBDC SQL Driver][SQL Server] Invalid
> length parameter passed to the substring function."
> It has been working fine for months until yesterday, with no code changes
> being done. I thought maybe teh data is the problem, but if I run the stor
ed
> procedure from the SQL Query Analyzer on the server, I receive no error
> message. It seems to be something between Access and SQL.
> What could be up? Below is the code that lies behind a form button that
> calls the stored procedure. Help!
> Dim objConn As ADODB.Connection
> Dim objCmd As New ADODB.Command
> ' Open a connection without using a Data Source Name (DSN)
> Set objConn = New ADODB.Connection
> objConn.ConnectionString = "Driver=SQL Server;Server=TRADB1;" & _
> "Database=AllData;UID=user;PWD=password;"
> objConn.open
> Set objCmd.ActiveConnection = objConn
> objCmd.CommandText = "WorkOrderTransferImport"
> objCmd.CommandType = adCmdStoredProc
> objCmd.Parameters.Refresh
> objCmd.Execute
> ' clean up
> objConn.Close
> Set objConn = Nothing
>
> Thanks,
> Joe
>
>
>|||Can you post the sp code?
AMB
"Joe Williams" wrote:

> I am getting an error when I try to call a stored procedure from Access. T
he
> error is as follows: "[Microsoft][OBDC SQL Driver][SQL Server] Invalid
> length parameter passed to the substring function."
> It has been working fine for months until yesterday, with no code changes
> being done. I thought maybe teh data is the problem, but if I run the stor
ed
> procedure from the SQL Query Analyzer on the server, I receive no error
> message. It seems to be something between Access and SQL.
> What could be up? Below is the code that lies behind a form button that
> calls the stored procedure. Help!
> Dim objConn As ADODB.Connection
> Dim objCmd As New ADODB.Command
> ' Open a connection without using a Data Source Name (DSN)
> Set objConn = New ADODB.Connection
> objConn.ConnectionString = "Driver=SQL Server;Server=TRADB1;" & _
> "Database=AllData;UID=user;PWD=password;"
> objConn.open
> Set objCmd.ActiveConnection = objConn
> objCmd.CommandText = "WorkOrderTransferImport"
> objCmd.CommandType = adCmdStoredProc
> objCmd.Parameters.Refresh
> objCmd.Execute
> ' clean up
> objConn.Close
> Set objConn = Nothing
>
> Thanks,
> Joe
>
>
>|||Thanks Jens
The odd part is that when you run it from SQL server you do not receive any
error messages. That is very odd.
The other thing is that the store procedure has about 10 other nested stored
procedures so I am not sure where to even start debugging it!
Is there a way to have SQL be more specific about what record, SP, etc that
the error is occuring on?
Thanks
Joe
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23R9TMjDQFHA.128@.tk2msftngp13.phx.gbl...
>I think the problem is not located within the ADO Code rather than the SQL
>Code, your calling something like a stringfunction which expects paramters
>like len, e.g. LEFT(Text,Lenght). That expression evaluates not right and
>that s the problem whats all about. If the problem appears now, without
>code changing, a special row was inserted meanwhile which causes the error.
>As far as you didnt post your SP i cant guess what the problem in the SP
>is, just post it, we will help you.
>
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "Joe Williams" <joe@.anywhere.com> schrieb im Newsbeitrag
> news:ur0LCgDQFHA.2736@.TK2MSFTNGP09.phx.gbl...
>|||Always helpful is to put debugging information in the procedures, like
Print 'Hit First Procedure' --and so on.
EXEC Something
Print 'Done with First Procedure'
Run Profiler to see, where the error occures, display the exceptions when
you secify the column to display.
Jens Smeyer.
"Joe Williams" <joe@.anywhere.com> schrieb im Newsbeitrag
news:OF05vlDQFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Thanks Jens
> The odd part is that when you run it from SQL server you do not receive
> any error messages. That is very odd.
> The other thing is that the store procedure has about 10 other nested
> stored procedures so I am not sure where to even start debugging it!
> Is there a way to have SQL be more specific about what record, SP, etc
> that the error is occuring on?
> Thanks
> Joe
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:%23R9TMjDQFHA.128@.tk2msftngp13.phx.gbl...
>