execution progress: type

Showing posts with label type. Show all posts

Friday, March 30, 2012

How to get type of parameter passed in stored procedure?

Hi ,

I wrote a stored procedure with one uinqueidentifier parameter.Depending upon the value passed during execution type of parameter passed is changing.

Example:

CREATE PROCEDURE ABC_Proc

@.ABC uniqueidentifier = NULL

Execution:

EXEc ABC_Proc ' '

If I pass the uiqueidentifier from the same table then the program executes properly.If same uinqueidentifier from other table or NULL or '' is passed the following error is displayed

"conversion failed when converting from a character string to unique identifier."

Questions:IS there a way to get the datatype of parameter passed in the querry?so, that I can modify accordingly.

Thanks,

Kodela.

Hi Kodela,

The easiest way is to use the new sql_variant datatype as the parameter, and then within the procedure, you can test for the base datatype and execute the appropriate action:

declare @.v sql_variant

set @.v = 123

if SQL_VARIANT_PROPERTY ( @.v , 'BaseType' ) = 'int'
begin
print 'int type passed'
end
else if SQL_VARIANT_PROPERTY ( @.v ,'BaseType' ) = 'uniqueidentifier'
begin
print 'uid type passed'
end

Cheers,
Rob

|||

You could use sql_variant, but the best thing to do is to treat parameters as strongly typed and only pass proper values to that parameter. You can get the type of the parameter from the information_schema:

CREATE PROCEDURE ABC_Proc
@.ABC uniqueidentifier = NULL
as
select @.ABC
go

select parameter_name, data_type
from information_schema.parameters
where specific_name = 'ABC_Proc'
and specific_schema = 'dbo'

|||

Robert Varga wrote:

use the new sql_variant datatype

Robert,

There is a reason that the variant datatype has been removed from Visual Studio.Net -it opens the code to potential attack vectors. Similarly, it is not a particularly good thing to use in SQL Server. Often the use of variants demonstrates a poor design, or just plain sloppy coding. -NOT drawing aspersions to you or your techniques, just commenting...

Regards,

|||

Hi Arnie,

I have to say I disagree completely. The SQL_VARIANT datatype was introduced in SQL 2005 for good reason. Just as there are valid reasons for using cursors, there are valid reasons for using the sql_variant datatype, and blanket statements alluding to their use constituting "poor design" are flawed.

In reality, we've been using varchar's as "variant" datatypes for years - ( CONVERT(varchar(15), GETDATE(), 103) anyone? ) and the new datatype removes precision related errors.

Cheeers,
Rob

|||

I actually have to agree that the SQL_VARIANT datatype should generally be avoided (as should converting dates to varchar values too, since that is usually a formatting gimmick that we *should* let the presentation layer handle :) for the same reason that VB programmers shouldn't generally use Option Strict (I think that was (is?) the syntax).

Especially in this situation, picking the proper datatype is part of the contract between caller and callee. In your example, it would not be possible to tell a GUID from a character value, certainly not when being passed in as a parameter, since it would be passed as a string of characters (or as a binary value) which could not be distinguished in code.

It is not completely useless when used for sparse property tables where you define the metadata of the column at runtime is a pretty good use, but generally having a column of unknown type means more code, more work, and greater chance for errors later.

|||

Hi Louis,

Sure; just as liberal use of, say, cursors is not a good thing, neither is the use of generic datatypes. My point is that there are circumstances in which the use of sql_variant (or cursors) are valid and should not be discounted by zealots simply because some Wrox book said they were "bad".

Cheers,
Rob

|||

Louis,

You demonstrate my point precisely. There is no need to use a sql_variant in this situation. The metadata is available. Strongly typed variables/parameters reduce coding and coding errors.

Using an object datatype (the .NET replacement for 'variant' has it's place and needs, and slq_variant has it's places and usages. However, in my opinion, it should not be used in a situation where the exact need does not exist. This is not one of those situations.

And gosh, if one wanted to morph a varchar() parameter, the datatype could be just another input parameter. For me, strongly typing is the key. If the dataype is known when the call is made, it should be strongly typed.

how to get Time time difference in sql?

Could any one please tell me how to find time difference between two dates?

i have two fields in database as datetime data type.i need to get time difference between this two fields.how to do that?

i use this one

SELECT outdate, (datediff(mi, outtime, intime) / 60.0)AS TimeUtilized FROM breaktime

but it giving me results as

1.00000000

1.250000

3.00000

i jus want it to be

1.25

How to do this?

Thanks for any help.

To find the time difference in days, hours, minutes. Try it, you can replace the dates with you column datetime.

selectDATEDIFF(day,2007-11-30,2007-11-20)AS NumberOfDays,DATEDIFF(hour,2007-11-30,2007-11-20)AS NumberOfHours,DATEDIFF(minute,2007-11-30,2007-11-20)AS NumberOfMinutesFROM test_table

|||

hi, thanks for the reply i dint useDATEDIFF(hour,2007-11-30,2007-11-20) this option jus because

i have one data as 10:45 and another as 12:00 it gives me time diff as 2 in hrs but actual diff is only 1:15 min thats y im convertin to min first and dividing it by 60.

but my outputs are like 1.02345,2.4567

i jus need to be like 1, 2.45

|||

Tweety@.net:
SELECT outdate, (datediff(mi, outtime, intime) / 60.0)AS TimeUtilized FROM breaktime

simple...

just write this query as...

SELECT outdate, ROUND(cast((datediff(mi, outtime, intime) / 60.0) as FLOAT),2) AS TimeUtilized FROM breaktime

and it should work.

hope this helps./.

|||

Try casting the result to decimal data type. The syntax is cast ( <number> as decimal ( 5 , 2 ) ).

sql

How to get thru security of Sql server http endpoint?

I have write the coding to create http endpoint in the sql server 2005, but if i type my pc ownself ip address (ex, 192.168.0.110) to connect to the sql server http endpoint, it prompt me for Username and Password. I didn't set any username and password before, what should i type ? Or what should i do to pass thru this authentication?

Thanks in advance.

Cheers,

Winson

Hi Winson,

For security reasons, the SQL Server 2005 http endpoints for Native XML Web Services require the client application to send user credentials. By default, the user account who created the HTTP endpoint will have access to connect to the endpoint. Users of the sysadmin role will also have access to the endpoint. All other SQL login accounts, must be granted explicit connect permissions. Please refer to Books Online's (BOL) GRANT Endpoint permission topic available at http://msdn2.microsoft.com/en-us/library/ms187811.aspx for additional information. For information regarding how to specify user credentials in the client application in .Net Frameworks, please refer to the following BOL topic http://msdn2.microsoft.com/en-us/library/ms175929.aspx.

Jimmy

|||

Hello Jimmy,

Thanks for the help, actually the situation is like this. I using nusoap (php) to connect to the Sql server http endpoint. but i fail to pass thru the security of sql sever http endpoint over the Internet. What do you think? Do you have any idea regarding this this of connection? nusoap (php) <> sql server http endpoint?

Thanks in advanced.

Cheers,

Winson

How to get this out put

Hi i have a small question

when i use this SQL bellow

select EmpId,Type,Description from Employee
it will return me this out put

EmpId Type Description
01 Private Private company owner
02 Self Home based
03 Self Home based

Now my requirement is to get this out put

EmpId Type Description
01 Private
02 Self Home based
03 Self Home based

i need to get the description of the employee when employee type only 'Self'
rest of the employee Type should leave it blank ?
how do i do this task ?

regards
suis

Try the example below.

Chris

Code Snippet

DECLARE @.MyTable TABLE

(

[EmpID] CHAR(2),

[Type] CHAR(10),

[Description] VARCHAR(50)

)

INSERT INTO @.MyTable([EmpID], [Type], [Description])

SELECT '01', 'Private', 'Private company owner' UNION ALL

SELECT '02', 'Self', 'Home based' UNION ALL

SELECT '03', 'Self', 'Home based'

SELECT [EmpID],

[Type],

CASE WHEN [Type] = 'Self' THEN [Description]

ELSE ''

END AS [Description]

FROM @.MyTable

|||Hi chris
thank you very much for u r quick response,
i could manage to sort out my problem using u r comments,
thank you very much again for this forum
regards

suis

Wednesday, March 28, 2012

How to get the stored procedure return type

I am having a problem finding the stored procedure return type. I am having no problem with the getting the stored procedure parameters collection and processing as needed or determining if the parameter is an output column. But if the stored procedure returns a value, how to I determine that return value type with SMO. I would much appreciate it if someone could answer this...

Steve Graddy
Orgbrat Consulting

Stored Procedures return multiple values in the form of output parameters. Thus the return types would be the datatype of StoredProcedure.Parameters which have IsOutputParameter as true.
Hope that helps.

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

Ok I am confused.. I understand about the ISOutputParameter property. let me give you an example and maybe you can explain. Using the Pub's database and the "reptq3" stored procedure as an example. if you look at this stored procedure definition there is no output parameter, but when you look at the parameters list in the object tree of the SQL Server 2005 Management Studio the last parameter or item is "Returns Integer". But when you get the stored procedures parametr collaection with SMO, you only get the first three parametrs that are in the stored procedure parameter list. Where is the SQL Server 2005 Management Studio coming up with that last item or does it always assume that if there are no output parametrs declared, there is always a last item "Returns Integer"? Here is a quick and dirty code section and as you see, I am asking for every paramter and then handling if it is input or output in the application. Probklem is, I only get three parametrs back from this call.

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;
if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;
dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

A stored procedure always returns an integer whose value is 0 if the execution was successful and non-zero in case of any failure. The return parameter which is mentioned above is different from this. From BOL - A stored procedure Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

How to get the stored procedure return type

Steve Graddy
Orgbrat Consulting

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;
if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;
dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

How to get the stored procedure return type

Steve Graddy
Orgbrat Consulting

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;
if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;
dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

Monday, March 26, 2012

How to get the parameter type in VB.net?

I am currently using the following embedded code to get the selected values from a Parameter and display in a textbox. I however have a number of reports and I don't want to include this in all of them. I have created a dll with some other functions but when i try to include the following function it throws an error on the Parameter type. What am I doing wrong, I am not all that familiar with dot net?

Public Function ShowParameterValues(ByVal parameter as Parameter) as String
Dim s as String

If parameter.count <= 5 then
If parameter.IsMultiValue and parameter.count > 1 then
s = " "
For i as integer = 0 to parameter.Count-1
if i = parameter.count - 1
s = s + CStr(parameter.Label(i))
else
s = s + CStr(parameter.Label(i)) + ", "
end if

Next
Else
s = " " + CStr(parameter.Label(0))
End If
else
s = " Only a maximum of 5 selection values can be displayed. "
End if
Return s
End Function

What's the error message?|||I am sorry..it says the type Parameter is not defined.|||

A multi-valued parameter is a 0-based array of objects, I'm pretty sure. But you should be passing the *name* of the parameter you want to reference, as a string, not the object. No?

Then you can say Parameters(tsParamName).Label(i) and Parameters(tsParamName).Count in your code...

HTH,

>L<

|||

It simply means it cannot find the type Parameter, either you need to reference the dll that contains the definition of the type Parameter or make to sure to include the CS file where that type Parameter is defined.

In your code,you are passing parameter as Parameter in the signature of the function, this is where it's complaining, it cannot find the Assembly where the type Parameter is defined.

|||

>>either you need to reference the dll that contains the definition of the type Parameter or make to sure to include the CS file where that type Parameter is defined.

Jonel, I honestly don't think that's correct... rather than including the reference to Parameter, I think the actual type he would need is ReportParameter here ... but in this case he could actually be passing the name of the parameter from the textbox expression (or wherever he's using the function), as I said...

>L<

|||

Hi, Lisa,

From within a custom assembly, you need to provide a fully qualified reference for the parameters type:

Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameters

If you use this reference, then when you install new releases of Reporting Services, you may need to recompile and redeploy the custom assembly.

|||

My apologies, Mary, Jonel, and everybody reading -- I misread the original post (saw the "embedded code" part but not that the poster was moving the code into a DLL!). Had a braino...

>L<

|||Thanks guys for your responses. After searching the net last week I found the answer. Thanks much for all your help.

How to get the metadata of a table

As title, such as the PK, data type of each column, etc.

Thanks,

Ricky.

The "standard" way is to use the information_schema views. It is the best way in 2000:

use tempdb

create table viewMetadata

(

column1 varchar(10) primary key,

column2 int unique,

check (column2 > column1)

)

select *

from information_schema.tables

where table_name = 'viewMetadata'

select *

from information_schema.columns

where table_name = 'viewMetadata'

select *

from information_schema.table_constraints

where table_name = 'viewMetadata'

etc

And there are more. Look up information_schema in BOL. In 2005, you can also use the system catalog views. They are not as convienient as the information_schema views, but they are a lot more powerful. For example:

select *

from sys.tables

where name = 'viewMetadata'

select *

from sys.columns

where object_id = object_id('viewMetadata')

select *

from sys.check_constraints

where parent_object_id = object_id('viewMetadata')

select *

from sys.key_constraints

where parent_object_id = object_id('viewMetadata')

It is really a big topic, I would suggest you look in BOL for Object Catalog Views.

|||

Hi, Louis. Thanks for your reply first.

However, I got empty results following your instruction like:

select *

from sys.tables

where name = 'AdventureWorks.Person.Address'

By the way, if I want to detect the primary key of 'AdventureWorks.Person.Address', what SQL statement should I issue to SQL 2005?

Regards,

Ricky.

|||

The name of the table is just Address, the schema is Person, and the database is AdventureWorks:

use AdventureWorks

select *

from sys.tables

where object_id = object_id('Person.Address')

/* alternative

where name = 'Address'

and schema_id = schema_id('Person')

select *

from sys.key_constraints

where parent_object_id = object_id('Person.Address')

and type = 'PK'

|||

Thanks a lot, Louis.

Ricky.

Monday, March 19, 2012

How to get Stored Procedure output ?

I have a variable @.NetPay as type money, and a stored proc spGetNetPay.
The output of spGetNetPay has one column NetPay, also with type of money, and always has one row.

Now I need assgin output from spGetNetPay to user variable @.NetPay. How can I do That?

Set @.NetPay = (Exec spGetNetPay) Sorry this does not work. Is it possible to create a user defined function?

I have little knowledge about User defided function. Is is the way I should go?

Thanks.

David J.


Create Procedure dbo.spGetNetPay (
 @.NetPay Money OUTPUT
) ASSET @.NetPay = (Select Top 1 NetPay From SomeTable)
GO

|||Post your code for spGetNetPay to enable determine the best solution for you.|||I use Kay Lee's solution. My spGetNetPay is huge, having more than 200 factors to determine the net pay. However, adding an output parameter is an easy job for me:)

I am still interested in if it is possible to code "user defined function". It has its beauty such that you can add it in select statment column list. But it is totally new for me. I even never seen sample code...

How to get statistics on DML statements per table

Can I get statistics on which type of DML statements (e.g. insert, delete, update) that are executed by users on a table without creating triggers? I want to be able to show the number of executed statements per statement type. I have tried the 2005 Profiler but it outputs the entire batch statement which makes it a bit more difficult to create statistics.

Rgds

Bob

There is no easy way to obtain this information. You could also take a look at 3rd party tools that can get this sort of information from the transaction logs.

how to get sql procedure parameters count and their type?

Hi All,
i am using DBLib and VC++. i want to know the number of parameters
of a procedure at run time. is it posible if yes then how can i do this.
thanks.Use ADO's OpenSchema method with the schema ID: adProcedureParameters|||is there another way of getting parameter count other than ADO.
actually we dont use ADO. some winodws api etc.

thanks for reply.

omar|||Check out INFORMATION_SCHEMA.ROUTINE_COLUMNS

How to get size of an image data type field stored in my SQL Server Database

Hi,
I have a problem and don't know how to solve it.
I am storing files (jpg, etc) in a table as binaries (image data type)
and would like to be able to show progress bar when I am loading it
from database.
But to do that, I need somehow to get the size of that file that is
stored in database (as image data type). Unfortunatelly my table does
not have a 'file_size' column from where I could get the size of image
and I am not in position to change that.
So far I am reading or writing to databasse successfully using
FileStream or BinaryREader/Writer (in VB.NET), so that is not the
problem.
Does anybody know how can I get the size of an file stored in
database?
Any help will be greatelly appreciatedThis is a multi-part message in MIME format.
--030403020708070709060401
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
SELECT DATALENGTH(MyImageColumn), MyImageColumn, ... FROM TableOwner.MyTable
WHERE ...
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Dino Buljubasic wrote:
>Hi,
>I have a problem and don't know how to solve it.
>I am storing files (jpg, etc) in a table as binaries (image data type)
>and would like to be able to show progress bar when I am loading it
>from database.
>But to do that, I need somehow to get the size of that file that is
>stored in database (as image data type). Unfortunatelly my table does
>not have a 'file_size' column from where I could get the size of image
>and I am not in position to change that.
>So far I am reading or writing to databasse successfully using
>FileStream or BinaryREader/Writer (in VB.NET), so that is not the
>problem.
>Does anybody know how can I get the size of an file stored in
>database?
>Any help will be greatelly appreciated
>
>
--030403020708070709060401
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>SELECT DATALENGTH(MyImageColumn), MyImageColumn, ... FROM
TableOwner.MyTable 
WHERE ... 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
Dino Buljubasic wrote:
<blockquote cite="midj74v21t1saqrl2b25cr16dnc93mo00emv3@.4ax.com"
type="cite">
<pre wrap="">Hi,
I have a problem and don't know how to solve it.
I am storing files (jpg, etc) in a table as binaries (image data type)
and would like to be able to show progress bar when I am loading it
from database.
But to do that, I need somehow to get the size of that file that is
stored in database (as image data type). Unfortunatelly my table does
not have a 'file_size' column from where I could get the size of image
and I am not in position to change that.
So far I am reading or writing to databasse successfully using
FileStream or BinaryREader/Writer (in VB.NET), so that is not the
problem.
Does anybody know how can I get the size of an file stored in
database?
Any help will be greatelly appreciated
</pre>
</blockquote>
</body>
</html>
--030403020708070709060401--|||Try the following function
select datalength(imagecolumn) from table where x=y
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:j74v21t1saqrl2b25cr16dnc93mo00emv3@.4ax.com...
> Hi,
> I have a problem and don't know how to solve it.
> I am storing files (jpg, etc) in a table as binaries (image data type)
> and would like to be able to show progress bar when I am loading it
> from database.
> But to do that, I need somehow to get the size of that file that is
> stored in database (as image data type). Unfortunatelly my table does
> not have a 'file_size' column from where I could get the size of image
> and I am not in position to change that.
> So far I am reading or writing to databasse successfully using
> FileStream or BinaryREader/Writer (in VB.NET), so that is not the
> problem.
> Does anybody know how can I get the size of an file stored in
> database?
> Any help will be greatelly appreciated
>

How to get size of an image data type field stored in my SQL Server Database

Monday, March 12, 2012

How to get rid of the zeros in money type, Thanks!

How to get rid of the zeros in money type,

i.e. only show dollars, no cents?

I use:

CONVERT(varchar, Price, 1) AS Price

It gives me the result with 2 digits decimals. like 123,456.00

I only need the dollar part, how to get rid of the zeros, Thanks!

CONVERT(int, a) AS Price

CONVERT(numeric, a) AS Price

Also take a look at the following for CAST and CONVERT

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

|||

Consider the functions CEILING() and FLOOR().

Zlatko

Wednesday, March 7, 2012

How to get month from a datetime type?

Hello guys,

I have datetime value as "mm/dd/yyyy h:m:s", my question is as title. help pls!

Cheers,

Elton

SUBSTRING(<your-value>, 1, 2)

-Jamie

|||use DATEPART function in expressions|||

If the source column is a date type, such as DT_DBTIMESTAMP, then you can use the date related functions, DATEPART, or there is also a more direct MONTH function.

Integration Services Expression Reference
(http://msdn2.microsoft.com/en-us/library/8b80403f-6d45-4001-8b12-25a933c663a2.aspx)

Friday, February 24, 2012

How to get IRowsetPosition...

Hello everyone.

I make the IRowset form IOpenRowset::OpenRowset() method because I want to use the base type cursor, scrollable and updatable.

But I don't know how to get the IRowsetPosition in this case..

I want to know the current cursor position after calling IRowset::GetNextRows().

I'm developing MFC application using OLE DB interface in VM5.

Thanks in advance..

You could read the identity column?|||When using a base table cursor the best approach to do what you want is to use the bookmark column (at column index 0). You must expose the IRowsetBookmark (if memory serves me well) and then you can reposition the cursor on any bookmark of the open rowset.|||

Thank you..

After posting my question, I updated my code by IRowsetBookmark and it worked.

Now I have the confidence that the way I did is the best.. :-)

Anyway..

Is there no way to get the IRowsetPosition..? just for curiosity

How to get Image value in SP

Hi,
I have a SP that needs to get the value of image field (the data type is
image).
But I can't declare a image data type variable, it response:
The text, ntext, and image data types are invalid for local variables.
So, How can I get image value in Store Procedure?
Thanks for help!
AngiImage (and text/ntext) data types cannot be declared as local variables.
These are mostly intended to be transferred to/from application code
directly.
What do you plan to do with the image value in the proc? You can use
SUBSTRING to assign 'chunks' of the image value to a local varbinary
variable.
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a SP that needs to get the value of image field (the data type is
> image).
> But I can't declare a image data type variable, it response:
> The text, ntext, and image data types are invalid for local variables.
> So, How can I get image value in Store Procedure?
> Thanks for help!
> Angi
>|||Thanks for Dan.
I use VARBINARY and it's work!
What do you plan to do with the image value in the proc?
I assign a value to SP and want to response correct image code embeded on
SQL.
Then use the SQL to present image and other information on RS report!
Last, I use Function instead of Store Procedure!
Angi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> glsD:uem$6elfGHA.5088@.TK2MSFTN
GP02.phx.gbl...
> Image (and text/ntext) data types cannot be declared as local variables.
> These are mostly intended to be transferred to/from application code
> directly.
> What do you plan to do with the image value in the proc? You can use
> SUBSTRING to assign 'chunks' of the image value to a local varbinary
> variable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "angi" <angi@.news.microsoft.com> wrote in message
> news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
>|||If you simply need to return reasonably sized image values back the
application, you don't need a variable. See the examples below.
CREATE PROC dbo.GetImageAsResult
@.ImageID int
AS
SELECT MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
CREATE PROC dbo.GetImageAsOutputParameter
@.ImageID int,
@.Image image OUTPUT
AS
SELECT @.Image = MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:eMYmm3xfGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Thanks for Dan.
> I use VARBINARY and it's work!
> What do you plan to do with the image value in the proc?
> I assign a value to SP and want to response correct image code embeded on
> SQL.
> Then use the SQL to present image and other information on RS report!
> Last, I use Function instead of Store Procedure!
> Angi
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> glsD:uem$6elfGHA.5088@.TK2MSFTNGP02.phx.gbl...
>

How to get Image value in SP

Hi,
I have a SP that needs to get the value of image field (the data type is
image).
But I can't declare a image data type variable, it response:
The text, ntext, and image data types are invalid for local variables.
So, How can I get image value in Store Procedure?
Thanks for help!
AngiImage (and text/ntext) data types cannot be declared as local variables.
These are mostly intended to be transferred to/from application code
directly.
What do you plan to do with the image value in the proc? You can use
SUBSTRING to assign 'chunks' of the image value to a local varbinary
variable.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a SP that needs to get the value of image field (the data type is
> image).
> But I can't declare a image data type variable, it response:
> The text, ntext, and image data types are invalid for local variables.
> So, How can I get image value in Store Procedure?
> Thanks for help!
> Angi
>|||Thanks for Dan.
I use VARBINARY and it's work!
What do you plan to do with the image value in the proc?
I assign a value to SP and want to response correct image code embeded on
SQL.
Then use the SQL to present image and other information on RS report!
Last, I use Function instead of Store Procedure!
Angi
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> ¼¶¼g©ó¶l¥ó·s»D:uem$6elfGHA.5088@.TK2MSFTNGP02.phx.gbl...
> Image (and text/ntext) data types cannot be declared as local variables.
> These are mostly intended to be transferred to/from application code
> directly.
> What do you plan to do with the image value in the proc? You can use
> SUBSTRING to assign 'chunks' of the image value to a local varbinary
> variable.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "angi" <angi@.news.microsoft.com> wrote in message
> news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a SP that needs to get the value of image field (the data type is
>> image).
>> But I can't declare a image data type variable, it response:
>> The text, ntext, and image data types are invalid for local variables.
>> So, How can I get image value in Store Procedure?
>> Thanks for help!
>> Angi
>|||If you simply need to return reasonably sized image values back the
application, you don't need a variable. See the examples below.
CREATE PROC dbo.GetImageAsResult
@.ImageID int
AS
SELECT MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
CREATE PROC dbo.GetImageAsOutputParameter
@.ImageID int,
@.Image image OUTPUT
AS
SELECT @.Image = MyImage
FROM dbo.MyTable
WHERE ImageID =@.ImageID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"angi" <angi@.news.microsoft.com> wrote in message
news:eMYmm3xfGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Thanks for Dan.
> I use VARBINARY and it's work!
> What do you plan to do with the image value in the proc?
> I assign a value to SP and want to response correct image code embeded on
> SQL.
> Then use the SQL to present image and other information on RS report!
> Last, I use Function instead of Store Procedure!
> Angi
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> ¼¶¼g©ó¶l¥ó·s»D:uem$6elfGHA.5088@.TK2MSFTNGP02.phx.gbl...
>> Image (and text/ntext) data types cannot be declared as local variables.
>> These are mostly intended to be transferred to/from application code
>> directly.
>> What do you plan to do with the image value in the proc? You can use
>> SUBSTRING to assign 'chunks' of the image value to a local varbinary
>> variable.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "angi" <angi@.news.microsoft.com> wrote in message
>> news:ujFRXlkfGHA.764@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a SP that needs to get the value of image field (the data type is
>> image).
>> But I can't declare a image data type variable, it response:
>> The text, ntext, and image data types are invalid for local variables.
>> So, How can I get image value in Store Procedure?
>> Thanks for help!
>> Angi
>>
>

Friday, March 30, 2012

How to get type of parameter passed in stored procedure?

how to get Time time difference in sql?

How to get thru security of Sql server http endpoint?

How to get this out put

Wednesday, March 28, 2012

How to get the stored procedure return type

How to get the stored procedure return type

How to get the stored procedure return type

Monday, March 26, 2012

How to get the parameter type in VB.net?

How to get the metadata of a table

Monday, March 19, 2012

How to get Stored Procedure output ?

How to get statistics on DML statements per table

how to get sql procedure parameters count and their type?

How to get size of an image data type field stored in my SQL Server Database

How to get size of an image data type field stored in my SQL Server Database

How to get size of an image data type field stored in my SQL Server Database

Monday, March 12, 2012

How to get rid of the zeros in money type, Thanks!

Wednesday, March 7, 2012

How to get month from a datetime type?

Friday, February 24, 2012

How to get IRowsetPosition...

How to get Image value in SP

How to get Image value in SP

execution progress

Blog Archive

About Me