Default language and dates

I've just moved servers - WK3 to WK3 - installed SQLserver 2005 and uploaded the database. Code base has not changed but now I'm getting

"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .

I also ran

EXEC @.ret=sp_defaultlanguage'sa','British English'

as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

ashaig:

"String was not recognized as a valid DateTime.Couldn't store <21/07/2006>" . I suspect its to do with the default language , but although the default is English (United States) as in my last server, I have set the language in Advanced settings to be British English. I can't see any difference between the settings and my last server. I'm British BTW .

Yes it may be related to the language setting. Actually there are some differences between English (United States) and British English, including date format. You can use the following command to check details of all language settings:

EXEC sp_helplanguage

From the result we can see the dateformat of us_en is mdy, while the dateformate of British is dmy.

ashaig:

as the only login is sa . WK3 is itself set to English (United Kingdom) - I thought SQL 2005 would inherit this setting? Any help would be much appreciated.

You can change the default language setting of a SQL instance by using such command:

EXEC sp_configure 'default language',0
reconfigure with override

Where 0 is the id of the language. Here are some useful links about the language setting in SQL Server:

Default Language option:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3xny.asp

sp_configure:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_0put.asp

sql

Decoding MIME Base64

Hi

How do you decode an image (found inside an xml file) which has been encoded in MIME Base 64. If you read in the string, how would you get the image?

I know other database systems (like ASA) that has a build in function to do it for you but I cant find a similar function in SQL Express?

Thanks

Hi

I found out that I can encode images to base64 by using the FOR XML functionality. As far as I can tell I am suppose to be able to use the OPENXML function to turn it back into an image. This does not work in SQL Express, but it does work in ASA?

The only deferens is that in SQL Express I have to get a handle on the xml doc before calling OPENXML. Why will this make a difference and how do I get the correct end result?

Here is the code for SQL EXPRESS (the image encoding is not the full version since it takes to much space):

begin

declare @.doc varchar(max);

DECLARE @.idoc int;

declare @.pic varbinary(max);

set @.doc = '<row><itm_pic>/9j/4FFFFAH//2Q==</itm_pic></row>';

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc;

SELECT *

FROM OPENXML( @.idoc,'/row/itm_pic')

WITH ( picture image 'text()');

EXEC sp_xml_removedocument @.idoc;

end;

To get the string for the xml doc with the image run this select on a image in a table:

SELECT picture

FROM prod_images

where id_pic = 1'

FOR XML RAW, BINARY BASE64, ELEMENTS

Thanks

DECODE please help

Hi-

I am trying to accomplish this in my SELECT statement...

If the length of the retreived string data is more than 10 characters, it should return the first 10 characters followed by a literal string '..' else return the string data as is

I tried to use IIF, CASE but didn't got it work, kept getting errors...

SELECT FinalName = IIF ( DATALENGTH ( NameString ) > 10, SUBSTRING ( NameString, 0, 10 ) + '..' , NameString ) FROM SomeTable

Any help is highly appreciated... Thanks for your quick responses...T-SQL does not provide an IIF function. You need to use CASE.

SELECT
 CASE
 WHEN LEN(NameString) > 10 THEN SUBSTRING ( NameString, 0, 10 ) + '..'
 ELSE NameString
 END AS FinalName
FROM
 SomeTable

Terri|||And actually, for the SUBSTRING function you should be using a 1,10 not 0,10. And note that the LEN function would be more correct for your purposes than DATALENGTH.

Terri|||Thank you so very much Terri... This worked perfect...
:)

Decode Base64 to Image!

Hi,

I have a xml string which is consist of some images encoded in base64; I have to extract these images in a stored procedure and save them in some tables.

The problem is that I can't decode this base64s to images. Is there a way to do it? (I use SQL Server 2005 Enterprise)

Thanx

Convert.FromBase64String
You probably want to take a look at that. You could possibly create a memory stream from the byte array and then create a bitmap from the stream.

|||

Thank you for your help but as I've mentioned I have to extract images in a stored procedure, so I have to decode them in it too.

Sorry for my late reply.

|||hwat about usin a CLR procedure if you have SQL2k5 already. YOu could use the mentioned class in the last post. That should work for you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Declare Variable Dynamically

I'm attempting to modify some Crosstab generating code, and I need some
advice/examples.

Currently, the code uses a single string variable to store the
dynamically generated query (www.johnmacintyre.ca). The problem is that
I am trying to pivot biological taxonomy information, and may end up
with a table containing over 200 columns. This takes the dynamic string
well over the 8000char limit for variables.

