This 'should' work:
SELECT convert( varchar(10), MyColumn, 101 )
For example, using today's date:
SELECT convert( varchar(10), getdate(), 101 )
-
05/25/2007
It the style number which indicates how the system should transform your data.
Here is more detail.
http://msdn2.microsoft.com/en-us/library/ms187928.aspx
|||thanks a lot MVPsqlThanks
Guess I couldn't see the tree for the forest. Here is the solution in case anyone else has the issue. Get the NextRunDate property of the Job object in the Microsoft.SqlServer.Management.Smo.Agent namespace.
I need to determine when (maybe) and if (definitely) a SQL Agent job will run again. I need to maintain a table of the next pending execution for each job. I need to be able to update this table from within a SQL Agent job, but preferably from within an executing SSIS package in the job. Is this possible and if so, any suggestions on how?
Thanks
Hello, your question is really SQL agent related so you likely want to post in the mgt tools forum but I can say what I know. SQL agent jobs and job steps can be manipulate from TSQL/Stored procedures so in theory you could call those from an SSIS pacakge. For example, using the SSIS Execute SQL task.
The following looks like a good reference to SQL Agent SPs.
http://msdn2.microsoft.com/en-us/library/ms187763.aspx
Hope that helps
|||Here is the solution in case anyone else has the issue. Get the NextRunDate property of the Job object in the Microsoft.SqlServer.Management.Smo.Agent namespace.
I've run into a bit of a sticky design issue. We have products in
three categories which I will call 'A', 'B' and 'C'. We have "kits"
which contain three products, one from each category.
Below is some sample SQL to set things up, but I need to ensure that
each kit gets three products -- one from each category. Obviously,
this basic SQL doesn't allow that. Any suggestions? Do I need a
different schema design, or is there something else I should be
looking at?
Cheers,
Curtis
CREATE TABLE category (
id int identity primary key,
name varchar(30)
);
CREATE TABLE products (
id int identity primary key,
name varchar(30),
category_id int references category(id)
);
CREATE TABLE kits (
id int identity primary key,
name varchar(30)
);
CREATE TABLE kit_products (
kit_id int references kits(id),
product_id int references products(id)
);>> We have products in three categories which I will call 'A', 'B' and
'C'. <<
... and you declared them as INTEGER.
>> We have "kits" which contain three products, one from each
category. <<
So, do you have only three categories??
>> Do I need a different schema design, ... <<
Oh yeah! You do not have any keys (IDENTITY is never a key by
definition) and "id" is to vague to be a data element name (read
ISO-11179 rules). Category is singular, while the other table names
are plural; ergo, category must have one and only one row? All the
important data is NULL-able.
I am going to assume that you have so many categories that they
require a separate table; if not, put them in a CHECK() clause.
CREATE TABLE Categories
(category_id INTEGER PRIMARY KEY,
category_name VARCHAR(30) NOT NULL);
CREATE TABLE Products
(product_name VARCHAR(30) NOT NULL,
product_id INTEGER NOT NULL UNIQUE,
category_id INTEGER NOT NULL
REFERENCES Categories(id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (product_id, category_id));
CREATE TABLE ProductKits
(kit_id INTEGER NOT NULL
kit_name VARCHAR(30) NOT NULL,
product_id_1 INTEGER NOT NULL,
category_id_1 INTEGER NOT NULL
FOREIGN KEY (product_id_1, category_id_1)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
product_id_2 INTEGER NOT NULL,
category_id_2 INTEGER NOT NULL
FOREIGN KEY (product_id_2, category_id_2)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
product_id_3 INTEGER NOT NULL,
category_id_3 INTEGER NOT NULL
FOREIGN KEY (product_id_3, category_id_3)
REFERENCES Products (product_id, category_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
CHECK (category_id_1 = 1
AND category_id_2 = 2
AND category_id_3 = 3));
The sneaky trick is to put both (product_id, category_id) in the
primary key of Products, so both can be referenced. The product_id is
still unique (another assumption, since your original schema allowed a
product to be named NULL or repeated under a thousand different
IDENTITY numbers, making data integrity impossible). I also assume
that the categories for the kits is (1, 2, 3) instead of ('a', 'b',
'c').
If there are onlyn three categories, then use this and no separate
Categories table:
CREATE TABLE Products
(product_name VARCHAR(30) NOT NULL,
product_id INTEGER NOT NULL UNIQUE,
category_id INTEGER NOT NULL
CHECK(category_id IN (3,2,1))
PRIMARY KEY (product_id, category_id));
Hi,
I am creating a application to show reports for daily based information.
I have created the reports and i am changing the data source at run time.In order to view the updated reports, i have to deploy.
So i need to deploy the reports automatically.
can any one gave a idea to do this.
thanks
Hi,
You can do this via the RS command.
What you need to do is write a script (.rss) that would serve as an input file to the rs command.
this script should loop through a folder and upload all extension with .rdl
Create a folder in your D: drive for example called RS within that have your script and have a sub folder called reports which were you will have all your reports.
once this has been done go to cmd and do the following
1. Map to your folder ie cd: D:\RS
2. type in the following command
rs -i PublishReports.rss -s http://localhost/reportserver
PublishReports.rss is your script and http://localhost/reportserver is where you want to deploy the reports.
Hope this helps.
|||
Hi,
Thanks for your solution.
It's work fine.
Hi,
I am creating a application to show reports for daily based information.
I have created the reports and i am changing the data source at run time.In order to view the updated reports, i have to deploy.
So i need to deploy the reports automatically.
can any one gave a idea to do this.
thanks
Hi,
You can do this via the RS command.
What you need to do is write a script (.rss) that would serve as an input file to the rs command.
this script should loop through a folder and upload all extension with .rdl
Create a folder in your D: drive for example called RS within that have your script and have a sub folder called reports which were you will have all your reports.
once this has been done go to cmd and do the following
1. Map to your folder ie cd: D:\RS
2. type in the following command
rs -i PublishReports.rss -s http://localhost/reportserver
PublishReports.rss is your script and http://localhost/reportserver is where you want to deploy the reports.
Hope this helps.
|||
Hi,
Thanks for your solution.
It's work fine.
Hi Guys,
I am using MS Visual Studio .Net 2003 to design reports from the data in MS SQL Server.
I have no problem to deploy, view or run the reports in my pc with MS SQL Reporting Services installed.
Now, my client's server with MS SQL Reporting Services only installed, but no MS Visual Studio installed.
How to deploy all my reports in my pc into my client's server so that user can view the reports in the server thru IE?
Thanks.
Yes, you can upload the .RDL file into Server.
You need to open http://<servername>/Reports page and press Upload File Button on the command bar. Then you can browse for your .RDL files (files that are copied from your home computer) and after you upload these reports to your server you may consider to change the Data Source.
For changing the Data Source you must open the report and go to the Properties Tab, than select Data Sources. You can choose from Shared Data source or Custom Data source, after you make your selection you need to press Apply button (on the bottom of the page).
Good luck.
|||Thanks for your reply.
Just manage to get the server up.
But when I open http://<servername>/Reports I can't see any Upload File Button. I got only "Home | My Subscriptions | Help" on right top corner.
Hi,
#1. go to Control Panel->Administrative Tools -> IIS , open Internet Information Services.
#2. Double click your server name-> goto web sites-> Default WebSite
#3. right click on report server goto directory security tab click edit button and deselect the anonymous acces check box.
do the step 3 for "Reports" also,.
and go to http://<servername>/Reports ull be able to see the upload button now.
Hope this solves your problem.
|||Yupe.. I see it now.. Thanks to both PFerns and Pavel Cristian Gabriel!
