Error during Execute sp with IMAGE parameter

Hello.
It is the first time that Im using IMAGE field type..

I created a stored procedure that should return an IMAGE field back to a
c-sharp program .
(@.OutImg IMAGE declared as an output parameter of the procedure.)
(Select @.OutImg = ImgFld from table)
Well,I can compile it , but when execute i get this error :
Msg 2739, Level 16, State 1, Line 14
The text, ntext, and image data types are invalid for local variable.

Is there something I can do ,OR should I use any cast/convert func to solve it ..?
Many thanks.:ofrom 2005 BOL:

text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure.

So you'll have to return this in a record set, not in an OUT param.

Error Creating User on Database

Hi there,

I am setting up a database for the first time using SQL Server Express.

I have managed to create a database and create a table with data etc, all straight forward.

The problem I am having is when I come to creating a user for the database. I have the following users in there already, which I am guessing are put in by default:

dbo

guest

INFORMATION_SCHEMA

sys

When I try creating a new user via this screen I get the following error:

TITLE: Microsoft SQL Server Management Studio Express

Create failed for User 'growstudiouser'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

'growstudio' is not a valid login or you do not have permission. (Microsoft SQL Server, Error: 15007)

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

I was wondering if anybody could advise me as to what I'm doing wrong. I am typing in the name as "growstudiouser" and the login name as "growstudio".

Or am I just using the wrong section to do what I want. I'm trying to create a user so that when the database is built I can connect to it using a username and password that I have created. I'm not sure I'm taking to the correct route as there is no password option anywhere when creating a new user.

Thanks in advance,

Kevin

hi Kevin,

usually a database user is associated with a server's login, if you do not define a user as

USE tempdb; GO CREATE USER testUser WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo; GO DROP USER testUser;

where the database user is not associated with a server level principal, but this is another story..

so you should first create the server login as

USE master; CREATE LOGIN testLogin WITH PASSWORD = 'his strong password'; GO USE tempdb; GO CREATE USER testUser FOR LOGIN testLogin WITH DEFAULT_SCHEMA = dbo; GO DROP USER testUser; GO USE master; GO DROP LOGIN testLogin;

regards

|||

SQL Server follows two level security architecture. Login and User . Login is to access to the server. If you have login it does not mean that you have access to all the databases in that server. NO. To access database, the Login has to be mapped/added to the database as user. So , in your case what your are probably doing is you are not maping the user with a login . You should select a login while creating user

Madhu

error converting date time

Hi i m tring to convert a date time

declare @.a datetime
declare @.b varchar(10)
set @.b='26/04/2004'
set @.a= Convert(datetime, @.b)

but it gives me this error:

Server: Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I believe is my date format..i want to know how to make sure that i am the dd/mm/yyyy format is correct way?Why are you sending the date in as a varchar ?|||Use SET DATEFORMAT

Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.|||Hi,
thanks for all ur reply.. basically i wanted to send date in as dateTime but i cant because i cant assign a date to null so i have to send it to my database and from there i convert it to valid date time.

Thanks alot.

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 Connecting to SQL 2000

I am using Visual Studio 2005 with SQL Server 2000. I'm having an issue where I can build my website just fine for long periods of time, and then for what appears to be no reason, my site errors out when trying to connect to the database with the message below -- the thing is I don't want to use a SQL Server 2005 database, nor do I have Server 2005 or Server Express loaded. How can I unhook this and make it use SQL 2000?

"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. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) "

This does not seem to be related to SSIS. You might get a better answer if you try to post this message into a more appropriate forum.

Error Connecting to SQL 2000

I am using Visual Studio 2005 withSQL Server 2000. I'm having an issue where I can build my website just fine for long periods of time, and then for what appears to be no reason, my site errors out when trying to connect to the database with the message below -- the thing is I don't want to use a SQL Server 2005 database, nor do I have Server 2005 or Server Express loaded. How can I unhook this and make it use SQL 2000?

"An error has occurred while establishing a connection to theserver. 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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) "

This is the standard message for failing to connect to the server, even if it is SQL Server 2000.

The message talks about 2005, but if you're using 2000, you may ignore this addition.

The important sentence here is: "No connection could be made because the target machine actively refused it".

One thing you could try is just refresh the page and try again.

Jos

|||

Hi i gues u need to restart ur services including SQL services as well n yes it a common error so dont worry about it may it helps u

|||

I've tried restarting my SQL services and rebooting repeatedly but it doesn't help.

|||

Hi tjherman,

Did you made any modifications to your connection string before? Check it.

Besides, I also tend to believe that the problem is related to your database engine.Make sure you sql sever service is started(Since you said that you have restared your sql server2000, i would suggest you to also check if the database service is started), you can first connect to your database using Management Studio and see what happened

Hope my suggestion helps

|||

ya Bo Chen is rite u should first try it using Enterprise Manager n see ur DB is working fi9 or

Error at deployment time (SSAS 2005)

Hello all,

May be somebody know what does this error mean:

"Error 1 Exception of type 'System.OutOfMemoryException' was thrown. 0 0"

I get it during deployment of cube after small changes I made in cube role.

When I deleted the role to return project to prviouse condition which was working, I still get the same error.

If anybody has any idea - it would be very appreciated.

Thanks

Inna

Hard to say on what is going on. Not clear which component produced an error.

Try connecting to Analysis Server from SQL Profiler and see if you will get the same error from the server.

Can you try and make changes to the role from SQL Managment studio and see if you are getting the same error?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

Hello Inna

Could it be possible that Your workstation has not enough memory for the Business Intelligence Studio ? You could also have some trouble in he Control Panel/System/Performance. I recommend a contact with Your System Support.

regards

Matti

|||

Hi Edward,

Thank you for your answer.

From SQL Profiler I didn't see my deployment process - when task failed it doesn't reach server and doesn't apply any changes there.

I tried and made changes to the role from SQL Management studio as you advised and it worked without any errors.

At the same time project stood not up to date and I don't know how to export the changes I made directly on the server to VS to continue development:

I can open Analysis Database in V Studio and make changes directly on the server.

After all I reopened the project, deleted role at all and got the same error.

Every time after reboot - it let me to make and to deploy changes for only one time.

Do you have any idea what could be a problem?

Thanks a lot

Inna

|||

Hello Matti,

Thank you for your reply.

Unfortunately now I have to work directly on the server (Windows Server 2003 Standard x64 edition - 6 GB memory and 1 CPU). BI studio installed there. When I get this error during deployment time – memory doesn’t grow out of 2 GB. So I thought enough is still available.

May be I need to configure BI Studio the way it can raise the memory it needs? - I have no idea if it makes sense.

I didn't notice any degradation in performance on the server at the time I got memory error.

All additional ideas would be much appreciated.

Regards

Inna

|||

Hi Inna again

You seem to have lots of memory.

I have met same error and same behaviour ones with my own Java application in Windows Xp.

In that case I could make the correction by changing in the registry the limit for the space of memory the operating system allocates to that type of applications.

Sorry, but I am not an expert in Windows Server 2003. I expect that there somewhere is a limit for the size of memory the operating system allocates to one task.

This is only a quess, but I hope it would be helpfull.

regards

Matti