Problems installing External Stored procedure

Hi There

I'm having problems installing a external stored procedure that I created in Visual C++. I've created the .ddl and put it in the correct directory.

I've run the following commands to install it
sp_addextendedproc xp_regex, "D:\garth\ExternalStoredProcedures\Tecas\xp_regex.dll"

I then exec the procedure and get the following:

execute master..xp_regex

ODBC: Msg 0, Level 16, State 1
Cannot load the DLL \\tedevsql02\garth\ExternalStoredProcedures\Tecas, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

The .dll is definately in that directory. I'm not sure what I'm doing wrong. Any tips?

Thanks

SynAck
Sorry, that error should read as follows:

ODBC: Msg 0, Level 16, State 1
Cannot load the DLL D:\garth\ExternalStoredProcedures\Tecas\xp_regex.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

|||

Make sure you have also copied to that directory other .dll's that you reference from you program.

|||At the moment, the program is the equivilent of a "hello world" program

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 in variable passing from shell to procedure

hi,
i need to passing the variable from shell script to procedure,but it does not work.the following are my shell and procedure.
job(){
sqlplus user/password@.orcl <<EOF
exec count_prize_proc($1,$2,$3)
EOF
}
if [ $# -eq 3 ] ;then
job
else
echo " EXAMPLEjobstart.sh 155 200 210 "
fi

create or replace procedure count_prize_proc
(t_play_code in varchar2,
t_begin_term in varchar2,
t_end_term in varchar2 )
IS

------------------
call_proc.sh 005 100 200
write win_prize Program
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Oct 30 13:59:02 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production

SQL> BEGIN count_prize_proc(,,); END;
*
ERROR at line 1:
ORA-06550: line 1, column 24:
PLS-00103: Encountered the symbol "," when expecting one of the following:
( ) - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table avg count current exists max min prior sql stddev sum
variance execute multiset the both leading trailing forall
year month DAY_ HOUR_ MINUTE_ second TIMEZONE_HOUR_
TIMEZONE_MINUTE_ time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>

SQL> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.0.0 - 64bit Production
-------------------
then i modified my shell
exec count_prize_proc("$1","$2","$3")

------------------
SQL> ERROR:
ORA-01741: illegal zero-length identifier

Any ideas/suggestions?
thanks advance

graceHi,

ORA-01741: illegal zero-length identifier is raised because, an attempt was made to use two double quotes ("") as an identifier.

An identifier must be at least one character long. Action: Insert at least one character between the double quotes in the identifier. If a blank identifier is required, specify a blank space between the double quotes (" ").sql

Problems having moved from SQL 7 to SQL 2000

We ported our database 2 weeks ago. We have come across a problem with
one
stored procedure.
1) Running some queries / Procedures in Query Analyser return
"[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
The SQL Logs have the following errors whenever the procedure runs
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
I have tried to run the procedure from SQL Analyser on my client
machine and locally on the server. Both return the same error.
The stored procedure completes and the records appear in the relvent
tables. At best we get the errors described above and entries in teh
SQL log. The worst scenario we have had is each time the procedure
fired the AQL server services terminated unexpectedly disconencting
everyone from the SQL server and all databases (More of a problem I am
sure you will agree).
The stored procedure is outlined below. Running in SQL Analyser does
not display the printed debug messages although they are viewable in
the Stack dump.
SQL VERSION : Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec
17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
CREATE PROCEDURE spSaveNewCall
/*----*/
/* PROCEDURE : spSaveNewCall */
/* Description : saves a new call in database */
/*----*/
@.i_str_CustID varchar(50),
@.i_str_CustRef varchar(10),
@.i_str_ContactID int,
@.i_str_ProductCode varchar(6),
@.i_str_ModuleCode varchar(6),
@.i_str_Version varchar(10),
@.i_str_PatchLevel varchar(10),
@.i_str_EmergencyPatch varchar(10),
@.i_str_Environment varchar(4),
@.i_str_CallType varchar(100),
@.i_str_CallDescription text,
@.i_str_Priority varchar(1),
@.i_str_SLAclass varchar(10),
@.i_str_CustType varchar(15),
@.i_str_ModuleName varchar(40),
@.i_str_ProductName varchar(40),
@.i_str_ServerPlatform varchar(40)
AS
DECLARE @.l_str_CallID varchar(8)
DECLARE @.l_boo_calllog int
DECLARE @.l_boo_asgnmnt int
DECLARE @.l_boo_detail int
DECLARE @.l_boo_subset int
BEGIN TRANSACTION
print 'DEBUG : --update sequence number for anyone else trying to log
a call'
UPDATE HeatSeq
SET SeqValue = SeqValue + 1
WHERE SeqKey = 'CallID'
--get the next call id
SET @.l_str_CallID = (SELECT SeqValue AS NextCall FROM HEATSeq WHERE
SeqKey = 'CallID')
SET @.l_str_CallID = RIGHT('00000000' + @.l_str_CallID,8)
print 'DEBUG : --start logging call'
SET @.l_boo_calllog = 0
SET @.l_boo_asgnmnt = 0
SET @.l_boo_detail = 0
SET @.l_boo_subset = 0
INSERT INTO CallLog
( CallID,
CompanyRef,
CustID,
CallType,
CallStatus,
Tracker,
Priority,
CallDesc,
RecvdBy,
RecvdDate,
RecvdTime,
ModBy,
ModDate,
ModTime,
CallSource,
SLAClass,
Environment,
ProductCode,
ModuleCode,
OnHold,
ProductVersion,
PatchLevel,
EmergencyPatch,
CustType,
ProductModule,
Product,
DBPlatform,
SLA_ClockStatus,
SLA_CalcCBWarn,
SLA_CalcCloseWarn,
SLA_CalcClose,
SLA_CalcCB,
SLA_Status,
Weighting,
PriorityDesc,
ServerPlatform,
DTLastMod,
Escalated,
EscalationOrder
)
( SELECT @.l_str_CallID,
@.i_str_CustRef,
@.i_str_CustID,
@.i_str_CallType,
'Open',
'HSS',
@.i_str_Priority,
@.i_str_CallDescription,
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'Website',
@.i_str_SLAclass,
@.i_str_Environment,
@.i_str_ProductCode,
@.i_str_ModuleCode,
'F',
@.i_str_Version,
@.i_str_PatchLevel,
@.i_str_EmergencyPatch,
@.i_str_CustType,
@.i_str_ModuleName,
@.i_str_ProductName,
'Progress',
'SLA is Stopped',
SLA_WarnResponse,
SLA_WarnComplete,
SLA_TgtComplete,
SLA_TgtResponse,
'OK',
Weighting,
SLA_PriorityDesc,
@.i_str_ServerPlatform,
datediff(ss,'01-01-1970',getdate()),
'F',
0
FROM SLAMatrix,
Priority
WHERE SLA_Priority = Priority.Priority
AND SLA_Priority = @.i_str_Priority
AND SLA_Class = @.i_str_SLAclass
)
print 'DEBUG : --check calllog has been updated'
SET @.l_boo_calllog = @.@.ERROR
print 'DEBUG : --save the subset'
INSERT INTO Subset
( CustID,
CallID,
CustType,
EmailID,
Phone,
CompanyName,
Contact,
Ext,
CustRefReq,
OrchMgr,
Phone2,
Ext2,
PhoneDesc1,
PhoneDesc2,
ContactMethod,
Fax1,
Alert,
AccMgr,
ProjMgr,
Supported,
Mobile,
KeyCustomer,
ContactSeqNum
)
( SELECT @.i_str_CustID,
@.l_str_CallID,
CustType,
Email1,
Telephone1,
CustomerName,
ContactName,
Extension1,
CustRef,
FPOC,
Telephone2,
Extension2,
Tel1Description,
Tel2Description,
ContactMethod,
Fax1,
ALERT,
AccountManager,
ProjectManager,
Supported,
Mobile1,
KeyCustomer,
ContactSeqNum
FROM Contacts, Profile
WHERE ContactSeqNum = @.i_str_ContactID
AND Contacts.CustID = Profile.CustID
)
print 'DEBUG : --check subset has been updated'
SET @.l_boo_subset = @.@.ERROR
print 'DEBUG : --save the details'
INSERT INTO Detail
(
CallID,
Details
)
VALUES
(
@.l_str_CallID,
''
)
print 'DEBUG : --check detail has been updated'
SET @.l_boo_detail = @.@.ERROR
print 'DEBUG : --save the assignment'
INSERT INTO Asgnmnt
(
AssignedBy,
DateAssign,
TimeAssign,
GroupName,
CallID,
HEATSeq,
GroupEMail,
DTLastMod,
Assignee,
GroupDesc,
ResolveOrder,
WhoResolv
)
VALUES
(
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'WebUpdate',
@.l_str_CallID,
datediff(ss,'01-01-1970',getdate()),
'CustomerServices@.orchard-systems.co.uk',
datediff(ss,'01-01-1970',getdate()),
'HSS',
'New call logged on-line',
0,
''
)
print 'DEBUG : --check asgnmnt has been updated'
SET @.l_boo_asgnmnt = @.@.ERROR
IF @.l_boo_calllog = 0 AND @.l_boo_asgnmnt = 0 AND @.l_boo_detail = 0
AND @.l_boo_subset = 0
BEGIN
print 'DEBUG : --Transaction Commit'
COMMIT TRANSACTION
--return the call id
SELECT @.l_str_CallID AS NextCall
END
ELSE
BEGIN
print 'DEBUG : --Transaction Rollback'
ROLLBACK TRANSACTION
SELECT 'No Call Raised' AS NextCall
END
/*----*/
GOAt the very least, you need to correct your logic to accurately figure out
what is happening. You have a fundamental mistake in your error handling.
Try the following:
if object_id ('tempdb..#test') is not null
drop table #test
create table #test (test_id int not null)
insert #test (test_id) values (null)
print 'debug'
select @.@.error
insert #test (test_id) values (null)
select @.@.error
print 'debug'
go
Notice how the print statement changes the value of @.@.error. Erland has an
excellent discussion of error handling
(http://www.sommarskog.se/error-handling-II.html). Once you understand the
above, you should then correct the 2nd logic error. There would appear to
be no need to execute any insert statements that follow the first failed
insert statement. Yet your procedure just keeps blindly inserting until the
end. This only wastes server resources. You should also review any trigger
logic that will execute - this is a frequent problem area.
Inspite of the logic issues, the error log indicates that the fault is
within sql server. This requires assistance from MS to diagnose and
correct. The profiler might offer some insight - perhaps there is a problem
with parallelism. If so, you might be able to at least avoid the problem.
Perhaps the problem can be avoided entirely by correcting the logic flaws
alone.sql

Problems having moved from SQL 7 to SQL 2000

We ported our database 2 weeks ago. We have come across a problem with
one
stored procedure.
1) Running some queries / Procedures in Query Analyser return
"[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
The SQL Logs have the following errors whenever the procedure runs
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
I have tried to run the procedure from SQL Analyser on my client
machine and locally on the server. Both return the same error.
The stored procedure completes and the records appear in the relvent
tables. At best we get the errors described above and entries in teh
SQL log. The worst scenario we have had is each time the procedure
fired the AQL server services terminated unexpectedly disconencting
everyone from the SQL server and all databases (More of a problem I am
sure you will agree).
The stored procedure is outlined below. Running in SQL Analyser does
not display the printed debug messages although they are viewable in
the Stack dump.
SQL VERSION : Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec
17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
CREATE PROCEDURE spSaveNewCall
/*----*/
/* PROCEDURE : spSaveNewCall */
/* Description : saves a new call in database */
/*----*/
@.i_str_CustID varchar(50),
@.i_str_CustRef varchar(10),
@.i_str_ContactID int,
@.i_str_ProductCode varchar(6),
@.i_str_ModuleCode varchar(6),
@.i_str_Version varchar(10),
@.i_str_PatchLevel varchar(10),
@.i_str_EmergencyPatch varchar(10),
@.i_str_Environment varchar(4),
@.i_str_CallType varchar(100),
@.i_str_CallDescription text,
@.i_str_Priority varchar(1),
@.i_str_SLAclass varchar(10),
@.i_str_CustType varchar(15),
@.i_str_ModuleName varchar(40),
@.i_str_ProductName varchar(40),
@.i_str_ServerPlatform varchar(40)
AS
DECLARE @.l_str_CallID varchar(8)
DECLARE @.l_boo_calllog int
DECLARE @.l_boo_asgnmnt int
DECLARE @.l_boo_detail int
DECLARE @.l_boo_subset int
BEGIN TRANSACTION
print 'DEBUG : --update sequence number for anyone else trying to log
a call'
UPDATE HeatSeq
SET SeqValue = SeqValue + 1
WHERE SeqKey = 'CallID'
--get the next call id
SET @.l_str_CallID = (SELECT SeqValue AS NextCall FROM HEATSeq WHERE
SeqKey = 'CallID')
SET @.l_str_CallID = RIGHT('00000000' + @.l_str_CallID,8)
print 'DEBUG : --start logging call'
SET @.l_boo_calllog = 0
SET @.l_boo_asgnmnt = 0
SET @.l_boo_detail = 0
SET @.l_boo_subset = 0
INSERT INTO CallLog
( CallID,
CompanyRef,
CustID,
CallType,
CallStatus,
Tracker,
Priority,
CallDesc,
RecvdBy,
RecvdDate,
RecvdTime,
ModBy,
ModDate,
ModTime,
CallSource,
SLAClass,
Environment,
ProductCode,
ModuleCode,
OnHold,
ProductVersion,
PatchLevel,
EmergencyPatch,
CustType,
ProductModule,
Product,
DBPlatform,
SLA_ClockStatus,
SLA_CalcCBWarn,
SLA_CalcCloseWarn,
SLA_CalcClose,
SLA_CalcCB,
SLA_Status,
Weighting,
PriorityDesc,
ServerPlatform,
DTLastMod,
Escalated,
EscalationOrder
)
( SELECT @.l_str_CallID,
@.i_str_CustRef,
@.i_str_CustID,
@.i_str_CallType,
'Open',
'HSS',
@.i_str_Priority,
@.i_str_CallDescription,
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'Website',
@.i_str_SLAclass,
@.i_str_Environment,
@.i_str_ProductCode,
@.i_str_ModuleCode,
'F',
@.i_str_Version,
@.i_str_PatchLevel,
@.i_str_EmergencyPatch,
@.i_str_CustType,
@.i_str_ModuleName,
@.i_str_ProductName,
'Progress',
'SLA is Stopped',
SLA_WarnResponse,
SLA_WarnComplete,
SLA_TgtComplete,
SLA_TgtResponse,
'OK',
Weighting,
SLA_PriorityDesc,
@.i_str_ServerPlatform,
datediff(ss,'01-01-1970',getdate()),
'F',
0
FROM SLAMatrix,
Priority
WHERE SLA_Priority = Priority.Priority
AND SLA_Priority = @.i_str_Priority
AND SLA_Class = @.i_str_SLAclass
)
print 'DEBUG : --check calllog has been updated'
SET @.l_boo_calllog = @.@.ERROR
print 'DEBUG : --save the subset'
INSERT INTO Subset
( CustID,
CallID,
CustType,
EmailID,
Phone,
CompanyName,
Contact,
Ext,
CustRefReq,
OrchMgr,
Phone2,
Ext2,
PhoneDesc1,
PhoneDesc2,
ContactMethod,
Fax1,
Alert,
AccMgr,
ProjMgr,
Supported,
Mobile,
KeyCustomer,
ContactSeqNum
)
( SELECT @.i_str_CustID,
@.l_str_CallID,
CustType,
Email1,
Telephone1,
CustomerName,
ContactName,
Extension1,
CustRef,
FPOC,
Telephone2,
Extension2,
Tel1Description,
Tel2Description,
ContactMethod,
Fax1,
ALERT,
AccountManager,
ProjectManager,
Supported,
Mobile1,
KeyCustomer,
ContactSeqNum
FROM Contacts, Profile
WHERE ContactSeqNum = @.i_str_ContactID
AND Contacts.CustID = Profile.CustID
)
print 'DEBUG : --check subset has been updated'
SET @.l_boo_subset = @.@.ERROR
print 'DEBUG : --save the details'
INSERT INTO Detail
(
CallID,
Details
)
VALUES
(
@.l_str_CallID,
''
)
print 'DEBUG : --check detail has been updated'
SET @.l_boo_detail = @.@.ERROR
print 'DEBUG : --save the assignment'
INSERT INTO Asgnmnt
(
AssignedBy,
DateAssign,
TimeAssign,
GroupName,
CallID,
HEATSeq,
GroupEMail,
DTLastMod,
Assignee,
GroupDesc,
ResolveOrder,
WhoResolv
)
VALUES
(
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'WebUpdate',
@.l_str_CallID,
datediff(ss,'01-01-1970',getdate()),
'CustomerServices@.orchard-systems.co.uk',
datediff(ss,'01-01-1970',getdate()),
'HSS',
'New call logged on-line',
0,
''
)
print 'DEBUG : --check asgnmnt has been updated'
SET @.l_boo_asgnmnt = @.@.ERROR
IF @.l_boo_calllog = 0 AND @.l_boo_asgnmnt = 0 AND @.l_boo_detail = 0
AND @.l_boo_subset = 0
BEGIN
print 'DEBUG : --Transaction Commit'
COMMIT TRANSACTION
--return the call id
SELECT @.l_str_CallID AS NextCall
END
ELSE
BEGIN
print 'DEBUG : --Transaction Rollback'
ROLLBACK TRANSACTION
SELECT 'No Call Raised' AS NextCall
END
/*----*/
GOAt the very least, you need to correct your logic to accurately figure out
what is happening. You have a fundamental mistake in your error handling.
Try the following:
if object_id ('tempdb..#test') is not null
drop table #test
create table #test (test_id int not null)
insert #test (test_id) values (null)
print 'debug'
select @.@.error
insert #test (test_id) values (null)
select @.@.error
print 'debug'
go
Notice how the print statement changes the value of @.@.error. Erland has an
excellent discussion of error handling
(http://www.sommarskog.se/error-handling-II.html). Once you understand the
above, you should then correct the 2nd logic error. There would appear to
be no need to execute any insert statements that follow the first failed
insert statement. Yet your procedure just keeps blindly inserting until the
end. This only wastes server resources. You should also review any trigger
logic that will execute - this is a frequent problem area.
Inspite of the logic issues, the error log indicates that the fault is
within sql server. This requires assistance from MS to diagnose and
correct. The profiler might offer some insight - perhaps there is a problem
with parallelism. If so, you might be able to at least avoid the problem.
Perhaps the problem can be avoided entirely by correcting the logic flaws
alone.

Problems having moved from SQL 7 to SQL 2000

We ported our database 2 weeks ago. We have come across a problem with
one
stored procedure.
1) Running some queries / Procedures in Query Analyser return
"[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
The SQL Logs have the following errors whenever the procedure runs
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
I have tried to run the procedure from SQL Analyser on my client
machine and locally on the server. Both return the same error.
The stored procedure completes and the records appear in the relvent
tables. At best we get the errors described above and entries in teh
SQL log. The worst scenario we have had is each time the procedure
fired the AQL server services terminated unexpectedly disconencting
everyone from the SQL server and all databases (More of a problem I am
sure you will agree).
The stored procedure is outlined below. Running in SQL Analyser does
not display the printed debug messages although they are viewable in
the Stack dump.
SQL VERSION : Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec
17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
CREATE PROCEDURE spSaveNewCall
/*----*/
/* PROCEDURE :spSaveNewCall */
/* Description : saves a new call in database */
/*----*/
@.i_str_CustID varchar(50),
@.i_str_CustRefvarchar(10),
@.i_str_ContactID int,
@.i_str_ProductCode varchar(6),
@.i_str_ModuleCode varchar(6),
@.i_str_Version varchar(10),
@.i_str_PatchLevel varchar(10),
@.i_str_EmergencyPatch varchar(10),
@.i_str_Environment varchar(4),
@.i_str_CallType varchar(100),
@.i_str_CallDescription text,
@.i_str_Priorityvarchar(1),
@.i_str_SLAclassvarchar(10),
@.i_str_CustTypevarchar(15),
@.i_str_ModuleNamevarchar(40),
@.i_str_ProductNamevarchar(40),
@.i_str_ServerPlatformvarchar(40)
AS
DECLARE @.l_str_CallID varchar(8)
DECLARE @.l_boo_calllog int
DECLARE @.l_boo_asgnmnt int
DECLARE @.l_boo_detail int
DECLARE @.l_boo_subset int
BEGIN TRANSACTION
print 'DEBUG : --update sequence number for anyone else trying to log
a call'
UPDATE HeatSeq
SET SeqValue = SeqValue + 1
WHERE SeqKey = 'CallID'
--get the next call id
SET @.l_str_CallID = (SELECT SeqValue AS NextCall FROM HEATSeq WHERE
SeqKey = 'CallID')
SET @.l_str_CallID = RIGHT('00000000' + @.l_str_CallID,8)
print 'DEBUG : --start logging call'
SET @.l_boo_calllog = 0
SET @.l_boo_asgnmnt = 0
SET @.l_boo_detail = 0
SET @.l_boo_subset = 0
INSERT INTO CallLog
(CallID,
CompanyRef,
CustID,
CallType,
CallStatus,
Tracker,
Priority,
CallDesc,
RecvdBy,
RecvdDate,
RecvdTime,
ModBy,
ModDate,
ModTime,
CallSource,
SLAClass,
Environment,
ProductCode,
ModuleCode,
OnHold,
ProductVersion,
PatchLevel,
EmergencyPatch,
CustType,
ProductModule,
Product,
DBPlatform,
SLA_ClockStatus,
SLA_CalcCBWarn,
SLA_CalcCloseWarn,
SLA_CalcClose,
SLA_CalcCB,
SLA_Status,
Weighting,
PriorityDesc,
ServerPlatform,
DTLastMod,
Escalated,
EscalationOrder
)
(SELECT@.l_str_CallID,
@.i_str_CustRef,
@.i_str_CustID,
@.i_str_CallType,
'Open',
'HSS',
@.i_str_Priority,
@.i_str_CallDescription,
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'Website',
@.i_str_SLAclass,
@.i_str_Environment,
@.i_str_ProductCode,
@.i_str_ModuleCode,
'F',
@.i_str_Version,
@.i_str_PatchLevel,
@.i_str_EmergencyPatch,
@.i_str_CustType,
@.i_str_ModuleName,
@.i_str_ProductName,
'Progress',
'SLA is Stopped',
SLA_WarnResponse,
SLA_WarnComplete,
SLA_TgtComplete,
SLA_TgtResponse,
'OK',
Weighting,
SLA_PriorityDesc,
@.i_str_ServerPlatform,
datediff(ss,'01-01-1970',getdate()),
'F',
0
FROMSLAMatrix,
Priority
WHERESLA_Priority=Priority.Priority
ANDSLA_Priority=@.i_str_Priority
ANDSLA_Class=@.i_str_SLAclass
)
print 'DEBUG : --check calllog has been updated'
SET @.l_boo_calllog = @.@.ERROR
print 'DEBUG : --save the subset'
INSERT INTO Subset
(CustID,
CallID,
CustType,
EmailID,
Phone,
CompanyName,
Contact,
Ext,
CustRefReq,
OrchMgr,
Phone2,
Ext2,
PhoneDesc1,
PhoneDesc2,
ContactMethod,
Fax1,
Alert,
AccMgr,
ProjMgr,
Supported,
Mobile,
KeyCustomer,
ContactSeqNum
)
(SELECT@.i_str_CustID,
@.l_str_CallID,
CustType,
Email1,
Telephone1,
CustomerName,
ContactName,
Extension1,
CustRef,
FPOC,
Telephone2,
Extension2,
Tel1Description,
Tel2Description,
ContactMethod,
Fax1,
ALERT,
AccountManager,
ProjectManager,
Supported,
Mobile1,
KeyCustomer,
ContactSeqNum
FROM Contacts, Profile
WHEREContactSeqNum=@.i_str_ContactID
ANDContacts.CustID = Profile.CustID
)
print 'DEBUG : --check subset has been updated'
SET @.l_boo_subset = @.@.ERROR
print 'DEBUG : --save the details'
INSERT INTO Detail
(
CallID,
Details
)
VALUES
(
@.l_str_CallID,
''
)
print 'DEBUG : --check detail has been updated'
SET @.l_boo_detail = @.@.ERROR
print 'DEBUG : --save the assignment'
INSERT INTO Asgnmnt
(
AssignedBy,
DateAssign,
TimeAssign,
GroupName,
CallID,
HEATSeq,
GroupEMail,
DTLastMod,
Assignee,
GroupDesc,
ResolveOrder,
WhoResolv
)
VALUES
(
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'WebUpdate',
@.l_str_CallID,
datediff(ss,'01-01-1970',getdate()),
'CustomerServices@.orchard-systems.co.uk',
datediff(ss,'01-01-1970',getdate()),
'HSS',
'New call logged on-line',
0,
''
)
print 'DEBUG : --check asgnmnt has been updated'
SET @.l_boo_asgnmnt = @.@.ERROR
IF @.l_boo_calllog = 0 AND @.l_boo_asgnmnt = 0 AND @.l_boo_detail = 0
AND @.l_boo_subset = 0
BEGIN
print 'DEBUG : --Transaction Commit'
COMMIT TRANSACTION
--return the call id
SELECT @.l_str_CallID AS NextCall
END
ELSE
BEGIN
print 'DEBUG : --Transaction Rollback'
ROLLBACK TRANSACTION
SELECT 'No Call Raised' AS NextCall
END
/*----*/
GO
At the very least, you need to correct your logic to accurately figure out
what is happening. You have a fundamental mistake in your error handling.
Try the following:
if object_id ('tempdb..#test') is not null
drop table #test
create table #test (test_id int not null)
insert #test (test_id) values (null)
print 'debug'
select @.@.error
insert #test (test_id) values (null)
select @.@.error
print 'debug'
go
Notice how the print statement changes the value of @.@.error. Erland has an
excellent discussion of error handling
(http://www.sommarskog.se/error-handling-II.html). Once you understand the
above, you should then correct the 2nd logic error. There would appear to
be no need to execute any insert statements that follow the first failed
insert statement. Yet your procedure just keeps blindly inserting until the
end. This only wastes server resources. You should also review any trigger
logic that will execute - this is a frequent problem area.
Inspite of the logic issues, the error log indicates that the fault is
within sql server. This requires assistance from MS to diagnose and
correct. The profiler might offer some insight - perhaps there is a problem
with parallelism. If so, you might be able to at least avoid the problem.
Perhaps the problem can be avoided entirely by correcting the logic flaws
alone.

Problems getting all the data back with a XML query

I am having a nightmare trying to get to the bottom of this problem.
I have a stored procedure that has a user defined function to create a
select table from a csv string of IDs.
The function and the Stored procedure works fine and returns the XML,
Elements as required except that when i process this sp on the client most
of the data is missing.
If i run the query in the query analyser with the csv parameter and no FOR
XML output i get 72 records (10 unique resources, the rest are due to the
joins in the query)
If i run it with the FOR XML i get 6 lines of XML although it is oddly
truncated. If i cut and past it into xmlspy there are missing elements so i
can't verify exactly what i'm getting.
But when i run this in my web app and use am XMLTextReader to turn the
results into a string I get 2 records, when i was expecting 10. I can verify
this by pasting the xml into xmlspy.
I have tried SQLXML managed classes but can't get them to accept parameters,
i keep getting the message that the sp is expecting parameter despite using
createParameter against the command as per all the documentation, so i gave
up with this approach. I thought that this way i could populate a new
dataset and then bind a table to a datagrid control to see what was
returned. I can get it all to work bar the parameters.
Can anyone shed any light on why the results are significantly less than the
sp should return
and can someone give me an example of working with a parameterised sp and
SQLXML
Many thanks
John
VS2003, SQLXML sp2, XPpro sp2
I am confused about what you are trying to get.
FOR XML results a single XML stream if you use the supported APIs through
ADO.Net, ADO, or OLEDB's stream interfaces. Query Analyser is using ODBC and
thus shows the XML result junked into 2033 characters per row. You should
not use QA if you plan on further process the result.
So if you can provide us with some more information about what exactly you
do on the API level, we may be able to help...
Best regards
Michael
"John Mas" <mase@.btopenworld.org> wrote in message
news:G2x8d.316$Xy3.217@.newsfe6-gui.ntli.net...
>I am having a nightmare trying to get to the bottom of this problem.
> I have a stored procedure that has a user defined function to create a
> select table from a csv string of IDs.
> The function and the Stored procedure works fine and returns the XML,
> Elements as required except that when i process this sp on the client most
> of the data is missing.
> If i run the query in the query analyser with the csv parameter and no FOR
> XML output i get 72 records (10 unique resources, the rest are due to the
> joins in the query)
> If i run it with the FOR XML i get 6 lines of XML although it is oddly
> truncated. If i cut and past it into xmlspy there are missing elements so
> i can't verify exactly what i'm getting.
> But when i run this in my web app and use am XMLTextReader to turn the
> results into a string I get 2 records, when i was expecting 10. I can
> verify this by pasting the xml into xmlspy.
> I have tried SQLXML managed classes but can't get them to accept
> parameters, i keep getting the message that the sp is expecting parameter
> despite using createParameter against the command as per all the
> documentation, so i gave up with this approach. I thought that this way i
> could populate a new dataset and then bind a table to a datagrid control
> to see what was returned. I can get it all to work bar the parameters.
> Can anyone shed any light on why the results are significantly less than
> the sp should return
> and can someone give me an example of working with a parameterised sp and
> SQLXML
>
> Many thanks
> John
> VS2003, SQLXML sp2, XPpro sp2
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.xml:25072
Michael,
thanks I have sorted the problem eventually. Brain fade and not enough time
thinking it throgh. The problem was in the sql statement which took ages to
track down but there we go.
One question that is unanswered is how do i pass parameters to SQLXML with
stored procedures, not raw sql text?
i keep getting the message expecting parameter as per my post
thanks
john
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Ok7uv9DrEHA.2796@.TK2MSFTNGP10.phx.gbl...
>I am confused about what you are trying to get.
> FOR XML results a single XML stream if you use the supported APIs through
> ADO.Net, ADO, or OLEDB's stream interfaces. Query Analyser is using ODBC
> and thus shows the XML result junked into 2033 characters per row. You
> should not use QA if you plan on further process the result.
> So if you can provide us with some more information about what exactly you
> do on the API level, we may be able to help...
> Best regards
> Michael
> "John Mas" <mase@.btopenworld.org> wrote in message
> news:G2x8d.316$Xy3.217@.newsfe6-gui.ntli.net...
>
|||To what exactly do you want to pass parameters? SQLXML is a general term and
the name of the mid-tier component.
Do you mean how to pass parameters into the SQL statement that uses FOR XML
via stored procs?
Thanks
Michael
"John Mas" <mase@.btopenworld.org> wrote in message
news:Fmz9d.270$Vd.96@.newsfe5-win.ntli.net...
> Michael,
> thanks I have sorted the problem eventually. Brain fade and not enough
> time thinking it throgh. The problem was in the sql statement which took
> ages to track down but there we go.
> One question that is unanswered is how do i pass parameters to SQLXML with
> stored procedures, not raw sql text?
> i keep getting the message expecting parameter as per my post
> thanks
> john
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:Ok7uv9DrEHA.2796@.TK2MSFTNGP10.phx.gbl...
>
|||Michael
here is the code that i am using
Dim strConn = "provider=SQLOLEDB;data source='....';initial
catalog=......;user id=sa;password=......"
Dim sxcCmd As New SqlXmlCommand(strConn)
Dim sdaDA As New SqlXmlAdapter(sxcCmd)
Dim sxpParam As SqlXmlParameter
Dim xr As Xml.XmlReader
Dim ds As New DataSet
With sxcCmd
..RootTag = "root"
..CommandType = SqlXmlCommandType.Sql
..CommandText = "Test2"
sxpParam = .CreateParameter
End With
With sxpParam
..Name = "@.IDs"
..Value = 5
End With
sdaDA.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
and here is the sp
ALTER PROCEDURE dbo.test2
(
@.IDs int
)
AS
/* SET NOCOUNT ON */
SELECT '<root>'
SELECT * FROM tblResource WHERE ResourceID=@.Ids
FOR XML AUTO, Elements
SELECT '</root>'
as you can see the sp has this parameter @.IDs but when i run the code the
error meesage says ' expecting parameter @.IDs'' yet i am passing it. If i
change the command text to a sql statement with a ? for the parameter then
it works.
Obviously i am missing something here, I presume the command type might be
wrongly set.
thanks
john
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:O45cbdZrEHA.1964@.TK2MSFTNGP12.phx.gbl...
> To what exactly do you want to pass parameters? SQLXML is a general term
> and the name of the mid-tier component.
> Do you mean how to pass parameters into the SQL statement that uses FOR
> XML via stored procs?
> Thanks
> Michael
> "John Mas" <mase@.btopenworld.org> wrote in message
> news:Fmz9d.270$Vd.96@.newsfe5-win.ntli.net...
>

Problems Executing Stored procedure OLEDB

<sigh> been doing all kinds of stuff in SSIS and then I get to what I thought should be simple: Running a stored procedure, and I've had nothing but headache:

OLEDB connection, ResultSet None

SQL Statement: EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT

Parameters Mapping page:

User::gvSourceName Input VARCHAR 0, User::gvDestinationName Input VARCHAR 1, System::UserName Input VARCHAR 2 etc.. etc.. UserlvDataImportID Output NUMERIC 6

I've emptied my stored procedure out... but that doesn't seem to matter it doesn't ever run the procedure. If I try to parse the Query I get the error "The Query failed to parse. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

If I run the package I get the error: [Execute SQL Task] Error: Executing the query "EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT" failed with the following error: "Unspecified error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've tried without the output variable and I still get the corrupt memory error and when I run the package I get: [Execute SQL Task] Error: Executing the query "EXEC dbo.pDataImportInfoInitINSERT ?, ?, ?, ?, ?, ?" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Even though I have made sure the datatypes match exactly between the parameters in the stored procedure and in the execute sql. I also know my connection is good as I use it in many steps before I get to the execute sql task...

Any ideas? I've tried just about everything I can think of short of building a whole new package...

FYI I can get it to working using the ADO.NET method (spelling out the parameter names etc) so long as I set the "IsQueryStoredProcedure" to false... I don't know why the oledb method is giving me so many problems - I'd like to keep only one connection to the database so having an OLE DB connection for everything except my execute sql tasks is only an option if there is no other way....|||I don't know what the deal was... I deleted the object and rebuilt it and now it is working including the output parameter.. except it doesn't like the date parameters I pass it... is there any way to actually see what the SSIS is passing to SQL? In the logs it just shows all ? marks.|||

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

IMHO this is the biggest pain with SSIS - the datatyping (and the lack of errors to tell you datatyping is the issue when it fails). Now I know why they did it etc (so you know every explicit conversion for tuning purposes) - which I understand and agree with but the different objects have different choices is what is so tough for me... ie the parameters datatypes didn't have INT or INT16/32 etc... and the datatypes you can choose for your variables are another set etc... In the DB world I generally use Varchar, int, and smalldatetimes and what datatype to map that to in SSIS seems to be a mystery that depends on what object you are dealing with..

Any chance Microsoft can at the very least issue a whitepaper on tips for datatypes when dealing with SSIS and SQL server?

|||

Chris Honcoop wrote:

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

could you not map the parameter to an ssis variable? afaik, this is the preferred way in ssis to execute stored procedures.

|||The parameter was mapped as an output parameter (that is what I meant by "return type" sorry that is not very clear). In the SQL the output parameter was INT... in SSIS my variable mapping was NUMERIC... which caused errors while LONG worked great.|||

Hi,

I too have similar problem but the error what i am getting now is

[Execute SQL Task] Error: Executing the query "exec spm_Utopia_FinanceUsage_Create_MonthlyTable ?,? OUTPUT " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The parametrs used are user:tableName input varchar 0,user:filegroupName input varchar 1,user:return_value output Long 2

sp returns a integer value so i am using long.

I tried using returnvalue instead of output still no use.

I tried the ADO.net connection it works fine but since i am using oledb connection i want to keep it through out.

|||Hmmm I have not seen that error. How are you "returning" the value - with a output variable or just returning it from the sproc?. That determines if you use the ? OUTPUT (if you return it via variable) or result set (for example if you select/print etc the result in the sp)sql

Problems Executing Stored procedure OLEDB

<sigh> been doing all kinds of stuff in SSIS and then I get to what I thought should be simple: Running a stored procedure, and I've had nothing but headache:

OLEDB connection, ResultSet None

SQL Statement: EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT

Parameters Mapping page:

User::gvSourceName Input VARCHAR 0, User::gvDestinationName Input VARCHAR 1, System::UserName Input VARCHAR 2 etc.. etc.. UserlvDataImportID Output NUMERIC 6

I've emptied my stored procedure out... but that doesn't seem to matter it doesn't ever run the procedure. If I try to parse the Query I get the error "The Query failed to parse. Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

If I run the package I get the error: [Execute SQL Task] Error: Executing the query "EXEC dbo.pStoredProcedure ?, ?, ?, ?, ?, ?, ? OUTPUT" failed with the following error: "Unspecified error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've tried without the output variable and I still get the corrupt memory error and when I run the package I get: [Execute SQL Task] Error: Executing the query "EXEC dbo.pDataImportInfoInitINSERT ?, ?, ?, ?, ?, ?" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Even though I have made sure the datatypes match exactly between the parameters in the stored procedure and in the execute sql. I also know my connection is good as I use it in many steps before I get to the execute sql task...

Any ideas? I've tried just about everything I can think of short of building a whole new package...

FYI I can get it to working using the ADO.NET method (spelling out the parameter names etc) so long as I set the "IsQueryStoredProcedure" to false... I don't know why the oledb method is giving me so many problems - I'd like to keep only one connection to the database so having an OLE DB connection for everything except my execute sql tasks is only an option if there is no other way....|||I don't know what the deal was... I deleted the object and rebuilt it and now it is working including the output parameter.. except it doesn't like the date parameters I pass it... is there any way to actually see what the SSIS is passing to SQL? In the logs it just shows all ? marks.|||

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

IMHO this is the biggest pain with SSIS - the datatyping (and the lack of errors to tell you datatyping is the issue when it fails). Now I know why they did it etc (so you know every explicit conversion for tuning purposes) - which I understand and agree with but the different objects have different choices is what is so tough for me... ie the parameters datatypes didn't have INT or INT16/32 etc... and the datatypes you can choose for your variables are another set etc... In the DB world I generally use Varchar, int, and smalldatetimes and what datatype to map that to in SSIS seems to be a mystery that depends on what object you are dealing with..

Any chance Microsoft can at the very least issue a whitepaper on tips for datatypes when dealing with SSIS and SQL server?

|||

Chris Honcoop wrote:

Good news to those having similar problems (if anyone)... I found the problem:

I was using NUMERIC for the return type (in SQL the return was an INT) in my original object that had all the issues. In the new one I used LONG as the return.

could you not map the parameter to an ssis variable? afaik, this is the preferred way in ssis to execute stored procedures.

|||The parameter was mapped as an output parameter (that is what I meant by "return type" sorry that is not very clear). In the SQL the output parameter was INT... in SSIS my variable mapping was NUMERIC... which caused errors while LONG worked great.|||

Hi,

I too have similar problem but the error what i am getting now is

[Execute SQL Task] Error: Executing the query "exec spm_Utopia_FinanceUsage_Create_MonthlyTable ?,? OUTPUT " failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

The parametrs used are user:tableName input varchar 0,user:filegroupName input varchar 1,user:return_value output Long 2

sp returns a integer value so i am using long.

I tried using returnvalue instead of output still no use.

I tried the ADO.net connection it works fine but since i am using oledb connection i want to keep it through out.

|||Hmmm I have not seen that error. How are you "returning" the value - with a output variable or just returning it from the sproc?. That determines if you use the ? OUTPUT (if you return it via variable) or result set (for example if you select/print etc the result in the sp)

Problems creating Error File when using Bulk Insert or BCP from xp_cmdshell.

BCP thru xp_cmdshell from stored procedure:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE

EXEC xp_cmdshell 'bcp database.dbo.table in c:\scheduled.csv -S SERVER\SQLEXPRESS -T -t, -r\n -c -e "error.txt"';

This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work.

=================================================================

SQLState = HY000, NativeError = 0

Error = [Microsoft][SQL Native Client]Unable to open BCP error-file

=================================================================

Error message when using BULK INSERT as follows:

BULK INSERT database.dbo.table from 'c:\unscheduled.csv' with

(FIELDTERMINATOR = ',', ERRORFILE = 'c:\error.txt');

Returns the following error message:

=================================================================

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt" could not be opened. Operating system error code 80(The file exists.).

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.).

