Exceptions calling basic CLR stored procs...

OK to get straight to the point, is it possible to use CLR stored procs from
SQLExpress instance or from an MDF external database file ?
If the answer is "no and no" then there's no need to read on, otherwise
please read on....
I have a very simple stored proc...
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetCustomer(int customerid)
{
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand("Select * from Customer
where CustomerID = " + customerid);
SqlContext.Pipe.ExecuteAndSend(command);
}
}
I have attempted to call this from within a database within SQLExpress and
also from within an MDF external database file with results as follows....
--SQLEXPRESS
CASE----
I've switched on CLR for SQLExpress by going to the SQL Server 2005 Surface
Area Configuration selecting CLR Integration and checking "Enable CLR
integration".
When I attempt to call this stored proc within a database inside my
SQLExpress instance I get...
System.Data.SqlClient.SqlException was caught
Message="A .NET Framework error occurred during execution of user defined
routine or aggregate 'GetCustomer': \r\nSystem.InvalidOperationException:
The context connection is already in
use.\r\nSystem.InvalidOperationException: \r\n at
System.Data.SqlClient.SqlInternalConnectionSmi.Activate()\r\n at
System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectio
nString
options, Object providerInfo, DbConnection owningConnection)\r\n at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOpti
ons
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)\r\n at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbCon
nection
owningConnection, DbConnectionPoolGroup poolGroup)\r\n at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)\r\n at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)\r\n at
System.Data.SqlClient.SqlConnection.Open()\r\n at
Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command)\r\n
at StoredProcedures.GetCustomer(String customerid)\r\n."
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=16
LineNumber=0
Number=6522
Procedure="GetCustomer"
Server=".\\SQLExpress"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bjec
t
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
ds, RunBehavior runBehavior, String resetOptionsString)
at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
at
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
behavior)
at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBeh
avior
behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
in
E:\DEV.NET2. 0\Test\Windows\WindowsApplication1\Windo
wsApplication1\Form1.cs:
line
35
I also get this same exception when I EXEC the proc from within a Management
Studio query window or from my test windows application.
I have no problems calling
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetCustomer(int customerid)
{
SqlContext.Pipe..Pipe.Send("Hello world!" + customerid + "\n");
}
--END SQLEXPRESS
CASE----
--STANDALONE MDF
CASE----
When I attempt to call it from a standalone MDF file I get...
System.Data.SqlClient.SqlException was caught
Message="Execution of user code in the .NET Framework is disabled. Enable
\"clr enabled\" configuration option."
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=16
LineNumber=1
Number=6263
Procedure=""
Server="\\\\.\\pipe\\13D536B9-DDF4-45\\tsql\\query"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bjec
t
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
ds, RunBehavior runBehavior, String resetOptionsString)
at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
at
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
behavior)
at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBeh
avior
behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
in
E:\DEV.NET2. 0\Test\Windows\WindowsApplication1\Windo
wsApplication1\Form1.cs:
line
37
It's pretty clear I need to switch on CLR integration for my MDF but how ?
--END STANDALONE MDF
CASE----
Has anyone else encountered these problems ?
Do you have a solution ?
Thanks in advance...
MichaelWhen you use User Instance = True, you get a new (virgin) instance of SQL
Server Express. This does not have the CLR enabled which means your
application will have to enable it (at least once).
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
news:e$eBxqjNGHA.1312@.TK2MSFTNGP09.phx.gbl...
> OK to get straight to the point, is it possible to use CLR stored procs
> from SQLExpress instance or from an MDF external database file ?
> If the answer is "no and no" then there's no need to read on, otherwise
> please read on....
> I have a very simple stored proc...
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void GetCustomer(int customerid)
> {
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand command = new SqlCommand("Select * from Customer
> where CustomerID = " + customerid);
> SqlContext.Pipe.ExecuteAndSend(command);
> }
> }
> I have attempted to call this from within a database within SQLExpress and
> also from within an MDF external database file with results as
> follows....
> --SQLEXPRESS
> CASE----
> I've switched on CLR for SQLExpress by going to the SQL Server 2005
> Surface Area Configuration selecting CLR Integration and checking "Enable
> CLR integration".
> When I attempt to call this stored proc within a database inside my
> SQLExpress instance I get...
> System.Data.SqlClient.SqlException was caught
> Message="A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetCustomer': \r\nSystem.InvalidOperationException:
> The context connection is already in
> use.\r\nSystem.InvalidOperationException: \r\n at
> System.Data.SqlClient.SqlInternalConnectionSmi.Activate()\r\n at
> System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnect
ionString
> options, Object providerInfo, DbConnection owningConnection)\r\n at
> System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOp
tions
> options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> owningConnection)\r\n at
> System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbC
onnection
> owningConnection, DbConnectionPoolGroup poolGroup)\r\n at
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection)\r\n at
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory)\r\n at
> System.Data.SqlClient.SqlConnection.Open()\r\n at
> Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command)\r\n
> at StoredProcedures.GetCustomer(String customerid)\r\n."
> Source=".Net SqlClient Data Provider"
> ErrorCode=-2146232060
> Class=16
> LineNumber=0
> Number=6522
> Procedure="GetCustomer"
> Server=".\\SQLExpress"
> State=1
> StackTrace:
> at System.Data.SqlClient.SqlConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bj
ect
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at
> System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
> DbAsyncResult result)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method)
> at
> System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
> behavior)
> at
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandB
ehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable, IDbCommand command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
> in
> E:\DEV.NET2. 0\Test\Windows\WindowsApplication1\Windo
wsApplication1\Form1.c
s:line
> 35
>
> I also get this same exception when I EXEC the proc from within a
> Management Studio query window or from my test windows application.
> I have no problems calling
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void GetCustomer(int customerid)
> {
> SqlContext.Pipe..Pipe.Send("Hello world!" + customerid + "\n");
> }
>
> --END SQLEXPRESS
> CASE----
> --STANDALONE MDF
> CASE----
> When I attempt to call it from a standalone MDF file I get...
> System.Data.SqlClient.SqlException was caught
> Message="Execution of user code in the .NET Framework is disabled. Enable
> \"clr enabled\" configuration option."
> Source=".Net SqlClient Data Provider"
> ErrorCode=-2146232060
> Class=16
> LineNumber=1
> Number=6263
> Procedure=""
> Server="\\\\.\\pipe\\13D536B9-DDF4-45\\tsql\\query"
> State=1
> StackTrace:
> at System.Data.SqlClient.SqlConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bj
ect
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at
> System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
> DbAsyncResult result)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method)
> at
> System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
> behavior)
> at
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandB
ehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable, IDbCommand command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
> in
> E:\DEV.NET2. 0\Test\Windows\WindowsApplication1\Windo
wsApplication1\Form1.c
s:line
> 37
>
> It's pretty clear I need to switch on CLR integration for my MDF but how ?
> --END STANDALONE MDF
> CASE----
> Has anyone else encountered these problems ?
> Do you have a solution ?
> Thanks in advance...
> Michael
>
>|||Try setting the Command object's connection property before ExecuteAndSend:
command.Connection = connection;
Hope this helps.
Dan Guzman
SQL Server MVP
"Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
news:e$eBxqjNGHA.1312@.TK2MSFTNGP09.phx.gbl...
> OK to get straight to the point, is it possible to use CLR stored procs
> from SQLExpress instance or from an MDF external database file ?
> If the answer is "no and no" then there's no need to read on, otherwise
> please read on....
> I have a very simple stored proc...
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void GetCustomer(int customerid)
> {
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand command = new SqlCommand("Select * from Customer
> where CustomerID = " + customerid);
> SqlContext.Pipe.ExecuteAndSend(command);
> }
> }
> I have attempted to call this from within a database within SQLExpress and
> also from within an MDF external database file with results as
> follows....
> --SQLEXPRESS
> CASE----
> I've switched on CLR for SQLExpress by going to the SQL Server 2005
> Surface Area Configuration selecting CLR Integration and checking "Enable
> CLR integration".
> When I attempt to call this stored proc within a database inside my
> SQLExpress instance I get...
> System.Data.SqlClient.SqlException was caught
> Message="A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetCustomer': \r\nSystem.InvalidOperationException:
> The context connection is already in
> use.\r\nSystem.InvalidOperationException: \r\n at
> System.Data.SqlClient.SqlInternalConnectionSmi.Activate()\r\n at
> System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnect
ionString
> options, Object providerInfo, DbConnection owningConnection)\r\n at
> System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOp
tions
> options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> owningConnection)\r\n at
> System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbC
onnection
> owningConnection, DbConnectionPoolGroup poolGroup)\r\n at
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection)\r\n at
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory)\r\n at
> System.Data.SqlClient.SqlConnection.Open()\r\n at
> Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command)\r\n
> at StoredProcedures.GetCustomer(String customerid)\r\n."
> Source=".Net SqlClient Data Provider"
> ErrorCode=-2146232060
> Class=16
> LineNumber=0
> Number=6522
> Procedure="GetCustomer"
> Server=".\\SQLExpress"
> State=1
> StackTrace:
> at System.Data.SqlClient.SqlConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bj
ect
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at
> System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
> DbAsyncResult result)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method)
> at
> System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
> behavior)
> at
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandB
ehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable, IDbCommand command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
> in
> E:\DEV.NET2. 0\Test\Windows\WindowsApplication1\Windo
wsApplication1\Form1.c
s:line
> 35
>
> I also get this same exception when I EXEC the proc from within a
> Management Studio query window or from my test windows application.
> I have no problems calling
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void GetCustomer(int customerid)
> {
> SqlContext.Pipe..Pipe.Send("Hello world!" + customerid + "\n");
> }
>
> --END SQLEXPRESS
> CASE----
> --STANDALONE MDF
> CASE----
> When I attempt to call it from a standalone MDF file I get...
> System.Data.SqlClient.SqlException was caught
> Message="Execution of user code in the .NET Framework is disabled. Enable
> \"clr enabled\" configuration option."
> Source=".Net SqlClient Data Provider"
> ErrorCode=-2146232060
> Class=16
> LineNumber=1
> Number=6263
> Procedure=""
> Server="\\\\.\\pipe\\13D536B9-DDF4-45\\tsql\\query"
> State=1
> StackTrace:
> at System.Data.SqlClient.SqlConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bj
ect
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at
> System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
> DbAsyncResult result)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method)
> at
> System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
> behavior)
> at
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandB
ehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable, IDbCommand command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
> in
> E:\DEV.NET2. 0\Test\Windows\WindowsApplication1\Windo
wsApplication1\Form1.c
s:line
> 37
>
> It's pretty clear I need to switch on CLR integration for my MDF but how ?
> --END STANDALONE MDF
> CASE----
> Has anyone else encountered these problems ?
> Do you have a solution ?
> Thanks in advance...
> Michael
>
>|||Thanks...
This fixed the prob with using SQLExpress ... I did have a break point in
the stored proc, I've only just realised it wasn't hitting it for some
reason but thats another issue.
I'm still at a loss though how I enable the CLR for an MDF file.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23pyO4GkNGHA.536@.TK2MSFTNGP09.phx.gbl...
> Try setting the Command object's connection property before
> ExecuteAndSend:
> command.Connection = connection;
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
> news:e$eBxqjNGHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||Hey thanks for the reply...
I did have CLR enabled for SQLExpress. I don't know of anyway to turn it on
for an MDF. I know you can set it in code for SQLExpress using...
EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
EXEC sp_configure 'show advanced options' , '1';
go
But correct me if I'm mistaken but these system procs rnt available in an
MDF. So does this mean T-SQL only for MDF database's (not hosted by an
instance of SQLServer) ?
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:eQQPd9jNGHA.3360@.TK2MSFTNGP15.phx.gbl...
> When you use User Instance = True, you get a new (virgin) instance of SQL
> Server Express. This does not have the CLR enabled which means your
> application will have to enable it (at least once).
> hth
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> "Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
> news:e$eBxqjNGHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||> I'm still at a loss though how I enable the CLR for an MDF file.
You need to enable CLR at the *instance* level, not the database level. If
you are using a user instance as Bill suggested, you'll need to enable it
during initialization of your app. This is because SQL Server Express
creates a new instance from the SQL Express template files each time you
connect with 'User Instance=true'. After connecting:
SqlCommand command = new SqlCommand(
"EXEC sp_configure 'clr enabled', 1 RECONFIGURE",
connection);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
news:Ogxd3YoNGHA.3864@.TK2MSFTNGP10.phx.gbl...
> Thanks...
> This fixed the prob with using SQLExpress ... I did have a break point in
> the stored proc, I've only just realised it wasn't hitting it for some
> reason but thats another issue.
> I'm still at a loss though how I enable the CLR for an MDF file.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23pyO4GkNGHA.536@.TK2MSFTNGP09.phx.gbl...
>|||Thanks...
This did work. I must say I find it quite strange this needs to be done in
code, when it's really a one off administrative setting.
I did actually try to call this stored proc from a query window within
VS.NET. It seems whenever I use EXEC from a query window in VS.NET (go to
server explorer, right click a Data Connection select "New Query") I get an
error "The EXEC SQL construct or statement is not supported.". Strangely
enough it does seem to continue on and call the stored proc anyhow, results
do appear for the proc. In the case of sp_configure however it does seem to
have failed... I mistakenly mistook this failure as an indication that
this system proc wasn't actually available within the "User Instance"
database file.
As you might tell I'm new to this, I'm finding that the errors and behavior
of the tools, don't seem to be quite as intuitive as they were with VS
2002/2003...
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u5PGWruNGHA.524@.TK2MSFTNGP09.phx.gbl...
> You need to enable CLR at the *instance* level, not the database level.
> If you are using a user instance as Bill suggested, you'll need to enable
> it during initialization of your app. This is because SQL Server Express
> creates a new instance from the SQL Express template files each time you
> connect with 'User Instance=true'. After connecting:
> SqlCommand command = new SqlCommand(
> "EXEC sp_configure 'clr enabled', 1 RECONFIGURE",
> connection);
> command.ExecuteNonQuery();
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
> news:Ogxd3YoNGHA.3864@.TK2MSFTNGP10.phx.gbl...
>|||You're right. It is a bit goofy (that's a technical term I picked up in
Orlando). The new feature (User Instances) has a few wrinkles that they're
working out. The fact that you're allowed to do this at all (it's an admin
task) is testament to the way the user instance of SS is created and owned.
The other question you (and everyone) need to ask is do I have a problem
that MUST be solved by a CLR executable. There are few good reasons to use
them... just be aware of these issues before going crazy with the new shiny
feature.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
news:%23y4Fm7uNGHA.428@.tk2msftngp13.phx.gbl...
> Thanks...
> This did work. I must say I find it quite strange this needs to be done
> in code, when it's really a one off administrative setting.
> I did actually try to call this stored proc from a query window within
> VS.NET. It seems whenever I use EXEC from a query window in VS.NET (go to
> server explorer, right click a Data Connection select "New Query") I get
> an error "The EXEC SQL construct or statement is not supported.".
> Strangely enough it does seem to continue on and call the stored proc
> anyhow, results do appear for the proc. In the case of sp_configure
> however it does seem to have failed... I mistakenly mistook this failure
> as an indication that this system proc wasn't actually available within
> the "User Instance" database file.
> As you might tell I'm new to this, I'm finding that the errors and
> behavior of the tools, don't seem to be quite as intuitive as they were
> with VS 2002/2003...
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u5PGWruNGHA.524@.TK2MSFTNGP09.phx.gbl...
>