>From my understanding, the EXEC() command does not have the 8000char
limit if the execution string is broken into chunks, and concatenated
e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
to:

1) start a counter at the beginining of the dynamic generation
2) append the counter value to the end of a string variable name
3) DECLARE the new variable and attach that loop cycle of text to it,
or attach each chunk of characters < 8000
4) build the EXEC() string by concatenating each dynamic varible

Can this be done? Should it be done? Is there a better way to address
this type of problem?

Thanks for any ideas or insights

Tim Pascoe>> Can this be done? <<

Maybe, maybe not.

>> Should it be done? <<

No, this is a mis-use of SQL.

>> Is there a better way to address this type of problem? <<

Crosstabs (thank you for not calling them "pivot tables") are a report
and not a query. you ought to use a report tool and not SQL for this
kind of job.|||Hi
You may want to look at previous posts regarding crosstab queries, as Joe
says it is better to do this in the reporting tool such as RAC
http://www.rac4sql.net/ and others.

Here are a few links you may want to read:
http://www.windowsitpro.com/SQLServ...5608/15608.html
http://support.microsoft.com/defaul...b;EN-US;q175574

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

John

<tim.pascoe@.cciw.ca> wrote in message
news:1102971154.140806.89470@.f14g2000cwb.googlegro ups.com...
> I'm attempting to modify some Crosstab generating code, and I need some
> advice/examples.
> Currently, the code uses a single string variable to store the
> dynamically generated query (www.johnmacintyre.ca). The problem is that
> I am trying to pivot biological taxonomy information, and may end up
> with a table containing over 200 columns. This takes the dynamic string
> well over the 8000char limit for variables.
>>From my understanding, the EXEC() command does not have the 8000char
> limit if the execution string is broken into chunks, and concatenated
> e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
> to:
> 1) start a counter at the beginining of the dynamic generation
> 2) append the counter value to the end of a string variable name
> 3) DECLARE the new variable and attach that loop cycle of text to it,
> or attach each chunk of characters < 8000
> 4) build the EXEC() string by concatenating each dynamic varible
> Can this be done? Should it be done? Is there a better way to address
> this type of problem?
> Thanks for any ideas or insights
> Tim Pascoe|||RAC was my next point of investigation. The problem is that I'm not
actually producing a report of the data (although I agree this is
generally what crosstabs are for). Instead, the data is required in
this format so it can be fed into statistical software, which requires
the crosstabulated structure. I'll look into RAC and the other sites
you listed.

Thanks.

John Bell wrote:
> Hi
> You may want to look at previous posts regarding crosstab queries, as
Joe
> says it is better to do this in the reporting tool such as RAC
> http://www.rac4sql.net/ and others.
> Here are a few links you may want to read:
http://www.windowsitpro.com/SQLServ...5608/15608.html
> http://support.microsoft.com/defaul...b;EN-US;q175574
> http://www.sqlteam.com/item.asp?ItemID=2955
> John
> <tim.pascoe@.cciw.ca> wrote in message
> news:1102971154.140806.89470@.f14g2000cwb.googlegro ups.com...
> > I'm attempting to modify some Crosstab generating code, and I need
some
> > advice/examples.
> > Currently, the code uses a single string variable to store the
> > dynamically generated query (www.johnmacintyre.ca). The problem is
that
> > I am trying to pivot biological taxonomy information, and may end
up
> > with a table containing over 200 columns. This takes the dynamic
string
> > well over the 8000char limit for variables.
> >>From my understanding, the EXEC() command does not have the
8000char
> > limit if the execution string is broken into chunks, and
concatenated
> > e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need
is
> > to:
> > 1) start a counter at the beginining of the dynamic generation
> > 2) append the counter value to the end of a string variable name
> > 3) DECLARE the new variable and attach that loop cycle of text to
it,
> > or attach each chunk of characters < 8000
> > 4) build the EXEC() string by concatenating each dynamic varible
> > Can this be done? Should it be done? Is there a better way to
address
> > this type of problem?
> > Thanks for any ideas or insights
> > Tim Pascoe|||Hi

You may want to consider Analysis services then?

John