=================================================================

The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks.

EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2

I had this error too. It looks like a bug as BULK INSERT works fine without the -ERRORFILE option. It seems that the execution of the BULK INSERT first creates the file without closing it which results in the error message afterwards. As Workaround just don't use the option

Nobsay

Problems creating a stored procedure in Management Studio

Hi,

I am probably going to sound really stupid but I have recently installed SQL Server Management Studio and been trying to figure out how to create a stored procedure in it. I have created a database called 'BTL_Dictionary' and that works fine. In the Object Explorer window when I click on the database, I get the tree view of all the different options. In order to write the stored procedure , I clicked on Programmability --> Stored Procedures --> New Stored Procedure.

This opened up the template and i made the following changes to the template :

-- =============================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: blah

-- Create date:

-- Description: Adds the values into the DictionaryTable

-- =============================================

CREATE PROCEDURE [dbo].[AddDictionary]

-- Add the parameters for the stored procedure here

@.DictionaryID int ,

@.DictionaryName nvarchar(50),

@.DictionaryType nvarchar(50)

AS

BEGIN

-- Insert statements for procedure here

INSERT INTO DictionaryTable

(

DictionaryID,

DictionaryName,

DictionaryType

)

VALUES

(

@.DictionaryID ,

@.DictionaryName ,

@.DictionaryType

)

