retrieve Datetime: store

Showing posts with label store. Show all posts

Friday, March 30, 2012

return big string

Hello,
How can I return more then 8000 characters from a store proc.?
When I do this
CREATE PROCEDURE SP_GetClassCode
AS
SELECT 'very long string'
GO
It work's
But when I do this
CREATE PROCEDURE SP_GetClassCode
@.Replace AS VARCHAR(50)
AS
SELECT 'very long ' + @.Replace
GO
This time it only return the 8000 first characters
What do I have to do to return more then 8000 characters from a Store Proc?
Thank you
Marc R.Marc Robitaille wrote:
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
You could return the values as two or more columns and concatenate on
the client. The data type you are working with (char/varchar) is limited
to 8000 bytes.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Is there an other datatype that I could use to return what I need?
"David Gugick" <david.gugick-nospam@.quest.com> a écrit dans le message de
news: %235Wco9uhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Marc Robitaille wrote:
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
> You could return the values as two or more columns and concatenate on the
> client. The data type you are working with (char/varchar) is limited to
> 8000 bytes.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||How are you determining the length returned? Query Analyzer has a limit of
8000 bytes per column. And you should avoid using sp_ as a prefix to stored
procedures.
--
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
>|||I try to build a VB.NET class with a Store proc. I specify the name of the
table to my SP then it return's a string. If I Copy/Paste the string in a
VB file, I have a fully fonctional class that represent my table. So, I have
writen a template that I use in my SP. My template has 38000 characters. In
some place, in my template, there are speacials words that are going to be
replace when the SP is execute. When I execute the SP without parameters, my
template is return entirely but with no modification in the template. When I
execute the SP with a parameter, only the 8000 first characters are return
with the modification. I don't run the SP in Query analyser but in a VB.NET
programm that I did. How can I return a big string with parameters?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
> How are you determining the length returned? Query Analyzer has a limit
> of 8000 bytes per column. And you should avoid using sp_ as a prefix to
> stored procedures.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>|||It looks like SQL Server is implicitly converting your string to the
datatype of the variable you are concatenating with it. You can create a
table variable with a text column and build your string in the text column.
The issue a select from the table variable at the end to get the full
output.
--
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>I try to build a VB.NET class with a Store proc. I specify the name of the
>table to my SP then it return's a string. If I Copy/Paste the string in a
>VB file, I have a fully fonctional class that represent my table. So, I
>have writen a template that I use in my SP. My template has 38000
>characters. In some place, in my template, there are speacials words that
>are going to be replace when the SP is execute. When I execute the SP
>without parameters, my template is return entirely but with no modification
>in the template. When I execute the SP with a parameter, only the 8000
>first characters are return with the modification. I don't run the SP in
>Query analyser but in a VB.NET programm that I did. How can I return a big
>string with parameters?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
> news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
>> How are you determining the length returned? Query Analyzer has a limit
>> of 8000 bytes per column. And you should avoid using sp_ as a prefix to
>> stored procedures.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
>> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>>
>|||Great idea
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
news: eFA9CewhFHA.1052@.TK2MSFTNGP10.phx.gbl...
> It looks like SQL Server is implicitly converting your string to the
> datatype of the variable you are concatenating with it. You can create a
> table variable with a text column and build your string in the text
> column. The issue a select from the table variable at the end to get the
> full output.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>>I try to build a VB.NET class with a Store proc. I specify the name of
>>the table to my SP then it return's a string. If I Copy/Paste the string
>>in a VB file, I have a fully fonctional class that represent my table. So,
>>I have writen a template that I use in my SP. My template has 38000
>>characters. In some place, in my template, there are speacials words that
>>are going to be replace when the SP is execute. When I execute the SP
>>without parameters, my template is return entirely but with no
>>modification in the template. When I execute the SP with a parameter, only
>>the 8000 first characters are return with the modification. I don't run
>>the SP in Query analyser but in a VB.NET programm that I did. How can I
>>return a big string with parameters?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message
>> de news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
>> How are you determining the length returned? Query Analyzer has a
>> limit of 8000 bytes per column. And you should avoid using sp_ as a
>> prefix to stored procedures.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
>> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>>
>>
>

return big string

Monday, March 12, 2012

retrieving connection parameters

I would like retrieve a connection's server and initail catalog to store in an audit file.

What object contains this information and could you point me to some sample code that accesses this information from a script somponent?

In the script component, add a connection (say, MyConnection) to the component using the Connection Managers tab in the Script Component Editor. Then, in the script, you can reference

Me.Connections.MyConnection.ConnectionString

to retrieve the connection string. The server and initial catalog are not available from within the script component but you could parse out information you want from there.

Donald

Retrieving and storing file timestamp

I have a package that processes a flat file, which is generated by a separate system each weekday around 3:30AM. I would like my package to store the timestamp of the input file and compare it to the timestamp of the next day's file. If they are the same, then the package can exit without reprocessing a particular file again.

So, my first question is whether a package variable can be updated so that its value will persist from one run to the next?

Second, what is the syntax to retrieve the file's timestamp, either directly from the file connection manager or within a script task?

Thanks,

Phil

Philsky wrote:

I have a package that processes a flat file, which is generated by a separate system each weekday around 3:30AM. I would like my package to store the timestamp of the input file and compare it to the timestamp of the next day's file. If they are the same, then the package can exit without reprocessing a particular file again.

So, my first question is whether a package variable can be updated so that its value will persist from one run to the next?

No! That'd be nice wouldn't it. I've requested similar functionality in the past although not formally via the Product Feedback Center. Perhaps you could submit it?

Philsky wrote:

Second, what is the syntax to retrieve the file's timestamp, either directly from the file connection manager or within a script task?

Thanks,

Phil

There's some code here that shows how you can access this information: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

Check lines 7 & 8 of the first code block.

-Jamie

Friday, March 9, 2012

Retrieving all users in db with specified AGE range

Hello, I have a table called Member in my database that I use to store information about users including the date of birth for each person. I have a search function in my application that is supposed to look through the Member table and spit out a list of users with a user-inputted age range (min and max ages). Now, I could have stored ages instead of dob in the table, but I would think that's bad practice since age changes and would need continuous recomputing (which is db intensive) as opposed to dob which stays the same.

So what I'm thinking is getting the min and max user inputted ages, convert them to dob values (of type DateTime) in the application. And then, to query the db and return a list of all users in the Member whose dob falls in between those two dates (is a BETWEEN even possible with DateTime values?).

How is the best way to go about this? There are many sites out there that return users with user specified age ranges. Is there a best way to do this that's the least taxing on the db?

TIA.

You can use the DateDiff function to get there age

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

|||

Thanks for the reply. But what about my database table implementation... is that the right way to go? i'm assuming having an age field is poor practice and I should just have a dob field.

And for the next step, how would I determine the list of users who are say, between the ages of 40 and 55? The DateDiff requires that I know 2 dates ahead of time and that would force me to do a table scan (would be time costly if the table gets huge), computing all dob values in the table with the current time to see if a user falls between 40 and 55. Is there a way that I can convert age into a DateTime? If so, I could compute the DateTimes for the min and max values that are inputted by the user. And then I can maybe do like a "WHERE dob BETWEEN min AND max"...... something to that effect to retrieve the necessary rows. BETWEEN works for integers, not sure for DateTime.

Sorry for the stupid questions, I'm a relative newbie. TIA.

|||

Anybody? Smile

Ok, I think this is the way to go. Just have a DOB field in the Member table. Having an age field is bad practice, I'd think. Then, when a user wants to search for all members within a specified age range (a min and a max age value in years), I would translate the age values into a DateTime format (current time minus age to derive DOB) on the application side. Then I would run an SQL query along the lines of "SELECT * FROM Member WHERE dobDateMin BETWEEN dobDateMax". This should return all members whose DOBs fall within the age range.

The question is, how do I calculate a DOB in DateTime given an age in years? Pseudo-speaking, it would be like.... take the current system time, minus the age in years, and derive the date in DateTime format, so I can feed it into the SQL statement.

Am I on the right track with this? How do all the sites that allow searches for age ranges do this?

TIA

|||

Yes, you would use the dateadd function for this.

SELECT *

FROM MyTable

WHERE DoB Between DATEADD(year,0-@.MaxAge,floor(cast(getutcdate() as float))) AND DATEADD(year,0-@.MinAge,floor(cast(getutcdate() as float)+1))

Assuming that DoB is a datetime field in the MyTable table, and there is an index on that field, it will do an index scan range to return your results. GetUTCDate() may not give you the date you are looking for, adjust as necessary.

|||

Thank you - that seems like what I'm looking for and I appreciate the example so I could visualize how that could be used here. I would have liked to do all of the calculating on the app side to avoid database overhead, but I'll take what I can get. Yes, the DOB field is already indexed to avoid a costly table scan. So do you think other sites use this approach when trying to return results of users within an age range? I'll try it out and see if it works. Thanks.

***** BTW, just out of curiosity, why did you cast the third parameter into float and then floor it? Is this so that you can get pinpoint accuracy at the date level? If so, since the third field accepted a datetime, don't we have to recast it again into a datetime (or smalldatetime)? Like ....

WHERE dob BETWEEN DATEADD(year,0-@.MaxAge,CAST(FLOOR(CAST(getdate() AS float)) AS datetime)) AND DATEADD(year,0-@.MinAge,CAST(FLOOR(CAST(getdate() AS float)+1) AS datetime))

|||

It's a quick hack. Datetimes when converted to floats are in a format that the date portion is stored in a whole numbers, and the time portion as a fraction of a number. By flooring it, we lose the time portion (or more accurately, we get the very smallest time for that date -- exactly midnight).

As for casting it back again, SQL Server will do that as an implicit conversion (It doesn't need to be stated, but you can if you want).

As for where to calculate the dates, I would do it on the server side. It's not really a hard calculation to make for SQL Server, and it abstracts the implementation of the age search to SQL Server. Meaning, if at a later time, we decide that we need to (for whatever reason, performance, scalability, integration) change how we do the search the application code doesn't need to change. We could for example, add an age column to the member table, run a batch process at night that goes through and updates all the user's age, and change the query (assuming it's in a stored procedure, or depends on a view) and the application(s) wouldn't even notice except for possibly better performance. All depends on your environment really. If you aren't using stored procedures then it won't really buy you very much though.

Wednesday, March 7, 2012

retrieve special column with Sql Full Text Search

hi,
I'm using SQL Server to store files in image column (blob).
Previuosly I Used 'Index Server' with FileSystem To Search text in the files
by doing this i could retrieve special column like DocAuthor,Content, ...
I didn't find a way to do the same in SQL Server Full Text Search to
retrieve the
abstract of the file.
Is there a way to do this and extend the query to more "in file" data?
Any help would be greatly appreciated.
Nissim L
This feature is rumored to ship in SQL 2005. It is not present in SQL 2000.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"nissiml" <nissiml@.discussions.microsoft.com> wrote in message
news:BBA52221-EAEF-428D-BAB1-D61AAE21AE65@.microsoft.com...
> hi,
> I'm using SQL Server to store files in image column (blob).
> Previuosly I Used 'Index Server' with FileSystem To Search text in the
files
> by doing this i could retrieve special column like DocAuthor,Content, ...
> I didn't find a way to do the same in SQL Server Full Text Search to
> retrieve the
> abstract of the file.
> Is there a way to do this and extend the query to more "in file" data?
> Any help would be greatly appreciated.
> Nissim L
>

Saturday, February 25, 2012

Retrieve output value from store procedure

I'm trying to use a store procedure to add an item into the database and retrieve the id of such item within and output parameter but this is not happening.

The item is added into the db but the output param is not modified.

Here is the code:

CREATE procedure dbo.AddItem
(
@.Desc nvarchar(100),
@.intItemID int output
)
as
insert into RR_Item (
desc
)
values (
@.Desc
)

select @.intItemID = SCOPE_IDENTITY()
GO

I have tried in the last line of the SP

select @.intItemID = SCOPE_IDENTITY()

select @.intItemID = @.@.IDENTITY

select @.intItemID = max(itemid) from RR_Item

but nothing seems to work.

I'm calling the store procedure as follows from asp.net:

Dim intItemIDAsNew SqlParameter("@.intItemID", SqlDbType.Int)

intItemID.Direction = ParameterDirection.Output

SqlHelper.ExecuteDataset(objConn.ConnectionString,"AddItem", desc, intItemID)

MsgBox(intItemID.Value.ToString)

Any help would be appreciated.

Thanks

(1) Use SCOPE_IDENTITY().

(2) check the 2nd portion ofthis articleto retrieve the OUTPUT value.

Retrieve length of image or BLOB

I use the data type image to store BLOB's on the database. Now I would
like to Query the length of the BLOB with T-SQL to calculate the used
disk-space for this item.
Has some one a good idea?Check out the DATALENGTH function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jehle@.centralnet.ch> wrote in message news:1151484946.825820.229140@.x69g2000cwx.googlegroups.com...
>I use the data type image to store BLOB's on the database. Now I would
> like to Query the length of the BLOB with T-SQL to calculate the used
> disk-space for this item.
> Has some one a good idea?
>

retrieve Datetime

Friday, March 30, 2012

return big string

return big string

return big string

Monday, March 12, 2012

retrieving connection parameters

Retrieving and storing file timestamp

Friday, March 9, 2012

Retrieving all users in db with specified AGE range

Wednesday, March 7, 2012

retrieve special column with Sql Full Text Search

Saturday, February 25, 2012

Retrieve output value from store procedure

Retrieve length of image or BLOB

Followers

Blog Archive

About Me