Failed to open malformed assembly 'MyAssembly' with HRESULT 0x80040154

Hello,

I wrote SQL Stored Procedures in C# and I am trying to register the asembly with SQL Server 2005 (Express Edition) using CREATE ASSEMBLY statment. I get this following error.

"Failed to open malformed assembly 'MyAssembly' with HRESULT 0x80040154"

Does anyone know how to solve this problem? It is driving me nuts for the last few days. I did enable CLR for SQL Server and also tried re-install SQL Server, but still I get this error.

Thanks in Advance,

Ravi

If this is an OLE/COM Stored Procedure, you need to create it using sp_OaCreate, rather than Create Assembly. Create Assembly is only for pure CLR based stored procedures. Also, using sp_OaCreate to load CLR based code in SQL Server is not supported: http://support.microsoft.com/kb/322884, so it is recommended that you transition your SP to a CLR based SP that does not rely on COM.

Steven

|||

Hi Steven,

Thanks for the reply. Its is a pure .NET Managed code assembly. It only does simple database operations, I didnt even have any new assemblies referenced also other than the ones Visual Studio adds by default ex: System, System.Data. The same assembly works in a differenct computer.

Ravi

|||

Hi Ravi,

Do you get the same error message when creating a different assembly? Perhaps, could you try one of the CLR samples from the BOL samples available here and let me know what happens? http://www.codeplex.com/MSFTEngProdSamples

Thanks,

Steven

|||

Yes, I get the same error for any assembly I create, I will try these samples and let you know.

Thanks,

Ravi

Failed to manage image field

In my simple table in MSDE 2000A, I have an image field. I have enabled 'text in row' using the stored procedure 'sp_tableoption'.

How do I get jpeg images in the table, and how do I read them?

Now, I ask a naive question. Has anybody ever captured an image from the file system, and saved it in SQL 2000 (or MSDE 2000A, which I am using for development)?

If you don't save image direct to database, how do you manage yor images stored seperate from the database (i.e on the filesystem)

For the last two days, I have searched the net and I regret to say I am not making any headway. Lots of stuff on the net have not been very useful.

Please help. How did you do yours? If it matters, I am using VB .NET 2003 front-end. Let me know whatever method you used.

I am desperate.

|||

Finally done!

I used the MemoryStream conversion to Byte then saved to table. To retrieve, I used the converse.

Failed Second updat in dbo.aspnet_Membership?

hello,

I would to update the email indbo.aspnet_Membership and[dbo].[WebMaster].

I write a stored procedure that contain 2 update one for[dbo].[WebMaster] and another for dbo.aspnet_Membership

the update in WebMaster table succes but in the dbo.aspnet_Membersh failed.

No synatxe error in stored procedure.

Can any one help me ?

Amer Azzaz

Did you get any error? Can you post your script to create the stored procedure ?

Fail to get Return_Value in stored procedure NullValueReference

I have a stored procedure that insert into a table and return a value indicating if the insert really occured.
If the a insert occurs then it returns the identity of that insert.

I have slimmed down my code here, so you can see.
My problem is that a NullValueReference exceptions occurs when trying to reference the returnvalue.
Is it not possible to get a return value along with a recordset ?

publicint RegisterCustomer(Customer customer)

{

SqlCommand myCommand =newSqlCommand("ins_RegistrerKunde", myConnection);

myCommand.CommandType =CommandType.StoredProcedure;

SqlParameter Epost =newSqlParameter("@.Epost",SqlDbType.VarChar, 25);

SqlParameter Passord =newSqlParameter("@.Passord",SqlDbType.VarChar, 25);

SqlParameter ReturnValue =newSqlParameter("@.RETURN_VALUE",SqlDbType.Int, 4);

ReturnValue.Direction =ParameterDirection.ReturnValue;

Epost.Value = customer.Email;

Passord.Value = customer.Password;

myCommand.Parameters.Add(ReturnValue);

myCommand.Parameters.Add(Epost);

myCommand.Parameters.Add(Passord);

myConnection.Open();

SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.SingleRow);

int isRegistered = (int)myCommand.Parameters["@.RETURN_VALUE"].Value;

myConnection.Close();

if (isRegistered == 1)

{

result.Read();

returnConvert.ToInt32(result["Kunde_ID"]);

result.Close();

}

returnConvert.ToInt32(isRegistered);

}

//Stored Procedure ins_RegistrerKunde
create procedure%PROC% (@.Epost varchar(25), @.Passord varchar(25))
as
begin
IF EXISTS( SELECT EpostFROM tbl_KundeWHERE Epost = @.Epost)
RETURN 0
ELSE
INSERT INTO tbl_Kunde( Epost, Passord)
VALUES (@.Epost, @.Passord)
SELECT @.@.IDENTITY AS Kunde_ID
RETURN 1
end
go

Output parameters and return values can be accessed only after a datareader is closed. My suggestion to you is to use an outputparameter for the @.@.IDENTITY value, and to then use an ExecuteNonQueryinstead of an ExecuteReader.

Also, instead of @.@.IDENTITY, use SCOPE_IDENTITY(), as this will returnthe the last Identity value in the current scope. SeeIdentity Crisis andUnderstanding Identity Columns for an explanation.|||

I see.

Thank you.

Fail to attach to MDF file

I tried to use sp_attach_single_file_db to attach to a MDF file which is probably corrupted in a power failure. But, whenever I run this stored procedure, I get some error message like
Change language setting to us_english
Server: msg 945, Level 14, State 2, line 1
Database 'AlbumSQL' can not be opened because some of the files could not be activated.
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'AlbumSQL'. CREATE DATABASE is aborted.
There is also a Ldf file in the same folder, but when I tried to use sp_attach_db, I get the other message
Server: Msg 5172, Level 16, State 15, Line 1
The header for file 'd:\data\albumsql_log.ldf' is not a valid database file header. The PageAudit property is incorrect.
Can you help me some hint to solve this problem? Thanks.
-Alex HuangIS it possible that there is more than one data file.. Attach single file db
only works when there is only one data file... (Based on the error message,
that kind of sounds like it might be happening.)
Otherwise it is probably time to open a call to PSS>
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alex Huang" <alexh@.ulead.com> wrote in message
news:3DBA8115-8DCC-4853-8ED5-F53B15619452@.microsoft.com...
> I tried to use sp_attach_single_file_db to attach to a MDF file which is
probably corrupted in a power failure. But, whenever I run this stored
procedure, I get some error message like
> Change language setting to us_english
> Server: msg 945, Level 14, State 2, line 1
> Database 'AlbumSQL' can not be opened because some of the files could not
be activated.
> Server: Msg 1813, Level 16, State 2, Line 1
> Could not open new database 'AlbumSQL'. CREATE DATABASE is aborted.
> There is also a Ldf file in the same folder, but when I tried to use
sp_attach_db, I get the other message
> Server: Msg 5172, Level 16, State 15, Line 1
> The header for file 'd:\data\albumsql_log.ldf' is not a valid database
file header. The PageAudit property is incorrect.
> Can you help me some hint to solve this problem? Thanks.
> -Alex Huang

Fail part of a Stored Procedures but continue processing

Here is a fun one, and I am not sure its possible.
I have 2 SQL Stored Procedures.
The first one calls the second one. All the first one does is grab the
next database name and pass that variable to the second stored
procedures. Once the second one finishes it goes back to the first one
goes to the next database and repeats the process until all the
databases are finished.
The problem is if the second Stored Procedures fails, it fails the
first.
My Question; is there a way that if the second stored procedure fails,
that the first stored procedure will still continue onto the next
database? Just because one database locks up for some reason does not
me they all will.
USE [DBAdmin]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UD_Rebuild_All_Database_Indexes]
@.maxFrag DECIMAL = 30.0, -- The maximum fragmentation to allow
@.indexSizeForRebuild INT = 100 -- Maximum index size to do a
rebuild instead of defrag
AS
DECLARE @.ExecSQLcmd varchar(1024)
DECLARE @.DBNum_to_Name int
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.DBNum_to_Name is not null
BEGIN
SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes ' +
CONVERT(VARCHAR,@.maxFrag) + ', ' +
CONVERT(VARCHAR,@.indexSizeForRebuild)
FROM master.dbo.sysdatabases
WHERE dbid =@.DBNum_to_Name
-- Print (@.ExecSQLcmd) -- For Debugging
EXEC (@.ExecSQLcmd)
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.DBNum_to_Name AND DATABASEPROPERTY(name, 'IsReadOnly') =
0
ENDHow are you running this procedure? Have you tried it in QA? Without
testing it, I believe it should continue on, as long as the caller lets it.
It does depend on the error you are getting (an error can stop the batch.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Matthew" <MKruer@.gmail.com> wrote in message
news:1138314691.791878.263020@.o13g2000cwo.googlegroups.com...
> Here is a fun one, and I am not sure its possible.
> I have 2 SQL Stored Procedures.
> The first one calls the second one. All the first one does is grab the
> next database name and pass that variable to the second stored
> procedures. Once the second one finishes it goes back to the first one
> goes to the next database and repeats the process until all the
> databases are finished.
> The problem is if the second Stored Procedures fails, it fails the
> first.
> My Question; is there a way that if the second stored procedure fails,
> that the first stored procedure will still continue onto the next
> database? Just because one database locks up for some reason does not
> me they all will.
> USE [DBAdmin]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE proc [dbo].[UD_Rebuild_All_Database_Indexes]
> @.maxFrag DECIMAL = 30.0, -- The maximum fragmentation to allow
> @.indexSizeForRebuild INT = 100 -- Maximum index size to do a
> rebuild instead of defrag
> AS
> DECLARE @.ExecSQLcmd varchar(1024)
> DECLARE @.DBNum_to_Name int
> SELECT @.DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4
> WHILE @.DBNum_to_Name is not null
> BEGIN
> SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes ' +
> CONVERT(VARCHAR,@.maxFrag) + ', ' +
> CONVERT(VARCHAR,@.indexSizeForRebuild)
> FROM master.dbo.sysdatabases
> WHERE dbid =@.DBNum_to_Name
> -- Print (@.ExecSQLcmd) -- For Debugging
> EXEC (@.ExecSQLcmd)
> SELECT @.DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > @.DBNum_to_Name AND DATABASEPROPERTY(name, 'IsReadOnly') =
> 0
> END
>|||Matthew (MKruer@.gmail.com) writes:
> The problem is if the second Stored Procedures fails, it fails the
> first.
> My Question; is there a way that if the second stored procedure fails,
> that the first stored procedure will still continue onto the next
> database? Just because one database locks up for some reason does not
> me they all will.
Error handling in SQL Server is a messy topic. Some errors cancels the
entire batch, and in SQL 2000 there is no way you can catch that from
SQL.
If you are on SQL 2005, you can use the new TRY-CATCH construct.
If you are on SQL 2000, there is actually a workaround. It's not really
useful in the general case, but for a symin script like yours, it
would be OK. Since I have this in an article on my web site, I just post
the link: http://www.sommarskog.se/error-hand...#linked-servers
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Facing problem with SSRS using Temporary tables in stored procedures

Hi,
I am facing a problem using temporary tables in SQL Server stored
procedures when using them in SQL Server Reporting Services.
It gives an error while fetching fields when creating a dataset in
report designer of MS Visual Studio.Net 2005.
Has anybody else faced the same problem?
Regards,
SunnyWhat's the specific error it's producing? You can see this if the procedure
raises errors or you do alot of SELECT...INTOs rather than declaring your
temporary tables.
-T
"sunny" <sunny.mohan@.gmail.com> wrote in message
news:1148742869.635884.186170@.38g2000cwa.googlegroups.com...
> Hi,
> I am facing a problem using temporary tables in SQL Server stored
> procedures when using them in SQL Server Reporting Services.
> It gives an error while fetching fields when creating a dataset in
> report designer of MS Visual Studio.Net 2005.
> Has anybody else faced the same problem?
> Regards,
> Sunny
>|||Temporarily modify your procedure to create a table. Use the temporary procedure to populate your dataset. After SSRS has the metadata from the table, you can drop the table and restore your original procudure.
From http://www.developmentnow.com/g/115_2006_5_0_0_763042/Facing-problem-with-SSRS-using-Temporary-tables-in-stored-procedures.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||I used temporary tables in my stored procedures just fine without going
through this monkey business. One of the biggest problems is that users do
not change their command type to stored procedure!
If they did so, then it works just fine. I.e. when you create a dataset,
specify that the command type is StoredProcedure instead of the default
which is text. Then just type in the name of the stored procedure.
The other thing is to make sure they ARE running the latest Service Pack for
RS.
=-Chris
"Jeff" <nospam@.developmentnow.com> wrote in message
news:e6b4a988-3741-40f4-8359-487d262ce05c@.developmentnow.com...
> Temporarily modify your procedure to create a table. Use the temporary
> procedure to populate your dataset. After SSRS has the metadata from the
> table, you can drop the table and restore your original procudure.
> From
> http://www.developmentnow.com/g/115_2006_5_0_0_763042/Facing-problem-with-SSRS-using-Temporary-tables-in-stored-procedures.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||I'm having the same problem and the drop-down list for Command Type is greyed out, so I'm unable to change the default command type of text. How can I activate the command type drop-down list
From http://www.developmentnow.com/g/115_2006_5_0_0_763042/Facing-problem-with-SSRS-using-Temporary-tables-in-stored-procedures.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Extremly bad performance Stored Procedures

The last few days my sqlserver executes stored procedures badly.
When I run the Query Analyzer to execute a certain stored procedure it takes more than 2 minutes to execute the procedure. When I copy the contents of de SP to Query analyser to run it as an sql statement it find's the results within a second.
This behavior dissapear's after a while and comes back randomly.
I had the problem Friday afternoon then tuesday and now again.
Between these day's my sqlserver works fine.
Can anybody please help me with this problem.Have you tried using the "with recompile" option ? Your query plan is probably based on an outdated data distribution or schema. Running the "with recompile" option will regenerate the query plan. Also, are you parameters to the stored procedure vary enough that the execution plans change ? Do a comparison in query analyzer - using show execution plan.|||I was able to elimante the problem by altering de SP.
In the SP there where more than 4 joins to the same table.
When I made a user defined function and replaced those joins with this function, it all works fine.

But one question remains. How is it possible that the query analyser didn't have problems with the joins but de SP did have?|||Did you try the recompile ? Sometimes, if your table(s) involved in the query change enough - the query plan needs to change as well. When you run it in query analyzer, the query plan is generated dynamically. For the sp, it could still be using the original query plan when you created it. That is why I suggested to run the 2 in query analyzer with the "show execution plan".

Extracting XML data stored in a LONGTEXT column

I have a table that has XML data in a LONGTEXT column. I need to extract all
the data into a table. Can anyone give me some tips on the easiest way to do
this. The data is written to the table and will be extracted in real time.
Do you mean TEXT /NTEXT?
Are you using SQL Server 2000?
What is the nature of your extraction? Do you shred parts of the document
into relational fields? Do you need to preserve the full XML?
Thanks
Michael
"Mark D" <Mark D@.discussions.microsoft.com> wrote in message
news:80CA4109-C7A4-4595-914C-CBF60DA137A9@.microsoft.com...
>I have a table that has XML data in a LONGTEXT column. I need to extract
>all
> the data into a table. Can anyone give me some tips on the easiest way to
> do
> this. The data is written to the table and will be extracted in real
> time.
|||SQL Server 2000, column is TEXT. Information is uploaded from a desktop and
manipulated on a web application. The vendor is storing the entire XML
document in this field. I need to extract each data element into a column in
another table to populate another application.
"Michael Rys [MSFT]" wrote:

> Do you mean TEXT /NTEXT?
> Are you using SQL Server 2000?
> What is the nature of your extraction? Do you shred parts of the document
> into relational fields? Do you need to preserve the full XML?
> Thanks
> Michael
> "Mark D" <Mark D@.discussions.microsoft.com> wrote in message
> news:80CA4109-C7A4-4595-914C-CBF60DA137A9@.microsoft.com...
>
>

Extracting XML data stored in a LONGTEXT column

I have a table that has XML data in a LONGTEXT column. I need to extract al
l
the data into a table. Can anyone give me some tips on the easiest way to d
o
this. The data is written to the table and will be extracted in real time.Do you mean TEXT /NTEXT?
Are you using SQL Server 2000?
What is the nature of your extraction? Do you shred parts of the document
into relational fields? Do you need to preserve the full XML?
Thanks
Michael
"Mark D" <Mark D@.discussions.microsoft.com> wrote in message
news:80CA4109-C7A4-4595-914C-CBF60DA137A9@.microsoft.com...
>I have a table that has XML data in a LONGTEXT column. I need to extract
>all
> the data into a table. Can anyone give me some tips on the easiest way to
> do
> this. The data is written to the table and will be extracted in real
> time.|||SQL Server 2000, column is TEXT. Information is uploaded from a desktop and
manipulated on a web application. The vendor is storing the entire XML
document in this field. I need to extract each data element into a column i
n
another table to populate another application.
"Michael Rys [MSFT]" wrote:

> Do you mean TEXT /NTEXT?
> Are you using SQL Server 2000?
> What is the nature of your extraction? Do you shred parts of the document
> into relational fields? Do you need to preserve the full XML?
> Thanks
> Michael
> "Mark D" <Mark D@.discussions.microsoft.com> wrote in message
> news:80CA4109-C7A4-4595-914C-CBF60DA137A9@.microsoft.com...
>
>

Extracting the sql query in the stored procedure in asp.net

Hi,

I have set programmaticaly as follows for sql dataadapter

Commandtext="name of stored proc "

commandtype="stored proc"

now I want the query in the stored proc which i will store in the string .is there any way to get the query from sp progrmmaticaly?

Swati

Usesp_helptext

Extracting SQL fields from XML Data stored in NText field

I have a interesting situation, where XML is stored inside of SQL NText
field.
Unfortunately, I need to extract that data as SQL from the XML.
Here is the data in the BillingAddress field:
<?xml version="1.0" encoding="utf-16"?>
<Address>
<ID>-1</ID>
<FirstName>Joe</FirstName>
<LastName>McLean</LastName>
<StreeLine1>360 E Columbia 48</StreeLine1>
<City>Emerson</City>
<StateName>Arkansas</StateName>
<StateCode>4</StateCode>
<PostalCode>71740</PostalCode>
<CountryName>United States</CountryName>
<CountryCode>en-US</CountryCode>
<PhoneNumber>870-547-2251</PhoneNumber>
</Address>
I need to be able to have a SQL query that returns all of those nodes
(FirstName, LastName, etc) as individual SQL fields.
Can this be done?
Thanks in advance!
Robert Johnson
Internet Web ZoneHave you looked in to using OPENXML? SQL Server Books Online has tons of
information and examples on this. Also, check out www.sqlxml.org
Anith|||I spent hours looking. That is why I posted the question. If you don't have
an answer, please don't respond.
Robert
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23DNdh8yAFHA.3376@.TK2MSFTNGP12.phx.gbl...
> Have you looked in to using OPENXML? SQL Server Books Online has tons of
> information and examples on this. Also, check out www.sqlxml.org
> --
> Anith
>|||Here is an example:
CREATE TABLE t (
keycol INT NOT NULL PRIMARY KEY,
xmlval NTEXT )
INSERT t SELECT 1,
'<Address>
<ID>-1</ID>
<FirstName>Joe</FirstName>
<LastName>McLean</LastName>
<StreeLine1>360 E Columbia 48</StreeLine1>
<City>Emerson</City>
<StateName>Arkansas</StateName>
<StateCode>4</StateCode>
<PostalCode>71740</PostalCode>
<CountryName>United States</CountryName>
<CountryCode>en-US</CountryCode>
<PhoneNumber>870-547-2251</PhoneNumber>
</Address>'
DECLARE @.xml VARCHAR(8000), @.idoc INT
SET @.xml = ( SELECT CAST( xmlval AS VARCHAR(8000 ) )
FROM t WHERE keycol = 1 )
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xml
SELECT *
FROM OPENXML ( @.idoc, '/Address', 2 )
WITH ( FirstName VARCHAR(20),
LastName VARCHAR(20) )
EXEC sp_xml_removedocument @.idoc
Anith|||There is no need to be rude. Where does it say in your original post that
you " ... spent hours looking ..."? And specifically, where does it say that
you spent hours looking at the topics in BOL about OpenXML? Or browsing
through the excellent resource at sqlxml.org?
All we know is what we see in your post. Don't expect us to be able to
somehow be aware of all the research that you've already done before posting
your question.
Ah, I see that Anith was gracious enough to overlook your rudeness and post
an example. Good for Anith.
Bob Barrows
Robert Johnson wrote:
> I spent hours looking. That is why I posted the question. If you
> don't have an answer, please don't respond.
> Robert
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23DNdh8yAFHA.3376@.TK2MSFTNGP12.phx.gbl...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

extracting results of XML stored proc to file

Hi all, I need a bit of guidance!
I have created a few queries using FOR XML AUTO, but am unsure how to extract them from the results into an XML file
out side of the sql server. is there a way I can make the stored procedure push the result to an xml file on my server? Or do
I have to use ado to extract the data from the server. I would rather push than pull!
thanks in advance!
Mike
There's no easy way to do it using T-SQL (I guess you could write an
extended Stored Proc or something, but it'd be a lot of effort.)
Your best bet is to install SQLXML 3.0
(http://www.microsoft.com/downloads/d...33a9-cf10-4e22
-8004-477098a407ac&displaylang=en) and use a script like the following
example to generate the file. You could then wrap this script up in a SQL
Agent job and execute it from within SQL Server.
Example script:
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Const adCmdStoredProc = 4
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=localhost;DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Specify the root tag
cmd.Properties("xml root") = "QueryResults"
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command
cmd.CommandText = "MyStoredProc"
cmd.CommandType = adCmdStoredProc
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
' Save the file
xmlDoc.Save("C:\Results.xml")
MsgBox("File saved")
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Mike UK" <anonymous@.discussions.microsoft.com> wrote in message
news:6DA91318-4FF8-4DF7-B27B-0CDA3A876389@.microsoft.com...
> Hi all, I need a bit of guidance!
> I have created a few queries using FOR XML AUTO, but am unsure how to
extract them from the results into an XML file
> out side of the sql server. is there a way I can make the stored procedure
push the result to an xml file on my server? Or do
> I have to use ado to extract the data from the server. I would rather push
than pull!
> thanks in advance!
> Mike
|||Thanks Graeme, I will certainly give it a go! I must admit I m a little suprised we cant export from a stored proc,
especiall as we can get the results of a FOR XML query into the results pane.
Thanks again for the script!
Mike.
|||Hi Mike,
You can export from an SP! - We're doing it via BCP (bulk copy) and XML templates. It is a bit tedious to write but it has been working very well in our production environment for the past 3 months.
We have one stored proc that contains our FOR XML statement. For the purposes of discussion, I'll call it 'record_return' (in our case, we're using FOR XML EXPLICIT but it would not make a difference using AUTO) and another stored procedure 'launch_record
_return' that calls 'record_return'.
Within launch_record_return we build a dynamic SQL statement and execute the call to BCP. In our case, we are dynamically building the full path and file name for the exported XML.
Example (from within launch_record_return):
-- create full file path for our output XML document
SET @.xmlname = '\'+@.ord+'.xml' (in this case, @.ord is the name of the file which we're generating dynamically)
SET @.finalname = '\xx'+@.ord+'.xml'
SET @.fname=@.fullDirPath+@.xmlname
SET @.fullPathAndFinalName = @.fullDirPath+@.finalname
SET @.fname=@.fullDirPath+@.xmlname
SET @.bcpCommand = 'bcp "EXEC DB_NAME..record_return ' + "'" + @.ord + "'" + '"' +" queryout "
SET @.bcpCommand = @.bcpCommand + @.fname + ' -c -t -r'
SET @.appendCommand = 'copy/b c:\yourdirectory\header.txt+'+@.fname+'+c:\yourdire ctory\footer.txt '+ @.fullPathAndFinalName
-- execute commands
EXEC master..xp_cmdshell @.bcpCommand
EXEC master..xp_cmdshell @.appendCommand
-- delete temp case file
SET @.appendCommand = ' del ' + @.fname
EXEC master..xp_cmdshell @.appendCommand
The header.txt and footer.txt files contain the head of the document because you'll find that BCP does not wrap the XML in a root element.
This code would of course need to be revised to meet your specific requirements but should provide you with some insight on how to go about generating an actual XML file from an SP. We have also setup a DTS job that runs the 'launch' procedure because the
re is a bug (http://support.microsoft.com/default...;en-us;Q275583) that occurs when executing FOR XML statements via ODBC. We found that it was easy to get around this by creating a DTS job and connecting via OLE.
Hope this helps.
Have a great weekend.
Any questions, feel free to email me.
Dee
Denise E. White
IT Developer
Legal Marketing Services
Cheshire, England
|||Hi Denise,
I thank you for this great post. One thing I wanted to note - a way to get your XML Explicit sproc to have a root element without appending text files: start your sproc with a record that from your highest level table that you know occurs only once, per
haps a transaction identifier value, or perhaps a string value like your company name and wrap it as below
SELECT
1 AS Tag,
NULL AS Parent,
TR.TransSetCntrlNmbr AS [ROOT!1!Filler!Hide],
....
|||Thanks.This is a great post.can any one post the complete
code of the procedure?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Thanks JT...I have been using this technique however, I still include a header and footer file beacause I cannot find a way using this method to have the root element contain a reference to my XSD schema.
Any thoughts?
Denise

Extracting dates from XML stored in SQL Server 2005

I have recently started storing xml files within SQL 2005. I have a problem extracting a date of format 20-02-2007 out of the XML and storing it in a field of datetime. The database is in American format (2007-02-20) and is failing when I try and insert the date. In the past when extracting the date at .Net level and passing through to SQL Server this was never a problem. I have tried to convert the date first but still fails. Is there an easy way to get around this problem or do I need to store the date as a string?

Thanks

Danny

You could use CONVERT function in you T-SQL statements with 105 style:

declare @.dt varchar(10)

set @.dt ='20-02-2007'

select convert(datetime,@.dt,105)

--RESULT

2007-02-20 00:00:00.000

|||

Yes this worked, but I had to convert to varchar before converting to datetime (cannot convert XML directly to datetime).

Thanks

Danny

extracting csv string and using in NOTIN clause

Hi,
I'm trying to pass a stored proc a list of values in a comma seperated strin
g:
("3125,3126,3129...") The values represent an integer column in a table.
Then I want to use the string to exclude the values from a SELECT as in:
CREATE PROCEDURE myProc
@.transactionIDs varchar(255)
AS
SELECT TransactionID FROM SalesTransactions
WHERE TransactionID NOT IN (@.transactionIDs)
GO
SQL Server complains that it can't cast a varchar to an int
Any suggestions on how to do this?
Can someone provide an example?
ThanksWhile someone might suggest you use dynamic SQL for this, I
wouldn't. Instead, consider either this not-so efficient solution:
select TransactionID
from SalesTransactions
where
charindex(
',' + rtrim(TransactionID) + ',',
',' + @.transactionIDs + ','
) = 0
-- be sure @.transactionIDs has no spaces
or, for greater efficiency, the more involved solution
that uses a table-valued function to convert @.transactionIDs
into a table of id values:
http://groups.google.co.uk/groups?q...wierzbicki+kass
You can then write:
select TransactionID
from SalesTransactions
where TransactionID not in (
select Item from ListTable(@.transactionIDs)
)
Steve Kass
Drew University
Opa wrote:

>Hi,
>I'm trying to pass a stored proc a list of values in a comma seperated stri
ng:
>("3125,3126,3129...") The values represent an integer column in a table.
>Then I want to use the string to exclude the values from a SELECT as in:
>CREATE PROCEDURE myProc
> @.transactionIDs varchar(255)
>AS
>SELECT TransactionID FROM SalesTransactions
>WHERE TransactionID NOT IN (@.transactionIDs)
>GO
>SQL Server complains that it can't cast a varchar to an int
>
>Any suggestions on how to do this?
>Can someone provide an example?
>Thanks
>
>
>

Extracting Binary data from SQL DB to a location on the web server

Hey all,

WE have a document management system where by Adminstrators can upload documents, once the document is uploaded the binary data is stored on in a folder on the web server. We used to stored the documents in the actaul db table, but we found that there were to many documents and it was using alot of space on db server.

So my boss has decided we are now going to upload the binary data onto the web server. Currently we are donig this with new documents which have been added or documents which are gettinguploaded when reloading, but there are many documents in the db table which have not been updated and are still embedded in the db table. So i need to figure out how to go about copying the data storewd in the db table and storing it in web servers folder location.

I've tried various things for a enitre day but im going round in circiles.

MemoryStream mStream = new MemoryStream((Byte[])dtrResults["file"]);
BinaryReader bReader = new BinaryReader(mStream);
int intFileSize = (int)mStream.Length;
Byte[] byteFile = (Byte[])dtrResults["file"];

i can get to this state but then how do i create a folder on the BinaryREader to then store the binary data of the file to the location.

BinaryReader bReader2 = new BinaryReader(File.Open(strDocFolder + strSavedFileName, FileMode.Create));
int count2 = bReader2.Read(byteFile, 0, intFileSize);

bReader2.Close();

i've also tried this but when the file gets created in the folder there is no content.

i do know that the file does contain content as ive tried this and downlaoding the file from that page acctually works

string strContentTpe = WValue.WStr(dtrResults["contenttype"]);
int intFileSize = VValue.VInt(dtrResults["filesize"]);
/ Byte[] byteFile = ((Byte[])dtrResults["file"]);
//Downloads the data correctly
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("Content-Disposition", "attachment; filename=\"" + WValue.WStr(dtrResults["docfilename"]) + "\"");
Response.AddHeader("Content-Length", WValue.WStr(intFileSize));
Response.ContentType = strContentTpe;
Response.BinaryWrite(byteFile);

I hope ive made some snese andthat someone can hlep me.

Have a nice day

Zal

i dont think u should extract all the data by a response object .....how many files will u sit & click save .......bt just retrive the byte information from the database & send it across the network using socket programming.(dont get scared..it could be a command line utility)

while using socket programing u will need to make client which would extract the data & send it ...while at the new location could use a server which would recv data & and make files out of it.

this would only be advisable if the files are more than 50000.....

if less just write a command line utility to remove the data & store as .dat files with filenames......then physically transfer the data to new location

|||

Thanks for the advice, but it seems all a bit ot scary for me.

Ive actaully found a way of doing it by reading and writing to the reader, it might not be perfect but this code will only be used once.

Zal

Extracting Attribute Values from XML Using XQuery

I am serializing a .NET object as XML and passing it as a parameter to a stored procedure. I am new to XQuery and have tried other places to find the answer to this question but have so far been unsuccessful.

How can I get the value of an attribute out of an XML document as a standard SQL Server data type? For instance, if my XML document were:

<RootNode>
<Object ID = "1234">
<AnElement>Some Other Value</AnElement>
</Object>
</RootNode>

What would be the correct XQuery to get the value of the ID attribute in the Object element?

Thanks in advance.

Kevin J Lambert

create table #xml_temp (

xml_col xml

)

insert into #xml_temp values('<RootNode>

<Object ID = "1234">

<AnElement>Some Other Value</AnElement>

</Object>

</RootNode>')

--With query() function

select xml_col.query('data(/RootNode/Object/@.ID)') from #xml_temp

--With value() function

select xml_col.value('data(/RootNode/Object/@.ID)[1]','int') from #xml_temp

Extract values from XML in a Strored Proc

I pass an xml to a Stored Proc similar to below.
How do I extaract the id and value from below XML?
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs><id>1</id><value>Costs</value><id>2</id><value>MarketPowerInfluence</value><value>IndustryStanding</value><value>SupplierRelations</value></MnemonicIDs>'
SELECT T.MnemonicIDs.value('/value', 'VARCHAR(50)'),
T.MnemonicIDs.value('/id', 'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
You probably want:
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs>
<id>1</id><value>Costs</value>
<id>2</id><value>MarketPowerInfluence</value><value>IndustryStanding</value>
<value>SupplierRelations</value>
</MnemonicIDs>'
SELECT T.MnemonicIDs.value('.', 'VARCHAR(50)'),
T.MnemonicIDs.value('(for $v in . return ../id[. << $v])[last()]',
'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs/value') AS T(MnemonicIDs)
However, if you can change the XML structure to something like:
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs>
<id>1</id><value>Costs</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>MarketPowerInfluence</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>IndustryStanding</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>SupplierRelations</value>
</MnemonicIDs>'
then the query becomes simpler:
SELECT T.MnemonicIDs.value('value[1]', 'VARCHAR(50)'),
T.MnemonicIDs.value('id[1]', 'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
Best regards
Michael
"C" <C@.discussions.microsoft.com> wrote in message
news:BCFF9712-A442-42E9-9F7F-E686F65137D0@.microsoft.com...
>I pass an xml to a Stored Proc similar to below.
> How do I extaract the id and value from below XML?
>
> DECLARE @.piMnemonicIDs xml
> SET @.piMnemonicIDs =
> '<MnemonicIDs><id>1</id><value>Costs</value><id>2</id><value>MarketPowerInfluence</value><value>IndustryStanding</value><value>SupplierRelations</value></MnemonicIDs>'
> SELECT T.MnemonicIDs.value('/value', 'VARCHAR(50)'),
> T.MnemonicIDs.value('/id', 'VARCHAR(50)')
> FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
>

Extract values from XML in a Strored Proc

I pass an xml to a Stored Proc similar to below.
How do I extaract the id and value from below XML?
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs><id>1</id><value>Costs</value><id>2</id><value>MarketPowerInfl
uence</value><value>IndustryStanding</value><value>SupplierRelations</value>
</MnemonicIDs>'
SELECT T.MnemonicIDs.value('/value', 'VARCHAR(50)'),
T.MnemonicIDs.value('/id', 'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)You probably want:
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs>
<id>1</id><value>Costs</value>
<id>2</id><value>MarketPowerInfluence</value><value>IndustryStanding</value>
<value>SupplierRelations</value>
</MnemonicIDs>'
SELECT T.MnemonicIDs.value('.', 'VARCHAR(50)'),
T.MnemonicIDs.value('(for $v in . return ../id[. << $v])[last()]',
'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs/value') AS T(MnemonicIDs)
However, if you can change the XML structure to something like:
DECLARE @.piMnemonicIDs xml
SET @.piMnemonicIDs =
'<MnemonicIDs>
<id>1</id><value>Costs</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>MarketPowerInfluence</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>IndustryStanding</value>
</MnemonicIDs>
<MnemonicIDs>
<id>2</id><value>SupplierRelations</value>
</MnemonicIDs>'
then the query becomes simpler:
SELECT T.MnemonicIDs.value('value[1]', 'VARCHAR(50)'),
T.MnemonicIDs.value('id[1]', 'VARCHAR(50)')
FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
Best regards
Michael
"C" <C@.discussions.microsoft.com> wrote in message
news:BCFF9712-A442-42E9-9F7F-E686F65137D0@.microsoft.com...
>I pass an xml to a Stored Proc similar to below.
> How do I extaract the id and value from below XML?
>
> DECLARE @.piMnemonicIDs xml
> SET @.piMnemonicIDs =
> '<MnemonicIDs><id>1</id><value>Costs</value><id>2</id><value>MarketPowerIn
fluence</value><value>IndustryStanding</value><value>SupplierRelations</valu
e></MnemonicIDs>'
> SELECT T.MnemonicIDs.value('/value', 'VARCHAR(50)'),
> T.MnemonicIDs.value('/id', 'VARCHAR(50)')
> FROM @.piMnemonicIDs.nodes('/MnemonicIDs') AS T(MnemonicIDs)
>