END

GO

-- =============================================

I saved the stored procedure, but when I refresh the database, I cannot see the stored procedure there. I trired re-opening the query and it indicates that the procedure belongs to the 'master' database instead of the 'BTL_Dictionary' database (its prefixed 'master.AddDictionary' instead of ''BTL_Dictionary.AddDictionary')

Can anyone tell me whats is is that I am doing wrong and how I can get rid of this .

Thanks

When you click "New Stored Procedure" MS will open a new query window with the PROC template in it. Just check that the database context is set to your database and not the master database. Or add a "USE" statement before the first statement in the template to ensure that the context is correct.

|||Thanks a lot for , that resolved the problem.

Problems Connecting to Database

I am having a hard time getting my registration form to connect to my database via a stored procedure. Any help would be appreciated. The code is listed below

protectedvoid submit_Click(object sender,EventArgs e)

{

SqlConnection connection =

newSqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);

SqlCommand command =

newSqlCommand("GE_sp_INSERT_USER", connection);

command.Parameters.Add("@.FirstName",SqlDbType.NVarChar).Value = txtFirstName.Text;

command.Parameters.Add("@.LastName",SqlDbType.NVarChar).Value = txtLastName.Text;

command.Parameters.Add("@.UserName",SqlDbType.NVarChar).Value = txtUserName.Text;

command.Parameters.Add("@.Password",SqlDbType.NVarChar).Value = txtPassword.Text;

command.Parameters.Add("@.PhoneNumber",SqlDbType.NVarChar).Value = txtPhoneNumber.Text;

command.Parameters.Add("@.EmailAddress",SqlDbType.NVarChar).Value = txtEmailAddress.Text;

command.Parameters.Add("@.Address",SqlDbType.NVarChar).Value = txtAddress.Text;

command.Parameters.Add("@.City",SqlDbType.NVarChar).Value = txtCity.Text;

command.Parameters.Add("@.State",SqlDbType.NVarChar).Value = txtState.Text;command.Parameters.Add("@.ZipCode",SqlDbType.NVarChar).Value = txtZipCode.Text;

connection.Open();

command.ExecuteNonQuery();

connection.Dispose();

}