<tim.pascoe@.cciw.ca> wrote in message
news:1103032212.575699.29580@.c13g2000cwb.googlegro ups.com...
> RAC was my next point of investigation. The problem is that I'm not
> actually producing a report of the data (although I agree this is
> generally what crosstabs are for). Instead, the data is required in
> this format so it can be fed into statistical software, which requires
> the crosstabulated structure. I'll look into RAC and the other sites
> you listed.
> Thanks.
>
> John Bell wrote:
>> Hi
>> You may want to look at previous posts regarding crosstab queries, as
> Joe
>> says it is better to do this in the reporting tool such as RAC
>> http://www.rac4sql.net/ and others.
>>
>> Here are a few links you may want to read:
>>
> http://www.windowsitpro.com/SQLServ...5608/15608.html
>> http://support.microsoft.com/defaul...b;EN-US;q175574
>>
>> http://www.sqlteam.com/item.asp?ItemID=2955
>>
>> John
>>
>> <tim.pascoe@.cciw.ca> wrote in message
>> news:1102971154.140806.89470@.f14g2000cwb.googlegro ups.com...
>> > I'm attempting to modify some Crosstab generating code, and I need
> some
>> > advice/examples.
>>> > Currently, the code uses a single string variable to store the
>> > dynamically generated query (www.johnmacintyre.ca). The problem is
> that
>> > I am trying to pivot biological taxonomy information, and may end
> up
>> > with a table containing over 200 columns. This takes the dynamic
> string
>> > well over the 8000char limit for variables.
>>> >>From my understanding, the EXEC() command does not have the
> 8000char
>> > limit if the execution string is broken into chunks, and
> concatenated
>> > e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need
> is
>> > to:
>>> > 1) start a counter at the beginining of the dynamic generation
>> > 2) append the counter value to the end of a string variable name
>> > 3) DECLARE the new variable and attach that loop cycle of text to
> it,
>> > or attach each chunk of characters < 8000
>> > 4) build the EXEC() string by concatenating each dynamic varible
>>> > Can this be done? Should it be done? Is there a better way to
> address
>> > this type of problem?
>>> > Thanks for any ideas or insights
>>> > Tim Pascoe
>|||(tim.pascoe@.cciw.ca) writes:
> I'm attempting to modify some Crosstab generating code, and I need some
> advice/examples.
> Currently, the code uses a single string variable to store the
> dynamically generated query (www.johnmacintyre.ca). The problem is that
> I am trying to pivot biological taxonomy information, and may end up
> with a table containing over 200 columns. This takes the dynamic string
> well over the 8000char limit for variables.
>>From my understanding, the EXEC() command does not have the 8000char
> limit if the execution string is broken into chunks, and concatenated
> e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
> to:
> 1) start a counter at the beginining of the dynamic generation
> 2) append the counter value to the end of a string variable name
> 3) DECLARE the new variable and attach that loop cycle of text to it,
> or attach each chunk of characters < 8000
> 4) build the EXEC() string by concatenating each dynamic varible
> Can this be done? Should it be done? Is there a better way to address
> this type of problem?

I think it can be done, but I would not like to do it. You would have
to generate dynamic SQL which in its turn generates the dynamic SQL
that executes the query.

It may be a better alternative to use a client language to generate the
SQL. If you use a language like Perl or Visual Basic which has unlimited
strings, you are saved the restriction of varchar(8000). Note that all
that you would bring to the client would be the meta data needed to
form the SQL statement.

In SQL 2005 there is a new data type varchar(MAX) which is akin to text,
but works more like varchar, and thus in SQL 2005 you would also be saved
from the varchar(8000) restriction. SQL 2005 is currently in beta, with
release planned next year.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Declare dynamic Cursor from String

Hi,
is it possible to create a cursor from a dynamic string?
Like:

DECLARE @.cursor nvarchar(1000)
SET @.cursor = N'SELECT product.product_id
FROM product WHERE fund_amt > 0'

DECLARE ic_uv_cursor CURSOR FOR @.cursor

instead of using this

--SELECT product.product_id
--FROM product WHERE fund_amt > 0 -- AND mpc_product.status
= 'aktiv'

Havn't found anything in the net...
Thanks,
PeppiNot within the stored procedure, but I do know their are some undocumented
sps - such as "sp_cursoropen" and a few others with "sp_cursor*" which might
be abloe to do the job for you.

--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm

<peppi911@.hotmail.com> wrote in message
news:1146129244.595060.254470@.v46g2000cwv.googlegr oups.com...
> Hi,
> is it possible to create a cursor from a dynamic string?
> Like:
>
> DECLARE @.cursor nvarchar(1000)
> SET @.cursor = N'SELECT product.product_id
> FROM product WHERE fund_amt > 0'
> DECLARE ic_uv_cursor CURSOR FOR @.cursor
> instead of using this
> --SELECT product.product_id
> --FROM product WHERE fund_amt > 0 -- AND mpc_product.status
> = 'aktiv'
> Havn't found anything in the net...
> Thanks,
> Peppi|||(peppi911@.hotmail.com) writes:
> is it possible to create a cursor from a dynamic string?
> Like:
>
> DECLARE @.cursor nvarchar(1000)
> SET @.cursor = N'SELECT product.product_id
> FROM product WHERE fund_amt > 0'
> DECLARE ic_uv_cursor CURSOR FOR @.cursor
> instead of using this
> --SELECT product.product_id
> --FROM product WHERE fund_amt > 0 -- AND mpc_product.status
>= 'aktiv'

