Problems inserting datetime object into sql server 2005

hey everyone,

Im trying run a stored procedure, but I am getting the following message when trying to execute it.

Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

which seems strange, as there is no integer going to a datetime field. Also, it does not return the value that is incorrect, nor the parameter that is failing. If there is anyway to view this in VS 2005 Team suite, please let me know.

It should also be noted that when I execute this sproc from within sql management studio, it executes fine.

This is the stored procedure im trying to execute...

CREATE PROCEDURE [dbo].[sp_UpdateRequest]

@.request_id INT,
@.request_client_id INT,
@.request_description VARCHAR(150), @.request_date DATETIME,
@.request_edit_date DATETIME, @.request_status VARCHAR(25), @.request_approve_date DATETIME,
@.request_activity_code VARCHAR(7), @.request_department_code VARCHAR(3), @.request_participants INT,
@.request_activity_date DATETIME, @.request_activity_due_date DATETIME,
@.request_company_id INT, @.request_company_code INT,
@.request_notes VARCHAR(2000)

AS
BEGIN

UPDATE invoice_requests

SET request_client_id = @.request_client_id, request_description = @.request_description,
request_date = @.request_date, request_edit_date = @.request_edit_date,
request_status = @.request_status, request_approve_date = @.request_approve_date,
request_activity_code = @.request_activity_code,
request_department_code = @.request_department_code,
request_participants = @.request_participants,
request_activity_date = @.request_activity_date,
request_activity_due_date = @.request_activity_due_date,
request_company_code = @.request_company_code, request_company_id = @.request_company_id,
request_notes = @.request_notes

WHERE request_id = @.request_id

END
GO

in my .net app, all the datetime objects are valid dates, so I could not see why this would be generating the error that it is, so I ran the SQL Profiler, and this was the output. Again, i cannot see where im going wrong...

please note, I have seperated the output from the single slab of text, making sure I didnt remove anything...

exec sp_executesql
N'EXECUTE sp_UpdateRequest
@.request_id,
@.request_client_id,
@.request_description,
@.request_date,
@.request_edit_date,
@.request_status,
@.request_approve_date,
@.request_activity_code,
@.request_participants,
@.request_activity_date,
@.request_activity_due_date,
@.request_company_id,
@.request_company_code,
@.request_notes',

N'@.request_id int,
@.request_client_id int,
@.request_description varchar(8000),
@.request_date datetime,
@.request_edit_date datetime,
@.request_status varchar(5),
@.request_approve_date datetime,
@.request_activity_code varchar(8000),
@.request_department_code varchar(8000),
@.request_participants int,
@.request_activity_date datetime,
@.request_activity_due_date datetime,
@.request_company_id int,
@.request_company_code int,
@.request_notes varchar(8000)',

@.request_id=5,
@.request_client_id=1,
@.request_description='',
@.request_date=''2007-11-22 16:34:32:997'',
@.request_edit_date=''2007-11-22 16:34:32:997'',
@.request_status='Draft',
@.request_approve_date=''1970-01-01 00:00:00:000'',
@.request_activity_code='',
@.request_department_code='',
@.request_participants=0,
@.request_activity_date=''2007-11-29 00:00:00:000'',
@.request_activity_due_date=''2007-11-23 00:00:00:000'',
@.request_company_id=0,
@.request_company_code=1,
@.request_notes=''

any help on this would be greatly appreciated.
Cheershey everyone, when i try to run the the following trace result as a query, i get an error near 2007...

exec sp_executesql N'EXECUTE sp_UpdateRequest @.request_id, @.request_client_id, @.request_description, @.request_date, @.request_edit_date, @.request_status, @.request_approve_date, @.request_activity_code, @.request_participants,
@.request_activity_date, @.request_activity_due_date, @.request_company_id, @.request_company_code, @.request_notes',N'@.request_id int,@.request_client_id int,@.request_description varchar(6),@.request_date datetime,@.request_edit_date
datetime,@.request_status varchar(5),@.request_approve_date datetime,@.request_activity_code varchar(4),@.request_department_code varchar(3),@.request_participants int,@.request_activity_date datetime,@.request_activity_due_date
datetime,@.request_company_id int,@.request_company_code int,@.request_notes varchar(11)',@.request_id=1,@.request_client_id=1,@.r equest_description='Justin',@.request_date=''2007-11-23 08:59:04:250'',@.request_edit_date=''2007-11-23
08:59:04:250'',@.request_status='Draft',@.request_ap prove_date=''1970-01-01 00:00:00:000'',@.request_activity_code='0000',@.requ est_department_code='022',@.request_participants=50 ,@.request_activity_date=''2007-11-23
00:00:00:000'',@.request_activity_due_date=''2007-11-30 00:00:00:000'',@.request_company_id=5292,@.request_c ompany_code=1,@.request_notes='Other Notes'

