Addition to the above.

I forgot to add, you'll see that ZONE has less records than STATE, thats
because the current system is storing NAMES not keys, and a ZONE name can be
in more than on STATE, e.g. STATE:"New York" can have a zone called
ZONE:"AGRICULTURE" and STATE:"California" can have a zone called
ZONE:"AGRICULTURE"Sorry, another thing... the record counts in the first post are DISTINCT
counts...

Adding Reference to App_Code of current website

I know it's possible to add a reference to a report to a custom class, but I was wondering if it was possible to refer to the code in the App_Code directory of the current website?

We have a lot of built in functionality that I could take advantage of, if I can make a call to the classes in the current site. If I have to create a new class, there's a lot of things that would have to be duplicated to get it to work and we definately don't want to have to duplicate efforts.

TIA

Well, you can reference custom assemblies from within your reports (it could be private or signed ones)

herehttp://msdn2.microsoft.com/en-us/library/aa179513(SQL.80).aspx you gonna find all you need to do so.

Hope this helps

adding new queries to Server Management Studio

When ever I hit the CTRL+N to add a new query and then save it, why doesn't it get added to the current project I'm working on? Since it's not added, I have to open Windows Explorer, navigate to the right folder, then drag'n'drop it into the right project in my solution. A lot of work for something it should be doing by default.

What am I missing? Thanks.

This does not appear possible with SQL Server 2005 SP2. Could you file this on Microsoft Connect? http://connect.microsoft.com/SQLServer/Feedback/

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||Done, id 275345.

adding new queries to Server Management Studio

When ever I hit the CTRL+N to add a new query and then save it, why doesn't it get added to the current project I'm working on? Since it's not added, I have to open Windows Explorer, navigate to the right folder, then drag'n'drop it into the right project in my solution. A lot of work for something it should be doing by default.

What am I missing? Thanks.

This does not appear possible with SQL Server 2005 SP2. Could you file this on Microsoft Connect? http://connect.microsoft.com/SQLServer/Feedback/

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server Manageability

http://blogs.msdn.com/sqlrem/

|||Done, id 275345.

Adding New Disk

When I add a new disk and put a file on it and make it part of the primary
filegroup, how does disk space get used? Basically my current disk is 90%
full and I'd like to relieve it as much as possible.
Files within a file group get filled in a proportion fill method that is
based of the amount of free space in the file. So if you added another file
some new data would be inserted into the existing one(s) but most would go
into the new one. At least until they leveled off. If your concerned it
would be best to BCP out all the data, truncate the tables, add the new
file(s) and then BCP the data back in. That would fill them more evenly and
place more of the existing data on the new files.
Andrew J. Kelly SQL MVP
"Brad" <me@.privacy.net> wrote in message
news:MPG.1b435bcab46a01ee98bdd7@.news...
> When I add a new disk and put a file on it and make it part of the primary
> filegroup, how does disk space get used? Basically my current disk is 90%
> full and I'd like to relieve it as much as possible.

Adding New Disk

When I add a new disk and put a file on it and make it part of the primary
filegroup, how does disk space get used? Basically my current disk is 90%
full and I'd like to relieve it as much as possible.Files within a file group get filled in a proportion fill method that is
based of the amount of free space in the file. So if you added another file
some new data would be inserted into the existing one(s) but most would go
into the new one. At least until they leveled off. If your concerned it
would be best to BCP out all the data, truncate the tables, add the new
file(s) and then BCP the data back in. That would fill them more evenly and
place more of the existing data on the new files.
Andrew J. Kelly SQL MVP
"Brad" <me@.privacy.net> wrote in message
news:MPG.1b435bcab46a01ee98bdd7@.news...
> When I add a new disk and put a file on it and make it part of the primary
> filegroup, how does disk space get used? Basically my current disk is 90%
> full and I'd like to relieve it as much as possible.

Adding New Disk

