Reading a field from Cursor

Hi,
I have a cursor that reads a table. During the reading of the table I want
to read e "field" directly (Not usign FETCH NEXT FROM authors_cursors INTO
@.col1, @.col2).
Thanks!claude81 wrote:
> Hi,
> I have a cursor that reads a table. During the reading of the table I
> want to read e "field" directly (Not usign FETCH NEXT FROM
> authors_cursors INTO @.col1, @.col2).
>
You have to FETCH NEXT INTO. There is no other way.
Cursors should be a last resort.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thaks!!
"Bob Barrows [MVP]" wrote:

> claude81 wrote:
> You have to FETCH NEXT INTO. There is no other way.
> Cursors should be a last resort.
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

reading / writing binary data to db

I'm trying to read a byte array of an image datatype from sql server, and then to put this in another field in the database. I get a byte array, but somehow the image doesn't get into the db well with the sql parameters. Does anyone have an idea how to tackle this problem?

Thanks a lot, Hugohttp://www.dotnetspider.com/Technology/QA/ViewQuestion.aspx?QuestionId=117

I think that link above has the answers to your question. Make sure you're using Image type, and passing in the byte array, and that you don't have anything blocking your connection (firewall or switch that's set to cap communication sizes).

Otherwise, I don't know what to tell ya. Need to see code.|||Thanks for the link, it helps me somehow although not enough, however...

Basically, what I'm trying to do is to "pump" binary data from one field in one table to another field in another table (both are of the image data type, length 16).

What I have now, is a byte array read from the original field, and set it as a value of an SqlParameter to insert it into the new field. This is the code fragment:

Dim SQLSel As String = "SELECT imgImage FROM tblProducts WHERE autoProductID=" & CategoryID
Dim reader As SqlClient.SqlDataReader
reader = _data.sqlRetrieveDataReader(SQLSel) 'retrieve a data reader via DAL
reader.Read()
'set the binary data of the original image in the byte array
Dim b() As Byte = CType(reader.GetValue(0), Byte())
Dim stream As System.IO.MemoryStream = New System.IO.MemoryStream(b, True)
stream.Write(b, 0, b.Length)
'insert the byte array into the new parameter
sqlComInsert.Parameters.Item("@.imgImage").Value = b

No errors are trapped when this code is executed. However, only red crossed (i.e. bad images) are displayed in the browser. The following code is used to display images from the database (which has proven to work on the original images/binary data):

 Dim ProductID As String = Request.QueryString("ProductID")
 Dim conn As New SqlClient.SqlConnection(CType(configurationAppSettings.GetValue("sqlConn.ConnectionString", GetType(System.String)), String))
 Dim SQLSel As String = "SELECT imgImage FROM tblProducts WHERE autoProductID=" & CategoryID
 Dim com As New SqlClient.SqlCommand(SQLSel, conn)
 conn.Open()
 Try
 Response.ContentType = "image/gif"
 Response.BinaryWrite(com.ExecuteScalar)
 Response.End()
 Finally
 conn.Close()
 End Try

I really have no clue what causes the movement of the images/binary data via asp.net to fail... if anyone knows how to tackle this problem, please help. Thanks a lot in advance, Hugo

Read one field in every row during SELECT

Hello

I have a table that keeps track of every access to the system. We insert the UserID, TimeOfAccess and TypeOfAccess.

We want to create a report and due to the limitations of Reporting Services for programatically processing, we want to create a temporary table to have the columns already set up for the report with the info that we need.

ORIGINAL TABLE

USERID | TIMEOFACCESS | TYPE OF ACCESS

2323 | 12/15/2007 03:52:54 | CLOCKIN

2323 | 12/15/2007 04:32:54 | CLOCKOUT

2323 | 12/15/2007 05:42:54 | CLOCKIN

2323 | 12/15/2007 07:53:54 | CLOCKOUT

2323 | 12/15/2007 09:18:54 | CLOCKIN

2323 | 12/15/2007 10:24:54 | CLOCKOUT

TEMPORARY TABLE

USERID | CLOCKIN | CLOCKOUT | ELAPSEDTIME

2323 | 03:52:54 | 04:32:54 | 0:40:54

OK. the problem is that I need to read the fields one by one in the SELECT statement so i can insert (and Update) the fields of the temporary table.

I create my temporary table

CREATE #TempTable

(

UserID int,

ClockIN DateTime null,

ClockOUT DateTime null,

ElapsedTime DateTime null,

)

I want to be able to do this in my stored procedure. I can doit in a form but i want to return from my database the datatable already suitable for my report.

Does anyone know how to read in a SELECT statement one field as it's been read?

you can do that using a cursor in your stored procedure.

See: http://www.sqlteam.com/item.asp?ItemID=553

If you dont want to use a cursor, its possible as well:

http://www.sql-server-performance.com/dp_no_cursors.asp

|||

hi,

you can achieve this using a better solution by dong a self join on userid and midnight(dateaccess)

you just have to drop the timepart of the date on the join. you dont even need the temp table.

here's the pseudocode: you just need to imporve this.

select userid, convert( varchar(20),dateaccess,102) as dateaccess, convert( varchar (20),dateaccess,108) as clockin, t2.clockout from table1 t1

where [type of access]='clockin'

join

(select userid, convert( varchar(20),dateaccess,102) as dateaccess,

convert( varchar(20),dateaccess,108) as clockout, from table1

where [type of access]='clockout')

as t2

on t1.userid=t2.userid and t1.dateaccess=t2.dateaccess

regards,

joey

|||

Try selecting the minimum "clockin" and maximum "clockout" per each userid, then calculate the diff in seconds.

Code Snippet

;with cte

as

(

select

userid,

min(case when [type of access] = 'clockin' then [time of access] end) as clockin,

max(case when [type of access] = 'clockout' then [time of access] end) as clockout,

datediff(

seconds,

min(case when [type of access] = 'clockin' then [time of access] end) as clockin,

max(case when [type of access] = 'clockout' then [time of access] end) as clockout

) as elapsed_time_sec

from

dbo.t1

group by

userid

)

select

userid,

clockin,

clockout,

right('00' + ltrim(elapsed_time_sec / 3600), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) / 60), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) % 60), 2)