are there meant to be the double single quotes('') before and after the date?

Cheers,|||are there meant to be the double single quotes('') before and after the date?No, there should be a single apostrophe surrounding (before and after) date constants.

Your constant ''2007-11-23 08:59:04:250'' syntactically ought to be '2007-11-23 08:59:04:250' for use in Microsoft SQL Server.

-PatP|||No, there should be a single apostrophe surrounding (before and after) date constants.

Your constant ''2007-11-23 08:59:04:250'' syntactically ought to be '2007-11-23 08:59:04:250' for use in Microsoft SQL Server.

-PatP

I'm not sure because it's already surrounded by single quotes.

maybe you can try '''2007-11-23 08:59:04:250'''|||Sorry, I should have looked at your whole example.

There are multiple ways that I could parse what you've posted. The one that makes the most sense to me at the moment is:exec sp_executesql N'EXECUTE sp_UpdateRequest
@.request_id, @.request_client_id, @.request_description
, @.request_date, @.request_edit_date, @.request_status
, @.request_approve_date, @.request_activity_code, @.request_participants
, @.request_activity_date, @.request_activity_due_date, @.request_company_id
, @.request_company_code, @.request_notes'', N''@.request_id int
, @.request_client_id int, @.request_description varchar(6), @.request_date datetime
, @.request_edit_date datetime, @.request_status varchar(5), @.request_approve_date datetime
, @.request_activity_code varchar(4), @.request_department_code varchar(3), @.request_participants int
, @.request_activity_date datetime, @.request_activity_due_date datetime, @.request_company_id int
, @.request_company_code int, @.request_notes varchar(11)''
, @.request_id = 1, @.request_client_id = 1, @.request_description = ''Justin''
, @.request_date = ''2007-11-23 08:59:04:250'', @.request_edit_date = ''2007-11-23 08:59:04:250''
, @.request_status = ''Draft'', @.request_approve_date = ''1970-01-01 00:00:00:000''
, @.request_activity_code = ''0000'', @.request_department_code = ''022'', @.request_participants = 50
, @.request_activity_date = ''2007-11-23 00:00:00:000'', @.request_activity_due_date = ''2007-11-30 00:00:00:000''
, @.request_company_id = 5292, @.request_company_code = 1, @.request_notes = ''Other Notes''
'See if that represents what you think you meant, and let me know how it works for you.

-PatPsql

Problems disabling distributor/publication.

Hi, i had a server that was a publisher/distributor. We decide to change
the server name. When we want to execute the sp_dropserver and
sp_addserver display some errors, so we decided to disable the the
publisher/distributor option. When we did it, the distribution database
wasnt deleted.
Do somebody know if is correct that the distribution database already
exists? Can i delete it manually?
I i try to reconfigure the publisher/distributor options it reports to
me that already exists a database with the distribution name, and i have
to select another name.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***
Maria,
please have a look at sp_dropdistributiondb and sp_dropdistributor.
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Problems calling an SP with SQL Server ( EXECUTE permission denied )

Hi,
I'm trying to test an SP with SQL Query Analyser from my client db an
it gives me this error :
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
denied on object 'sp_sdidebug', database 'master', owner 'dbo'.
I'm using Microsoft SQL SE SP4 and i'm new to it. I read on gg forums
that you have to execute sp_sdidebug with the 'legacy_on' parameter and
you need EXECUTE permission on sp_sdidebug. How do you do that !
Need help ! Thanks !
Olivier
To grant permission, you can use:
GRANT EXECUTE ON sp_sdidebug TO <YourUser>
To enable it for legacy clients, use:
EXEC sp_sdidebug 'legacy_on'
-Sue
On 14 Feb 2005 06:24:02 -0800, "OliE" <olie.rej@.gmail.com>
wrote:

>Hi,
>I'm trying to test an SP with SQL Query Analyser from my client db an
>it gives me this error :
>[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
>denied on object 'sp_sdidebug', database 'master', owner 'dbo'.
>I'm using Microsoft SQL SE SP4 and i'm new to it. I read on gg forums
>that you have to execute sp_sdidebug with the 'legacy_on' parameter and
>you need EXECUTE permission on sp_sdidebug. How do you do that !
>Need help ! Thanks !
>Olivier
sql

Problems calling an SP with SQL Server ( EXECUTE permission denied )

Hi,
I'm trying to test an SP with SQL Query Analyser from my client db an
it gives me this error :
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permissio
n
denied on object 'sp_sdidebug', database 'master', owner 'dbo'.
I'm using Microsoft SQL SE SP4 and i'm new to it. I read on gg forums
that you have to execute sp_sdidebug with the 'legacy_on' parameter and
you need EXECUTE permission on sp_sdidebug. How do you do that !
Need help ! Thanks !
OlivierTo grant permission, you can use:
GRANT EXECUTE ON sp_sdidebug TO <YourUser>
To enable it for legacy clients, use:
EXEC sp_sdidebug 'legacy_on'
-Sue
On 14 Feb 2005 06:24:02 -0800, "OliE" <olie.rej@.gmail.com>
wrote:

>Hi,
>I'm trying to test an SP with SQL Query Analyser from my client db an
>it gives me this error :
>[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permissi
on
>denied on object 'sp_sdidebug', database 'master', owner 'dbo'.
>I'm using Microsoft SQL SE SP4 and i'm new to it. I read on gg forums
>that you have to execute sp_sdidebug with the 'legacy_on' parameter and
>you need EXECUTE permission on sp_sdidebug. How do you do that !
>Need help ! Thanks !
>Olivier

Problem: Can dhcp callout dll insert new row into dsn database?

I current working on this dll, but i found the dll doesn't response to any of the cdatabase execute response? I have try all the cdatabase and sql in c++ console and found that it can be run. The database is open success and the sql statement is correct.
After then i put it into the dhcp callout dll, and compile success.
When i add it into the registry and try the dhcp server, it doesn't have any response database table. And i using sql server for the dsn.
Here is my code
#include "stdafx.h"
#include "callout.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#endif

CDatabase dbCallout;

struct tm *newtime;
char am_pm[] = "AM";
__time64_t long_time;

CString strCmd, strPrimaryKey, strTime;
int nRetCode = 0;

BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved )
{
switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
break;
case DLL_THREAD_ATTACH:
break;
case DLL_THREAD_DETACH:
break;
case DLL_PROCESS_DETACH:
break;
} return TRUE;
}

DWORD CALLBACK DhcpControlHook(DWORD dwControlCode,LPVOID lpReserved)
{
switch (dwControlCode)
{
case DHCP_CONTROL_START: {
_time64( &long_time ); /* Get time as long integer. */
newtime = _localtime64(&long_time); /* Convert to local time. */

if( newtime->tm_hour > 12 ) /* Set up extension. */
strcpy( am_pm, "PM" );
if( newtime->tm_hour > 12 ) /* Convert from 24-hour */
newtime->tm_hour -= 12; /* to 12-hour clock. */
if( newtime->tm_hour == 0 ) /*Set hour to 12 if midnight. */
newtime->tm_hour = 12;

strPrimaryKey.Format("C%.2d%.2d%.2d%.2d%.2d%.2d", newtime->tm_year - 100, newtime->tm_mon, newtime->tm_mday, newtime->tm_hour, newtime->tm_min, newtime->tm_sec);
strTime = asctime(newtime);

strCmd = "INSERT INTO Callout_Control (control_id, control_desc, control_date) VALUES ('" + strPrimaryKey + "', 'DHCP server have started!', '" + strTime + "')";

dbCallout.ExecuteSQL(strCmd);
dbCallout.Close();

break;
}
case DHCP_CONTROL_STOP:
{

break;
}
case DHCP_CONTROL_PAUSE:
{

break;
}
case DHCP_CONTROL_CONTINUE:
{
break;
}
}
return ERROR_SUCCESS;
}

DWORD CALLBACK DhcpServerCalloutEntry(LPWSTR ChainDlls,DWORD CalloutVersion,LPDHCP_CALLOUT_TABLE CalloutTbl)
{

CalloutTbl->DhcpAddressDelHook=DhcpAddressDelHook;
CalloutTbl->DhcpControlHook=DhcpControlHook;
CalloutTbl->DhcpDeleteClientHook=DhcpDeleteClientHook;
CalloutTbl->DhcpPktDropHook=DhcpPktDropHook;
CalloutTbl->DhcpAddressDelHook=DhcpAddressDelHook;
CalloutTbl->DhcpNewPktHook=DhcpNewPktHook;
CalloutTbl->DhcpPktSendHook=DhcpPktSendHook;

dbCallout.Open(_T("CALLOUT"), FALSE, FALSE, _T("ODBC;"));

return ERROR_SUCCESS;
}
What wrong in this code? Can dll file insert new row into dsn? Some expert please help me!!!