ALTER PROCEDUREdbo.GE_sp_INSERT_USER

(

@.FirstNamenvarchar(50),

@.LastNamenvarchar(50),

@.UserNamenvarchar(50),

@.Passwordnvarchar(50),

@.PhoneNumbernvarchar(50),

@.EmailAddressnvarchar(50),

@.Addressnvarchar(50),

@.Citynvarchar(50),

@.Statenvarchar(50),@.ZipCodenvarchar(50)

)

AS

INSERT INTOUSERS

(FirstName, LastName, UserName, Password, PhoneNumber, EmailAddress, Address, City, State, ZipCode)

VALUES

(@.FirstName, @.LastName, @.UserName, @.Password, @.PhoneNumber, @.EmailAddress, @.Address, @.City, @.State, @.ZipCode)

RETURN

You say you are having a hard time "connecting" to your database. What exactly is the problem you're having? Are you getting an error?

|||

You need to set the CommandType:

command.CommandType =CommandType.StoredProcedure;

|||

I have added the code below to my code - I placed it right below my sqlCommand. - it still does not work. the error says it cannot find the stored procedure. The webconfig looks correct.

SqlCommand command =newSqlCommand("GE_sp_INSERT_USER", connection);

command.CommandType =CommandType.StoredProcedure;

thanks for your help|||