from

cte;

AMB

|||

None of this solutions actually work.

I don't get the desired result. I think this is something impossible to do in a Stored Procedure wich is very disappointing because is not so difficult to do in a form. But that's what i don't want. There has to be a way... i guess i need to study more the t-sql language. I just can't believe t-sql is so poor.

|||

Try:

Code Snippet

;with cte_1

as

(

select

userid,

[TIME OF ACCESS],

[TYPE OF ACCESS],

row_number() over(partition by userid order by [TIME OF ACCESS]) as rn

from

dbo.t1

),

cte_2

as

(

select

a.userid,

a.[TIME OF ACCESS] as CLOCKIN,

b.[TIME OF ACCESS] as CLOCKOUT,

datediff(second, a.[TIME OF ACCESS], b.[TIME OF ACCESS]) as elapsed_time_sec

from

cte_1 as a

inner join

cte_1 as b

on a.userid = b.userid

and a.rn = b.rn - 1

and a.[TYPE OF ACCESS] = 'CLOCKIN'

and b.[TYPE OF ACCESS] = 'CLOCKOUT'

)

select

userid,

CLOCKIN,

CLOCKOUT,

right('00' + ltrim(elapsed_time_sec / 3600), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) / 60), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) % 60), 2)

from

cte_2

order by

userid,

CLOCKIN;

go

BTW, I wonder why elapsed time for the first two rows is "00:40:54", is they both have same number of seconds. I think it should be "00:40:00".

AMB

|||

This is it.

It works perfectly fine.

Definitely i am gonna buy a couple of T-SQL books. Any suggestion on the best title or publisher?

Jose

|||

I will suggest the serie "Inside SQL Server 2005".

Inside Microsoft SQL Server 2005: T-SQL Querying

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=pd_bbs_sr_3/002-2026708-7606405?ie=UTF8&s=books&qid=1179781170&sr=1-3

Inside Microsoft SQL Server 2005: T-SQL Programming

http://www.amazon.com/Inside-Microsoft-Server-2005-Pro-Developer/dp/0735621977/ref=pd_sim_b_1/002-2026708-7606405?ie=UTF8&qid=1179781170&sr=1-3

Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_sim_b_2/002-2026708-7606405?ie=UTF8&qid=1179781170&sr=1-3

AMB

P.S. Your alias exposed you.

PP --> Pepe (Jose)

CUBAN --> from Cuba

Oye chico, vinistes a bailar en casa del trompo.

|||

AMB

P.S. Your alias exposed you.

As one who belives in transparency, and thinks that a lot of folks are stuck in grade school with their childish and 'cutsy' nom de plumes, exactly how this is a problem in this venue?

Granted, anonymity has it purposes and places -but really, are these forums such place to fear exposure?

|||