Exception while calling the Webservice from CLR

Hi,

I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.

At the final execution i am getting some security exception

The following is the exception I am getting….

CREATE PROCEDURE GetProductVendorDetails(@.ProductID int)

AS

EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService

GO

EXECUTE GetProductVendorDetails 2

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidOperationException: There is an error in XML document (1, 281). > System.Security.SecurityException: That assembly does not allow partially trusted callers.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()

at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

System.InvalidOperationException:

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)

at StoredProcedures.CallWebService(Int32 ProductID)

.

My Web Service Method ….

[WebMethod]

private void GetProductDetails(int ProductID)

{

// String ProductVendorDetail="";

//Set the connection string for the database

string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";

//Create Connection and open it

SqlConnection conn = new SqlConnection(connectionstring);

conn.Open();

//Create the command object

SqlCommand comm = new SqlCommand();

comm.Connection = conn;

comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"

+ " FROM Production.Product P "

+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "

+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "

+ " WHERE P.ProductID =" + ProductID.ToString();

SqlDataReader thisReader = comm.ExecuteReader();

while (thisReader.Read())

{

//Console.WriteLine(myReader["Column1"].ToString());

//Console.WriteLine(myReader["Column2"].ToString());

pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());

pvinfo.ProductName = thisReader["ProductName"].ToString();

pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();

pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();

pvinfo.VendorName = thisReader["VendorName"].ToString(); ;

}

