Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
latingntlman
Yak Posting Veteran
96 Posts |
 Posted - 2006-08-14 : 14:12:36
|
| I've created and run several DTS jobs successfully, but now I'm trying to add a Create Table step to become a Tab in a Excel file, but it won't work because the table name is the concatenation of text, month and year. The month and year are for that current month. This report is to run once every month. Below is the code I'm using.CREATE TABLE ` 'Standalones -' + ' ' + convert(varchar(8),datename(month,getdate()),1) + ' ' +convert(varchar(8),datepart(year,getdate()),1) `If this actually worked it would return: Standalones - August 2006 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-14 : 14:18:08
|
| First, you'll need dynamic SQL for this to work. Check out the dynamic SQL article on this site (go to the main page and do a search in the articles for dynamic SQL). Second, you really need to change the name of that table. Get rid of the spaces so that you don't need to use square brackets when referring to it. I'd suggest Standalones_082006.Tara Kizer |
 |
|
|
latingntlman
Yak Posting Veteran
96 Posts |
Posted - 2006-08-14 : 15:00:07
|
| Ok, I read the document, but it still doesn't help me. I tried some of the examples but no go. I need to create a table with a varying name in the DTS that outputs data from a sql server to a excel spreadsheet. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-14 : 15:09:46
|
| Please post what you tried.Tara Kizer |
|
|
|
latingntlman
Yak Posting Veteran
96 Posts |
Posted - 2006-08-14 : 16:35:01
|
| CREATE TABLE select('Standalones_' + convert(varchar(8),datename(month,getdate()),1) + convert(varchar(8),datepart(year,getdate()),1)) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-14 : 16:38:12
|
| What you have posted is not dynamic SQL. What you are wanting requires dynamic SQL. Here is an example of dynamic SQL:DECLARE @s varchar(2), @SQL nvarchar(4000)SET @i = 'leSET @SQL = 'SELECT * FROM SomeTab ' + @sPRINT @SQL --always runs this when testing to ensure string is correctEXEC (@SQL)Tara Kizer |
|
|
|
|
|
|
|
|
can't create table in DTS