When I add a new disk and put a file on it and make it part of the primary
filegroup, how does disk space get used? Basically my current disk is 90%
full and I'd like to relieve it as much as possible.Files within a file group get filled in a proportion fill method that is
based of the amount of free space in the file. So if you added another file
some new data would be inserted into the existing one(s) but most would go
into the new one. At least until they leveled off. If your concerned it
would be best to BCP out all the data, truncate the tables, add the new
file(s) and then BCP the data back in. That would fill them more evenly and
place more of the existing data on the new files.
--
Andrew J. Kelly SQL MVP
"Brad" <me@.privacy.net> wrote in message
news:MPG.1b435bcab46a01ee98bdd7@.news...
> When I add a new disk and put a file on it and make it part of the primary
> filegroup, how does disk space get used? Basically my current disk is 90%
> full and I'd like to relieve it as much as possible.

Adding Integers

The code below has this line
SET @.SOGallons = @.ODTGallons

I need it to add the Current value of @.SOGallons to the newly selected value of @.ODTGallons and set that as the new value of @.SOGallons.

I've tried
SET @.SOGallons = @.SOGallons + @.ODTGallons

SET @.SOGalTemp = @.SOGallons
SET @.SOGallons= @.SOGalTemp + @.ODTGallons

Neither Worked

