Showing posts with label objects. Show all posts
Showing posts with label objects. Show all posts

Tuesday, March 20, 2012

Convert XML Date in T-SQL

Our persistent objects use an internal dataset to hold their data. When I
save to SQL Server 2000, I do so in one call to the database sending the XML
representation output from the DataSet.GetXml() method and shredding the XML
values into their tables & columns via T-SQL. Everything works great, except
for converting the default XML DateTime output from the DataSet. The output
is like this: 2004-10-20T16:00:00.0000000-05:00. But the list of compatible
T-SQL formats accepted by CONVERT does not show this format (List found here
http://msdn.microsoft.com/library/de...ca-co_2f3o.asp).
The closest format I can see is Style 126: yyyy-mm-dd Thh:mm:ss.mmm. If I
pass the default DataSet XML date format to CONVERT it throws an error. What
I’m currently doing is pre-processing the XML string before sending it to the
database. I iterate through each known date field and convert the value
before sending to SQL Server. This seems unnecessary, but it also seems
unnecessary that the default ADO XML date output is by all appearances
incompatible with SQL Server’s T-SQL CONVERT function. What am I missing
here? Know any ideas?
Hello, MPalmer78!
You wrote on Thu, 21 Oct 2004 09:24:23 -0700:
[Sorry, skipped]
See KB811767.
With best regards, Alex Shirshov.

Friday, February 10, 2012

Convert COM objects to Stored Procedure

Is there a 3rd party utility program that can convert complex COM objects
and DLLs to SQL Server stored procedures?
Thanks,
I doubt it. Are you asking about SQL Server 2000 or 2005? In SQL Server 2000
stored procedures are primarily for data manipulation. Data manipulation
should generally be set-based and declarative so the most efficient approach
to implementing procedural code in Transact SQL is usually to rewrite it as
set-based SQL - not something that is ever likely to automate well. As for
calling external COM code from SQL Server 2000, your main options are to
write an extended stored procedure or to use the sp_OA procs to invoke COM
objects from your procs.
SQL Server 2005 is a different matter. You can now catalogue .NET2.0 CLR
assemblies and call them directly as stored procedures. If you can implement
your COM as CLR code then in principle you may be able to run that code in
SQL Server 2005. That's not to say that it would always be a good idea to do
so. Data manipulation stuff will generally still be best implemented in SQL.
David Portas
SQL Server MVP

Convert COM objects to Stored Procedure

Is there a 3rd party utility program that can convert complex COM objects
and DLLs to SQL Server stored procedures?
Thanks,I doubt it. Are you asking about SQL Server 2000 or 2005? In SQL Server 2000
stored procedures are primarily for data manipulation. Data manipulation
should generally be set-based and declarative so the most efficient approach
to implementing procedural code in Transact SQL is usually to rewrite it as
set-based SQL - not something that is ever likely to automate well. As for
calling external COM code from SQL Server 2000, your main options are to
write an extended stored procedure or to use the sp_OA procs to invoke COM
objects from your procs.
SQL Server 2005 is a different matter. You can now catalogue .NET2.0 CLR
assemblies and call them directly as stored procedures. If you can implement
your COM as CLR code then in principle you may be able to run that code in
SQL Server 2005. That's not to say that it would always be a good idea to do
so. Data manipulation stuff will generally still be best implemented in SQL.
David Portas
SQL Server MVP
--

Convert COM objects to Stored Procedure

Is there a 3rd party utility program that can convert complex COM objects
and DLLs to SQL Server stored procedures?
Thanks,I doubt it. Are you asking about SQL Server 2000 or 2005? In SQL Server 2000
stored procedures are primarily for data manipulation. Data manipulation
should generally be set-based and declarative so the most efficient approach
to implementing procedural code in Transact SQL is usually to rewrite it as
set-based SQL - not something that is ever likely to automate well. As for
calling external COM code from SQL Server 2000, your main options are to
write an extended stored procedure or to use the sp_OA procs to invoke COM
objects from your procs.
SQL Server 2005 is a different matter. You can now catalogue .NET2.0 CLR
assemblies and call them directly as stored procedures. If you can implement
your COM as CLR code then in principle you may be able to run that code in
SQL Server 2005. That's not to say that it would always be a good idea to do
so. Data manipulation stuff will generally still be best implemented in SQL.
--
David Portas
SQL Server MVP
--