thisReader.Close();

conn.Close();

}

[WebMethod]

public ProductVendorInfo GetProductVendorDetails(int ProductID)

{

GetProductDetails(ProductID);

ProductVendorInfo pvi = new ProductVendorInfo();

pvi.ProductID = pvinfo.ProductID;

pvi.ProductName = pvinfo.ProductName;

pvi.ProductNumber = pvinfo.ProductNumber;

pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;

pvi.VendorName = pvinfo.VendorName;

return pvi;

}

My CLR Procedure code is as follows….

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void CallWebService(int ProductID)

{

// Put your code here

ProductVendorInfoService S = new ProductVendorInfoService();

S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

pvi = S.GetProductVendorDetails(ProductID);

String PN = pvi.ProductName;

String PNum = pvi.ProductNumber;

String VANum = pvi.VendorAccountNumber;

String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"

+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

};

Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?

Thanks

Raj

Is your sgen:ed assembly strongly named, by any chance? If so, sign your clr assembly with the same key.

Actually, try and sign your sqlclr assmembly anyway.

Niels
|||

It is already have strong key name...

I added "Integrated Security=true" in the connection string then the security exception was solved..

Still i am getting the exception:

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidCastException: Unable to cast object of type 'System.Data.SqlTypes.SqlInt32' to type 'System.IConvertible'.

