more mystery on (local) vs localhost

Hi,
There are known issues and bugs filed against the use of 'localhost'.
You should always use '(local)' in your connection string.
Is there any specific reason to use '(local)' instead of 'localhost'?
Thanks in advance,
PI
PING PING !!!
ipramod@.gmail.com wrote:
> Hi,
> There are known issues and bugs filed against the use of 'localhost'.
> You should always use '(local)' in your connection string.
> Is there any specific reason to use '(local)' instead of 'localhost'?
> Thanks in advance,
> PI

More Bytes returned from Image Data Type

I have a field set to Image data type, and store a long string(possible othe
r
objects)into it with size 5663, and returned with 5663+28 bytes.
These strings wil be deserialized after retrieving. However failde because
the bytes seems to include some extra bytes with \0 and 0x01 and have 28 mor
e
bytes ahead of the original string.
How could I deal with it? Just remove the first 28 bytes? It this method
formal? I had thought the returned bytes should have the same size with the
one I stored into, it seems not, am I right?
Thanks.BOL talks about "BLOBs and OLE Objects" and the normal process for images wa
s
to read in "chunks" (e.g. Sussman's ADO 2.6 Wrox book), but now should use
Stream object instead - check your doc [you omitted to say what
language/version you were using]
HTH
Dick
"zhaounknown" wrote:

> I have a field set to Image data type, and store a long string(possible ot
her
> objects)into it with size 5663, and returned with 5663+28 bytes.
> These strings wil be deserialized after retrieving. However failde because
> the bytes seems to include some extra bytes with \0 and 0x01 and have 28 m
ore
> bytes ahead of the original string.
> How could I deal with it? Just remove the first 28 bytes? It this method
> formal? I had thought the returned bytes should have the same size with th
e
> one I stored into, it seems not, am I right?
> Thanks.|||I am using C#, in .Net framework 1.1.
What documentation I shoudl refer to, since I can't find one addressing
this, partly because I am new to the BLOB data type.
However, how the underlying BLOB field is implemented doesn't matter to the
queried result for a BLOB field, is that right?
Please give more detailed guidance for this.
Thank you very much.
"Dick in UK" wrote:
> BOL talks about "BLOBs and OLE Objects" and the normal process for images
was
> to read in "chunks" (e.g. Sussman's ADO 2.6 Wrox book), but now should use
> Stream object instead - check your doc [you omitted to say what
> language/version you were using]
> HTH
> Dick
> "zhaounknown" wrote:
>

Months string

can sum one help with this:
I want 3 strings what represent the last three months from today's date inclusive.
for eg: if today is november 19th, 2003.
How would i get 3 strings as '2003_11', '2003_10', '2003_09'
Thanks a million.declare @.DateValue datetime
set @.DateValue = getdate()

select replace(convert(varchar(7), @.DateValue, 120), '-', '_')
select replace(convert(varchar(7), dateadd(m, -1, @.DateValue), 120), '-', '_')
select replace(convert(varchar(7), dateadd(m, -2, @.DateValue), 120), '-', '_')

You can ignore the REPLACE functions if you are picky about the format returned, but SQL Server does not have a datetime format using underscore characters.

blindman|||Thanks for the reply appreciate it.

Well this is what i'm doing with these strings:

I have monthly tables named as 'Tablename_yyyy_mm' etc.
I want to make a view that will capture the current months table and the last 3 months data.
for eg: if today is november 19th, 2003.
The view should capture 'Tablename_2003_11', 'Tablename_2003_10', 'Tablename_2003_09' tables
if today is jan 01,2003
The view should capture 'Tablename_2003_01', 'Tablename_2002_12', 'Tablename_2002_11' tables

Thanks a lot.|||9 times out of 10 this is a bad database design. Unless you are dealing with terabytes of data, it is better to create a single table with 1 extra column indicating the appropriate month. Easier to code, and generally more efficient.

blindman|||Originally posted by blindman
9 times out of 10 this is a bad database design. Unless you are dealing with terabytes of data, it is better to create a single table with 1 extra column indicating the appropriate month. Easier to code, and generally more efficient.

blindman

Well the original table was getting unmanagable, ie, it had about 160 million records and was growing fast. That why archiving the table into monthly tables was adopted. Now the original table will be split into monthyl tables and views are created for say each quarter, annual etc.|||Had you fully exhausted all the other possibilities for performance improvement?

Drives.
Processors.
Memory.
Indexing.
Normalization.
Pre-aggregation.
Query optimization.

Magic 8 Ball says outlook not good.

blindman|||Yes, indexing would take alot of space itself (almost the same as the table). The table is being used for reporting purposes so most of the time previous years data is not even touched, but still it's there in one big table. The whole process became slow due to the size of the table. Memory is not an issue. The current year's data is being used most often so monthly tables would give us more flexibility in viewing data.|||Did you try a 12 month partitioned view before this dynamic creation thing?

Or how about 13...make luck 13 an archive of infrequently used data, and set up an archive method...|||yes the 12 month archiving was thought of too.
for reporting etc. last 6 months data could be required. that that would make the 12 or 13 month archival method bogus. the monthly tables would be most flexible.

Another question: Is there a way to get the records counts for all the tables on (server a) and save those counts on a table in server B. And we r running this script or proc from server b.

server a and server b or not on a trusted connection but a password and userid are given.|||The 13th "month" would all previous yeard data...then you create a partitioned view to see all of the data

As for counts

Did you set up a linked server?|||Well the original table has data from jan 2001 till present.

for counts

i'm using openrowset(...) to get all the table names on server a. Would linked server be better? if so how would u setup linked servers? ne sample code would be helpful

thx|||I have setup linked servers, but how would you get the tables counts of all the tables on the linked server?|||Do you mean like SELECT COUNT(*) FROM myTable99?

Theres alos sp_spaceused, and a system table contains the info...

All of which (except SELECT COUNT(*)) need to statistics run to make sure the current..|||I cant do the select count(*) becuase in the sysservers tabe the linked i added has a null value in the srvnetname column. But all the sp_columns_ex and sp_tables_ex command work.

month ('jan','feb',...) string to date conversion fails

I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime.
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:

Derived Column [73]]

Error: The component "Derived Column" failed because error code

0xC0049064 occurred, and the error row disposition on "output column"...

I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ?

srem wrote:

I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime.
I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4))
However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails.
I have also tried inverting month and day, year/month/day but all with the same result:

Derived Column [73]] Error: The component "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "output column"...

I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ?

Its not an ideal situation but if you want to achieve this in a single derived column component what you will need to do is build some nested conditional operators. Its a bit of a fraught process to begin with but once you get the hang of it it isn't too bad.

I hope Microsoft give us the ability to extend the expression language in the next version by allowing us to build our own custom expression functions.

-Jamie

|||I suppose you could build a quick lookup table using a simple stored procedure. Then all you'd have to do is join on that "Jan 02 2005" column to get you a real date field in return.

Might even work better than doing all of the CASTs and conditional logic tests.|||

If you know exactly what the month strngs are, and they are all 3 chars long, you could do something like the following to get month number:

(FINDSTRING(month, "JANFEBMARAPR....DEC",1) + 2 ) / 3

If there are multiple options, or the lengths are irregular you could still do something similar, but it might not be worth it anymore over the nested conditionals.