Broken Subscriptions Due to Empty Table

Hi,

I was wondering what the best way was to deal with subscriptions breaking due to an empty table. I have subscriptions that people have scheduled to go out daily, but on certain days the table may be empty, in this case the subscription doesn't read the parameters for the report and then the subscription breaks.

My original solution involved creating a #temp table with the same columns as the original table and inserting one row into it which I'd union with the original table, this row in the temp table had all its values set to 0. The solution worked when I ran it in SQL Server Management Studio but it seems SRS doesn't like the INSERT INTO statement, which is the error I get, but I've read on these forums that it doesn't like #temp tables either. I proceeded to use a stored procedure with all the code in it, but I might have trouble filtering on multi value parameters, because at times these parameter lists get real big, plus I have to do this for multiple reports and don't want to get into creating stored procedures for each report.

Following is what the code I used look something like that executes and does the job in Management Studio but not SRS. I'm mainly just looking for the easiest and cleanest way to do this, since it'll have to be done across multiple reports, so disregard the code if there's an easier way to do it. Thanks in advance.

create table #dummytable

(
name varchar(35),

country varchar(35),

idnumber (int)

)

GO

insert into #dummytable (name),values('0');

select name, country, idnumber

from originaltable

where name in (@.name)

union

select name = 0, country = 0, idnumber = 0

from #dummytable

drop #dummytable

Another solution might be to do a union with a single known row that will always be delivered.

If the delivery query returns 0 rows, what would you want the subscription to do?

Thanks,

-Lukasz

|||

>> SRS doesn't like the INSERT

Instead of creating a temp table and INSERTing, why not just do a SELECT of literals?

Code Snippet

SELECT Name, Country, idnumber FROM

OriginalTable WHERE Name IN (@.Name)

UNION

SELECT SPACE(0),SPACE(0), 0

>L<

|||

Thank you Lisa, that does get the job done, much appreciated.

breaking web service?

I currently have a SQL 2000 RS SP2 installation and upgraded the back end to
2005. I think I read somewhere that the web service is not compatible
anymore? I am using the web services and don't want to have to change any
code if I don't have to. Will my 2000 WS implementation continue to work
after I install SSRS 2005?
Thanks
ScottHi Scott,
As for the SQL Server reporting service, the webservice interface should
definitely change from version 2000 to version 2005 as Reporting service
2005 has involved some new objects and methods signatures. A simple way to
check this is compare the WSDL service description of the two ones and look
for the service interface definiations. If you want to migrate your
application from RS 2000 to RS 2005, I think it is necessary to regenerate
the service proxy and use the new proxy methods.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Breaking up sql server backups into smaller files

Hey guys,

I'm wondering how most people manage very very large backups. What is the best approach to breaking up the backup files if you're restricted to a drive size (450gig in my case). I unix, you can pipe the backup to gzip and split, I'm not sure how the same thing could be accomplished in windows.

Thanks,
-KilkaHave you looked into backing up filegroups or doing differential backups?|||Hey Thrasymachus,

I had a look and neither of those two would solve the problem. In order to accomplish either of the two, you do need a full backup to recover from scratch. However, after a little investigation, I found out that you can split a full backup into a set of files, I previously never knew you could do this.

Check this out:
restore database warehouse_test FROM
disk = 'X:\warehouse_test1.bak',disk = 'X:\warehouse_test2.bak'

Peace,
-Kilka

Breaking up parametersT

One of my parameters is a particular date (in the datetime format 11/05/2002 12:00:00 AM) and I wanted to display only the month and year. How can I do this to just display November 2002 on my report.Try using the DATENAME function. Here's an example using the current date:

declare @.CurrentDate datetime
set @.CurrentDate = GetDate()

select DATENAME(m, @.CurrentDate) + ' ' + DATENAME(yy, @.CurrentDate)

|||Im using visual basic business intelligence. I did figure out the year...

=Year(Parameters!reportdate.Value)

I want the month to be displayed as November, January, July...etc not 11, 1, 7 '

=Month(Parameters!reportdate.Value) gives me 10 which I dont want

and

=MonthName(Parameters!reportdate.Value) gives me an error. I am using SQL 2000.

|||Try the code that I gave above. My output from running the statement

select DATENAME(m, @.CurrentDate) + ' ' + DATENAME(yy, @.CurrentDate)

is:

Column1
-
April 2006
No rows affected.
(1 row(s) returned)

That gives the month name (not number), as you requested...|||Have a look at:
http://msdn2.microsoft.com/en-US/library/ms174395(SQL.90).aspx

It's the documentation for the TSQL DATENAME command.|||

try the following

Switch(Month(Parameters!BeginDate.Value)=01,"January" & Year(Parameters!BeginDate.Value),Month(Parameters!BeginDate.Value)=02,"Feb" & Year( Parameters!BeginDate.Value) )

|||The DATENAME function should also work in SQL Server 2000 - have a look here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_1dph.asp

Also have a look at this article for some further examples:
http://www.sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Breaking up parameters

One of my parameters is a particular date (in the datetime format 11/05/2002 12:00:00 AM) and I wanted to display only the month and year. How can I do this to just display November 2002 on my report.Try using the DATENAME function. Here's an example using the current date:

declare @.CurrentDate datetime
set @.CurrentDate = GetDate()

select DATENAME(m, @.CurrentDate) + ' ' + DATENAME(yy, @.CurrentDate)

|||Im using visual basic business intelligence. I did figure out the year...

=Year(Parameters!reportdate.Value)

I want the month to be displayed as November, January, July...etc not 11, 1, 7 '

=Month(Parameters!reportdate.Value) gives me 10 which I dont want

and

=MonthName(Parameters!reportdate.Value) gives me an error. I am using SQL 2000.

|||Try the code that I gave above. My output from running the statement

select DATENAME(m, @.CurrentDate) + ' ' + DATENAME(yy, @.CurrentDate)

is:

Column1
-
April 2006
No rows affected.
(1 row(s) returned)

That gives the month name (not number), as you requested...|||Have a look at:
http://msdn2.microsoft.com/en-US/library/ms174395(SQL.90).aspx

It's the documentation for the TSQL DATENAME command.|||

try the following

Switch(Month(Parameters!BeginDate.Value)=01,"January" & Year(Parameters!BeginDate.Value),Month(Parameters!BeginDate.Value)=02,"Feb" & Year( Parameters!BeginDate.Value) )