System.InvalidCastException:

at System.Convert.ToInt32(Object value)

at StoredProcedures.CallWebService(SqlInt32 ProductID)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(SqlInt32 ProductID)
{
// Put your code here

S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

int ProdID = System.Convert.ToInt32(ProductID);

pvi = S.GetProductVendorDetails(ProdID);

String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}

}

};

the exeception seems to be related to conversion..... :-(

|||In your call to Convert.ToInt32 you send in ProductId, which is of type SqlInt32. ToInt32 does not take SqlInt32. I don't really understand why you call ToInt32 in this scenario. Why don't you just do:

int ProdId = ProductId.Value;

All SqlTypes do have a Value property which gives you back the underlying CLR type. Just make sure that ProductId is not NULL before you do this.

Niels
|||

hi,

The following is the CLR code and i was able to compile and create the sp from the assembly

CREATE PROCEDURE GetProductSuppliersDetails(@.Product int)

AS

EXTERNAL NAME GetProductSupplierAssembly.StoredProcedures.GetSuppliers

GO

and on execution

EXECUTE GetProductSuppliersDetails 1

i am getting the following exception

Msg 6522, Level 16, State 1, Procedure GetProductSuppliersDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductSuppliersDetails":

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at StoredProcedures.GetSuppliers(Int32 ProductID)

.

Is there any thing wrong in the code ....

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using TestProject.ProductSupplier;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service S = new Service();

S.UseDefaultCredentials = true;

DataSet ds = new DataSet();

ds = S.GetProductSupplier(ProductID);

DataTable dt = new DataTable();

dt = ds.Tables["ProductSuppliers"];

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

foreach (DataRow row in dt.Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProdcutID,ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('";

int cCount = 0;

foreach (DataColumn col in dt.Columns)

{

if (dt.Columns.Count < cCount)

{

query += row[col.ColumnName].ToString() + ",";

}

else

{

query += row[col.ColumnName].ToString() + "')";

}

cCount++;

}

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

};

Thanks in Advance...

|||Well, that error message is really hard to interpret, can't you debug into the CLR method and see where the exception happens? Alternatively, you could refactor the code into a console application and just check and see what happens.

I would look closer at where you assign the data table to a table from the dataset, are you sure there exists a table in the DataSet called "ProductSuppliers"?

Niels

|||

Issue solved . I got it corrected.

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service objService = new Service();

objService.UseDefaultCredentials = true;

DataSet dsProdSupply = objService.GetProductSupplier(ProductID);

//using (SqlConnection cn = new SqlConnection("Server=PC013584;Database=NorthWing;User=Raj;Password=password;Integrated Security=SSPI"))

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

if (dsProdSupply != null)

{

if (dsProdSupply.Tables[0] != null)

{

foreach (DataRow drProdSupply in dsProdSupply.Tables[0].Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('" + drProdSupply["ProductName"].ToString() + "','" + drProdSupply["CompanyName"].ToString() + "','" + drProdSupply["ContactName"].ToString() + "','" + drProdSupply["ContactTitle"].ToString() + "','" + drProdSupply["Address"].ToString() + "','" + drProdSupply["City"].ToString() + "')";

/*

int cCount = 0;

foreach (DataColumn dcProdSupply in dsProdSupply.Tables[0].Columns)

{

if (dsProdSupply.Tables[0].Columns.Count < cCount)

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + ",";

}

else

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + "')";

}

cCount++;

}* */

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

}

}

Thanks To Karthik Who helped me in correcting the issue...

Exception while calling the Webservice from CLR

Hi,

I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.

At the final execution i am getting some security exception

The following is the exception I am getting….

CREATE PROCEDURE GetProductVendorDetails(@.ProductID int)

AS

EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService

GO

EXECUTE GetProductVendorDetails 2

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidOperationException: There is an error in XML document (1, 281). > System.Security.SecurityException: That assembly does not allow partially trusted callers.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()

at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

System.InvalidOperationException:

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)

at StoredProcedures.CallWebService(Int32 ProductID)

.

My Web Service Method ….

[WebMethod]

private void GetProductDetails(int ProductID)

{

// String ProductVendorDetail="";

//Set the connection string for the database

string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";

//Create Connection and open it

SqlConnection conn = new SqlConnection(connectionstring);

conn.Open();

//Create the command object

SqlCommand comm = new SqlCommand();

comm.Connection = conn;

comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"

+ " FROM Production.Product P "

+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "

+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "

+ " WHERE P.ProductID =" + ProductID.ToString();

SqlDataReader thisReader = comm.ExecuteReader();

while (thisReader.Read())

{

//Console.WriteLine(myReader["Column1"].ToString());

//Console.WriteLine(myReader["Column2"].ToString());

pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());

pvinfo.ProductName = thisReader["ProductName"].ToString();

pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();

pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();

pvinfo.VendorName = thisReader["VendorName"].ToString(); ;

}

thisReader.Close();

conn.Close();

}

[WebMethod]

public ProductVendorInfo GetProductVendorDetails(int ProductID)

{

GetProductDetails(ProductID);

ProductVendorInfo pvi = new ProductVendorInfo();

pvi.ProductID = pvinfo.ProductID;

pvi.ProductName = pvinfo.ProductName;

pvi.ProductNumber = pvinfo.ProductNumber;

pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;

pvi.VendorName = pvinfo.VendorName;

return pvi;

}

My CLR Procedure code is as follows….

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void CallWebService(int ProductID)

{

// Put your code here

ProductVendorInfoService S = new ProductVendorInfoService();

S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

pvi = S.GetProductVendorDetails(ProductID);

String PN = pvi.ProductName;

String PNum = pvi.ProductNumber;

String VANum = pvi.VendorAccountNumber;

String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"

+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

};

Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?

Thanks

Raj

Is your sgen:ed assembly strongly named, by any chance? If so, sign your clr assembly with the same key.

Actually, try and sign your sqlclr assmembly anyway.

Niels
|||

It is already have strong key name...

I added "Integrated Security=true" in the connection string then the security exception was solved..

Still i am getting the exception:

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidCastException: Unable to cast object of type 'System.Data.SqlTypes.SqlInt32' to type 'System.IConvertible'.

System.InvalidCastException:

at System.Convert.ToInt32(Object value)

at StoredProcedures.CallWebService(SqlInt32 ProductID)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(SqlInt32 ProductID)
{
// Put your code here

S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

int ProdID = System.Convert.ToInt32(ProductID);

pvi = S.GetProductVendorDetails(ProdID);

String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}

}

};