Yes, this is possible, but the question remains: why?

See here for details: http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your answers.
I'll have a look at the link.
The WHY ist that once a day the cursor should affect all products and
during the day every 5 minutes reclculate for inaktive ones.
Thats the reason.

Thanks,
mike|||peppi911@.hotmail.com wrote:
> The WHY ist that once a day the cursor should affect all products and
> during the day every 5 minutes reclculate for inaktive ones.
> Thats the reason.

That doesn't explain why you are using a cursor. It also doesn't
explain the need for dynamic SQL. Both are things you should avoid when
you can, I think that was what Erland was trying to get at.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||(peppi911@.hotmail.com) writes:
> Thanks for your answers.
> I'll have a look at the link.
> The WHY ist that once a day the cursor should affect all products and
> during the day every 5 minutes reclculate for inaktive ones.
> Thats the reason.

That does not explain the cursor - but could be that there is some
calculations are too complex to be carried out set-based. But there is
all reason to avoid the iteration if possible and handle all rows at
once. If there are many products this could mean serious reduction in
execution time.

On the other hand, there is enough information for me to tell that you
don't need any dynamic SQL. There are two possible solutions:

DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
AND (@.runforall = 1 OR fund_amt > 0)

If there is an index on the selection column for active products, it's
better to do:

IF @.runforall = 1
BEGIN
DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
END
ELSE
DECLARE mycur INSENSITIVE CURSOR FOR
SELECT ...
FROM ...
WHERE ...
AND fund_amt > 0
END

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

dec to varchar

Hello,
I need some help with string functions.
I have these statements:
declare @.text varchar(30), @.quantity dec(32,12), @.date datetime
select @.text = 'qwerty', @.quantity = -123.123456, @.date = getdate()
select @.text + ';' + convert(varchar, @.quantity) + ';' + convert(char(10),
@.date, 121) as string
They give this result:
string
---
qwerty;-123.123456000000;2005-04-12
@.quantity is shown as -123.123456000000. Is there an easy way to get rid of
those tailing zeros?
EskoSorry, that does not help me.
My quantities can have any number of decimal digits between 0 and 12 and I
always want to get rid of all tailing zeros.
So 1.000000000000 should be shown as "1", -33.123456789010 as
"-33.12345678901", -0.112233440000 as "-0.11223344" and so on.
"mark baekdal" wrote:

> can you do this?
> declare @.text varchar(30), @.quantity dec(32,6), @.date datetime
> select @.text = 'qwerty', @.quantity = -123.123456, @.date = getdate()
> select @.text + ';' + convert(varchar, @.quantity) + ';' + convert(char(10)
,
> @.date, 121) as string
>|||or maybe this...
declare @.text varchar(30), @.quantity dec(32,12), @.date datetime
select @.text = 'qwerty', @.quantity = -123.123456, @.date = getdate()
select @.text + ';' + replace(rtrim(replace(convert(varchar,@.q
uantity),'0','
')),' ','0') + ';' + convert(char(10),
@.date, 121) as string
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Esko" wrote:

> Hello,
> I need some help with string functions.
> I have these statements:
> declare @.text varchar(30), @.quantity dec(32,12), @.date datetime
> select @.text = 'qwerty', @.quantity = -123.123456, @.date = getdate()
> select @.text + ';' + convert(varchar, @.quantity) + ';' + convert(char(10)
,
> @.date, 121) as string
> They give this result:
> string
> ---
> qwerty;-123.123456000000;2005-04-12
> @.quantity is shown as -123.123456000000. Is there an easy way to get rid
of
> those tailing zeros?
> Esko
>|||the next query should, just watch the formatting as it changed in the post.
The replace function, replaces '0' with a single space ' ' and then trims th
e
result and then fills any gaps with '0', so this (as far as I've tested)
always works?
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Esko" wrote:
> Sorry, that does not help me.
> My quantities can have any number of decimal digits between 0 and 12 and I
> always want to get rid of all tailing zeros.
> So 1.000000000000 should be shown as "1", -33.123456789010 as
> "-33.12345678901", -0.112233440000 as "-0.11223344" and so on.
>
> "mark baekdal" wrote:
>