That error message has meaning.

Try this:

SqlCommand command =newSqlCommand("dbo.GE_sp_INSERT_USER", connection);

|||

That did not work either. Below is my webconfig - I may be missing something easy.

<connectionStrings>

<addname="myConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\gevjen.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

</connectionStrings>

thanks again for sticking with this to help me out...

|||

A few things

1) Is this connection string working somewhere else in your application?

2) If you connect to your db manually and execute the command do you get any result?

|||

hey i am too getting a hard time in making connection to sqlserver,like i have made a connection object and its working but when i reach the open function nothing works,i had been working over this since last 4 days!please guide me how to do it!

|||The database works elsewhere and the stored procedure works as well. I am missing something easy I believe within the connection or webconfig.|||

gevjen:

The database works elsewhere and the stored procedure works as well. I am missing something easy I believe within the connection or webconfig.

Could you copy and paste the actual error message, and the show the line that causes it?

|||

kamna:

hey i am too getting a hard time in making connection to sqlserver...

This should help you:http://www.mikesdotnetting.com/Article.aspx?ArticleID=69

sql

Problems adding % sign to a number

Hi,
I have a stored procedure which outputs a number as decimal(5,1). I
would like to add a % sign to the end of this number, however when I do
this using CAST(x as VARCHAR)+'%' I get the following error:
Microsoft OLE DB Provider for SQL Server error '80040e07'
Error converting data type varchar to numeric.
The following code works:
SELECT
CASE dbo.ListProfiles.Value
WHEN 0 THEN NULL
ELSE
CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
END AS [Variance],
but when i change it to add the percentage sign (as follows) i get the
above error:
CASE dbo.ListProfiles.Value
WHEN 0 THEN NULL
ELSE
(CAST(
CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
AS VARCHAR) + '%')
END AS [Variance],
Can anyone shed any light onto this annoying problem. I have a feeling
that it may just be syntax related, but i've spent ages trying other
things without any success.
Many thanks,
ChrisChris
DECLARE @.dec AS DECIMAL(5,1)
SET @.dec=18.1
SELECT CAST(@.dec AS VARCHAR(10))+'%'
--OR
SELECT CAST(CAST((100-20)/2*100 AS DECIMAL(5,1))AS VARCHAR(10))+'%'
"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:1113220485.699029.101780@.l41g2000cwc.googlegroups.com...
> Hi,
> I have a stored procedure which outputs a number as decimal(5,1). I
> would like to add a % sign to the end of this number, however when I do
> this using CAST(x as VARCHAR)+'%' I get the following error:
> Microsoft OLE DB Provider for SQL Server error '80040e07'
> Error converting data type varchar to numeric.
> The following code works:
> SELECT
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
> END AS [Variance],
> but when i change it to add the percentage sign (as follows) i get the
> above error:
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> (CAST(
> CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
> AS VARCHAR) + '%')
> END AS [Variance],
> Can anyone shed any light onto this annoying problem. I have a feeling
> that it may just be syntax related, but i've spent ages trying other
> things without any success.
> Many thanks,
> Chris
>|||It's usually best to perform data formatting in application code rather than
in Transact-SQL. That approach is much more robust and scalable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:1113220485.699029.101780@.l41g2000cwc.googlegroups.com...
> Hi,
> I have a stored procedure which outputs a number as decimal(5,1). I
> would like to add a % sign to the end of this number, however when I do
> this using CAST(x as VARCHAR)+'%' I get the following error:
> Microsoft OLE DB Provider for SQL Server error '80040e07'
> Error converting data type varchar to numeric.
> The following code works:
> SELECT
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
> END AS [Variance],
> but when i change it to add the percentage sign (as follows) i get the
> above error:
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> (CAST(
> CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
> AS VARCHAR) + '%')
> END AS [Variance],
> Can anyone shed any light onto this annoying problem. I have a feeling
> that it may just be syntax related, but i've spent ages trying other
> things without any success.
> Many thanks,
> Chris
>|||Thanks guys,
nested casts worked a treat
Chris
*** Sent via Developersdex http://www.examnotes.net ***

Problem: Trigger and multiple updates to the table

Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.

UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20

Error I am getting is :

Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

What is the best possible way to make it work? Thank you.Figure out which trigger is blowing up, and fix it? Knowing that there is more code than we can see, I can't for the life of me figure out a way to help you find out where the problem is, much less how to fix it.

-PatP|||I guess this is the problematic trigger. Should I use cursor to handle multiple inserts/updates?

CREATE trigger t_thickupdate on dbo.sched_lot_hdr
for update
as

-- declare local variables
declare
@.lotno int,
@.charthick varchar(10),
@.decthick decimal(6,5)

-- populate local variables
set @.lotno = (select lotno from inserted)
set @.charthick = (select matcharthick from inserted)
set @.decthick = (select matthick from inserted)

-- determine which was updated: character thickness or decimal thickness
if update(matthick)

-- decimal thickness was updated
begin
set @.charthick = (select thkfrac from prod_thk_stds where thkdec = @.decthick)
update sched_lot_hdr set matcharthick = @.charthick where lotno = @.lotno
end
else

-- character thickness was updated
begin
set @.decthick = (select thkdec from prod_thk_stds where thkfrac = @.charthick)
update sched_lot_hdr set matthick = @.decthick where lotno = @.lotno
end|||A trigger act on 1 or more rows...depending on the DML

inserted and/or deleted can have more than 1 row...

You need to al;ter your thinking and think more set based...|||Also looking at it...

Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense...|||I'd suggest using:CREATE TRIGGER t_thickupdate
ON dbo.sched_lot_hdr
FOR UPDATE
AS

-- determine which was updated: character thickness or decimal thickness
IF Update(matthick) -- decimal thickness was updated
UPDATE sched_lot_hdr
SET matcharthick = s.thkfrac
FROM inserted AS i
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)
JOIN prod_thk_stds AS s
ON (s.thkdec = i.matthick)
ELSE -- character thickness was updated
update sched_lot_hdr
set matthick = s.thkdec
FROM inserted AS i
JOIN prod_thk_stds AS s
ON (s.thkfrac = i.matcharthick)
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)

RETURNNote that you need to test this up one side and down the other before you put it into production, this was a quick paste up job with no opportunity for me to test it!

-PatP|||yeah :(

This already been developed. I came to this project recently and working on some enhancements. Thanks for the help. I think I understand the problem, I will resolve it.

Update: thats fast. Thanks Pat, I will test it.|||Originally posted by Brett Kaiser
A trigger act on 1 or more rows...depending on the DML

inserted and/or deleted can have more than 1 row...

You need to al;ter your thinking and think more set based... Yea vous! I'm with you on that!
Originally posted by Brett Kaiser
Also looking at it...

Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense... Nah, that happens all the time when folks convert from what I call "unit-record" code to a database. They find ways to get the database to "clean up" data coming from disparate sources that would have required application changes when the only thing that could reach the data was their application.

-PatP

Problem writing XML files.

Hi guys,

I have a stored procedure that I will subsequently post below this
message. What the stored procedure does is, it reads some specific
tables in a database and created XML off it.

The problem comes in when the data is bigger than a few hundred/
thousand characters. The data is truncated and the XML file is not
fully made.

Now i have been reading some posts by some people and tried using TEXT/
NTEXT/ IMAGE type to write the files but they give me errors which,
again, I am adding to this email.

STORED PROC:
****************************

CREATE PROCEDURE usrp_sp_makexmlfile
@.str varchar(50),
@.templatefile varchar(255),
@.ReturnValue as image OUT

AS
SET NOCOUNT ON
DECLARE @.strVar as varchar(8000)
/*DECLARE @.ReturnValue as varchar(255)*/

Set @.strVar = 'Select * from ' + @.str + ' FOR XML AUTO'
Set @.templatefile = 'c:\template.tpl'

EXEC (@.strVar)

SELECT @.ReturnValue
GO

THIS IS WHAT I GET WHEN I USE IMAGE/NTEXT/TEXT TYPE:
************************************************** *******

An unhandled exception of type 'System.InvalidOperationException'
occurred in system.data.dll

Additional information: Parameter 2: '@.ReturnValue' of type: Byte[],
the property Size has an invalid size: 0

Also, if i try to add an integer value to the image/text/ntext like
8000 or something less than that, it tells me that the result has to be
discarded because the current process has had some error.

Can someone give me a suggestion on how to resolve this or an example
that would illustrate the solution? Thank you all in advance.

Pi.Hi

You may want to check out http://sqlxml.org/faqs.aspx?faq=29 but there may
be issues with line breaks for sp_makewebtask or look at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp

John

"Pi" <3.something@.gmail.com> wrote in message
news:1126041751.328093.220490@.f14g2000cwb.googlegr oups.com...
> Hi guys,
> I have a stored procedure that I will subsequently post below this
> message. What the stored procedure does is, it reads some specific
> tables in a database and created XML off it.
> The problem comes in when the data is bigger than a few hundred/
> thousand characters. The data is truncated and the XML file is not
> fully made.
> Now i have been reading some posts by some people and tried using TEXT/
> NTEXT/ IMAGE type to write the files but they give me errors which,
> again, I am adding to this email.
> STORED PROC:
> ****************************
> CREATE PROCEDURE usrp_sp_makexmlfile
> @.str varchar(50),
> @.templatefile varchar(255),
> @.ReturnValue as image OUT
> AS
> SET NOCOUNT ON
> DECLARE @.strVar as varchar(8000)
> /*DECLARE @.ReturnValue as varchar(255)*/
> Set @.strVar = 'Select * from ' + @.str + ' FOR XML AUTO'
> Set @.templatefile = 'c:\template.tpl'
> EXEC (@.strVar)
> SELECT @.ReturnValue
> GO
>
>
> THIS IS WHAT I GET WHEN I USE IMAGE/NTEXT/TEXT TYPE:
> ************************************************** *******
> An unhandled exception of type 'System.InvalidOperationException'
> occurred in system.data.dll
> Additional information: Parameter 2: '@.ReturnValue' of type: Byte[],
> the property Size has an invalid size: 0
>
> Also, if i try to add an integer value to the image/text/ntext like
> 8000 or something less than that, it tells me that the result has to be
> discarded because the current process has had some error.
>
> Can someone give me a suggestion on how to resolve this or an example
> that would illustrate the solution? Thank you all in advance.
> Pi.|||According to the Sybase ASE manual at :

<http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman/@.Generic__BookTextView/4429;pt=4429#X
<snip>
Restrictions on text and image columns
text and image columns cannot be used:
*As parameters to stored procedures or as values passed to
these parameters
*As local variables
</snip|||Hi

With Microsoft SQL Server text is a valid datatype for parameter.

John
"ZeldorBlat" <zeldorblat@.gmail.com> wrote in message
news:1126057842.315787.150600@.g14g2000cwa.googlegr oups.com...
> According to the Sybase ASE manual at :
> <http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman/@.Generic__BookTextView/4429;pt=4429#X>
> <snip>
> Restrictions on text and image columns
> text and image columns cannot be used:
> *As parameters to stored procedures or as values passed to
> these parameters
> *As local variables
> </snip

problem with xp_smtp_sendmail

Hi friends, i have trying to make a procedure to send an email tom my
e-mail account using xp_smtp_sendmail, but y
have a problem and i don't know why i dont receive any message. I make ping
to the server and it works but when i run this:
declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'dep.tecnico@.prmconsultores.com',
@.TO = N'dep.tecnico@.prmconsultores.com',
@.server = N'smtp.prmconsultores.com'
select RC = @.rc
go
the result of rc=1 and any message is send.
Someone can help me.
thanks in advance.
Sure, try to connect to the mailserver you are sending to. Use telnet
for this to clarify if the server accepts non-authenticated relaying
(most productional server don=B4t do this).
-Open the commandline
-type: telnet <Servername> 25
-type: helo
-type: mailfrom:<YourMailAdress>
-type: rcptto:<ReceiptientMailAdress>
-type: data
-type: <two returns>
Mail will be send OR and error message will be presented (eventually
during the steps above).
Post the error back if any.
HTH, jens Suessmeyer.
|||Thanks for help. I have tried to make telnet
telnet smtp.prmconsultores.com 25
but it returns error like this:
connection error, can't open the connection to the host to 25 port
i think that the host don't accepts non-authenticated relaying as yuo say.
thanks any way.
"Jens" <Jens@.sqlserver2005.de> escribi en el mensaje
news:1137756243.009895.16250@.g43g2000cwa.googlegro ups.com...
Sure, try to connect to the mailserver you are sending to. Use telnet
for this to clarify if the server accepts non-authenticated relaying
(most productional server dont do this).
-Open the commandline
-type: telnet <Servername> 25
-type: helo
-type: mailfrom:<YourMailAdress>
-type: rcptto:<ReceiptientMailAdress>
-type: data
-type: <two returns>
Mail will be send OR and error message will be presented (eventually
during the steps above).
Post the error back if any.
HTH, jens Suessmeyer.
|||Wait a minute, wait a minute. The attempt you made was just below the
application layer. So there might be a connection problem rather than a
authentification problem. "Denied relying" is presented when you
connect successfully to the remote computer and stated the command
above. If the server above is public you can be sure that unknown
mailservers are blocked. But a way around would be to install a virtual
SMTP server (like this one which comes with IIS) and relay the message
to a trusted mail server and then send it with credentials to a public
webserver.
HTH, jens Suessmeyer.

problem with xp_smtp_sendmail

Hi friends, i have trying to make a procedure to send an email tom my
e-mail account using xp_smtp_sendmail, but y
have a problem and i don't know why i dont receive any message. I make ping
to the server and it works but when i run this:
declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'dep.tecnico@.prmconsultores.com',
@.TO = N'dep.tecnico@.prmconsultores.com',
@.server = N'smtp.prmconsultores.com'
select RC = @.rc
go
the result of rc=1 and any message is send.
Someone can help me.
thanks in advance.Sure, try to connect to the mailserver you are sending to. Use telnet
for this to clarify if the server accepts non-authenticated relaying
(most productional server don=B4t do this).
-Open the commandline
-type: telnet <Servername> 25
-type: helo
-type: mailfrom:<YourMailAdress>
-type: rcptto:<ReceiptientMailAdress>
-type: data
-type: <two returns>
Mail will be send OR and error message will be presented (eventually
during the steps above).
Post the error back if any.
HTH, jens Suessmeyer.|||Thanks for help. I have tried to make telnet
telnet smtp.prmconsultores.com 25
but it returns error like this:
connection error, can't open the connection to the host to 25 port
i think that the host don't accepts non-authenticated relaying as yuo say.
thanks any way.
"Jens" <Jens@.sqlserver2005.de> escribi en el mensaje
news:1137756243.009895.16250@.g43g2000cwa.googlegroups.com...
Sure, try to connect to the mailserver you are sending to. Use telnet
for this to clarify if the server accepts non-authenticated relaying
(most productional server dont do this).
-Open the commandline
-type: telnet <Servername> 25
-type: helo
-type: mailfrom:<YourMailAdress>
-type: rcptto:<ReceiptientMailAdress>
-type: data
-type: <two returns>
Mail will be send OR and error message will be presented (eventually
during the steps above).
Post the error back if any.
HTH, jens Suessmeyer.|||Wait a minute, wait a minute. The attempt you made was just below the
application layer. So there might be a connection problem rather than a
authentification problem. "Denied relying" is presented when you
connect successfully to the remote computer and stated the command
above. If the server above is public you can be sure that unknown
mailservers are blocked. But a way around would be to install a virtual
SMTP server (like this one which comes with IIS) and relay the message
to a trusted mail server and then send it with credentials to a public
webserver.
HTH, jens Suessmeyer.

problem with xp_smtp_sendmail

Hi friends, i have trying to make a procedure to send an email tom my
e-mail account using xp_smtp_sendmail, but y
have a problem and i don't know why i dont receive any message. I make ping
to the server and it works but when i run this:
declare @.rc int
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = N'dep.tecnico@.prmconsultores.com',
@.TO = N'dep.tecnico@.prmconsultores.com',
@.server = N'smtp.prmconsultores.com'
select RC = @.rc
go
the result of rc=1 and any message is send.
Someone can help me.
thanks in advance.Sure, try to connect to the mailserver you are sending to. Use telnet
for this to clarify if the server accepts non-authenticated relaying
(most productional server don=B4t do this).
-Open the commandline
-type: telnet <Servername> 25
-type: helo
-type: mailfrom:<YourMailAdress>
-type: rcptto:<ReceiptientMailAdress>
-type: data
-type: <two returns>
Mail will be send OR and error message will be presented (eventually
during the steps above).
Post the error back if any.
HTH, jens Suessmeyer.|||Thanks for help. I have tried to make telnet
telnet smtp.prmconsultores.com 25
but it returns error like this:
connection error, can't open the connection to the host to 25 port
i think that the host don't accepts non-authenticated relaying as yuo say.
thanks any way.
"Jens" <Jens@.sqlserver2005.de> escribió en el mensaje
news:1137756243.009895.16250@.g43g2000cwa.googlegroups.com...
Sure, try to connect to the mailserver you are sending to. Use telnet
for this to clarify if the server accepts non-authenticated relaying
(most productional server don´t do this).
-Open the commandline
-type: telnet <Servername> 25
-type: helo
-type: mailfrom:<YourMailAdress>
-type: rcptto:<ReceiptientMailAdress>
-type: data
-type: <two returns>
Mail will be send OR and error message will be presented (eventually
during the steps above).
Post the error back if any.
HTH, jens Suessmeyer.|||Wait a minute, wait a minute. The attempt you made was just below the
application layer. So there might be a connection problem rather than a
authentification problem. "Denied relying" is presented when you
connect successfully to the remote computer and stated the command
above. If the server above is public you can be sure that unknown
mailservers are blocked. But a way around would be to install a virtual
SMTP server (like this one which comes with IIS) and relay the message
to a trusted mail server and then send it with credentials to a public
webserver.
HTH, jens Suessmeyer.

problem with xml as input to stored procedure

I am facing a problem while i pass xml as an intput to stored procedure.
The problem is that there are ceratin special characters which when used as a part of xml give error.Like the input which i give to my sp is :

Declare @.XMLString XML
Set @.XMLString = N'<Company CompanyName = "Hilary Group & Sons" Code = "HGS" >
</Company>'
Exec sproc_Insert_Company @.XMLString

The error which i get on execution is: Msg 9421, Level 16, State 1, Line 2
XML parsing: line 2, character 34, illegal name character..

Its being generated because of the '&' being used in CompanyName.

In my sp i m using it as : .

Insert Into Company(

CompanyName,

Code,

)

Output Inserted.CompanyId Into @.tbl

-- TurnOver,

-- NetIncome,

-- YrOfIncorporation,

SELECT

CompanyName = ParamValues.Item.value( '@.CompanyName' , 'varchar(101)'),

Code = ParamValues.Item.value( '@.Code' , 'varchar(6)'),

FROM @.XMLString.nodes('Company') AS ParamValues(Item)

Its not only this but there are other special characters which create problem like '@.' and many more...

How to resolve it?
plzz do help at the earliest...

The ampersand is a "special" character.

Here is an article about this and how to handle it: http://www.xml.com/pub/a/2001/01/31/qanda.html

|||

Thanx a lot Louis....

u saved me from a big problem and that too on time....

Thanx once again..today i understood the power of microsoft forums...its been really helpful to me..

|||

i dont know how to close a thread or mark it as answered..i dont find any tab which says that my post has been answered...

|||I done it for you.

|||Thanx

problem with xml as input to stored procedure

I am facing a problem while i pass xml as an intput to stored procedure.
The problem is that there are ceratin special characters which when used as a part of xml give error.Like the input which i give to my sp is :

Declare @.XMLString XML
Set @.XMLString = N'<Company CompanyName = "Hilary Group & Sons" Code = "HGS" >
</Company>'
Exec sproc_Insert_Company @.XMLString

The error which i get on execution is: Msg 9421, Level 16, State 1, Line 2
XML parsing: line 2, character 34, illegal name character..

Its being generated because of the '&' being used in CompanyName.

In my sp i m using it as : .

Insert Into Company(

CompanyName,

Code,

)

Output Inserted.CompanyId Into @.tbl

-- TurnOver,

-- NetIncome,

-- YrOfIncorporation,

SELECT

CompanyName = ParamValues.Item.value( '@.CompanyName' , 'varchar(101)'),

Code = ParamValues.Item.value( '@.Code' , 'varchar(6)'),

FROM @.XMLString.nodes('Company') AS ParamValues(Item)

Its not only this but there are other special characters which create problem like '@.' and many more...

How to resolve it?
plzz do help at the earliest...

The ampersand is a "special" character.

Here is an article about this and how to handle it: http://www.xml.com/pub/a/2001/01/31/qanda.html

|||

Thanx a lot Louis....

u saved me from a big problem and that too on time....

Thanx once again..today i understood the power of microsoft forums...its been really helpful to me..

|||

i dont know how to close a thread or mark it as answered..i dont find any tab which says that my post has been answered...

|||I done it for you.

|||Thanx