the exeception seems to be related to conversion..... :-(

|||In your call to Convert.ToInt32 you send in ProductId, which is of type SqlInt32. ToInt32 does not take SqlInt32. I don't really understand why you call ToInt32 in this scenario. Why don't you just do:

int ProdId = ProductId.Value;

All SqlTypes do have a Value property which gives you back the underlying CLR type. Just make sure that ProductId is not NULL before you do this.

Niels
|||

hi,

The following is the CLR code and i was able to compile and create the sp from the assembly

CREATE PROCEDURE GetProductSuppliersDetails(@.Product int)

AS

EXTERNAL NAME GetProductSupplierAssembly.StoredProcedures.GetSuppliers

GO

and on execution

EXECUTE GetProductSuppliersDetails 1

i am getting the following exception

Msg 6522, Level 16, State 1, Procedure GetProductSuppliersDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductSuppliersDetails":

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at StoredProcedures.GetSuppliers(Int32 ProductID)

.

Is there any thing wrong in the code ....

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using TestProject.ProductSupplier;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service S = new Service();

S.UseDefaultCredentials = true;

DataSet ds = new DataSet();

ds = S.GetProductSupplier(ProductID);

DataTable dt = new DataTable();

dt = ds.Tables["ProductSuppliers"];

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

foreach (DataRow row in dt.Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProdcutID,ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('";

int cCount = 0;

foreach (DataColumn col in dt.Columns)

{

if (dt.Columns.Count < cCount)

{

query += row[col.ColumnName].ToString() + ",";

}

else

{

query += row[col.ColumnName].ToString() + "')";

}

cCount++;

}

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

};

Thanks in Advance...

|||Well, that error message is really hard to interpret, can't you debug into the CLR method and see where the exception happens? Alternatively, you could refactor the code into a console application and just check and see what happens.

I would look closer at where you assign the data table to a table from the dataset, are you sure there exists a table in the DataSet called "ProductSuppliers"?

Niels

|||

Issue solved . I got it corrected.

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service objService = new Service();

objService.UseDefaultCredentials = true;

DataSet dsProdSupply = objService.GetProductSupplier(ProductID);

//using (SqlConnection cn = new SqlConnection("Server=PC013584;Database=NorthWing;User=Raj;Password=password;Integrated Security=SSPI"))

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

if (dsProdSupply != null)

{

if (dsProdSupply.Tables[0] != null)

{

foreach (DataRow drProdSupply in dsProdSupply.Tables[0].Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('" + drProdSupply["ProductName"].ToString() + "','" + drProdSupply["CompanyName"].ToString() + "','" + drProdSupply["ContactName"].ToString() + "','" + drProdSupply["ContactTitle"].ToString() + "','" + drProdSupply["Address"].ToString() + "','" + drProdSupply["City"].ToString() + "')";

/*

int cCount = 0;

foreach (DataColumn dcProdSupply in dsProdSupply.Tables[0].Columns)

{

if (dsProdSupply.Tables[0].Columns.Count < cCount)

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + ",";

}

else

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + "')";

}

cCount++;

}* */

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

}

}

Thanks To Karthik Who helped me in correcting the issue...

Exception handling

it gives error while calling a sql stored procedure as "INPUT STRING WAS NOT IN A CORRECT FORMAT". I am providing the code here.

publicvoid get_issid(string cse_email,string tech_email,string subject,string issue_details,string response,string solv_date,outint issid)

{

// Establish Connection

SqlConnection oConnection = GetConnection();

// build the command

SqlCommand oCommand =newSqlCommand("get_issid", oConnection);

oCommand.CommandType =CommandType.StoredProcedure;

// Parameters

SqlParameter paracse_email =newSqlParameter("@.cse_email",SqlDbType.VarChar, 50);

paracse_email.Value =cse_email;

oCommand.Parameters.Add(paracse_email);

SqlParameter paratech_email =newSqlParameter("@.tech_email",SqlDbType.VarChar,50);

paratech_email.Value = cse_email;

oCommand.Parameters.Add(paratech_email);

SqlParameter parasubject =newSqlParameter("@.subject",SqlDbType.VarChar, 50);

parasubject.Value = subject;

oCommand.Parameters.Add(parasubject);

SqlParameter paraissue_details =newSqlParameter("@.issue_details",SqlDbType.VarChar, 500);

paraissue_details.Value = issue_details;

oCommand.Parameters.Add(paraissue_details);

SqlParameter pararesponse =newSqlParameter("@.response",SqlDbType.VarChar, 500);

pararesponse.Value = response;

oCommand.Parameters.Add(pararesponse);

SqlParameter parasolv_date =newSqlParameter("@.solv_date",SqlDbType.DateTime);

parasolv_date.Value = solv_date;

oCommand.Parameters.Add(parasolv_date);

SqlParameter paraissid =newSqlParameter("@.issid",SqlDbType.Int);paraissid.Direction =ParameterDirection.Output;

oCommand.Parameters.Add(paraissid);

try

{

oConnection.Open();

oCommand.ExecuteNonQuery();

issid =int.Parse(paraissid.Value.ToString());

}

catch (Exception oException)

{

throw oException;

}

finally

{

oConnection.Close();

}

}

the stored procedure is:

create proc [dbo].[get_issid](@.tech_emailvarchar(50), @.cse_emailvarchar(50),@.subjectvarchar(50),@.issue_detailsvarchar(500),@.responsevarchar(500),@.solv_datedatetime, @.issidintoutput)

as

select @.issid=tech_response.issue_idfrom tech_response,issue_detailswhere tech_response.tech_email=@.tech_emailand tech_response.cse_email=@.cse_emailand tech_response.subject=@.subjectand tech_response.issue_details=@.issue_detailsand response=@.responseand solv_date=@.solv_dateand tech_response.issue_id=issue_details.issue_id

requested to help in this

Use ExecuteScalar or ExecuteReader .

ExecuteNonQuery can be used only on DDL statements such as Insert and Delete statements.

|||

Hi,

Thank u for your reply. but the error is coming again same as earlier.

pls help me in this regard.

your amibly,

nagireddy

Exception comes from where?

Being new to SQLServer, I may be doing something very basic wrong, but here's
my problem:
Java servlet code is calling a MSSQL stored procedure, returning this error
message:
Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
Java code as follows:
public String dbInsertPerson(Connection conn) throws Exception {
String curErrorId = "";
CallableStatement procCall = null;
String procString = "";
// Make sure no errors have occurred.
if (curErrorId.equals("")) {
try {
procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
procCall = conn.prepareCall(procString);
procCall.setString(1, this.lastName);
procCall.setString(2, this.firstName);
procCall.setString(3, this.middleName);
procCall.setString(4, this.preferredName);
procCall.setString(5, this.dateOfBirth);
procCall.setString(6, this.gender);
procCall.setString(7, this.emailAddress);
procCall.setString(8, this.highSchoolName);
procCall.setString(9, this.highSchoolGradYear);
procCall.executeUpdate();
}
catch (SQLException e) {
curErrorId = "100";
throw e;
}
catch (Exception e) {
curErrorId = "101";
throw e;
}
finally {
if (procCall != null) procCall.close();
}
} // end if
return curErrorId;
}
Stored procedure code as follows:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure insert_person
@.lastName varchar(16),
@.firstName varchar(16),
@.middleName varchar(16),
@.preferredName varchar(16),
@.dateOfBirth varchar(30),
@.gender varchar(1),
@.emailAddress varchar(25),
@.highSchoolName varchar(20),
@.highSchoolGradYear varchar(4)
AS
insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
MiddleName, PreferredName, DateOfBirth, Gender,
EmailAddress, HighSchoolName, HighSchoolGradYear)
values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
@.preferredName, convert(datetime, @.dateOfBirth), @.gender,
@.emailAddress, @.highSchoolName, @.highSchoolGradYear)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Would one of you non-newbies be so kind as to straighten me out?
I figure it's my ignorance/syntax issue causing some problem, whether in the
java
callable statement syntax or the stored procedure itself.
Also, a pointer to any documentation that might help me resolve future issues
on my own would be much appreciated.
I think you need to do "insert person" instead of "insert_person" -
the _ makes it a single unrecognized word.
- dave
On Thu, 8 Sep 2005 09:10:05 -0700, "PJ Pugh"
<msee92_spamfree@.hotmail.com> wrote:

>Being new to SQLServer, I may be doing something very basic wrong, but here's
>my problem:
>Java servlet code is calling a MSSQL stored procedure, returning this error
>message:
>Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
>Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
>Java code as follows:
>public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
>try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
>}
>catch (SQLException e) {
>curErrorId = "100";
>throw e;
>}
>catch (Exception e) {
>curErrorId = "101";
>throw e;
>}
>finally {
>if (procCall != null) procCall.close();
>}
> } // end if
> return curErrorId;
>}
>Stored procedure code as follows:
>SET QUOTED_IDENTIFIER ON
>GO
>SET ANSI_NULLS ON
>GO
>CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
>AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
>GO
>SET QUOTED_IDENTIFIER OFF
>GO
>SET ANSI_NULLS ON
>GO
>
>Would one of you non-newbies be so kind as to straighten me out?
>I figure it's my ignorance/syntax issue causing some problem, whether in the
>java
>callable statement syntax or the stored procedure itself.
>Also, a pointer to any documentation that might help me resolve future issues
>on my own would be much appreciated.
>
david@.at-at-at@.windward.dot.dot.net
Windward Reports -- http://www.WindwardReports.com
Page 2 Stage -- http://www.Page2Stage.com
Enemy Nations -- http://www.EnemyNations.com
me -- http://dave.thielen.com
Barbie Science Fair -- http://www.BarbieScienceFair.info
(yes I have lots of links)
|||PJ Pugh wrote:

> Being new to SQLServer, I may be doing something very basic wrong, but here's
> my problem:
> Java servlet code is calling a MSSQL stored procedure, returning this error
> message:
> Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
> Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
I was unable to duplicate the problem. Here's my code. I pasted yours in
and just changed the parameters to strings:
Properties props = new Properties();
Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
props.put("user", "joe");
props.put("password", "joe");
c = d.connect("jdbc:microsoft:sqlserver://joe:1433", props );
DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
String procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
CallableStatement procCall = c.prepareCall(procString);
procCall.setString(1, "this.lastName");
procCall.setString(2, "this.firstName");
procCall.setString(3, "this.middleName");
procCall.setString(4, "this.preferredName");
procCall.setString(5, "this.dateOfBirth");
procCall.setString(6, "this.gender");
procCall.setString(7, "this.emailAddress");
procCall.setString(8, "this.highSchoolName");
procCall.setString(9, "this.highSchoolGradYear");
procCall.executeUpdate();
I get what I'd expect (because I have no procedure named insert_person),
but in order to get your problem, it would have been the SQL parser
that threw an exception, which would be before the query plan was
being created:
C:\ms_driver\examples>java foo
Driver version is 2.2.0037
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Could not find stored procedure 'insert_person'.
at
com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Ljava.lang.String;Ljava.lang.String;I)Ljava.s ql.SQLException;(Unknown Source)
at
com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Ljava.sql.SQLException;II[Ljava.lang.String;Ljav a.lang.String;I)Ljava.sql.SQLException;(Unknown
Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sErrorToken()V(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReplyToken(BLcom.microsoft.jdbc.base.BaseWarnings ;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.pro cessReplyToken(BLcom.microsoft.jdbc.base.BaseWarni ngs;)Z(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.proces sReply(Lcom.microsoft.jdbc.base.BaseWarnings;)V(Un known Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatemen t.getNextResultType()I(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonTransi tionToState(I)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.postImplExec ute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.post ImplExecute(Z)V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecut e()V(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeUpdat eInternal()I(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.exec uteUpdate()I(Unknown Source)
at foo.main(foo.java:40)

> Java code as follows:
> public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
> try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
> }
> catch (SQLException e) {
> curErrorId = "100";
> throw e;
> }
> catch (Exception e) {
> curErrorId = "101";
> throw e;
> }
> finally {
> if (procCall != null) procCall.close();
> }
> } // end if
> return curErrorId;
> }
> Stored procedure code as follows:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
> AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Would one of you non-newbies be so kind as to straighten me out?
> I figure it's my ignorance/syntax issue causing some problem, whether in the
> java
> callable statement syntax or the stored procedure itself.
> Also, a pointer to any documentation that might help me resolve future issues
> on my own would be much appreciated.
>
|||PJ Pugh wrote:

> Being new to SQLServer, I may be doing something very basic wrong, but here's
> my problem:
> Java servlet code is calling a MSSQL stored procedure, returning this error
> message:
> Main catch exception java.sql.SQLException: [Microsoft][SQLServer 2000
> Driver for JDBC][SQLServer]Line 1: Incorrect syntax near 'insert_person'
In fact, not only was I unable to duplicate the problem,
here;s a program with your execute() code pasted in, that
creates the table and procedure and runs without complaint.
Joe Weinstein at BEA Systems

> Java code as follows:
> public String dbInsertPerson(Connection conn) throws Exception {
> String curErrorId = "";
> CallableStatement procCall = null;
> String procString = "";
> // Make sure no errors have occurred.
> if (curErrorId.equals("")) {
> try {
> procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
> procCall = conn.prepareCall(procString);
> procCall.setString(1, this.lastName);
> procCall.setString(2, this.firstName);
> procCall.setString(3, this.middleName);
> procCall.setString(4, this.preferredName);
> procCall.setString(5, this.dateOfBirth);
> procCall.setString(6, this.gender);
> procCall.setString(7, this.emailAddress);
> procCall.setString(8, this.highSchoolName);
> procCall.setString(9, this.highSchoolGradYear);
> procCall.executeUpdate();
> }
> catch (SQLException e) {
> curErrorId = "100";
> throw e;
> }
> catch (Exception e) {
> curErrorId = "101";
> throw e;
> }
> finally {
> if (procCall != null) procCall.close();
> }
> } // end if
> return curErrorId;
> }
> Stored procedure code as follows:
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE procedure insert_person
> @.lastName varchar(16),
> @.firstName varchar(16),
> @.middleName varchar(16),
> @.preferredName varchar(16),
> @.dateOfBirth varchar(30),
> @.gender varchar(1),
> @.emailAddress varchar(25),
> @.highSchoolName varchar(20),
> @.highSchoolGradYear varchar(4)
> AS
> insert into people (LastName, FirstName, CreationDate, LastUpdateDate,
> MiddleName, PreferredName, DateOfBirth, Gender,
> EmailAddress, HighSchoolName, HighSchoolGradYear)
> values (@.lastName, @.firstName, getdate(), getDate(), @.middleName,
> @.preferredName, convert(datetime, @.dateOfBirth), @.gender,
> @.emailAddress, @.highSchoolName, @.highSchoolGradYear)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> Would one of you non-newbies be so kind as to straighten me out?
> I figure it's my ignorance/syntax issue causing some problem, whether in the
> java
> callable statement syntax or the stored procedure itself.
> Also, a pointer to any documentation that might help me resolve future issues
> on my own would be much appreciated.
>
|||Joe -
Thanks for taking the time to look at this for me.
After granting execute permission on the sp to my user (duh), I am still
getting
the error message "(same...) Incorrect syntax near 'Call' "
As long as you believe the java looks correct, I guess I'll focus on some
other area.
Never having called an sp before from java, I wanted to validate that my
syntax and usage in that regard wasn't the issue.

> In fact, not only was I unable to duplicate the problem,
> here;s a program with your execute() code pasted in, that
> creates the table and procedure and runs without complaint.
> Joe Weinstein at BEA Systems
Did you include other code somewhere that I missed? If not, throw it out
here if you get a chance. Every little bit helps! ;-)
Thanks for your feedback - it is appreciated.
"Joe Weinstein" wrote:
[vbcol=seagreen]
>
> PJ Pugh wrote:
>
> In fact, not only was I unable to duplicate the problem,
> here;s a program with your execute() code pasted in, that
> creates the table and procedure and runs without complaint.
> Joe Weinstein at BEA Systems
>
|||David -
insert_person is the name of the stored procedure. I don't think there is any
issue with having the name of an sp contain an underscore.
Thanks for looking.
"David Thielen" wrote:

> I think you need to do "insert person" instead of "insert_person" -
> the _ makes it a single unrecognized word.
> - dave
>
> On Thu, 8 Sep 2005 09:10:05 -0700, "PJ Pugh"
> <msee92_spamfree@.hotmail.com> wrote:
>
> david@.at-at-at@.windward.dot.dot.net
> Windward Reports -- http://www.WindwardReports.com
> Page 2 Stage -- http://www.Page2Stage.com
> Enemy Nations -- http://www.EnemyNations.com
> me -- http://dave.thielen.com
> Barbie Science Fair -- http://www.BarbieScienceFair.info
> (yes I have lots of links)
>
|||PJ Pugh wrote:

> Joe -
> Thanks for taking the time to look at this for me.
> After granting execute permission on the sp to my user (duh), I am still
> getting
> the error message "(same...) Incorrect syntax near 'Call' "
Why is it 'Call' instead of 'call'?

> As long as you believe the java looks correct, I guess I'll focus on some
> other area.
Well, try running the little program I attached, or comparing my code in it,
line-by-line to yours.

> Never having called an sp before from java, I wanted to validate that my
> syntax and usage in that regard wasn't the issue.
>
>
> Did you include other code somewhere that I missed? If not, throw it out
> here if you get a chance. Every little bit helps! ;-)
I *did* attach it to that last post, but I'll put it inline here:
import java.io.PrintStream;
import java.sql.*;
import java.util.Hashtable;
import java.util.Properties;
import java.util.*;
import java.math.*;
public class foo
{
public static void main(String args[])
throws Exception
{
Connection c = null;
try
{
Properties props = new Properties();
Driver d = new com.microsoft.jdbc.sqlserver.SQLServerDriver();
props.put("user", "joe");
props.put("password", "joe");
c = d.connect("jdbc:microsoft:sqlserver://joe:1433", props );
DatabaseMetaData dd = c.getMetaData();
System.out.println("Driver version is " + dd.getDriverVersion() );
Statement s = c.createStatement();
try{s.executeUpdate("drop proc insert_person");} catch (Exception ignore){}
try{s.executeUpdate("drop table people");} catch (Exception ignore){}
s.executeUpdate("create table people "
+ "(LastName varchar(30), FirstName varchar(30), CreationDate datetime, LastUpdateDate datetime, "
+ "MiddleName varchar(30), PreferredName varchar(30), DateOfBirth varchar(30), Gender varchar(30), "
+ "EmailAddress varchar(30), HighSchoolName varchar(30), HighSchoolGradYear varchar(30)) ");
s.executeUpdate("create proc insert_person "
+ " @.lastName varchar(30), "
+ " @.firstName varchar(30), "
+ " @.middleName varchar(30), "
+ " @.preferredName varchar(30), "
+ " @.dateOfBirth varchar(30), "
+ " @.gender varchar(30), "
+ " @.emailAddress varchar(30), "
+ " @.highSchoolName varchar(30), "
+ " @.highSchoolGradYear varchar(30) "
+ "AS "
+ " insert into people (LastName, FirstName, CreationDate, LastUpdateDate, "
+ " MiddleName, PreferredName, DateOfBirth, Gender, "
+ " EmailAddress, HighSchoolName, HighSchoolGradYear) "
+ " values (@.lastName, @.firstName, getdate(), getdate(), @.middleName, "
+ " @.preferredName, convert(datetime, @.dateOfBirth), @.gender, "
+ " @.emailAddress, @.highSchoolName, @.highSchoolGradYear) " );
String procString = "{call insert_person(?,?,?,?,?,?,?,?,? )}";
CallableStatement procCall = c.prepareCall(procString);
procCall.setString(1, "this.lastName");
procCall.setString(2, "this.firstName");
procCall.setString(3, "this.middleName");
procCall.setString(4, "this.preferredName");
procCall.setString(5, "11/11/1992 20:20:20");
procCall.setString(6, "this.gender");
procCall.setString(7, "this.emailAddress");
procCall.setString(8, "this.highSchoolName");
procCall.setString(9, "this.highSchoolGradYear");
procCall.executeUpdate();
}
catch(Exception exception1)
{
exception1.printStackTrace();
}
finally
{
if (c != null) try {c.close();} catch (Exception ignore){}
}
}
}
[vbcol=seagreen]
> Thanks for your feedback - it is appreciated.
> "Joe Weinstein" wrote:
>