<CODE>
FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM
ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND
ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @.OrdNum AND
([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')

OPEN TC1

FETCH NEXT FROM TC1 INTO @.LT, @.ODTGallons, @.ODTComm
WHILE @.@.FETCH_STATUS=0
BEGIN
IF @.LT = 'SO'
BEGIN
SET @.SplitTest = 1
SET @.SOGallons = @.ODTGallons
IF @.SOGallons > 0
BEGIN
SET @.SOGalTest = 1
END
ELSE
BEGIN
SET @.SOGalTest = 0
END
IF @.SplitTest <> @.SOGalTest
BEGIN
SET @.SOGalTest = 0
END
END
ELSE
BEGIN
SET @.SOGalTest = 1
END
FETCH NEXT FROM TC1 INTO @.LT, @.ODTGallons, @.ODTComm
END
CLOSE TC1
DEALLOCATE TC1</CODE>LineType Gallons Commodity
DL 4000 #2 ULSD DYED
IP 7000 87 NL / ETH
PU 4000 #2 ULSD DYED
SO 7000 87 NL / ETH

There may be multiple lines of any of the above line types.

IP = Initial Pickup
PU = Additional Pickup
SO = Stop Off
DL = Final Delivery

I need to know if all of the gallons that were picked up where delivered

IP + PU = SO + DL

I'm doing a check on the validity of the commodity type as well but with the forums helps we figured that one out yesterday.|||First, your issue is NULL issue.
You said
I've tried
SET @.SOGallons = @.SOGallons + @.ODTGallons

SET @.SOGalTemp = @.SOGallons
SET @.SOGallons= @.SOGalTemp + @.ODTGallons

But before you use @.SOGallons, you need to initialise it. Otherwise it stays as NULL. And whatever value added to it becomes NULL. This is why it failed. Say, insert this line "SET @.SOGallons = 0" before "SET @.SOGallons = @.SOGallons + @.ODTGallons".

Next, in your WHILE Loop, put "SET @.SplitTest = 1" before the loop started as it is a constant.|||Hey,

Thanks.

I know VB fairly well but am completely new to SQL as of about 3 weeks ago. I always seem to know what I want to do but am continually making small syntax errox that trip me up.

Adding extra parameter to my report doesn't work.

I have a problem in RS 2000 where when I add another parameter to my
current two it doesn't work. I've tested the procedures in SQL and
when declaring the datasets. The two parameters I have are:
"Report_Date" and "Project_Name". The one I add is called "Cap_Num".
I've modified all the existing datasets to use this new parameter in
addition to the others, but when I preview the report, nothing happens
when I select a value from "Report_Date" and "Cap_Num". I only get
results when I choose the "Project_Name" value and "Report_Date". On
the web when I run the report and I try to select just based on the
"Cap_Num" and "Report_Date" it says to choose a "Project_Name". I've
gone through all of my datasets and queries and have put the proper
conditions for each:
'where xxxx = @.Report_Date and @.Project_Name OR xxxx=@.Report_Date and
@.Cap_Num'
Like I said I tested all of the parameters by themselves and the work,
but when they are all integrated, it is still looking for the project
name to be selected. Any ideas?Hi,
I've had a few problems when adding parameters in the past myself.
Couple of things I would check are:
1. Go into the layout tab for the report, select Report Menu and go
into Report Parameters. Make sure the parameters are added in here.
2. Go into the data tab for the report, select the ... button to edit
the dataset, click to the parameters tab and make sure your have an
entry here for your third param should be like
Name Value
CAP_NUM =Parameters!CAP_NUM.Value
hope this helps|||I find that I need to do "Refresh Fields" in the Data window frequently.
"siphonalfusion" <jamiewyant@.comcast.net> wrote in message
news:1146781767.843959.274910@.v46g2000cwv.googlegroups.com...
>I have a problem in RS 2000 where when I add another parameter to my
> current two it doesn't work. I've tested the procedures in SQL and
> when declaring the datasets. The two parameters I have are:
> "Report_Date" and "Project_Name". The one I add is called "Cap_Num".
> I've modified all the existing datasets to use this new parameter in
> addition to the others, but when I preview the report, nothing happens
> when I select a value from "Report_Date" and "Cap_Num". I only get
> results when I choose the "Project_Name" value and "Report_Date". On
> the web when I run the report and I try to select just based on the
> "Cap_Num" and "Report_Date" it says to choose a "Project_Name". I've
> gone through all of my datasets and queries and have put the proper
> conditions for each:
> 'where xxxx = @.Report_Date and @.Project_Name OR xxxx=@.Report_Date and
> @.Cap_Num'
> Like I said I tested all of the parameters by themselves and the work,
> but when they are all integrated, it is still looking for the project
> name to be selected. Any ideas?
>|||I agree you will also need to do as Bill says|||I actually found out that I needed to use default parameters. Now it
is working. Thank you all!

Adding days and setting time

Hi,
Can someone please help me with a SQL Server 2005 issue with date and
time.
I want to take the current date and time and add 2 days to it, but the
time must be set to 5pm.
If the current date/time is past 5pm it will go to the next day at 5pm.
So if the date was:
2006-06-23 15:55:46.337 then the new date should say 2006-06-25
17:00:46.337
If the current time was past 5pm then it should be as follows:
2006-06-23 19:55:46.337 then the new date should say 2006-06-26
17:00:46.337
Notice the day is an extra day because 5pm has already gone by hence it
has to go to the next 5pm, which is the next day.
Thanks.
SimonTry this:
SELECT DATEADD(DAY, (CASE
WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) < 17 THEN 2
ELSE 3
END), DATEADD(HOUR, 17, CAST(CONVERT(char(8), CURRENT_TIMESTAMP, 112) AS
DATETIME)));
HTH
Vern Rabe
"simon_s_li@.hotmail.com" wrote:

> Hi,
> Can someone please help me with a SQL Server 2005 issue with date and
> time.
> I want to take the current date and time and add 2 days to it, but the
> time must be set to 5pm.
> If the current date/time is past 5pm it will go to the next day at 5pm.
> So if the date was:
> 2006-06-23 15:55:46.337 then the new date should say 2006-06-25
> 17:00:46.337
> If the current time was past 5pm then it should be as follows:
> 2006-06-23 19:55:46.337 then the new date should say 2006-06-26
> 17:00:46.337
> Notice the day is an extra day because 5pm has already gone by hence it
> has to go to the next 5pm, which is the next day.
> Thanks.
> Simon
>|||Something like:
DECLARE @.Date1 DATETIME;
SELECT @.Date1 = GETDATE();
PRINT @.Date1;
SET @.Date1 = CASE WHEN DATEPART(HH, @.date1) > 17 THEN DATEADD(DAY, 1,
@.Date1) ELSE @.Date1 END;
SET @.Date1 = DATEADD(HH, (17-DATEPART(HH, @.Date1)), @.Date1);
PRINT @.Date1;|||I just realized that you apparently want to retain the current seconds and
milliseconds, to be added to 5:00 PM. Seems strange, but to do that, this
should work:
SELECT DATEADD(DAY, (CASE
WHEN DATEPART(HOUR, CURRENT_TIMESTAMP) < 17 THEN 2
ELSE 3
END), DATEADD(HOUR, 17, CAST(CONVERT(varchar(10), CURRENT_TIMESTAMP, 110) +
' 00:00' + RIGHT(CONVERT(varchar(24), CURRENT_TIMESTAMP, 13), 7) AS
DATETIME)));
HTH
Vern Rabe
"simon_s_li@.hotmail.com" wrote:

> Hi,
> Can someone please help me with a SQL Server 2005 issue with date and
> time.
> I want to take the current date and time and add 2 days to it, but the
> time must be set to 5pm.
> If the current date/time is past 5pm it will go to the next day at 5pm.
> So if the date was:
> 2006-06-23 15:55:46.337 then the new date should say 2006-06-25
> 17:00:46.337
> If the current time was past 5pm then it should be as follows:
> 2006-06-23 19:55:46.337 then the new date should say 2006-06-26
> 17:00:46.337
> Notice the day is an extra day because 5pm has already gone by hence it
> has to go to the next 5pm, which is the next day.
> Thanks.
> Simon
>

Adding current date into attachment filename in reporting service

Hi there,

I have been using reporting service to generate my report and sending email with attachment report via subscription everyday. It is work well and no error.

My attachment file name is as same as reportname project. But customers asked me to add current date into an attachment filename which will help them to identify the report. I try to check in rsreportserver.config to change it but have no idea.

My reportname project is daily_file.rdl and my attachment filename in email is daily_file.csv. I'd like to change my attachment filename as day_month_year_file.csv.

Is there anyone know how to change an attachment filename to be not the same as reportname in reporting service 2005

Regards,

Hello...

I have the same question. My subscription generates a file that gets stored on our Network file server. A new file is created every day and the users want the generate date included in the name of the file that is created and saved. I see that this can be done in the e-mail subject line if your subscriptions email the users. How do you do it in the file name?

Thanks.

|||

You can't do change the name on an export, but you can take a look at a couple posts on here about using a data-driven subscription to handle this.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=168131&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1311897&SiteId=1

Hope this helps.

Jarret

Adding current date into attachment filename in reporting service

Hi there,

I have been using reporting service to generate my report and sending email with attachment report via subscription everyday. It is work well and no error.

My attachment file name is as same as reportname project. But customers asked me to add current date into an attachment filename which will help them to identify the report. I try to check in rsreportserver.config to change it but have no idea.

My reportname project is daily_file.rdl and my attachment filename in email is daily_file.csv. I'd like to change my attachment filename as day_month_year_file.csv.

Is there anyone know how to change an attachment filename to be not the same as reportname in reporting service 2005

Regards,

Hello...

I have the same question. My subscription generates a file that gets stored on our Network file server. A new file is created every day and the users want the generate date included in the name of the file that is created and saved. I see that this can be done in the e-mail subject line if your subscriptions email the users. How do you do it in the file name?

Thanks.

|||

You can't do change the name on an export, but you can take a look at a couple posts on here about using a data-driven subscription to handle this.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=168131&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1311897&SiteId=1

Hope this helps.

Jarret

Adding Button in Report

I would like to know whether there is any way to add a button in report. I have 3 report parameters. On clicking the button, the current values of the reports are to be obtained and saved in database i.e saving the report configuration. I will use this saved configuration to form the url and view report directly.

Thanks,

Pradeep

No, actually not. YOu could put alink with the parameters in your report, calling another report which then executes stored procedure to store the values.

Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-|||Or you could set up a subscription with the specified parameters. Also if you want to get at the parameters, RS saves them in its own database as part of its logging.

Adding a third NODE to an existing SQl server 2000 CLUSTER

Currently we have an ACTIVE/ACTIVE SQL Server 2000 cluster in our production
environment. The current configuration is such that each node has a dedicated
server hardware. Lets call one of the ACTIVE nodes as NODE1 and the other
ACTIVE node as NODE2. We are in the planning stages of a SQL Server 2005,
side by side upgrade. Also we are planning to introduce a new server hardware
into the equation to help the upgrade process so that the performance is not
affected. The way we want to use the new SQl server hardware is to have both
a SQl Server 2000 instance and a SQl Server 2005 instance in this new
hardware, lets call this NODE3. The databases for all the SQl servers will be
presented via a SAN.
(1)NODE2 will have another instance of SQl 2005 installed into it.
(2)The SQL 2005 instance of the NODE2 needs to be clustered with the SQL
2005 instance in the NODE3 as an ACTIVE-ACTIVE cluster.
(3)The SQL 2000 instance of the NODE2 then needs to be clustered with the
SQL 2000 instance in the NODE 1.
If the above was technically possible then the SQl 2005 databases will be
load balanced between NODE2 and NODE3 and the SQL 2000 databases will be load
balanced between NODE 1 and NODE 2. Eventually when we move all of the SQl
2000 databases to SQl 2005 then the SQl 2000 Server instances will be
upgraded to SQl 2005 as well.
So eventually we would end up with 2 ACTIVE/ACTIVE cluster of SQl 2005
Servers in 3 dedicated server hardware.
Am I dreaming here ? Or is this technically feasible. Any thoughts are
welcome. Any other alternatives are also welcome.
Many Thanks in advance.
RK
Active/Active does not share data across nodes. Technically, Active/Active
does not exist. The term described a unique configuration that existed for
SQL 7.0 when nodes were not equal peers in cluster. There is no load
balancing or shared databases across nodes in MSCS clustering.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"RK" <RK@.discussions.microsoft.com> wrote in message
news:C6A544B9-0573-4CE4-829F-D42727C52134@.microsoft.com...
> Currently we have an ACTIVE/ACTIVE SQL Server 2000 cluster in our
> production
> environment. The current configuration is such that each node has a
> dedicated
> server hardware. Lets call one of the ACTIVE nodes as NODE1 and the other
> ACTIVE node as NODE2. We are in the planning stages of a SQL Server 2005,
> side by side upgrade. Also we are planning to introduce a new server
> hardware
> into the equation to help the upgrade process so that the performance is
> not
> affected. The way we want to use the new SQl server hardware is to have
> both
> a SQl Server 2000 instance and a SQl Server 2005 instance in this new
> hardware, lets call this NODE3. The databases for all the SQl servers will
> be
> presented via a SAN.
> (1)NODE2 will have another instance of SQl 2005 installed into it.
> (2)The SQL 2005 instance of the NODE2 needs to be clustered with the SQL
> 2005 instance in the NODE3 as an ACTIVE-ACTIVE cluster.
> (3)The SQL 2000 instance of the NODE2 then needs to be clustered with the
> SQL 2000 instance in the NODE 1.
> If the above was technically possible then the SQl 2005 databases will be
> load balanced between NODE2 and NODE3 and the SQL 2000 databases will be
> load
> balanced between NODE 1 and NODE 2. Eventually when we move all of the SQl
> 2000 databases to SQl 2005 then the SQl 2000 Server instances will be
> upgraded to SQl 2005 as well.
> So eventually we would end up with 2 ACTIVE/ACTIVE cluster of SQl 2005
> Servers in 3 dedicated server hardware.
> Am I dreaming here ? Or is this technically feasible. Any thoughts are
> welcome. Any other alternatives are also welcome.
> Many Thanks in advance.
> RK
>
>
|||Hi,
Thanks for that. When I said shared SAN I didnt mean shared databases
between the cluster nodes. Again when I said load balancing what I intended
there was rather than have an Active/passive configuration and have all
databases managed by one instance of SQl Server 2000, in Active/Active it
will be managed by 2 SQL servers. But still you havent answered my main
question whether the new configuration as mentioned below is possible
technically ?
Many thanks in advance.
"Geoff N. Hiten" wrote:

> Active/Active does not share data across nodes. Technically, Active/Active
> does not exist. The term described a unique configuration that existed for
> SQL 7.0 when nodes were not equal peers in cluster. There is no load
> balancing or shared databases across nodes in MSCS clustering.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "RK" <RK@.discussions.microsoft.com> wrote in message
> news:C6A544B9-0573-4CE4-829F-D42727C52134@.microsoft.com...
>
|||You can have a three node cluster with multiple SQL instances of differing
versions. You can also set preferred node order for each instance
independently. Finally, you can set each instance to run on only a subset
of the nodes, but I would recommend allowing all instances on all nodes,
just to cover secondary failure possibilities.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"RK" <RK@.discussions.microsoft.com> wrote in message
news:C2CBECB3-A855-48E9-AAF2-FF2700DFEAD1@.microsoft.com...[vbcol=seagreen]
> Hi,
> Thanks for that. When I said shared SAN I didnt mean shared databases
> between the cluster nodes. Again when I said load balancing what I
> intended
> there was rather than have an Active/passive configuration and have all
> databases managed by one instance of SQl Server 2000, in Active/Active it
> will be managed by 2 SQL servers. But still you havent answered my main
> question whether the new configuration as mentioned below is possible
> technically ?
> Many thanks in advance.
>
> "Geoff N. Hiten" wrote:
|||You can have a three node cluster with multiple SQL instances of differing
versions. You can also set preferred node order for each instance
independently. Finally, you can set each instance to run on only a subset
of the nodes, but I would recommend allowing all instances on all nodes,
just to cover secondary failure possibilities.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"RK" <RK@.discussions.microsoft.com> wrote in message
news:C2CBECB3-A855-48E9-AAF2-FF2700DFEAD1@.microsoft.com...[vbcol=seagreen]
> Hi,
> Thanks for that. When I said shared SAN I didnt mean shared databases
> between the cluster nodes. Again when I said load balancing what I
> intended
> there was rather than have an Active/passive configuration and have all
> databases managed by one instance of SQl Server 2000, in Active/Active it
> will be managed by 2 SQL servers. But still you havent answered my main
> question whether the new configuration as mentioned below is possible
> technically ?
> Many thanks in advance.
>
> "Geoff N. Hiten" wrote:
|||Hi Geoff,
What I intended to acheive with this was TWO 2 node clusters from the 4 SQl
server instances spread around 3 servers. One cluster being a SQL 2000 based
and the other one based on SQL 2005. Possible still ?
Cheers
Rajeev
"Geoff N. Hiten" wrote:

> You can have a three node cluster with multiple SQL instances of differing
> versions. You can also set preferred node order for each instance
> independently. Finally, you can set each instance to run on only a subset
> of the nodes, but I would recommend allowing all instances on all nodes,
> just to cover secondary failure possibilities.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "RK" <RK@.discussions.microsoft.com> wrote in message
> news:C2CBECB3-A855-48E9-AAF2-FF2700DFEAD1@.microsoft.com...
>
|||No. Each machine can participate in one and only one cluster. You can have
a single cluster with two SQL instances and three host nodes.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"RK" <RK@.discussions.microsoft.com> wrote in message
news:C91E92BB-0C97-44CE-AC35-D426D9C92F5F@.microsoft.com...[vbcol=seagreen]
> Hi Geoff,
> What I intended to acheive with this was TWO 2 node clusters from the 4
> SQl
> server instances spread around 3 servers. One cluster being a SQL 2000
> based
> and the other one based on SQL 2005. Possible still ?
> Cheers
> Rajeev
> "Geoff N. Hiten" wrote: