Progromatically choosing yesterday's date as a parameter.

I have a report setup against an analysis service cube. It runs fine. Users
choose a date from the parameter list and then it pulls out the data nicely.
I need to programatically have it select a parameter for yesterday's date.
Somebody suggested setting a default script for the parameter of
=DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") to choose yesterday's date.
The problem is that my mdx date is in the format [Dimension Name].[All
Time].[Quarter].[Month].[Day] and the calendar that is being used is in the
tax year. (i.e - today, where in the first quarter of 2006)
How can I convert the current date to the appropriate format?
Any help would be greatly appreciated.
Thanks,
MattI have done similar things in the past and I wrote a SQL query that gets the
current date then I use the datepart function to get the parts (i.e quarters)
If you want to reformat the way a part displays I put the datepart inside a
CASE statement.
Another approach is to turn it into a string and use the datepart to get
the parts you want. It may look something like this.....
SELECT 'Q' + CAST(DATEPART(qq, GETDATE()) AS varchar(255)) AS Quarter
There is probably a better way to do this but this is how I did it.
"Matt" wrote:
> I have a report setup against an analysis service cube. It runs fine. Users
> choose a date from the parameter list and then it pulls out the data nicely.
> I need to programatically have it select a parameter for yesterday's date.
> Somebody suggested setting a default script for the parameter of
> =DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") to choose yesterday's date.
> The problem is that my mdx date is in the format [Dimension Name].[All
> Time].[Quarter].[Month].[Day] and the calendar that is being used is in the
> tax year. (i.e - today, where in the first quarter of 2006)
> How can I convert the current date to the appropriate format?
> Any help would be greatly appreciated.
> Thanks,
> Matt

Progress Versus SQL Server