|||The DATENAME function should also work in SQL Server 2000 - have a look here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_1dph.asp

Also have a look at this article for some further examples:
http://www.sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Breaking up a Table

I hope I can get this across clearly.

I have a table that needs to be broken into 3 tables.
Col1 Col2 Col3 Col4 Col5 Col6 Col7

Col1 and Col2 need to go into LookupTable1
Col3 and Col4 into LookupTable2

If Col5 is twice the width... haha just kidding...

so Col5 and Col6 go into LookupTable3

There is a 4th table which is made up of foreign keys which are the PK of
LookupTable1,2,3

My questions is, how to get the data from the columns of each row and add it
to its respective lookuptable
and sequentially step throw the table to repeat the above step until I've
processed each row

thanks folks

T.BThe Bear wrote:
> I hope I can get this across clearly.
> I have a table that needs to be broken into 3 tables.
> Col1 Col2 Col3 Col4 Col5 Col6 Col7
> Col1 and Col2 need to go into LookupTable1
> Col3 and Col4 into LookupTable2
> If Col5 is twice the width... haha just kidding...
> so Col5 and Col6 go into LookupTable3
> There is a 4th table which is made up of foreign keys which are the
> PK of LookupTable1,2,3
> My questions is, how to get the data from the columns of each row and
> add it to its respective lookuptable
> and sequentially step throw the table to repeat the above step until
> I've processed each row
> thanks folks
> T.B

Since your DDL is a mystery, I'll assume col1 and col2 are an
ID/Description combo:

Insert Into LookupTable1 (
col1,
col2 )
Select DISTINCT Col1, Col2
From Table

etc...

--
David Gugick
Imceda Software
www.imceda.com|||What's a "lookup table"? No such thing in any relational database.
There is only one kind of table.

I guess the following is what you want. There should be absolutely no
reason to do this sequentially row by row.

INSERT INTO Table1 (col1, col2)
SELECT DISTINCT col1, col2
FROM YourTable
WHERE col1 IS NOT NULL
AND col2 IS NOT NULL

INSERT INTO Table2 (col3, col4)
SELECT DISTINCT col3, col4
FROM YourTable
WHERE col3 IS NOT NULL
AND col4 IS NOT NULL

... etc

INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
FROM YourTable AS T0
JOIN Table1 AS T1
ON T0.col1 = T1.col1
AND T0.col2 = T1.col2
JOIN Table2 AS T2
ON T0.col3 = T2.col3
AND T0.col4 = T2.col4
... etc

--
David Portas
SQL Server MVP
--|||Thanks folks....

Lookup Table was a term used to help with the understanding of the question

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114019514.957668.75170@.f14g2000cwb.googlegro ups.com...
> What's a "lookup table"? No such thing in any relational database.
> There is only one kind of table.
> I guess the following is what you want. There should be absolutely no
> reason to do this sequentially row by row.
> INSERT INTO Table1 (col1, col2)
> SELECT DISTINCT col1, col2
> FROM YourTable
> WHERE col1 IS NOT NULL
> AND col2 IS NOT NULL
> INSERT INTO Table2 (col3, col4)
> SELECT DISTINCT col3, col4
> FROM YourTable
> WHERE col3 IS NOT NULL
> AND col4 IS NOT NULL
> .. etc
> INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
> SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
> FROM YourTable AS T0
> JOIN Table1 AS T1
> ON T0.col1 = T1.col1
> AND T0.col2 = T1.col2
> JOIN Table2 AS T2
> ON T0.col3 = T2.col3
> AND T0.col4 = T2.col4
> ... etc
> --
> David Portas
> SQL Server MVP
> --

Breaking up a Table

I hope I can get this across clearly.
I have a table that needs to be broken into 3 tables.
Col1 Col2 Col3 Col4 Col5 Col6 Col7
Col1 and Col2 need to go into LookupTable1
Col3 and Col4 into LookupTable2
If Col5 is twice the width... haha just kidding...
so Col5 and Col6 go into LookupTable3
There is a 4th table which is made up of foreign keys which are the PK of
LookupTable1,2,3
My questions is, how to get the data from the columns of each row and add it
to its respective lookuptable
and sequentially step throw the table to repeat the above step until I've
processed each row
thanks folks
T.BThe Bear wrote:
> I hope I can get this across clearly.
> I have a table that needs to be broken into 3 tables.
> Col1 Col2 Col3 Col4 Col5 Col6 Col7
> Col1 and Col2 need to go into LookupTable1
> Col3 and Col4 into LookupTable2
> If Col5 is twice the width... haha just kidding...
> so Col5 and Col6 go into LookupTable3
> There is a 4th table which is made up of foreign keys which are the
> PK of LookupTable1,2,3
> My questions is, how to get the data from the columns of each row and
> add it to its respective lookuptable
> and sequentially step throw the table to repeat the above step until
> I've processed each row
> thanks folks
> T.B
Since your DDL is a mystery, I'll assume col1 and col2 are an
ID/Description combo:
Insert Into LookupTable1 (
col1,
col2 )
Select DISTINCT Col1, Col2
From Table
etc...
David Gugick
Imceda Software
www.imceda.com|||What's a "lookup table"? No such thing in any relational database.
There is only one kind of table.
I guess the following is what you want. There should be absolutely no
reason to do this sequentially row by row.
INSERT INTO Table1 (col1, col2)
SELECT DISTINCT col1, col2
FROM YourTable
WHERE col1 IS NOT NULL
AND col2 IS NOT NULL
INSERT INTO Table2 (col3, col4)
SELECT DISTINCT col3, col4
FROM YourTable
WHERE col3 IS NOT NULL
AND col4 IS NOT NULL
.. etc
INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
FROM YourTable AS T0
JOIN Table1 AS T1
ON T0.col1 = T1.col1
AND T0.col2 = T1.col2
JOIN Table2 AS T2
ON T0.col3 = T2.col3
AND T0.col4 = T2.col4
.. etc
David Portas
SQL Server MVP
--|||Thanks folks....
Lookup Table was a term used to help with the understanding of the question
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114019514.957668.75170@.f14g2000cwb.googlegroups.com...
> What's a "lookup table"? No such thing in any relational database.
> There is only one kind of table.
> I guess the following is what you want. There should be absolutely no
> reason to do this sequentially row by row.
> INSERT INTO Table1 (col1, col2)
> SELECT DISTINCT col1, col2
> FROM YourTable
> WHERE col1 IS NOT NULL
> AND col2 IS NOT NULL
> INSERT INTO Table2 (col3, col4)
> SELECT DISTINCT col3, col4
> FROM YourTable
> WHERE col3 IS NOT NULL
> AND col4 IS NOT NULL
> .. etc
> INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
> SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
> FROM YourTable AS T0
> JOIN Table1 AS T1
> ON T0.col1 = T1.col1
> AND T0.col2 = T1.col2
> JOIN Table2 AS T2
> ON T0.col3 = T2.col3
> AND T0.col4 = T2.col4
> ... etc
> --
> David Portas
> SQL Server MVP
> --
>

Breaking up a string column into multiple records

Hopefully someone can help me. I have a table of records with the following fields:

AnswerID (int)
MultipleChoiceMultipleAnswer (varchar)
QuestionID (int)

now the multipleChoiceMultipleAnswer field data is in the following format:

1234,5678,99867
2345,456,7891

I want to break that field up into multiple records using the same AnswerID and QuestionID for each new multiple choice answer, so it would look like this:

10001 1234 9999
10001 5678 9999
10001 99867 9999
10002 2345 9998
10002 456 9998
10002 7891 9998

Is there an optimized method of doing this without using a cursor to iterate through each record?

Any help would be greatly appreciated.

ThanksLook at the following link - if you need something else, let me know -

link (http://dbforums.com/showthread.php?threadid=586248)

Breaking the 8kb barrier on UDT

Is it possible by any kind of workaround to break the 8kb limit on user-defines datatypes?

My datatype can contain an arbitrary number of double-precision points meaning that I in best case only can store 512 points (2 x 8 x 512). there's a few extra bytes used for something else, but this is roughly the maximum, which is far from what I in many cases need. I serialize the object myself to ensure that I only store what I really need.

Not really, if you still want to have your UDT in the database.

What you could do is to send down to the database and a SQLCLR proc/function a binary blob and insert that into the db in a varbinary(max) field. On the client you would then retrieve the binary and re-populate intio your type.

Niels

Breaking Replication??

Hi All
I have servers A, B and C with databases. Server A is a Publisher and Server
B is the Distributor/Subscriber. Server C is a Subscriber. Server A
replicates 7 publications to both server B and C. Initially when the
publications were very small, there were snapshot replications from A to both
B and C. Thereafter there have been transactional replications. The total
size of the databases has grown to about 30G.
My problem is I want to replace server C with a new server D. I have only 12
hrs to do this. If I do a snapshot replication to server D, it might take me
about 60hrs which I can’t do as this has to be done on a Sunday and ready for
Monday morning. I have a 150K pipe. I would like to avoid breaking the
replication on server B if possible. How can I do this replication within
12hours without breaking replication on server B?
Currently:
A>>>B
A>>>C
Future:
A>>>B
A>>>D
Thank you in advance.
You could create the snapshot, zip it up (WinZip 9.0), ftp, unzip and
restore, then specify an alternative snapshot location when initializing
server D. Alternatively you could zip up a backup of the database, ftp,
unzip restore then synchronize any changes to the data (if it is possible
there could be some). Prevent any further changes then do a nosync
initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Since server D is a new server, why don't you just set this new server as an
additional subscriber while you are still replicating to server C? Once D
has received the snapshot and is replicating normally you can pull server C.
I'm not sure how far away from your datacenter server D will be, but you
could also do the snapshot stuff on the local network (where server A&B are)
and then ship it to it's final location. One sticking point may be if you
are planning on using the same machine name for server D that you have for
server C, but it may not be an issue if you are using anonymous subscribers.
"MittyKom" wrote:

> Hi All
> I have servers A, B and C with databases. Server A is a Publisher and Server
> B is the Distributor/Subscriber. Server C is a Subscriber. Server A
> replicates 7 publications to both server B and C. Initially when the
> publications were very small, there were snapshot replications from A to both
> B and C. Thereafter there have been transactional replications. The total
> size of the databases has grown to about 30G.
> My problem is I want to replace server C with a new server D. I have only 12
> hrs to do this. If I do a snapshot replication to server D, it might take me
> about 60hrs which I can’t do as this has to be done on a Sunday and ready for
> Monday morning. I have a 150K pipe. I would like to avoid breaking the
> replication on server B if possible. How can I do this replication within
> 12hours without breaking replication on server B?
> Currently:
> A>>>B
> A>>>C
> Future:
> A>>>B
> A>>>D
> Thank you in advance.
>

Breaking Point

Hi,
We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
2003 (Standard Ed, 32bit) for our company.
I would like to know the breaking point of the following so we can foresee
any problems and perhaps be able to avoid them.
There will be a huge amount of pictures to be stored in our server. Where
will pics be stored? as BLOBS in SQL or as files?
I am new to this and appreciate any info. We have been ripped off by an IT
consultant before so am trying to learn as well.
Thanks,
Ian
Philippines
It will not be good practice to place huge pictures in BLOBs in sql server.if
possible you can arrange an file or image server and mapp them to application.
"Ian" wrote:

> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>
|||Thanks,
Do you know of any breaking points with SQL Server 2005 and WIndows Server
2003?
Ian
"Khwaja Arshaduddin" wrote:
[vbcol=seagreen]
> It will not be good practice to place huge pictures in BLOBs in sql server.if
> possible you can arrange an file or image server and mapp them to application.
>
> "Ian" wrote:
|||Why is it not good practive to place pics in BLOBs?
Thanks again,
Ian
"Khwaja Arshaduddin" wrote:
[vbcol=seagreen]
> It will not be good practice to place huge pictures in BLOBs in sql server.if
> possible you can arrange an file or image server and mapp them to application.
>
> "Ian" wrote:
|||As i posted earlier LARGE image file should not be loaded that is becuase
BLOBs store picture as text datatype u can find datatype in BOL .there will
be a severe impact on performance of database
"Ian" wrote:
[vbcol=seagreen]
> Why is it not good practive to place pics in BLOBs?
> Thanks again,
> Ian
> "Khwaja Arshaduddin" wrote:
|||> As i posted earlier LARGE image file should not be loaded that is becuase
> BLOBs store picture as text datatype u can find datatype in BOL .there
> will
> be a severe impact on performance of database
No , SQL Server stores it in BINARY format
"Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in
message news:380520D9-7BEF-4CFF-82F8-486304BA5B38@.microsoft.com...[vbcol=seagreen]
> As i posted earlier LARGE image file should not be loaded that is becuase
> BLOBs store picture as text datatype u can find datatype in BOL .there
> will
> be a severe impact on performance of database
> "Ian" wrote:
|||Hi
You may want to read
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
As far as breaking point this is will be significntly dependent on the
hardware and application, therefore running your own performance/stress tests
is about the only way you are really going to have some idea of what the
system is capable of. Products such as LoadRunner, Rational Performance
Tester, Visual Studio Team Edition for Software Testers
http://msdn2.microsoft.com/en-us/teamsystem/aa718823.aspx or Visual Studio
Team Suite can all help you performance test your web application(s).
John
"Ian" wrote:

> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>
|||yes uri is correct it is binary... thanks uri
"Uri Dimant" wrote:

> No , SQL Server stores it in BINARY format
>
>
> "Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in
> message news:380520D9-7BEF-4CFF-82F8-486304BA5B38@.microsoft.com...
>
>
|||At my last job, we used the database to store tif images. The tifs were
images of mandates which had bank details on so it made sense to store them
in the database as opposed to the filesystem. We took steps to try and ensure
the best performance, such as putting the blob column into a seperate
filegroup which we put onto a seperate raid array to the main database. The
mandates were scanned in as tifs, inserted into the db, and were viewable
from an asp front end, and tbh the solution worked well.
From a performance pov it'll be down to the quality of product, and the spec
of the machine.. ..a decent storage solution for this is a must.. ..also
spend some time on capacity planning, you'll find the db will grow quickly so
spend some time setting it up properly from the start...
"Ian" wrote:

> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>
|||Ian:
You'll love my response. It's the same as pretty much all of my other
responses:
"It depends".
Do you need to store your images transactionally? That is, it
critically important that your images are there when the database SAYS
they're there? If so, put 'em in the database.
Do you need to read the files from something like a Web server which
serves up a lot of other static content? If so, lean towards the
filesystem.
How big are these files? "Huge" means something very different these
days vs. 5 years ago. What are your size projections? A couple of
gigs? No problem, either way. A couple of terabytes? Do you want to
minimize the load on the database when retrieving images? How many
users are you going to support? These are all considerations.
Note that you'll have to be much more careful about how you size your
database files with the images inside the DB. I'd (sometimes, it
depends) recommend putting the tables storing the images in a different
filegroup...
Also note that in SQL Server 2005, you should use VARBINARY(MAX) instead
of IMAGE. IMAGE and TEXT datatypes are deprecated as of the new version.
-Dave
Ian wrote:
> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>
-Dave Markle
http://www.markleconsulting.com/blog

Breaking Point

Hi,
We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
2003 (Standard Ed, 32bit) for our company.
I would like to know the breaking point of the following so we can foresee
any problems and perhaps be able to avoid them.
There will be a huge amount of pictures to be stored in our server. Where
will pics be stored? as BLOBS in SQL or as files?
I am new to this and appreciate any info. We have been ripped off by an IT
consultant before so am trying to learn as well.
Thanks,
Ian
PhilippinesIt will not be good practice to place huge pictures in BLOBs in sql server.i
f
possible you can arrange an file or image server and mapp them to applicatio
n.
"Ian" wrote:

> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>|||If you can, place pictures in filesystem and links to them in database.
MC
"Ian" <Ian@.discussions.microsoft.com> wrote in message
news:DC7B1026-6DB6-493A-9B99-8CF719840007@.microsoft.com...
> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>|||Thanks,
Do you know of any breaking points with SQL Server 2005 and WIndows Server
2003?
Ian
"Khwaja Arshaduddin" wrote:
[vbcol=seagreen]
> It will not be good practice to place huge pictures in BLOBs in sql server
.if
> possible you can arrange an file or image server and mapp them to applicat
ion.
>
> "Ian" wrote:
>|||Why is it not good practive to place pics in BLOBs?
Thanks again,
Ian
"Khwaja Arshaduddin" wrote:
[vbcol=seagreen]
> It will not be good practice to place huge pictures in BLOBs in sql server
.if
> possible you can arrange an file or image server and mapp them to applicat
ion.
>
> "Ian" wrote:
>|||As i posted earlier LARGE image file should not be loaded that is becuase
BLOBs store picture as text datatype u can find datatype in BOL .there will
be a severe impact on performance of database
"Ian" wrote:
[vbcol=seagreen]
> Why is it not good practive to place pics in BLOBs?
> Thanks again,
> Ian
> "Khwaja Arshaduddin" wrote:
>|||> As i posted earlier LARGE image file should not be loaded that is becuase
> BLOBs store picture as text datatype u can find datatype in BOL .there
> will
> be a severe impact on performance of database
No , SQL Server stores it in BINARY format
"Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in
message news:380520D9-7BEF-4CFF-82F8-486304BA5B38@.microsoft.com...[vbcol=seagreen]
> As i posted earlier LARGE image file should not be loaded that is becuase
> BLOBs store picture as text datatype u can find datatype in BOL .there
> will
> be a severe impact on performance of database
> "Ian" wrote:
>|||Hi
You may want to read
http://databases.aspfaq.com/databas...filesystem.html
As far as breaking point this is will be significntly dependent on the
hardware and application, therefore running your own performance/stress test
s
is about the only way you are really going to have some idea of what the
system is capable of. Products such as LoadRunner, Rational Performance
Tester, Visual Studio Team Edition for Software Testers
http://msdn2.microsoft.com/en-us/te...m/aa718823.aspx or Visual Studio
Team Suite can all help you performance test your web application(s).
John
"Ian" wrote:

> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>|||yes uri is correct it is binary... thanks uri
"Uri Dimant" wrote:

> No , SQL Server stores it in BINARY format
>
>
> "Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote i
n
> message news:380520D9-7BEF-4CFF-82F8-486304BA5B38@.microsoft.com...
>
>|||At my last job, we used the database to store tif images. The tifs were
images of mandates which had bank details on so it made sense to store them
in the database as opposed to the filesystem. We took steps to try and ensur
e
the best performance, such as putting the blob column into a seperate
filegroup which we put onto a seperate raid array to the main database. The
mandates were scanned in as tifs, inserted into the db, and were viewable
from an asp front end, and tbh the solution worked well.
From a performance pov it'll be down to the quality of product, and the spec
of the machine.. ..a decent storage solution for this is a must.. ..also
spend some time on capacity planning, you'll find the db will grow quickly s
o
spend some time setting it up properly from the start...
"Ian" wrote:

> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>

Breaking Point

Hi,
We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
2003 (Standard Ed, 32bit) for our company.
I would like to know the breaking point of the following so we can foresee
any problems and perhaps be able to avoid them.
There will be a huge amount of pictures to be stored in our server. Where
will pics be stored? as BLOBS in SQL or as files?
I am new to this and appreciate any info. We have been ripped off by an IT
consultant before so am trying to learn as well.
Thanks,
Ian
PhilippinesIt will not be good practice to place huge pictures in BLOBs in sql server.if
possible you can arrange an file or image server and mapp them to application.
"Ian" wrote:
> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>|||If you can, place pictures in filesystem and links to them in database.
MC
"Ian" <Ian@.discussions.microsoft.com> wrote in message
news:DC7B1026-6DB6-493A-9B99-8CF719840007@.microsoft.com...
> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>|||Thanks,
Do you know of any breaking points with SQL Server 2005 and WIndows Server
2003?
Ian
"Khwaja Arshaduddin" wrote:
> It will not be good practice to place huge pictures in BLOBs in sql server.if
> possible you can arrange an file or image server and mapp them to application.
>
> "Ian" wrote:
> > Hi,
> >
> > We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> > 2003 (Standard Ed, 32bit) for our company.
> > I would like to know the breaking point of the following so we can foresee
> > any problems and perhaps be able to avoid them.
> >
> > There will be a huge amount of pictures to be stored in our server. Where
> > will pics be stored? as BLOBS in SQL or as files?
> >
> > I am new to this and appreciate any info. We have been ripped off by an IT
> > consultant before so am trying to learn as well.
> >
> > Thanks,
> >
> > Ian
> > Philippines
> >|||Why is it not good practive to place pics in BLOBs?
Thanks again,
Ian
"Khwaja Arshaduddin" wrote:
> It will not be good practice to place huge pictures in BLOBs in sql server.if
> possible you can arrange an file or image server and mapp them to application.
>
> "Ian" wrote:
> > Hi,
> >
> > We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> > 2003 (Standard Ed, 32bit) for our company.
> > I would like to know the breaking point of the following so we can foresee
> > any problems and perhaps be able to avoid them.
> >
> > There will be a huge amount of pictures to be stored in our server. Where
> > will pics be stored? as BLOBS in SQL or as files?
> >
> > I am new to this and appreciate any info. We have been ripped off by an IT
> > consultant before so am trying to learn as well.
> >
> > Thanks,
> >
> > Ian
> > Philippines
> >|||As i posted earlier LARGE image file should not be loaded that is becuase
BLOBs store picture as text datatype u can find datatype in BOL .there will
be a severe impact on performance of database
"Ian" wrote:
> Why is it not good practive to place pics in BLOBs?
> Thanks again,
> Ian
> "Khwaja Arshaduddin" wrote:
> > It will not be good practice to place huge pictures in BLOBs in sql server.if
> > possible you can arrange an file or image server and mapp them to application.
> >
> >
> > "Ian" wrote:
> >
> > > Hi,
> > >
> > > We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> > > 2003 (Standard Ed, 32bit) for our company.
> > > I would like to know the breaking point of the following so we can foresee
> > > any problems and perhaps be able to avoid them.
> > >
> > > There will be a huge amount of pictures to be stored in our server. Where
> > > will pics be stored? as BLOBS in SQL or as files?
> > >
> > > I am new to this and appreciate any info. We have been ripped off by an IT
> > > consultant before so am trying to learn as well.
> > >
> > > Thanks,
> > >
> > > Ian
> > > Philippines
> > >|||> As i posted earlier LARGE image file should not be loaded that is becuase
> BLOBs store picture as text datatype u can find datatype in BOL .there
> will
> be a severe impact on performance of database
No , SQL Server stores it in BINARY format
"Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in
message news:380520D9-7BEF-4CFF-82F8-486304BA5B38@.microsoft.com...
> As i posted earlier LARGE image file should not be loaded that is becuase
> BLOBs store picture as text datatype u can find datatype in BOL .there
> will
> be a severe impact on performance of database
> "Ian" wrote:
>> Why is it not good practive to place pics in BLOBs?
>> Thanks again,
>> Ian
>> "Khwaja Arshaduddin" wrote:
>> > It will not be good practice to place huge pictures in BLOBs in sql
>> > server.if
>> > possible you can arrange an file or image server and mapp them to
>> > application.
>> >
>> >
>> > "Ian" wrote:
>> >
>> > > Hi,
>> > >
>> > > We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS
>> > > Server
>> > > 2003 (Standard Ed, 32bit) for our company.
>> > > I would like to know the breaking point of the following so we can
>> > > foresee
>> > > any problems and perhaps be able to avoid them.
>> > >
>> > > There will be a huge amount of pictures to be stored in our server.
>> > > Where
>> > > will pics be stored? as BLOBS in SQL or as files?
>> > >
>> > > I am new to this and appreciate any info. We have been ripped off by
>> > > an IT
>> > > consultant before so am trying to learn as well.
>> > >
>> > > Thanks,
>> > >
>> > > Ian
>> > > Philippines
>> > >|||Hi
You may want to read
http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html
As far as breaking point this is will be significntly dependent on the
hardware and application, therefore running your own performance/stress tests
is about the only way you are really going to have some idea of what the
system is capable of. Products such as LoadRunner, Rational Performance
Tester, Visual Studio Team Edition for Software Testers
http://msdn2.microsoft.com/en-us/teamsystem/aa718823.aspx or Visual Studio
Team Suite can all help you performance test your web application(s).
John
"Ian" wrote:
> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>|||yes uri is correct it is binary... thanks uri
"Uri Dimant" wrote:
> > As i posted earlier LARGE image file should not be loaded that is becuase
> > BLOBs store picture as text datatype u can find datatype in BOL .there
> > will
> > be a severe impact on performance of database
> No , SQL Server stores it in BINARY format
>
>
> "Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in
> message news:380520D9-7BEF-4CFF-82F8-486304BA5B38@.microsoft.com...
> > As i posted earlier LARGE image file should not be loaded that is becuase
> > BLOBs store picture as text datatype u can find datatype in BOL .there
> > will
> > be a severe impact on performance of database
> >
> > "Ian" wrote:
> >
> >> Why is it not good practive to place pics in BLOBs?
> >>
> >> Thanks again,
> >>
> >> Ian
> >>
> >> "Khwaja Arshaduddin" wrote:
> >>
> >> > It will not be good practice to place huge pictures in BLOBs in sql
> >> > server.if
> >> > possible you can arrange an file or image server and mapp them to
> >> > application.
> >> >
> >> >
> >> > "Ian" wrote:
> >> >
> >> > > Hi,
> >> > >
> >> > > We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS
> >> > > Server
> >> > > 2003 (Standard Ed, 32bit) for our company.
> >> > > I would like to know the breaking point of the following so we can
> >> > > foresee
> >> > > any problems and perhaps be able to avoid them.
> >> > >
> >> > > There will be a huge amount of pictures to be stored in our server.
> >> > > Where
> >> > > will pics be stored? as BLOBS in SQL or as files?
> >> > >
> >> > > I am new to this and appreciate any info. We have been ripped off by
> >> > > an IT
> >> > > consultant before so am trying to learn as well.
> >> > >
> >> > > Thanks,
> >> > >
> >> > > Ian
> >> > > Philippines
> >> > >
>
>|||At my last job, we used the database to store tif images. The tifs were
images of mandates which had bank details on so it made sense to store them
in the database as opposed to the filesystem. We took steps to try and ensure
the best performance, such as putting the blob column into a seperate
filegroup which we put onto a seperate raid array to the main database. The
mandates were scanned in as tifs, inserted into the db, and were viewable
from an asp front end, and tbh the solution worked well.
From a performance pov it'll be down to the quality of product, and the spec
of the machine.. ..a decent storage solution for this is a must.. ..also
spend some time on capacity planning, you'll find the db will grow quickly so
spend some time setting it up properly from the start...
"Ian" wrote:
> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>|||Ian:
You'll love my response. It's the same as pretty much all of my other
responses:
"It depends".
Do you need to store your images transactionally? That is, it
critically important that your images are there when the database SAYS
they're there? If so, put 'em in the database.
Do you need to read the files from something like a Web server which
serves up a lot of other static content? If so, lean towards the
filesystem.
How big are these files? "Huge" means something very different these
days vs. 5 years ago. What are your size projections? A couple of
gigs? No problem, either way. A couple of terabytes? Do you want to
minimize the load on the database when retrieving images? How many
users are you going to support? These are all considerations.
Note that you'll have to be much more careful about how you size your
database files with the images inside the DB. I'd (sometimes, it
depends) recommend putting the tables storing the images in a different
filegroup...
Also note that in SQL Server 2005, you should use VARBINARY(MAX) instead
of IMAGE. IMAGE and TEXT datatypes are deprecated as of the new version.
-Dave
Ian wrote:
> Hi,
> We recently bought MS SQL Server 2005 (Standard Ed, 32bit) and MS Server
> 2003 (Standard Ed, 32bit) for our company.
> I would like to know the breaking point of the following so we can foresee
> any problems and perhaps be able to avoid them.
> There will be a huge amount of pictures to be stored in our server. Where
> will pics be stored? as BLOBS in SQL or as files?
> I am new to this and appreciate any info. We have been ripped off by an IT
> consultant before so am trying to learn as well.
> Thanks,
> Ian
> Philippines
>
-Dave Markle
http://www.markleconsulting.com/blog

Breaking out data from a text field type

In my database there is a text field type that is used to enter street
address. This address could be a few lines long, each line with a
carriage return at the end.
Is there a way to search for these carriage returns and break out what
is in each line seperately?

Thanks.
Mike[posted and mailed, please reply in news]

Mike (mrea@.ohiotravelbag.com) writes:
> In my database there is a text field type that is used to enter street
> address. This address could be a few lines long, each line with a
> carriage return at the end.
> Is there a way to search for these carriage returns and break out what
> is in each line seperately?

Is that really the datatype text? That seems a bit over kill for a street
address. They would very rarely be over 8000 bytes. Or even 4000 if you
are using varchar.

The functions to use are substring and charindex. And char(13) for the
CRs. Or char(13) + char(10) if it's actually CR + LF. charindex does not
handle text beyond the varchar limit, but I don't think this would be
an issue.

You could also do:

SELECT @.adr = adr FROM tbl WHERE ..
SELECT str
FROM iter_charlist_to_table(@.adr, char(13))
ORDER BY listpos

You find this function on
http://www.sommarskog.se/arrays-in-...list-of-strings

Note that if you need to use char(13) + char(10) as delimiter, you
will have to change the function. (And not only the length of delimiter.)

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Breaking of Sub Report?

Hi All,

I have created a Report that include 10 other sub reports. The problem is that if there is not enough space for that sub report on the page, it goes to the next page. Also, if the content of sub report is huge, it removes the page header details so that it can fit into one page.

Is there any way that the sub report breaks on its own and there is no wastage of space? I guess that will solve my problem of page header details as well.

If anyone has worked on such issue, please let me know.

Any help will be greatly appreciated.

TIA!!!

Hi,

Anyone has got any clue?

Regards!!!

Breaking monthly values down into daily values

Hi all, I'm new to MDX and am getting very confused with a script.

I'm running into problems breaking down monthly measures to daily values. If I have a monthly measure of 50, I would like to divide it by the number of days in the month to come up with daily values.

I believe I have set the proper granularity for the measurement relationship against the time dimension and have added the following script to my MDX script:

[Date].[Date].Members = [Measures].CurrentMember / [Date].[Calendar].CurrentMember.Parent.Children.Count

When I submit a query like this:

SELECT [Measures].[Measurement Objective] ON 0,
MTD([Date].[Calendar].[Date].&[20070207]) ON 1
FROM [Cube]

Everything looks good. The query returns seven rows, each with a properly scaled version of the monthly measurement. However, when I write the following query to return a single MTD value:

SELECT [Measures].[Measurement Objective] ON 0
FROM [Cube]
WHERE MTD([Date].[Calendar].[Date].&[20070207])

It doesn't work. It gives me the error:

The MDX Function CURRENTMEMBER failed because the coordinate for the
'Calendar Year' attribute contains a set

I'm sure this is just a matter of me misunderstanding MDX. Any help would be appreciate.
Thanks,
Richard

This is caused by the fact that you have used the .CurrentMember function in your calculation, but then have used a set (the MTD function returns a set of date members) in the WHERE clause. This means that there is not a single current member.

By introducing the ability to have sets in the WHERE clause, Microsoft have added extra complexity to writing calculated members.

The following variation should do the trick:

Code Snippet

[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].Members), [Measures].CurrentMember / [Date].[Calendar].CurrentMember.Parent.Children.Count)

The EXISTING() function returns the set of date members in the current context (the month to date members in your example query) and the Generate() function effectively "loops" over these members calculating your original expression. In this case the [Date].[Calendar].CurrentMember is evaluated within the context of each iteration of the "loop".

|||Thanks for the reply Darren.

I tried out the script and it didn't work. I was getting a divide-by-zero error in the results. Then I tried a simpler version without the division by the number of days in the month:

[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].Members), [Measures].CurrentMember)

which, given what you outlined, should just return the original monthly value (which is 128 for the month I'm using.) However, when I run the query each day has a value of 11891189. When I use the MTD function, it returns a concatenation of 11891189 * the number of days in the MTD set. The underlying measure is an integer.

Everything you outlined seems logical, but I'm just not sure where the 11891189 is coming from. I'm also restricting the scope of the MDX script to the single measure I'm testing.

Cheers,
Richard
|||

Actually I think I am missing a [Date] and the reference needs to be [Date].[Date].[Date].members

eg

[Date].[Date].[Date].Members = GENERATE( EXISTING( [Date].[Date].[Date].Members), [Measures].CurrentMember)

because [Date] is the dimension, [Date].[Date] is the hierarchy (which includes [Date].[Date].[All] which I think where the 11891189 might be coming from) [Date].[Date].[Date] is the actual attribute level.|||Hi Darren, it still didn't work and I've been doing a lot of investigation into the problem. I see what you mean by the problems of sets within the WHERE clause.

Instead of taking my original approach, I decided to use the Time Intelligence wizard within AS2005 and the MDX script that it generated worked like a charm. The extra YTD/MTD/QTD members in the time dimension makes a lot of sense.

Thanks again for your help
Richard

Breaking down Total Hours worked into Day and Evening hours

I have data coming from a telephony system that keeps track of when an
employee makes a phone call to conduct a survey and which project number
is being billed for the time the employee spends on that phone call in a
MS SQL Server 2000 database (which I don't own).

The data is being returned to me in a view (see DDL for w_HR_Call_Log
below). I link to this view in MS access through ODBC to create a
linked table. I have my own view in Access that converts the integer
numbers for start and end date to Date/Time and inserts some other
information i need.

This data is eventually going to be compared with data from some
electronic timesheets for purposes of comparing entered hours vs hours
actually spent on the telephone, and the people that will be viewing the
data need the total time on the telephone as wall as that total broken
down by day/evening and weekend. Getting weekend durations is easy
enough (see SQL for qryTelephonyData below), but I was wondering if
anyone knew of efficient set-based methods for doing a day/evening
breakdown of some duration given a start date and end date (with the
day/evening boundary being 17:59:59)? My impression is that to do this
correctly (i.e., handle employees working in different time zones,
adjusting for DST, and figuring out what the boundary is for switching
from evening back to day) will require procedural code (probably in
Visual Basic or VBA).

However, if there are set-based algorithms that can accomplish it in
SQL, I'd like to explore those, as well. Can anyone give any pointers?
Thanks.

--
DDL for view in MS SQL 2000 database:

CREATE VIEW dbo.w_HR_Call_Log
AS
SELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username AS
Initials, dbo.billing.startdate, dbo.billing.startdate +
dbo.billing.duration AS EndDate,
dbo.billing.duration, dbo.projects.name AS
PrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,

dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,
dbo.billing.interactionid
FROM dbo.projects INNER JOIN
dbo.projectsphone INNER JOIN
dbo.users INNER JOIN
dbo.TRCUsers ON dbo.users.userid =
dbo.TRCUsers.UserID INNER JOIN
dbo.billing ON dbo.users.userid =
dbo.billing.userid ON dbo.projectsphone.projectid =
dbo.billing.projectid ON
dbo.projects.projectid = dbo.projectsphone.projectid
WHERE (dbo.billing.userid 0)
ORDER BY dbo.billing.startdate

I don't have acess to the tables, but the fields in the view come
through as the following data types:
WinsID - varchar(10)
Initials - varchar(30)
startdate - long integer (seconds since 1970-01-01 00:00:00)
enddate - long integer (seconds since 1970-01-01 00:00:00)
duration - long integer (enddate - startdate)
ProjID10 - varchar(15)
interactionid - varchar(255) (the identifier for this phone call)

MS Access SQL statement for qryTelephonyData (based on the view,
w_HR_Call_Log):
SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,
FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970
00:00:00')),"yyyy-mm-dd") AS HoursDate,
CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) AS
StartDT,
CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,
DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,
IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) AS
WeekendSeconds,
FROM dbo_w_HR_Call_Log
WHERE WinsID<>'0'Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

This data is eventually going to be compared with data from some
electronic timesheets for purposes of comparing entered hours vs hours
actually spent on the telephone, and the people that will be viewing the
data need the total time on the telephone as wall as that total broken
down by day/evening and weekend. Getting weekend durations is easy
enough (see SQL for qryTelephonyData below), but I was wondering if
anyone knew of efficient set-based methods for doing a day/evening
breakdown of some duration given a start date and end date (with the
day/evening boundary being 17:59:59)? My impression is that to do this
correctly (i.e., handle employees working in different time zones,
adjusting for DST, and figuring out what the boundary is for switching
from evening back to day) will require procedural code (probably in
Visual Basic or VBA).
>
However, if there are set-based algorithms that can accomplish it in
SQL, I'd like to explore those, as well. Can anyone give any pointers?

It sounds perfectly possible to do that set-based, provided there is
enough data. Mapping the hour to day/night may be best be done
through a table, so you can enter the table with the hour and get
back what part of the day it is. With a calendar table, you can also
use this for days, so that you can catch non-working days in the middle
of the week.

The time zone is a little more complicated, but provided that there is
a time zone available somewhere this should not be any problem. Assuming
that all times are stored in UTC (or some other time zone), just add the
time-zone offset to get the local time.

Quote:

Originally Posted by

CREATE VIEW dbo.w_HR_Call_Log
AS
SELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username AS
>...
ORDER BY dbo.billing.startdate

I would recommend that you take out that TOP 100 PERCENT and ORDER BY,
as it fills no purpose, but just results in extra query overhead.

If you want the data to be sorted that way, you need to apply an
ORDER BY clause when you retrieve it. In SQL 2000 it may seen that
when you say "SELECT ... FROM view" that you get the order anyway,
but that is mere chance, and on SQL 2005 that does typically not happen.

--
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|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

>This data is eventually going to be compared with data from some
>electronic timesheets for purposes of comparing entered hours vs hours
>actually spent on the telephone, and the people that will be viewing the
>data need the total time on the telephone as wall as that total broken
>down by day/evening and weekend. Getting weekend durations is easy
>enough (see SQL for qryTelephonyData below), but I was wondering if
>anyone knew of efficient set-based methods for doing a day/evening
>breakdown of some duration given a start date and end date (with the
>day/evening boundary being 17:59:59)? My impression is that to do this
>correctly (i.e., handle employees working in different time zones,
>adjusting for DST, and figuring out what the boundary is for switching
>from evening back to day) will require procedural code (probably in
>Visual Basic or VBA).
>>
>However, if there are set-based algorithms that can accomplish it in
>SQL, I'd like to explore those, as well. Can anyone give any pointers?

>
It sounds perfectly possible to do that set-based, provided there is
enough data. Mapping the hour to day/night may be best be done
through a table, so you can enter the table with the hour and get
back what part of the day it is. With a calendar table, you can also
use this for days, so that you can catch non-working days in the middle
of the week.

Thanks for taking the time to reply. I always appreciate your advice
here. I'm a little confused by your suggestion. What I have is a
duration (start datetime and end datetime). Would an "hour" to "part of
day" table still work with this data or would I have to convert the
start and end date into something else first? Do you have any pointers
to good tutorials on calendar tables (or is google my friend)? It's a
concept I haven't heard of before.

Quote:

Originally Posted by

The time zone is a little more complicated, but provided that there is
a time zone available somewhere this should not be any problem. Assuming
that all times are stored in UTC (or some other time zone), just add the
time-zone offset to get the local time.

As returned by the view, the startdate and enddate are integers (number
of seconds since 1970-01-01 00:00:00) so it's fairly simple to convert
to UTC.

Quote:

Originally Posted by

Quote:

Originally Posted by

>CREATE VIEW dbo.w_HR_Call_Log
>AS
>SELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username AS
>...
>ORDER BY dbo.billing.startdate

>
I would recommend that you take out that TOP 100 PERCENT and ORDER BY,
as it fills no purpose, but just results in extra query overhead.
>
If you want the data to be sorted that way, you need to apply an
ORDER BY clause when you retrieve it. In SQL 2000 it may seen that
when you say "SELECT ... FROM view" that you get the order anyway,
but that is mere chance, and on SQL 2005 that does typically not happen.

Thank you for the advice. I learned that fact a little while ago in
this very newsgroup. I don't own that particular view, though.|||Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

Thanks for taking the time to reply. I always appreciate your advice
here. I'm a little confused by your suggestion. What I have is a
duration (start datetime and end datetime). Would an "hour" to "part of
day" table still work with this data or would I have to convert the
start and end date into something else first?

I don't know. That is, I don't know what your business requirements are,
so I cannot answer. I made the simple assumption that only the start time
applied. If you want to split a call that started at 17:23 and ended at
18:14 into day and evening, I don't know in which way you want to split it.

<Standard rant>

Please post:

o CREATE TABLE(s) statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

That makes it possible to easily copy and paste to develop a tested
solution.

</Standard rant>

(It's not likely that it will be me this time though, as I'm
off for vacation tomorrow.)

Quote:

Originally Posted by

Do you have any pointers to good tutorials on calendar tables (or is
google my friend)? It's a concept I haven't heard of before.

http://www.aspfaq.com, search for calendar. Aaron has several entries
on them.

Essentially a calendar is a table with one row for each day, and then
you associate attributes to the days that are appropriate for your
business like IsWorkingDay.

--
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

Breaking down goals

Hi,

we have a fact-table, containing sales-goales. Each goal relates to a year and a sales-person. We want to break down this annual goals as follows:

If the sales is $ 1000 for 2007 then the goal for 2007-12-31 is $ 1000; for 2007-12-30 the goal would be $ 1000 / 365 * 364; for 2007-12-29 $ 1000 / 365 * 363 and so on.

How to accomplish this with SSAS ?

Thx and Regards,

Manfred

you could do something like

Measures.[Sales Goal] * (count(YTD([Time].[Day].CurrentMember)) / 365)

You would probably want to scope such a calculation so that it only occurred at the day level as it would not work at higher levels.

Another approach might be to add a "Day of Year" attribute or even a "YTD proportion" to your time dimension which does this calculation in the DSV, then you don't have to dynamically count the days all the time and you could do something like.

Measures.[Sales Goal] * [Time].[YTD Proportion].MemberValue

Breaking down goals

Hi,

we have a fact-table, containing sales-goales. Each goal relates to a year and a sales-person. We want to break down this annual goals as follows:

If the sales is $ 1000 for 2007 then the goal for 2007-12-31 is $ 1000; for 2007-12-30 the goal would be $ 1000 / 365 * 364; for 2007-12-29 $ 1000 / 365 * 363 and so on.

How to accomplish this with SSAS ?

Thx and Regards,

Manfred

you could do something like

Measures.[Sales Goal] * (count(YTD([Time].[Day].CurrentMember)) / 365)

You would probably want to scope such a calculation so that it only occurred at the day level as it would not work at higher levels.

Another approach might be to add a "Day of Year" attribute or even a "YTD proportion" to your time dimension which does this calculation in the DSV, then you don't have to dynamically count the days all the time and you could do something like.

Measures.[Sales Goal] * [Time].[YTD Proportion].MemberValue

Breaking data into 1500 byte chunks

Hi,

I have a text file (5 MB). It appears as a single line in a text editor. But actually it has records of 1500 byte length each.

I want to strip it down to 1500 byte records. So 1500*3500 = 5 MB (approx). The record size is always 1500 bytes.

Does anyone have a script that I can run on this file to achieve this break.

ThanksYou have 1 row of 5,000,000 bytes?

There is no CR LF chars?

Are there any delimiters?|||No there are no delimiters. All I know is they are 1500 byte records. Each record has fixed column length. But all on a single line.|||Where did the file come from?

An lrecl of 5mb?

Tell them to fix the file...

I guess you would need to write a pearl script or something...