good point. I am not affraid of exposure. As a matter of fact, most of the people online knows me by that name. ppcuban@.{most famous emails}.com are my address, the domain ppcuban.com also... hahaha. So, basically everybody call me ppcuban as a nickname even out of internet.

AMB? Tu eres cubano?

|||

Jose,

Nacido y criado en la Vibora. Emigre hacia los Estados Unidos de america hace 9 anios. Vivi por un tiempo en Miami y ahora radico en Carolina del Sur (No pastelitos de guayava y mucho menos masareal).

Saludos,

Alejandro Mesa

Read DESCRIPTION from a backuped DB file in .Net

Hi,
How can I read the "Description" field from a backuped DB (file) in .Net?
Some ideas?
Thanks,
George.What version are you using?
Where does the "Description"column exist?
If you are using SQL Server 2005 see sys.database_files table
There is state_desc column.
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:u9E2vXRQGHA.740@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How can I read the "Description" field from a backuped DB (file) in .Net?
> Some ideas?
> Thanks,
> George.
>|||I am using SQL Server 2000 and I want to be able to read the descriprion
text entered for the DESCRIPTION option when backing up a database.
Example:
BACKUP DATABASE atsTables
TO DISK = 'c:\temp\20060306_1245_atsTables.dat'
WITH
DESCRIPTION = 'atsTables Backup1'
I want to build a simple application where the user can select a file
(backedup db file) and the application shows in a textbox the description
text ('atsTables Backup1' from the above example). Is this possible?
Thanks,
George.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:%23buDpeRQGHA.1096@.TK2MSFTNGP11.phx.gbl...
> What version are you using?
> Where does the "Description"column exist?
> If you are using SQL Server 2005 see sys.database_files table
> There is state_desc column.
>
>
>
> "George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
> news:u9E2vXRQGHA.740@.TK2MSFTNGP12.phx.gbl...
>|||Lookup backupmediaset system table in the BOL
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:OGFY%23lRQGHA.1688@.TK2MSFTNGP11.phx.gbl...
>I am using SQL Server 2000 and I want to be able to read the descriprion
>text entered for the DESCRIPTION option when backing up a database.
> Example:
> BACKUP DATABASE atsTables
> TO DISK = 'c:\temp\20060306_1245_atsTables.dat'
> WITH
> DESCRIPTION = 'atsTables Backup1'
>
> I want to build a simple application where the user can select a file
> (backedup db file) and the application shows in a textbox the description
> text ('atsTables Backup1' from the above example). Is this possible?
> Thanks,
> George.
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:%23buDpeRQGHA.1096@.TK2MSFTNGP11.phx.gbl...
>|||You mean the information returned from RESTORE HEADERONLY? Sure, just execut
e that SQL command and
treat the result as any result from a SELECT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
news:u9E2vXRQGHA.740@.TK2MSFTNGP12.phx.gbl...
> Hi,
> How can I read the "Description" field from a backuped DB (file) in .Net?
Some ideas?
> Thanks,
> George.
>|||Yes Tibor that's what I needed.
Thanks for help guys
George.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schrieb
im Newsbeitrag news:eO$ckrRQGHA.3984@.TK2MSFTNGP14.phx.gbl...
> You mean the information returned from RESTORE HEADERONLY? Sure, just
> execute that SQL command and treat the result as any result from a SELECT
> statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George Homorozeanu" <george_homorozeanu@.hotmail.com> wrote in message
> news:u9E2vXRQGHA.740@.TK2MSFTNGP12.phx.gbl...
>sql

Read BLOB under SQL 2005 Management Studio -> Open Table.

How to read the content of a BLOB field under SQL 2005 Management Studio?
under the Management Studio, how to tell the blob field contain information
or not.
Does the BLOB content store in the .mdb file or somewhere else?
Is there any way or tools available for me to look at the content in the BLOB
field without writing some code?
"John Bell" wrote:

> Hi
> I assume you are using the Open table option in SSMS, in which case if your
> column has data it will say <binary data> if you have data in a image or
> varbinary column. If you ran select query in a query windows against the
> column you would see the data itself. Blob data is stored in the database,
> with SQL 2008 and the filestream option for varbinary(max) you can store
> your blobs on the filesystem.
> John
> "Kam" <Kam@.discussions.microsoft.com> wrote in message
> news:C2B2D7F0-5C73-4EBC-9D2C-48EDAF73C29D@.microsoft.com...
>
>
|||If you are talking about some kind of general-purpose GUI tools, I'm afraid
not because what is stored in an image column is just a bit stream, and SQL
Server doesn't care about its semantics, i.e. what the content actually
represent. For a tool to properly display the content of an image column, it
would have to understand the semantics of the image values. When one value
can be a Word file, another can be an Excel file, another can be of an
arbitrary 3rd-party file format, and yet another can be a custom binary file,
no general-purpose tool can handle them all.
Linchi
"Kam" wrote:
[vbcol=seagreen]
> Is there any way or tools available for me to look at the content in the BLOB
> field without writing some code?
> "John Bell" wrote:

Read BLOB under SQL 2005 Management Studio -> Open Table.

How to read the content of a BLOB field under SQL 2005 Management Studio?
under the Management Studio, how to tell the blob field contain information
or not.
Does the BLOB content store in the .mdb file or somewhere else?Hi
I assume you are using the Open table option in SSMS, in which case if your
column has data it will say <binary data> if you have data in a image or
varbinary column. If you ran select query in a query windows against the
column you would see the data itself. Blob data is stored in the database,
with SQL 2008 and the filestream option for varbinary(max) you can store
your blobs on the filesystem.
John
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:C2B2D7F0-5C73-4EBC-9D2C-48EDAF73C29D@.microsoft.com...
> How to read the content of a BLOB field under SQL 2005 Management Studio?
> under the Management Studio, how to tell the blob field contain
> information
> or not.
> Does the BLOB content store in the .mdb file or somewhere else?
>
>|||Is there any way or tools available for me to look at the content in the BLOB
field without writing some code?
"John Bell" wrote:
> Hi
> I assume you are using the Open table option in SSMS, in which case if your
> column has data it will say <binary data> if you have data in a image or
> varbinary column. If you ran select query in a query windows against the
> column you would see the data itself. Blob data is stored in the database,
> with SQL 2008 and the filestream option for varbinary(max) you can store
> your blobs on the filesystem.
> John
> "Kam" <Kam@.discussions.microsoft.com> wrote in message
> news:C2B2D7F0-5C73-4EBC-9D2C-48EDAF73C29D@.microsoft.com...
> > How to read the content of a BLOB field under SQL 2005 Management Studio?
> >
> > under the Management Studio, how to tell the blob field contain
> > information
> > or not.
> >
> > Does the BLOB content store in the .mdb file or somewhere else?
> >
> >
> >
>
>|||If you are talking about some kind of general-purpose GUI tools, I'm afraid
not because what is stored in an image column is just a bit stream, and SQL
Server doesn't care about its semantics, i.e. what the content actually
represent. For a tool to properly display the content of an image column, it
would have to understand the semantics of the image values. When one value
can be a Word file, another can be an Excel file, another can be of an
arbitrary 3rd-party file format, and yet another can be a custom binary file,
no general-purpose tool can handle them all.
Linchi
"Kam" wrote:
> Is there any way or tools available for me to look at the content in the BLOB
> field without writing some code?
> "John Bell" wrote:
> > Hi
> >
> > I assume you are using the Open table option in SSMS, in which case if your
> > column has data it will say <binary data> if you have data in a image or
> > varbinary column. If you ran select query in a query windows against the
> > column you would see the data itself. Blob data is stored in the database,
> > with SQL 2008 and the filestream option for varbinary(max) you can store
> > your blobs on the filesystem.
> >
> > John
> >
> > "Kam" <Kam@.discussions.microsoft.com> wrote in message
> > news:C2B2D7F0-5C73-4EBC-9D2C-48EDAF73C29D@.microsoft.com...
> > > How to read the content of a BLOB field under SQL 2005 Management Studio?
> > >
> > > under the Management Studio, how to tell the blob field contain
> > > information
> > > or not.
> > >
> > > Does the BLOB content store in the .mdb file or somewhere else?
> > >
> > >
> > >
> >
> >
> >

Read autonumbering field from db

Situation: We are blank new to SQL server (previosuly access) and we need to
convert to sql server quickly. Consequently this is maybe not a very wice
question - but it is SO important to us :-)
**
this works in access - but not on sql server:
Tabel contains 2 fields:
id = autonumbering
text = textfield
****
Set Con = CreateObject(”ADODB.Connection
Con.open "PROVIDER=SQLOLEDB;DATA
SOURCE=192.168.2xx. xx;UID=xxxxxx;PWD=xxxxxxxx;DATABASE=xxxx
xx"
Set RS=CreateObject(”ADODB.RecordSet”)
RS.Open ”Select * FROM test”, 1, 3
RS.AddNew
RS(”Text”) = ”test tekst”
RS.Update
Response.write RS(”text”) & ”was given id=” & RS(”id”)
RS.Close
Con.Close
’*************************************
********************
this should output the given id number - which is actually IN the database.
But in the output from above - it is blank ...
Thanx in advance :-)Take a look at the @.@.IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT functions fo
r
SQL server in Books Online.
"Jensgjerloev" wrote:

> Situation: We are blank new to SQL server (previosuly access) and we need
to
> convert to sql server quickly. Consequently this is maybe not a very wice
> question - but it is SO important to us :-)
> **
> this works in access - but not on sql server:
> Tabel contains 2 fields:
> id = autonumbering
> text = textfield
> ****
> Set Con = CreateObject(”ADODB.Connection
> Con.open "PROVIDER=SQLOLEDB;DATA
> SOURCE=192.168.2xx. xx;UID=xxxxxx;PWD=xxxxxxxx;DATABASE=xxxx
xx"
> Set RS=CreateObject(”ADODB.RecordSet”)
> RS.Open ”Select * FROM test”, 1, 3
> RS.AddNew
> RS(”Text”) = ”test tekst”
> RS.Update
> Response.write RS(”text”) & ”was given id=” & RS(”id”)
> RS.Close
> Con.Close
> ’*************************************
********************
> this should output the given id number - which is actually IN the database
.
> But in the output from above - it is blank ...
> Thanx in advance :-)
>|||THANKs Mark - will do saturday morning.
Right now and here: Is there a command we need to add ?
Best regards
Jens
"Mark Williams" wrote:
> Take a look at the @.@.IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT functions
for
> SQL server in Books Online.
> --
>
> "Jensgjerloev" wrote:
>|||THANKX Mark - it's working now - thanks to your help :-))
Best Regards
Jens
"Mark Williams" wrote:
> Take a look at the @.@.IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT functions
for
> SQL server in Books Online.
> --
>
> "Jensgjerloev" wrote:
>

Re: How to encrypt a column in SQL Server

Hi everyone,

How can you encrypt a column in SQL Server?? I have a text box in Access when user types in his/her password I would like the field to display something like **** in the text box. The password field is a field in a table on SQL Server.
I'm using AC2K and SQL Server 2K.

Thanks in advance.To make the control show asterisks, set its password attribute.

To encrypt the data, pick an encryption algorithm and implement that algorithm in your Access MDB or data project.

-PatP|||we did this one for her already

http://www.dbforums.com/t1095162.html|||Yeah, I know we've done it before... I think more than once actually.

I just figure that if we consistantly give the same answers, eventually it will help solve the problem. Maybe I should have just posted a link to one of the previous answers, but I was feeling too lazy to go find it just then. I've been kind of busy lately, and that often leads me to take the easy answer instead of doing it up properly, especially if the easy and proper ways both generate the same results!

-PatP|||but it is kind of a waste of my employers hard paid salary to have me redudently goofing off.|||I suppose that's true, but they are rarely discriminating about goofing off. I've never heard of a case where redundant goofing off was treated differently than applied goofing off was.

-PatP|||Owhay ancay ooyay encryptay anay olumnkay innay SQLAY Erversay? Unway ethodmay issay ootay oosyay uhthay amousfay "Igpay Atinlay" gorithmlay. Rytay ittay eforebay ooyay ecideday ootay ostpay uthay amesay amnday estionquay againay.

Okay?

Re: How can you encrypt a column in SQL Server?

Hi everyone,

How can you encrypt a column in SQL Server?? I have a text box in Access when user types in his/her password I would like the field to display something like **** in the text box. The password field is a field in a table on SQL Server.
I'm using AC2K and SQL Server 2K.

Thanks in advance.Pick an encryption method, and handle it on the client. ROT13 is fine for most casual stuff, BlowFish is good for the really hairy stuff.

-PatP|||In access, right click on your textbox and go to properties. on the data tab, put Password in the input mask field.

this how ever is not encryption. There are third party dll available that use the RSA cypher and other methods to encrpyt data with public key encryption and you can write the encrypted data to SQL server but you can not really encrypt a column per se.|||You may want to look into SQLShield, but as it was noted before, - the encryption should be done in the front-end code, so that clear-text passwords are not travelling the network.|||sql server 2k gives you the ability to set permissions on a single column in a table if you wish.
however, you could give users access to the table through a view and not include the pwd column in the view definition.

just an alternative.

Re: Trouble in getting a value from bit data type in stored procedure

Hi eveyone,

I'm trying to get the stored procedure to return a value from a field in a table. The value in the field stores a bit value and default value is set to 0. So there should always a value in that field but it is giving me a null value. Can anyone see why.
I'm calling sp thru vb. Thanks much!

This is VB:
sub
Set cancel_inspection_query = Nothing
With cancel_inspection_query
.ActiveConnection = CurrentProject.Connection
.CommandText = "spInspec_cancel_initial_scan1"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@.inspec_id", adInteger, adParamInput, 4, Me!inspecid.Caption)
.Parameters.Append .CreateParameter("@.bag_num", adInteger, adParamInput, 4, Me!bag_num.Caption)
.Parameters.Append .CreateParameter("@.sampling_id", adInteger, adParamInput, 4, Me!rmr.Caption)
.Execute , , adExecuteNoRecords

End With
Debug.Print cancel_inspection_query("ret_val").Value
end sub

This is sp:

CREATE PROCEDURE dbo.spInspec_cancel_initial_scan1
@.inspec_id int,
@.sampling_id int,
@.bag_num int
AS
declare @.inspection_complete bit

SELECT @.inspection_complete = inspection_complete
FROM dbo.tblBag_results
WHERE bag_num = @.bag_num;
begin
if @.inspection_complete= 1
return(1)
Else
if @.inspection_complete = 0
return(100)
--else
--return(-1)The Table DDL would help.

Is Bag_Num a PK or unique index?

If not, that's a problem...

Also is the column defined as NOT NULL?

If not, that's a problem...

And why not use an OUTPUT variable instead?

You should let SQL Server manage the return value. I've seen times when it overrides your value...which could be a problem if you code for a particular value...

Re Any ideas on this one ?

Still have some problems with the last thing on my first report ! Not able
to get the timedate field to show without the time.
If your RS parameter is set to a string, the original
> "convert(datetime,period,105)" should give you the listing you desire.
When
> this parameter is then passed to SQL it "should" automatically be
recognized
> as a date, but if not, you could pass the parameter as as string, and
then
> declare and set a new SQL parameter to the cast(@.param as datetime).
This doesn't seem to work, if I set it to string and do the convert it
still
shows the time.
I'm trying to do it the other way around but get syntax error, what could
be
wrong here:
CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
RETURNS Nchar(20)
AS
BEGIN
RETURN
CONVERT(Nvarchar(20), datepart(mm,@.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(dd, @.indate))
+ @.separator
+ CONVERT(Nvarchar(20), datepart(yy, @.indate))
END
GO
SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1,
[dbo].[udf_MyDate]
(periodestart,'/') AS pstart
FROM DEBSTAT
WHERE (DAY(PERIODESTART) <> '31')
ORDER BY PERIODESTART
DROP FUNCTION [dbo].[udf_MyDate]
Jack
--
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 44893 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dkRight mouse click on the field, properties. Select the date format you want
(assuming I correctly understand what you are looking for. It sounds like
you are concerned about displaying it versus sending a parameter to a query
without a time.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
news:e41LODJjFHA.3472@.TK2MSFTNGP10.phx.gbl...
> Still have some problems with the last thing on my first report ! Not able
> to get the timedate field to show without the time.
> If your RS parameter is set to a string, the original
>> "convert(datetime,period,105)" should give you the listing you desire.
> When
>> this parameter is then passed to SQL it "should" automatically be
> recognized
>> as a date, but if not, you could pass the parameter as as string, and
> then
>> declare and set a new SQL parameter to the cast(@.param as datetime).
> This doesn't seem to work, if I set it to string and do the convert it
> still
> shows the time.
> I'm trying to do it the other way around but get syntax error, what could
> be
> wrong here:
> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> RETURNS Nchar(20)
> AS
> BEGIN
> RETURN
> CONVERT(Nvarchar(20), datepart(mm,@.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> + @.separator
> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> END
> GO
> SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1,
> [dbo].[udf_MyDate]
> (periodestart,'/') AS pstart
> FROM DEBSTAT
> WHERE (DAY(PERIODESTART) <> '31')
> ORDER BY PERIODESTART
> DROP FUNCTION [dbo].[udf_MyDate]
> Jack
>
>
> --
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at få 44893 spam-mails.
> Betalende brugere får ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>|||It's a dataset containing timedate fields, used as a parameter list, in
the
list I can only choose timedate not how to show it, tried a couple of
things
but it doesn't seem to work out as planned.
It shows up like this 05/08/05 00:00:00 and I don't need the time only the
date, Chris has tried to help me out with a userdef. function but I just
can't get it right, see the statement below.
Jack
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i en meddelelse
news:eMNLe1JjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Right mouse click on the field, properties. Select the date format you
> want (assuming I correctly understand what you are looking for. It
sounds
> like you are concerned about displaying it versus sending a parameter to
a
> query without a time.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
> news:e41LODJjFHA.3472@.TK2MSFTNGP10.phx.gbl...
>> Still have some problems with the last thing on my first report ! Not
>> able
>> to get the timedate field to show without the time.
>> If your RS parameter is set to a string, the original
>> "convert(datetime,period,105)" should give you the listing you desire.
>> When
>> this parameter is then passed to SQL it "should" automatically be
>> recognized
>> as a date, but if not, you could pass the parameter as as string, and
>> then
>> declare and set a new SQL parameter to the cast(@.param as datetime).
>> This doesn't seem to work, if I set it to string and do the convert it
>> still
>> shows the time.
>> I'm trying to do it the other way around but get syntax error, what
could
>> be
>> wrong here:
>> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
>> RETURNS Nchar(20)
>> AS
>> BEGIN
>> RETURN
>> CONVERT(Nvarchar(20), datepart(mm,@.indate))
>> + @.separator
>> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
>> + @.separator
>> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
>> END
>> GO
>> SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1,
>> [dbo].[udf_MyDate]
>> (periodestart,'/') AS pstart
>> FROM DEBSTAT
>> WHERE (DAY(PERIODESTART) <> '31')
>> ORDER BY PERIODESTART
>> DROP FUNCTION [dbo].[udf_MyDate]
>> Jack
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 44893 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dk|||Here is the issue. If you have the data type of the report parameter as a
datetime you have no choice, it will show the time. If you don't want to
show the time then you need to have it as string datatype for the report
parameter parameter.
select convert(varchar(10),getdate(), 101) as Param
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
news:%23kYqIZKjFHA.320@.TK2MSFTNGP09.phx.gbl...
> It's a dataset containing timedate fields, used as a parameter list, in
> the
> list I can only choose timedate not how to show it, tried a couple of
> things
> but it doesn't seem to work out as planned.
> It shows up like this 05/08/05 00:00:00 and I don't need the time only the
> date, Chris has tried to help me out with a userdef. function but I just
> can't get it right, see the statement below.
> Jack
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i en meddelelse
> news:eMNLe1JjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>> Right mouse click on the field, properties. Select the date format you
>> want (assuming I correctly understand what you are looking for. It
> sounds
>> like you are concerned about displaying it versus sending a parameter to
> a
>> query without a time.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
>> news:e41LODJjFHA.3472@.TK2MSFTNGP10.phx.gbl...
>> Still have some problems with the last thing on my first report ! Not
>> able
>> to get the timedate field to show without the time.
>> If your RS parameter is set to a string, the original
>> "convert(datetime,period,105)" should give you the listing you desire.
>> When
>> this parameter is then passed to SQL it "should" automatically be
>> recognized
>> as a date, but if not, you could pass the parameter as as string, and
>> then
>> declare and set a new SQL parameter to the cast(@.param as datetime).
>> This doesn't seem to work, if I set it to string and do the convert it
>> still
>> shows the time.
>> I'm trying to do it the other way around but get syntax error, what
> could
>> be
>> wrong here:
>> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
>> RETURNS Nchar(20)
>> AS
>> BEGIN
>> RETURN
>> CONVERT(Nvarchar(20), datepart(mm,@.indate))
>> + @.separator
>> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
>> + @.separator
>> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
>> END
>> GO
>> SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1,
>> [dbo].[udf_MyDate]
>> (periodestart,'/') AS pstart
>> FROM DEBSTAT
>> WHERE (DAY(PERIODESTART) <> '31')
>> ORDER BY PERIODESTART
>> DROP FUNCTION [dbo].[udf_MyDate]
>> Jack
>
> --
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at få 44893 spam-mails.
> Betalende brugere får ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>|||This is great, it works !
Thanks to all for your help, really appreciate it !
Jack
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i en meddelelse
news:OezEyjKjFHA.4000@.TK2MSFTNGP12.phx.gbl...
> Here is the issue. If you have the data type of the report parameter as a
> datetime you have no choice, it will show the time. If you don't want to
> show the time then you need to have it as string datatype for the report
> parameter parameter.
> select convert(varchar(10),getdate(), 101) as Param
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
> news:%23kYqIZKjFHA.320@.TK2MSFTNGP09.phx.gbl...
> > It's a dataset containing timedate fields, used as a parameter list, in
> > the
> > list I can only choose timedate not how to show it, tried a couple of
> > things
> > but it doesn't seem to work out as planned.
> >
> > It shows up like this 05/08/05 00:00:00 and I don't need the time only
the
> > date, Chris has tried to help me out with a userdef. function but I just
> > can't get it right, see the statement below.
> >
> > Jack
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> skrev i en meddelelse
> > news:eMNLe1JjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> >> Right mouse click on the field, properties. Select the date format you
> >> want (assuming I correctly understand what you are looking for. It
> > sounds
> >> like you are concerned about displaying it versus sending a parameter
to
> > a
> >> query without a time.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
> >> news:e41LODJjFHA.3472@.TK2MSFTNGP10.phx.gbl...
> >> Still have some problems with the last thing on my first report ! Not
> >> able
> >> to get the timedate field to show without the time.
> >>
> >> If your RS parameter is set to a string, the original
> >> "convert(datetime,period,105)" should give you the listing you
desire.
> >> When
> >> this parameter is then passed to SQL it "should" automatically be
> >> recognized
> >> as a date, but if not, you could pass the parameter as as string, and
> >> then
> >> declare and set a new SQL parameter to the cast(@.param as datetime).
> >>
> >> This doesn't seem to work, if I set it to string and do the convert it
> >> still
> >> shows the time.
> >>
> >> I'm trying to do it the other way around but get syntax error, what
> > could
> >> be
> >> wrong here:
> >>
> >> CREATE FUNCTION udf_MyDate (@.indate datetime, @.separator char(1))
> >> RETURNS Nchar(20)
> >> AS
> >> BEGIN
> >> RETURN
> >> CONVERT(Nvarchar(20), datepart(mm,@.indate))
> >> + @.separator
> >> + CONVERT(Nvarchar(20), datepart(dd, @.indate))
> >> + @.separator
> >> + CONVERT(Nvarchar(20), datepart(yy, @.indate))
> >> END
> >> GO
> >>
> >> SELECT DISTINCT PERIODESTART, DAY(PERIODESTART) AS Expr1,
> >> [dbo].[udf_MyDate]
> >> (periodestart,'/') AS pstart
> >> FROM DEBSTAT
> >> WHERE (DAY(PERIODESTART) <> '31')
> >> ORDER BY PERIODESTART
> >>
> >> DROP FUNCTION [dbo].[udf_MyDate]
> >>
> >> Jack
> >
> >
> >
> > --
> > Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> > Den har indtil videre sparet mig for at få 44893 spam-mails.
> > Betalende brugere får ikke denne besked i deres e-mails.
> > Hent gratis SPAMfighter her: www.spamfighter.dk
> >
> >
>

Re : Sql Server - table - formula

I am trying to use SQL Server table formula field. I have
two fields 'Field1','Field2' and I am using the formula
Field2/Field1. The problem is if I have 0 values in
Field1, I am getting errors. Is there a documentation as
what functions are supported in the formula field and how
to handle the above mentioned error.What do you want the value to be if there is a '0' in field1. You can
use a case statement...
Vijay wrote:
> I am trying to use SQL Server table formula field. I have
> two fields 'Field1','Field2' and I am using the formula
> Field2/Field1. The problem is if I have 0 values in
> Field1, I am getting errors. Is there a documentation as
> what functions are supported in the formula field and how
> to handle the above mentioned error.|||You can use a CASE expression or even NULLIF like:
CREATE TABLE tbl (
...
col1 INT,
col2 INT,
calc_col AS col2/NULLIF(col1, 0),
...);
--
- Anith
( Please reply to newsgroups only )

RDL generation problem

Hi,

I am working with SQL Server 2005 Reporting Service from few days, though I am

not expert, for some reason I have to run on field without having sound

knowledge of RDL, but need your help, gys. I am using SQL Server Business

Intelligence Development Studio to design report.

Here is the procedure of my work to populate a RDL report

I used a stored procedure for the DataSet of my RDL

Then I drag and drop necessary field to my report layout.

Put required parameters to preview tab and then run report.

This is quite simple, I didn't face any problem with this process, even though

the process may not correct, but working perfect. My problem is

when the stored procedure returns multiple result set. The data tab only

shows the first result set though the SP returns multiple result set, I have

run the SP in the Query Analyzer. I don't want change my stored procedure.

And please give me some suggestions about the best procedure

to develop RDL report in real life.

Please reply me ASAP, it’s very urgent.

Thank youTareqe

Hi there,

SQL RS doesn't support multiple resultsets. You will need to either write a wrapper stored procedure to retrieve the dataset you want or modify your stored procedure.

regards,

Andrew