Progress is a piece of crap! Sure it runs on Unix. But the time
invested in learning and working with it obscene! With SQL Server I
could easily plan a maintenance backup plan and transaction backup plan
- Progress - well you have to learn about AI files, sizing the BI and
AI files as well as the data files. It's a freakin nightmare. If I
want to see data in a table, can I simply click on a table and open it
- NO! Instead, I have to learn the 4GL language to write a script to
return records (what if I want to resort those records - write another
4GL script and run it again). But hey, why do you want to see the data
in the tables (at least that's what I was told by my company) Progress
GUI - what a joke. This database server might have been popular with
unix but the world today is GUI and Microsoft are the GUI experts.
Even with the GUI tools in Progress, I still can't click on a table,
open it and see the records. Maybe that's not important to others but
as a dba, it sure is a necessity. We're working with different
versions of Progress and none of them integrate with the other. I've
spent 2 months now trying to run some simple database maintenance on
Progress and all I can say is that I hate BI, AI, and D1, D2, D3 files.
Give me SQL Server any day where at least I can manage databases in a
normal logical way without having to deal with a stinkin .ST files and
prostrct this...prostrct that!!! Any Progress responses are welcome
but I'll take you on any day against SQL Server. If you're a
Pro-Progress and anti-SQL Server, you're behind the times - get with
the newest technology and GUI (not a GUI in development.) I have yet
to see a true relational database designed in Progress. Upsizing a
Progress database into a GUI environment - HA!I worked with Progress for several years running mission critical
applications and all I can say is that is an excellent technology as good as
SQL Server or Oracle.
Ben Nevarez, MCDBA, OCP
Database Administrator
"pkohn@.charter.net" wrote:

> Progress is a piece of crap! Sure it runs on Unix. But the time
> invested in learning and working with it obscene! With SQL Server I
> could easily plan a maintenance backup plan and transaction backup plan
> - Progress - well you have to learn about AI files, sizing the BI and
> AI files as well as the data files. It's a freakin nightmare. If I
> want to see data in a table, can I simply click on a table and open it
> - NO! Instead, I have to learn the 4GL language to write a script to
> return records (what if I want to resort those records - write another
> 4GL script and run it again). But hey, why do you want to see the data
> in the tables (at least that's what I was told by my company) Progress
> GUI - what a joke. This database server might have been popular with
> unix but the world today is GUI and Microsoft are the GUI experts.
> Even with the GUI tools in Progress, I still can't click on a table,
> open it and see the records. Maybe that's not important to others but
> as a dba, it sure is a necessity. We're working with different
> versions of Progress and none of them integrate with the other. I've
> spent 2 months now trying to run some simple database maintenance on
> Progress and all I can say is that I hate BI, AI, and D1, D2, D3 files.
> Give me SQL Server any day where at least I can manage databases in a
> normal logical way without having to deal with a stinkin .ST files and
> prostrct this...prostrct that!!! Any Progress responses are welcome
> but I'll take you on any day against SQL Server. If you're a
> Pro-Progress and anti-SQL Server, you're behind the times - get with
> the newest technology and GUI (not a GUI in development.) I have yet
> to see a true relational database designed in Progress. Upsizing a
> Progress database into a GUI environment - HA!
>|||pkohn@.charter.net wrote:
> Progress is a piece of crap! Sure it runs on Unix. But the time
> invested in learning and working with it obscene! With SQL Server I
> could easily plan a maintenance backup plan and transaction backup plan
> - Progress - well you have to learn about AI files, sizing the BI and
> AI files as well as the data files. It's a freakin nightmare. If I
> want to see data in a table, can I simply click on a table and open it
> - NO! Instead, I have to learn the 4GL language to write a script to
> return records (what if I want to resort those records - write another
> 4GL script and run it again). But hey, why do you want to see the data
> in the tables (at least that's what I was told by my company) Progress
> GUI - what a joke. This database server might have been popular with
> unix but the world today is GUI and Microsoft are the GUI experts.
> Even with the GUI tools in Progress, I still can't click on a table,
> open it and see the records. Maybe that's not important to others but
> as a dba, it sure is a necessity. We're working with different
> versions of Progress and none of them integrate with the other. I've
> spent 2 months now trying to run some simple database maintenance on
> Progress and all I can say is that I hate BI, AI, and D1, D2, D3 files.
> Give me SQL Server any day where at least I can manage databases in a
> normal logical way without having to deal with a stinkin .ST files and
> prostrct this...prostrct that!!! Any Progress responses are welcome
> but I'll take you on any day against SQL Server. If you're a
> Pro-Progress and anti-SQL Server, you're behind the times - get with
> the newest technology and GUI (not a GUI in development.) I have yet
> to see a true relational database designed in Progress. Upsizing a
> Progress database into a GUI environment - HA!
>
Tossing in my 2-cents... I'm not a Progress guy, never touched it. I
do however find it interesting that your entire argument seems to be
based on SQL offering a better GUI. I blame that GUI for some of the
basic, everyday questions that cross this newsgroup on a daily basis.
My database has been running for 2 years, and my transaction log is
250GB, why? How can I export a database to a different server? The GUI
that you seem to cherish allows any Access user to think he's a SQL
Server DBA, without forcing him/her to learn the most basic tasks.
Point-and-click through the maintenance plan wizard, without
understanding a thing about what it's actually doing, all is well until
that plan begins to fail. Since they never understood what it was
doing, they have no idea where to start troubleshooting. Oh, look,
there's a "New Database Wizard", I can create a database! What's my
backup strategy? Do I need transactional recovery capabilities, or are
daily full backups enough? Dunno, the wizard didn't ask me those questions.
Personally, the only time I touch Enterprise Manager is when I have to
work with some DTS package that somebody dragged objects onto,
connecting them with workflow lines, because that's the only way they
know how to execute a stored procedure.

Programming SQL Express

Hey, i have an installer that places ms sql express onto a machine. thats is fine. i want to create a post setup application that runs after ms sql server is installed. what for example i want to do is be able to create a user and have the DB allow remote connections. i can provide instructions for a 'peon' to make the required post setup changes but i want to see if i can cover that with an application. thanks

Charles Rezsonya

Write this as T-SQl and then either write code that calls into the database and execute the script(call this code from your installer), or include a .sql file with the script as part of your installation and then call that through a call to sqlcmd to execute.

Either way I recomend having a copy of the script as part of your install so that if something goes wrong you can at least walk a user through running the script by hand.

|||Hi,

These things can be easily changed using the SMO namespace:

using Microsoft.SqlServer.Management.Smo.Wmi;

(....)

ManagedComputer comp = new ManagedComputer(".");

comp.ClientProtocols["tcp"].IsEnabled = true;
(...)

Remeber to restart the service after changing this.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

amazing. i will certainly be trying that monday. Thanks. Keep an eye on this post incase i have add'l questions?

tia

Charles R

|||Sure, everyone who signed in for alerts (its by default in the forums will get an IM Alert and additional the email alert ( depending on the configuration)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

hey, anyway you can show me a script that would change the server to allow tcp remote connections? what application do i run to process the script?

|||ok. i'm not finding the dll to include the smo namespace using 'using' in the sql sdk directory. and when i go to the studio and go to projects -> add reference its not listed "microsoft.sqlserver.etc etc"....... where can i download this dll? any add'l source on connecting examples?|||actually i found it on one of my other servers i installed the sdk to. so i'm including it. next really is just to get it going now :)|||

damn, but i am having a problem. i am using visual studio 2003 and when i go to about it says my .net framework is 1.1. is that a problem for the following message?

a referance to 'c:\program file\microsoft sql server\90\sdk\microsoft.sqlserver.sqlenum.dll' could not be added. this is not a valid or COM component. only assemblies with extension 'dll' and com components can be referenced. please make sure that the file is accessible, and that it is a valid assembly or com component.

|||is it possible to do this with visual studio 2003?|||

I don't believe so.

SMO is a SQL 2005 and .NET Framework 2.0 technology. I think that VS 2003 is locked into NetFx1.1. (Any VS experts can feel free to correct me.) You have a couple alternatives:

Move up to VS 2005, you can get the VS Express Editions for free if you like. They have some reduced functionality.

Programming SQL Express

Hey, i have an installer that places ms sql express onto a machine. thats is fine. i want to create a post setup application that runs after ms sql server is installed. what for example i want to do is be able to create a user and have the DB allow remote connections. i can provide instructions for a 'peon' to make the required post setup changes but i want to see if i can cover that with an application. thanks

Charles Rezsonya

Write this as T-SQl and then either write code that calls into the database and execute the script(call this code from your installer), or include a .sql file with the script as part of your installation and then call that through a call to sqlcmd to execute.

Either way I recomend having a copy of the script as part of your install so that if something goes wrong you can at least walk a user through running the script by hand.

|||Hi,

These things can be easily changed using the SMO namespace:

using Microsoft.SqlServer.Management.Smo.Wmi;

(....)

ManagedComputer comp = new ManagedComputer(".");

comp.ClientProtocols["tcp"].IsEnabled = true;
(...)

Remeber to restart the service after changing this.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

amazing. i will certainly be trying that monday. Thanks. Keep an eye on this post incase i have add'l questions?

tia

Charles R

|||Sure, everyone who signed in for alerts (its by default in the forums will get an IM Alert and additional the email alert ( depending on the configuration)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

hey, anyway you can show me a script that would change the server to allow tcp remote connections? what application do i run to process the script?

|||ok. i'm not finding the dll to include the smo namespace using 'using' in the sql sdk directory. and when i go to the studio and go to projects -> add reference its not listed "microsoft.sqlserver.etc etc"....... where can i download this dll? any add'l source on connecting examples?|||actually i found it on one of my other servers i installed the sdk to. so i'm including it. next really is just to get it going now :)|||

damn, but i am having a problem. i am using visual studio 2003 and when i go to about it says my .net framework is 1.1. is that a problem for the following message?

a referance to 'c:\program file\microsoft sql server\90\sdk\microsoft.sqlserver.sqlenum.dll' could not be added. this is not a valid or COM component. only assemblies with extension 'dll' and com components can be referenced. please make sure that the file is accessible, and that it is a valid assembly or com component.

|||is it possible to do this with visual studio 2003?|||

I don't believe so.

SMO is a SQL 2005 and .NET Framework 2.0 technology. I think that VS 2003 is locked into NetFx1.1. (Any VS experts can feel free to correct me.) You have a couple alternatives:

Move up to VS 2005, you can get the VS Express Editions for free if you like. They have some reduced functionality.

programatic transferdatabasetask in ssis

I am trying to code a package that runs a transferdatabasetask with the following code

Dim package As New Package()

package.PackageType = DTSPackageType.DTSDesigner90

package.Name = "transfer db task"

package.Description = "transfer db task"

package.CreatorComputerName = System.Environment.MachineName

package.CreatorName = System.Environment.UserName

Dim dest As ConnectionManager = package.Connections.Add("OLEDB")

dest.Name = "Dest"

dest.ConnectionString = "Data Source=NSW97V9F1S\NSW97V9F1S;Initial Catalog=RGTemp;User Id=rgTest;Password=12345"

'dest.ConnectionString = "SqlServerName=PDNCNLNJ1S\SQLSERVER2005;UseWindowsAuthentication=True;UserName=sa;"

Dim source As ConnectionManager = package.Connections.Add("OLEDB")

source.Name = "Source"

source.ConnectionString = "Data Source=NSW97V9F1S\NSW97V9F1S;Initial Catalog=RGTemp;User Id=rgTest;Password=12345"

'source.ConnectionString = "SqlServerName=NSW97V9F1S\NSW97V9F1S;UseWindowsAuthentication=True;UserName=;"

Dim th As TaskHost = TryCast(package.Executables.Add("STOCK:TransferDatabaseTask"), TaskHost)

th.Name = "transfer db task"

th.Description = "The transfer task"

th.Properties("Action").SetValue(th, 0) '0: copy

th.Properties("Method").SetValue(th, 1) '1: destination online

th.Properties("DestinationConnection").SetValue(th, dest.ID)

th.Properties("DestinationDatabaseFiles").SetValue(th, "rgTemp1.mdf,C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA,'';rgTemp1_log.ldf,C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA,''")

'th.Properties("DestinationDatabaseFiles").SetValue(th, "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\rgTemp1.mdf;C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\rgTemp1_log.ldf")

th.Properties("DestinationDatabaseName").SetValue(th, "RGTemp1")

th.Properties("DestinationOverwrite").SetValue(th, True)

th.Properties("ReattachSourceDatabase").SetValue(th, False)

th.Properties("SourceConnection").SetValue(th, source.ID)

th.Properties("SourceDatabaseFiles").SetValue(th, "'rgTemp.mdf','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA','';'rgTemp_log.ldf','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA',''")

MsgBox(th.Properties("SourceDatabaseFiles").GetValue(th).ToString)

MsgBox(th.Properties("DestinationDatabaseFiles").GetValue(th).ToString)

th.Properties("SourceDatabaseName").SetValue(th, "RGTemp")

Dim status As DTSExecResult = package.Validate(Nothing, Nothing, Nothing, Nothing)

' If the package validated successfully, then execute it.

If status = DTSExecResult.Success Then

' Execute the package

Dim result As DTSExecResult = package.Execute(Nothing, Nothing, Nothing, Nothing, Nothing)

End If

'Dts.TaskResult = Dts.Results.Success

End Sub

The problem is that the package validation fails and the 'execute' statement never runs. the two message boxes report zero length strings in the source and destination files properties. It seems that the hard coded filenames that I have provided are not correct.

Can anyone shed any light on what is wrong here:

regards

Ray

What is the error message on validation?

When you add connection manager, the type should be "SMOServer" instead of "OLEDB". So, use something like:
package.Connections.Add("SMOServer")

Setting the properties is easier if you get the InnerObject from task host as in:
TransferDatabaseTask task = (TransferDatabaseTask) th.InnerObject;

For source and destination connections, you should set the name of the connection manager instead of ID. (I am not sure if using the ID is correct)

You have to escape '\' and " in the values for source and destination database file as
"\"rgTemp.mdf\",\"C:\\Program

Files\\Microsoft SQL

Server\\MSSQL.1\\MSSQL\\DATA\",\"\";"\"rgTemp.ldf\",\"C:\\Program

Files\\Microsoft SQL

Server\\MSSQL.1\\MSSQL\\DATA\",\"\";|||

Thanks for the reply

I tried your suggestions and got as follows

Using SMOServer invalidated the connection strings and I could only set them in a format that doesn't allow SQL Server login or a password

I escaped the file names in VB as double double quotes and that fixed the source and destination file name assignments

But the routine still returns 'failed' from the 'validate' call. there is no error message the 'validate' routine just returns 'failed' with no exception or message that I can find.

if i comment out the validate call the 'execute' call just returns failed with no exception.

I am not getting any help from the system here.

we have made a step forward here thanks to you but I am still stuck

Do you have any other ideas?

regards

Ray

|||To create connection manager you can use the following code:
connectionString = String.Format("SqlServerName={0};UseWindowsAuthentication=true;", serverName);
//connectionString = String.Format("SqlServerName={0};UseWindowsAuthentication=false;UserName={1};Password={2}",serverName, userName, passwd);

ConnectionManager connectionManager = package.Connections.Add("SMOServer");
connectionManager.ConnectionString = connectionString;
connectionManager.Name = connectionManagerName;