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 execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.
I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Fail to execute store procedure in OLD DB Source in Data Flow

Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source.
I select the SQL Command in the Data Access Mode, then use:

USE [SecurityMaster]
EXEC [dbo].[smf_ListEquity]

It runs ok in the Preview, but not in the Run. Then the system returns during executing the package:

Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider.
Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4.

Please give me some helps. Thanks.I'm having the same problem. Previously the stored procedure would not even populate the column metadata in the OLEDB Source designer. But on another thread I learned that I needed to replace all references to temp tables with table variables. Now the procedure populates the OLEDB source designer with the appropriate column metadata and runs with the preview button, but I get "A rowset based on the SQL command was not returned by the OLE DB provider."|||

"A rowset based on the SQL command was not returned by the OLE DB provider."

i once encountered this error. i resolved it by recreating the data flow task.

|||

Anyone have any more information on this problem. I'm encountering the same issue. OLE DB Data Source uses sql command to run a stored procedure. I can preview, map the data, etc. But when I run it I get the "A rowset based on the SQL command was not returned by the OLE DB. "

I've tried recreating the tasks, even the entire package, and no luck yet.

|||I'm having the same problem. Anybody nows how to fix it?

thanks|||It's probably the same issue as DTS used to have, where if you are doing something that updates,deletes or inserts some rows, it will return the rowcount. For whatever reason, the command interprets the rowcount as the end of the command, and returns - so it never gets to your final select statement. If you just put "set nocount on" as like the first line of your stored procedure, it should fix it...presuming this is what is going on.|||

Here is what worked for me:

1. Setting NOCOUNT ON

2. Removing PRINT, CAST and CONVERT statements from the stored procedure

3. If u use temp tables inside the SP use SET FMTONLY OFF command

Facing Error in Select statement

I have written one procedure .in that i have put following login.but I got
the error.
Declare
@.purgecount int,
@.totcovchrg money,
@.totpaymntamt money,
@.totreimamt money
Select
@.purgecount = count(D.EventID),
@.totcovchrg = sum(CoverChrgAmt),
@.totpaymntamt = sum(PayDetail.PaymntAmt),
@.totreimamt = sum(CalcTotalReimAmt)
from DelReimb D, Reimburse R, ReimburseOut RO,
(select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
from PaymentsDetail
where isnull(InvalidPayInd,'') <> 'Y' and
PaymntInd not in ('A','B','X')
group by ClaimID,EventID) PayDetail
where
D.ClaimID = R.ClaimID and
D.EventID = R.EventID and
D.ClaimID = RO.ClaimID and
D.EventID = RO.EventID and
D.ClaimID = PayDetail.ClaimID and
D.EventID = PayDetail.EventID
Error :
A SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.
Please tell me how to over come thisHi
You are missing a comma after the ISNULL statement
(select isnull(sum(PaymntAmt),0),PaymntAmt,Claim
ID,EventID
It is always worth posting DDL and example data
http://www.aspfaq.com/etiquette.asp?id=5006 so people can post your query
into Query Analyser
John
"spc" wrote:

> I have written one procedure .in that i have put following login.but I got
> the error.
>
> Declare
> @.purgecount int,
> @.totcovchrg money,
> @.totpaymntamt money,
> @.totreimamt money
>
> Select
> @.purgecount = count(D.EventID),
> @.totcovchrg = sum(CoverChrgAmt),
> @.totpaymntamt = sum(PayDetail.PaymntAmt),
> @.totreimamt = sum(CalcTotalReimAmt)
> from DelReimb D, Reimburse R, ReimburseOut RO,
> (select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
> from PaymentsDetail
> where isnull(InvalidPayInd,'') <> 'Y' and
> PaymntInd not in ('A','B','X')
> group by ClaimID,EventID) PayDetail
> where
> D.ClaimID = R.ClaimID and
> D.EventID = R.EventID and
> D.ClaimID = RO.ClaimID and
> D.EventID = RO.EventID and
> D.ClaimID = PayDetail.ClaimID and
> D.EventID = PayDetail.EventID
>
> Error :
> A SELECT statement that assigns a value to a variable must not be combined
> with data-retrieval operations.
>
> Please tell me how to over come this
>

Facing Error in Select statement

I have written one procedure .in that i have put following login.but I got
the error.
Declare
@.purgecount int,
@.totcovchrg money,
@.totpaymntamt money,
@.totreimamt money
Select
@.purgecount = count(D.EventID),
@.totcovchrg = sum(CoverChrgAmt),
@.totpaymntamt = sum(PayDetail.PaymntAmt),
@.totreimamt = sum(CalcTotalReimAmt)
from DelReimb D, Reimburse R, ReimburseOut RO,
(select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
from PaymentsDetail
where isnull(InvalidPayInd,'') <> 'Y' and
PaymntInd not in ('A','B','X')
group by ClaimID,EventID) PayDetail
where
D.ClaimID = R.ClaimID and
D.EventID = R.EventID and
D.ClaimID = RO.ClaimID and
D.EventID = RO.EventID and
D.ClaimID = PayDetail.ClaimID and
D.EventID = PayDetail.EventID
Error :
A SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.
Please tell me how to over come thisHi
You are missing a comma after the ISNULL statement
(select isnull(sum(PaymntAmt),0),PaymntAmt,ClaimID,EventID
It is always worth posting DDL and example data
http://www.aspfaq.com/etiquette.asp?id=5006 so people can post your query
into Query Analyser
John
"spc" wrote:
> I have written one procedure .in that i have put following login.but I got
> the error.
>
> Declare
> @.purgecount int,
> @.totcovchrg money,
> @.totpaymntamt money,
> @.totreimamt money
>
> Select
> @.purgecount = count(D.EventID),
> @.totcovchrg = sum(CoverChrgAmt),
> @.totpaymntamt = sum(PayDetail.PaymntAmt),
> @.totreimamt = sum(CalcTotalReimAmt)
> from DelReimb D, Reimburse R, ReimburseOut RO,
> (select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
> from PaymentsDetail
> where isnull(InvalidPayInd,'') <> 'Y' and
> PaymntInd not in ('A','B','X')
> group by ClaimID,EventID) PayDetail
> where
> D.ClaimID = R.ClaimID and
> D.EventID = R.EventID and
> D.ClaimID = RO.ClaimID and
> D.EventID = RO.EventID and
> D.ClaimID = PayDetail.ClaimID and
> D.EventID = PayDetail.EventID
>
> Error :
> A SELECT statement that assigns a value to a variable must not be combined
> with data-retrieval operations.
>
> Please tell me how to over come this
>

Facing Error in Select statement

I have written one procedure .in that i have put following login.but I got
the error.
Declare
@.purgecount int,
@.totcovchrg money,
@.totpaymntamt money,
@.totreimamt money
Select
@.purgecount = count(D.EventID),
@.totcovchrg = sum(CoverChrgAmt),
@.totpaymntamt = sum(PayDetail.PaymntAmt),
@.totreimamt = sum(CalcTotalReimAmt)
from DelReimb D, Reimburse R, ReimburseOut RO,
(select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
from PaymentsDetail
where isnull(InvalidPayInd,'') <> 'Y' and
PaymntInd not in ('A','B','X')
group by ClaimID,EventID) PayDetail
where
D.ClaimID = R.ClaimID and
D.EventID = R.EventID and
D.ClaimID = RO.ClaimID and
D.EventID = RO.EventID and
D.ClaimID = PayDetail.ClaimID and
D.EventID = PayDetail.EventID
Error :
A SELECT statement that assigns a value to a variable must not be combined
with data-retrieval operations.
Please tell me how to over come this
Hi
You are missing a comma after the ISNULL statement
(select isnull(sum(PaymntAmt),0),PaymntAmt,ClaimID,EventID
It is always worth posting DDL and example data
http://www.aspfaq.com/etiquette.asp?id=5006 so people can post your query
into Query Analyser
John
"spc" wrote:

> I have written one procedure .in that i have put following login.but I got
> the error.
>
> Declare
> @.purgecount int,
> @.totcovchrg money,
> @.totpaymntamt money,
> @.totreimamt money
>
> Select
> @.purgecount = count(D.EventID),
> @.totcovchrg = sum(CoverChrgAmt),
> @.totpaymntamt = sum(PayDetail.PaymntAmt),
> @.totreimamt = sum(CalcTotalReimAmt)
> from DelReimb D, Reimburse R, ReimburseOut RO,
> (select isnull(sum(PaymntAmt),0) PaymntAmt,ClaimID,EventID
> from PaymentsDetail
> where isnull(InvalidPayInd,'') <> 'Y' and
> PaymntInd not in ('A','B','X')
> group by ClaimID,EventID) PayDetail
> where
> D.ClaimID = R.ClaimID and
> D.EventID = R.EventID and
> D.ClaimID = RO.ClaimID and
> D.EventID = RO.EventID and
> D.ClaimID = PayDetail.ClaimID and
> D.EventID = PayDetail.EventID
>
> Error :
> A SELECT statement that assigns a value to a variable must not be combined
> with data-retrieval operations.
>
> Please tell me how to over come this
>

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 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 data types

How is it possible to
take out all data types ?
Which procedure( function, view)
should I use ?Try:
select [name]
from systypes
AMB
"Alur" wrote:

> How is it possible to
> take out all data types ?
> Which procedure( function, view)
> should I use ?
>|||Also see sp_datatype_info in SQL Server Books Online.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Alur" <Alur@.discussions.microsoft.com> wrote in message
news:D4FEECA6-E828-4FE8-8DF6-BCD8BDD94E13@.microsoft.com...
How is it possible to
take out all data types ?
Which procedure( function, view)
should I use ?|||Thank you.|||Thank you.

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