Hi

I think your first mitake is that :

1- In DhcpServerCalloutEntry you should set value of CalloutTbl to null if you dont impliment a function

regards

Problem: Can dhcp callout dll insert new row into dsn database?

I current working on this dll, but i found the dll doesn't response to any of the cdatabase execute response? I have try all the cdatabase and sql in c++ console and found that it can be run. The database is open success and the sql statement is correct.
After then i put it into the dhcp callout dll, and compile success.
When i add it into the registry and try the dhcp server, it doesn't have any response database table. And i using sql server for the dsn.
Here is my code
#include "stdafx.h"
#include "callout.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#endif

CDatabase dbCallout;

struct tm *newtime;
char am_pm[] = "AM";
__time64_t long_time;

CString strCmd, strPrimaryKey, strTime;
int nRetCode = 0;

BOOL APIENTRY DllMain( HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved )
{
switch (ul_reason_for_call)
{
case DLL_PROCESS_ATTACH:
break;
case DLL_THREAD_ATTACH:
break;
case DLL_THREAD_DETACH:
break;
case DLL_PROCESS_DETACH:
break;
} return TRUE;
}

DWORD CALLBACK DhcpControlHook(DWORD dwControlCode,LPVOID lpReserved)
{
switch (dwControlCode)
{
case DHCP_CONTROL_START: {
_time64( &long_time ); /* Get time as long integer. */
newtime = _localtime64(&long_time); /* Convert to local time. */

if( newtime->tm_hour > 12 ) /* Set up extension. */
strcpy( am_pm, "PM" );
if( newtime->tm_hour > 12 ) /* Convert from 24-hour */
newtime->tm_hour -= 12; /* to 12-hour clock. */
if( newtime->tm_hour == 0 ) /*Set hour to 12 if midnight. */
newtime->tm_hour = 12;

strPrimaryKey.Format("C%.2d%.2d%.2d%.2d%.2d%.2d", newtime->tm_year - 100, newtime->tm_mon, newtime->tm_mday, newtime->tm_hour, newtime->tm_min, newtime->tm_sec);
strTime = asctime(newtime);

strCmd = "INSERT INTO Callout_Control (control_id, control_desc, control_date) VALUES ('" + strPrimaryKey + "', 'DHCP server have started!', '" + strTime + "')";

dbCallout.ExecuteSQL(strCmd);
dbCallout.Close();

break;
}
case DHCP_CONTROL_STOP:
{

break;
}
case DHCP_CONTROL_PAUSE:
{

break;
}
case DHCP_CONTROL_CONTINUE:
{
break;
}
}
return ERROR_SUCCESS;
}

DWORD CALLBACK DhcpServerCalloutEntry(LPWSTR ChainDlls,DWORD CalloutVersion,LPDHCP_CALLOUT_TABLE CalloutTbl)
{

CalloutTbl->DhcpAddressDelHook=DhcpAddressDelHook;
CalloutTbl->DhcpControlHook=DhcpControlHook;
CalloutTbl->DhcpDeleteClientHook=DhcpDeleteClientHook;
CalloutTbl->DhcpPktDropHook=DhcpPktDropHook;
CalloutTbl->DhcpAddressDelHook=DhcpAddressDelHook;
CalloutTbl->DhcpNewPktHook=DhcpNewPktHook;
CalloutTbl->DhcpPktSendHook=DhcpPktSendHook;

dbCallout.Open(_T("CALLOUT"), FALSE, FALSE, _T("ODBC;"));

return ERROR_SUCCESS;
}
What wrong in this code? Can dll file insert new row into dsn? Some expert please help me!!!

Hi

I think your first mitake is that :

1- In DhcpServerCalloutEntry you should set value of CalloutTbl to null if you dont impliment a function

regards

Problem Xquery about XML node called 'data'

I have a xml code:
<root>
<data id="1">
<value>80</value>
</data>
</root>

I execute the following query XQuery in C# .NET:

For $b IN document(\"filename.xml\")/root/data
WHERE $b/@.id =\"1\" RETURN $b/value

and XQuery show me the following error:

'(' expected. Maybe you are using a known function name like last, data, text etc as an identifier. For this release , this is not allowed.

I can't change the name "data" for my Xml Node so
how can I solve my problem using the word "data" in XQuery?

Dear Luigi

Note that the XQuery engine in C# .Net was just a technology preview and is not supported.

One way to work around your problem is to replace /root/data with /root/*[local-name() = "data"].

Best regards
Michael|||Thank you,
but function local-name() generate an error:

User Defined Functions not supported

This error is try with other function eg. concat, tokenize, upper-case, etc.

I using XQuery Demo: Microsoft.Xml.XQuery.dll (19/02/2002)

Exists one more recent version?

|||You may need to use fn:local-name() instead.

Note that the demo is not supported and no newer version is available.

Problem with way to access all recordsets returned by sp_spaceused using ODBC APIs on C++

Hi all,
I have a problem executing a sp_spaceused on a database.
Please execute sp_spaceused on your local database so that further
discussion will be more easier to understand.
The code snippet is as follows :
SQLCHAR database_size[15];
SQLCHAR unused[15];
The database_size is member of first record set and
unused is member of second recordset returned by the sp_spaceused.
SQLINTEGER rlength;
retcode = SQLAllocHandle(SQL_HANDLE_STMT,hDBC,&hStmt);
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
{
retcode = SQLExecDirect(hStmt,(unsigned char *)sqlCommand,
SQL_NTS);
retcode = SQLBindCol(hStmt,2, SQL_CHAR, (SQLPOINTER) &database_size,
sizeof(database_size),&rlength);
//Problem - was not able to bind any
column of the second recordset so not able to fetch the unused space.
if (retcode != SQL_SUCCESS )
{
AfxMessageBox("Error occured while binding");
}
CString csDatabase_size;
//The while loop below returns after the first iteration as only first
recordset is available and nothing is accessible of second recordset so
cant get 'unused' field
while ((retcode = SQLFetch(hStmt)) == SQL_SUCCESS)
{
stored the return
values of database_size and unused.
}
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
}
else
{
AfxMessageBox("error while allocating a statement handle");
}
So the Problem is :
Is there any way to access all recordsets returned by sp_spaceused
using ODBC APIs on C++?
Please reply.
Thanks in advance
-Ashutosh
exec sp_spaceused returns:
Result set 1:
database_name
database_size
unallocated space
Result set 2:
reserved
data
index_size
unused
So, from your description you are trying to get database_size (col2) from
first result set, and unused (col4) from second result set.
You have to:
SQLExecDirect()
SQLBindCol( database_name, col2 )
SQLFetch() until all records read from first result set
SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
...
SQLFreeStmt( SQL_UNBIND )
SQLBindCol( unused, col4 )
SQLFetch() until all records read from second result set
cmk
|||Thanx alot for your help !
Chris Kushnir wrote:
> exec sp_spaceused returns:
> Result set 1:
> database_name
> database_size
> unallocated space
> Result set 2:
> reserved
> data
> index_size
> unused
> So, from your description you are trying to get database_size (col2) from
> first result set, and unused (col4) from second result set.
> You have to:
> SQLExecDirect()
> SQLBindCol( database_name, col2 )
> SQLFetch() until all records read from first result set
> SQLMoreResults() if returns SQL_SUCESS then you have another result set, so
> ...
> SQLFreeStmt( SQL_UNBIND )
> SQLBindCol( unused, col4 )
> SQLFetch() until all records read from second result set
>
> cmk

Problem With Variables in Execute SQL Task

I am trying to run a simple update (just to establish it works) passing in a variable in the execute SQL task. It is within a Foreach loop and the incoming data is from a RecordSet destination. One of the fields in the recordset is UniqueID. I have a variable called User::UniqueID which holds the uniqueID value. I then have a second variable which is the insert statement of

"INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@.[User::UniqueID] + ", null, null,null,null,null,null,null"

with delayvalidation set to True.

when I run the package i get the following error:

Error: 0xC0014054 at Execute SQL Task: Failed to lock variable "INSERT rptlifespan_transactionimagecopy SELECT 543, null, null,null,null,null,null,null" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

543 is the value of my first UniqueID so that bit is working ok. What I do not know is why the insert statement is failing.

Please help i have very little hair left!!

Is the SQLSourceType=Variable and the SourceVariable set to the name of your variable in the task editor? It looks as if maybe SQLSourceType=Variable and then you're setting the SqlStatementSource property through an expression to the value of your variable.
|||

Hi Jay

i have checked and the SqlSourceType is Variable and the SourceVariable is set to User:: SQLTest2 which is the name of the variable that contains the Value INSERT blah blah blah....

When I run it the package fails with the same error and when I go back into the Execute Sql Task editor the SourceVariable has changed to the actual INSERT statement.

Any help appreciated

|||Delete the Execute SQL Task and try recreating it. I think, too, there must be an expression set somewhere incorrectly.|||

Racsco wrote:

When I run it the package fails with the same error and when I go back into the Execute Sql Task editor the SourceVariable has changed to the actual INSERT statement.

That shouldn't be happening. Maybe you should delete the Execute SQL task and recreate it.
|||

Hi Guys

thanks for your continuing help.

I have deleted and recreated it and now i get the following error

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "INSERT rptlifespan_transactionimagecopy SELECT 543, null, null,null,null,null,null,null" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

any ideas?

|||Right. You're selecting more than one column and perhaps row, so you need to set the Execute SQL task to return a Full Result Set, not Single Row, and assign it to an OBJECT variable, not an integer variable.

Any reason you're selecting a bunch of NULL literals? Drop them and add them later using a derived column if you desire. It will make working with your result set easier.|||

Phil Brammer wrote:

Right. You're selecting more than one column and perhaps row, so you need to set the Execute SQL task to return a Full Result Set, not Single Row, and assign it to an OBJECT variable, not an integer variable.

Any reason you're selecting a bunch of NULL literals? Drop them and add them later using a derived column if you desire. It will make working with your result set easier.

It's an INSERT statement. There will be no resultset.
|||

JayH wrote:

Phil Brammer wrote:

Right. You're selecting more than one column and perhaps row, so you need to set the Execute SQL task to return a Full Result Set, not Single Row, and assign it to an OBJECT variable, not an integer variable.

Any reason you're selecting a bunch of NULL literals? Drop them and add them later using a derived column if you desire. It will make working with your result set easier.

It's an INSERT statement. There will be no resultset.

Oh, right. I saw the INSERT and the SELECT together and misread it as two distinct statements.

So unless you have another SQL statement after the result set, set the result set to "None."|||

OK I am nearing frustration point!!

I have ripped it back to the bare minimum just so I can get it to work. I have a data flow that does a select on 1 column - UniqueID from a table into a recordset destination as an object variable called LoadList. This then flows to a Foreach Container set as Foreach ADO Enumerator with 1 Variable mapped as User:: UniqueID and index 0. Within the Foreach Loop is the Execute SQL task with SQLSourceType of Variable and SourceVariable of User:: SQLTest3 and result set set to none. I have 2 package level variables which are User:: UniqueID which is INT32 and value is initially 0 and User:: SQLTest3 which is "INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@.[User::UniqueID] . So all i am trying to do is place the unique ID from the initial select into a different table.

Thanks once again for any help

|||

Racsco wrote:

OK I am nearing frustration point!!

I have ripped it back to the bare minimum just so I can get it to work. I have a data flow that does a select on 1 column - UniqueID from a table into a recordset destination as an object variable called LoadList. This then flows to a Foreach Container set as Foreach ADO Enumerator with 1 Variable mapped as User:: UniqueID and index 0. Within the Foreach Loop is the Execute SQL task with SQLSourceType of Variable and SourceVariable of User:: SQLTest3 and result set set to none. I have 2 package level variables which are User:: UniqueID which is INT32 and value is initially 0 and User:: SQLTest3 which is "INSERT rptlifespan_transactionimagecopy SELECT " + (DT_WSTR, 20)@.[User::UniqueID] . So all i am trying to do is place the unique ID from the initial select into a different table.

Thanks once again for any help

Your description sounds good. I assume you have a reason for doing it this way instead of just bulk loading into rptlifespan_transactionimagecopy from the data flow. So from your "frustration" comment I assume you're still having a problem. Is it the "An error occurred while extracting the result into a variable..."? I don't see how you could be getting that if your ResultSet is None and there are no mappings on the Resultset page.
|||Forget the data flow.

Code block because of the $@.!* emoticon conversions...

1 - Execute SQL Task: select uniqueID from table. Put that into an object variable (User::ObjectVar, perhaps) and set the result set type to "Full Result Set"
2 - Foreach Loop - Set it to loop on the object variable created from step 1 (User::ObjectVar). Map the output accordingly to your "holding" variable (User::UniqueID)
3 - Execute SQL Task within the foreach loop: SQLSourceType: Variable SourceVariable: User::SQLTest3

That's it. One thing to note: On the variable, User::UniqueID, make sure you have EvaluateAsExpression set to TRUE and that you are using the expression property to build the insert string.|||

Hi Phil

I have followed the above the only bit I am not sure on is where you say "you are using the expression property to build the insert string" as my variable User:: SqlTest3 contains the insert statement?

Cheers again

scott

|||

Racsco wrote:

Hi Phil

I have followed the above the only bit I am not sure on is where you say "you are using the expression property to build the insert string" as my variable User:: SqlTest3 contains the insert statement?

Cheers again

scott

On the properties for SQLTest3, since you are concatenating another user variable in your SQL statement, you need to do that in the expression. Set the property, EvaluateAsExpression = TRUE for the variable, SQLTest3. Then use the expression:

"INSERT INTO ... SELECT " + [User::UniqueID]|||

Hi Phil

Went home and slept on it, came in deleted the lot and started fresh. What I did differently was to cast the UniqueID as a string from the initial select rather than casting it in User:: SQLtest3 variable and it works fine now.

Thanks for all your help and patience and also thanks to JayH

scott

Problem with using bcp

Hello all,
I try to execute the code below. Whatever I try I get the error:
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'dc_temp_text'.
This also happens when I put the owner and the database name in from of the
table name.
Can anybody tell me what it is that I'm missing?
TIA
Ronald
CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
begin
dbcc checkident (dc_temp_text, reseed, 0)
declare @.BcpCommand varchar(5000)
declare @.TableName varchar(100)
set @.Tablename = QuoteName('dc_temp_text')
/*
Schrijf het text bestand
*/
delete from dbo.dc_temp_text
insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
set @.BcpCommand = 'bcp "select * from '
set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
set @.bcpCommand = @.BcpCommand + '" queryout
c:\testuser1\text.txt -Uuser -Ppassword -c'
exec master..xp_cmdshell @.BcpCommand
endI assume that you have created the stored procedure in the same database
that you have the table dc_temp_text.
If you comment out the BCP portion I am guessing that the proc works
correctly.
I do not see where you specify the database or the server within the BCP
string.
Instead of
exec master..xp_cmdshell @.BcpCommand
Try
SELECT @.BcpCommand AS HereIsTheBCPstatement
If the command is correct you should be able to run it within DOS from your
PC. Use the output of the query to test your BCP statement.
Keith Kratochvil
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
> Can anybody tell me what it is that I'm missing?
> TIA
> Ronald
>
> CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
> begin
> dbcc checkident (dc_temp_text, reseed, 0)
> declare @.BcpCommand varchar(5000)
> declare @.TableName varchar(100)
> set @.Tablename = QuoteName('dc_temp_text')
> /*
> Schrijf het text bestand
> */
> delete from dbo.dc_temp_text
> insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
> set @.BcpCommand = 'bcp "select * from '
> set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
> set @.bcpCommand = @.BcpCommand + '" queryout
> c:\testuser1\text.txt -Uuser -Ppassword -c'
> exec master..xp_cmdshell @.BcpCommand
> end
>|||Keith,

> If you comment out the BCP portion I am guessing that the proc works
> correctly.
Yes, ther is no problem then. The table is filled and everything checks out.

> I do not see where you specify the database or the server within the BCP
> string.
I added that a few minutes later with no effect. I also tried to run the
statement in de dos prompt with the same result.
Any other ideas?
Greetings
Ronald
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
news:eyKaX9DdGHA.4224@.TK2MSFTNGP04.phx.gbl...
>I assume that you have created the stored procedure in the same database
>that you have the table dc_temp_text.
> If you comment out the BCP portion I am guessing that the proc works
> correctly.
> I do not see where you specify the database or the server within the BCP
> string.
> Instead of
> exec master..xp_cmdshell @.BcpCommand
> Try
> SELECT @.BcpCommand AS HereIsTheBCPstatement
> If the command is correct you should be able to run it within DOS from
> your PC. Use the output of the query to test your BCP statement.
> --
> Keith Kratochvil
>
> "Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
> news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
>|||>I added that a few minutes later with no effect. I also tried to run the
>statement in de dos prompt with the same result.
Ok, now we have isolated the problem to the BCP statement.
Type BCP into Query Analyzer
Highlight the string BCP
Hit Shift_F1
Read up on BCP overview within Books Online. You will see that you are be
missing a few params.
Keith Kratochvil
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:%23U3ffCEdGHA.380@.TK2MSFTNGP04.phx.gbl...
> Keith,
>
> Yes, ther is no problem then. The table is filled and everything checks
> out.
>
> I added that a few minutes later with no effect. I also tried to run the
> statement in de dos prompt with the same result.
> Any other ideas?
> Greetings
> Ronald
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> schreef in bericht
> news:eyKaX9DdGHA.4224@.TK2MSFTNGP04.phx.gbl...
>|||On Wed, 10 May 2006 15:52:28 +0200, Ronald Hermans wrote:

>Hello all,
>I try to execute the code below. Whatever I try I get the error:
>Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
>'dc_temp_text'.
>This also happens when I put the owner and the database name in from of the
>table name.
>Can anybody tell me what it is that I'm missing?
Hi Ronald,
Where do you put the owner and database name in front of the table name?
The execution of bcp will open a new connection to the server that will
connect to the user's default database. It is really important that you
always database-qualify the tablename on the bcp command.
Also, try what happens if yoou replace
exec master..xp_cmdshell @.BcpCommand
with
PRINT @.BcpCommand
Maybe this helps you see the problem in the generated bcp command. And
if not, you can post the output of the PRINT command here and see if
someone else sees a problem with it.
Hugo Kornelis, SQL Server MVP|||Ronald Hermans (rhermans@.datamedicare.nl) writes:
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
Is the server you are running on a default instance? Or is it a named
instance?
Since you do not specify -S the BCP command will connect to the default
instance. You get can the server name from @.@.servername.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Yes It is a Default instance, but just to make sure I entered the the
servername with the -S parameter. Unfortunatly it had no effect.
Ronald
"Erland Sommarskog" <esquel@.sommarskog.se> schreef in bericht
news:Xns97C06E3A3171Yazorman@.127.0.0.1...
> Ronald Hermans (rhermans@.datamedicare.nl) writes:
> Is the server you are running on a default instance? Or is it a named
> instance?
> Since you do not specify -S the BCP command will connect to the default
> instance. You get can the server name from @.@.servername.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hugo,
bcp "select * from dc_temp_text" queryout
c:\\text.txt -Sservername -Uuser -Ppassword -c
I tried to put the db name and the owner name in front of the table name
with no effect. I still get the same error.
Ronald
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> schreef in bericht
news:ulo462drdh8s5rv72pjm9bd4l9v12afjd8@.
4ax.com...
> On Wed, 10 May 2006 15:52:28 +0200, Ronald Hermans wrote:
>
> Hi Ronald,
> Where do you put the owner and database name in front of the table name?
> The execution of bcp will open a new connection to the server that will
> connect to the user's default database. It is really important that you
> always database-qualify the tablename on the bcp command.
> Also, try what happens if yoou replace
> exec master..xp_cmdshell @.BcpCommand
> with
> PRINT @.BcpCommand
> Maybe this helps you see the problem in the generated bcp command. And
> if not, you can post the output of the PRINT command here and see if
> someone else sees a problem with it.
> --
> Hugo Kornelis, SQL Server MVP|||bcp "select * from [your_db_name_goes_here].[dbo].dc_temp_text" queryout
"c:\text.txt" -Uuser -Ppassword -c
-oj
"Ronald Hermans" <rhermans@.datamedicare.nl> wrote in message
news:OaINpgMdGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hugo,
> bcp "select * from dc_temp_text" queryout
> c:\\text.txt -Sservername -Uuser -Ppassword -c
> I tried to put the db name and the owner name in front of the table name
> with no effect. I still get the same error.
> Ronald
>|||Hello all,
I think I may have found the problem (and if so, I'm going to cry). The
databasename starts with a 1 (one digit) and I think the bcp tool has a
problem with that.
Can anybody confirm this?
TIA
Ronald
"Ronald Hermans" <rhermans@.datamedicare.nl> schreef in bericht
news:uA94VkDdGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Hello all,
> I try to execute the code below. Whatever I try I get the error:
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
> 'dc_temp_text'.
> This also happens when I put the owner and the database name in from of
> the table name.
> Can anybody tell me what it is that I'm missing?
> TIA
> Ronald
>
> CREATE PROCEDURE dbo.dbc_SchrijfTextFiles AS
> begin
> dbcc checkident (dc_temp_text, reseed, 0)
> declare @.BcpCommand varchar(5000)
> declare @.TableName varchar(100)
> set @.Tablename = QuoteName('dc_temp_text')
> /*
> Schrijf het text bestand
> */
> delete from dbo.dc_temp_text
> insert into dbo.dc_temp_text values ('HEADER1|HEADER2|HEADER3')
> set @.BcpCommand = 'bcp "select * from '
> set @.BcpCommand = @.BcpCommand + @.TableName--'dc_temp_text'--@.TableName
> set @.bcpCommand = @.BcpCommand + '" queryout
> c:\testuser1\text.txt -Uuser -Ppassword -c'
> exec master..xp_cmdshell @.BcpCommand
> end
>