create table statement

I need to create duplicates of certain tables.
It woud be great if i can find the 'create table statement', in any of
the system tables which the Create Table of the Object Browser in
Query Analyzer users, so that i can just change the name and create a
new table thus.
Please help me find the Create Table statement available in the system
tables
thank you
Right click on the table in SSMS object browser, select Script Table As ->
CREATE to -> New Query Window
Anith
|||Unfortunately, the ddl is not stored in the system tables. You can script
the tables from EM or SSMS though.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<thetaamommy@.gmail.com> wrote in message
news:1c72f9e7-1052-40c3-8a2d-0f72a4e4c5bf@.c33g2000hsd.googlegroups.com...
>I need to create duplicates of certain tables.
> It woud be great if i can find the 'create table statement', in any of
> the system tables which the Create Table of the Object Browser in
> Query Analyzer users, so that i can just change the name and create a
> new table thus.
> Please help me find the Create Table statement available in the system
> tables
> thank you
sql

create table statement

I need to create duplicates of certain tables.
It woud be great if i can find the 'create table statement', in any of
the system tables which the Create Table of the Object Browser in
Query Analyzer users, so that i can just change the name and create a
new table thus.
Please help me find the Create Table statement available in the system
tables
thank youRight click on the table in SSMS object browser, select Script Table As ->
CREATE to -> New Query Window
--
Anith|||Unfortunately, the ddl is not stored in the system tables. You can script
the tables from EM or SSMS though.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<thetaamommy@.gmail.com> wrote in message
news:1c72f9e7-1052-40c3-8a2d-0f72a4e4c5bf@.c33g2000hsd.googlegroups.com...
>I need to create duplicates of certain tables.
> It woud be great if i can find the 'create table statement', in any of
> the system tables which the Create Table of the Object Browser in
> Query Analyzer users, so that i can just change the name and create a
> new table thus.
> Please help me find the Create Table statement available in the system
> tables
> thank you|||Yes it's like he says
And you can do this also in your Query Analyaer (Right Click on your table I
mean) ;)
Arjen
"Anith Sen" <anith@.bizdatasolutions.com> schreef in bericht
news:u9MPgoUfIHA.4712@.TK2MSFTNGP04.phx.gbl...
> Right click on the table in SSMS object browser, select Script Table As ->
> CREATE to -> New Query Window
> --
> Anith
>

Create table problem

I have been trying to create a table since morning but it save as a system
table instead of a user table. anybody has any idea? I tried to create both
GUI(Enterprise) and Analyser prompt but all the the time system made a
system table instead of a user
thanksFarrukh wrote:
> I have been trying to create a table since morning but it save as a system
> table instead of a user table. anybody has any idea? I tried to create both
> GUI(Enterprise) and Analyser prompt but all the the time system made a
> system table instead of a user
> thanks
It looks like somone has executed the sp_MS_upd_sysobj_category
procedure with a parameter value of 1. Execute it with the value 2
as parameter and verify that objects now created will not be
system objects.
I strongly recommend that you search the net and read up on this
procedure before executing it. It is an undocumented proc and should be
avoided.

create table => system table

Hallo everybody,
when I create a table or a stored procedure it always becomes a system
object instead of a user object, who can I avoid this? I just want to create
user objects.
I'm using SQL Server 2000 and I have all the service packs installed, the
user I'm using to create the table is DBOwner of the database and is part of
the role "system administrators"
thanks for the help
CristianSomebody has been playing with the sp_MS_upd_sysobj_category procedure. Exec
ute it with the value 2
as parameter and verify that objects create from thereon will not be system
objects.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Cristian" <cristiansuazo@.hotmail.com> wrote in message news:O0iCVmKWGHA.752@.TK2MSFTNGP02.p
hx.gbl...
> Hallo everybody,
> when I create a table or a stored procedure it always becomes a system
> object instead of a user object, who can I avoid this? I just want to crea
te
> user objects.
> I'm using SQL Server 2000 and I have all the service packs installed, the
> user I'm using to create the table is DBOwner of the database and is part
of
> the role "system administrators"
> thanks for the help
> Cristian
>|||what do you mean by system object. How did you find that it was a system
object?
"Cristian" wrote:

> Hallo everybody,
> when I create a table or a stored procedure it always becomes a system
> object instead of a user object, who can I avoid this? I just want to crea
te
> user objects.
> I'm using SQL Server 2000 and I have all the service packs installed, the
> user I'm using to create the table is DBOwner of the database and is part
of
> the role "system administrators"
> thanks for the help
> Cristian
>
>|||What makes you think they're system objects?
*mike hodgson*
http://sqlnerd.blogspot.com
Cristian wrote:

>Hallo everybody,
>when I create a table or a stored procedure it always becomes a system
>object instead of a user object, who can I avoid this? I just want to creat
e
>user objects.
>I'm using SQL Server 2000 and I have all the service packs installed, the
>user I'm using to create the table is DBOwner of the database and is part o
f
>the role "system administrators"
>thanks for the help
>Cristian
>
>|||thanks man! that resolved everything... strange stored procedure, an
undocumented one, anyway thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e8cZjzKWGHA.3972@.TK2MSFTNGP02.phx.gbl...
> Somebody has been playing with the sp_MS_upd_sysobj_category procedure.
Execute it with the value 2
> as parameter and verify that objects create from thereon will not be
system objects.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Cristian" <cristiansuazo@.hotmail.com> wrote in message
news:O0iCVmKWGHA.752@.TK2MSFTNGP02.phx.gbl...
create
the
part of
>|||Perhaps trace flag 1717 is on. You can interrogate with DBCC TRACESTATUS
(1717) and turn off with DBCC TRACEOFF (1717, -1) . Remove it from startup
parameters, if present.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cristian" <cristiansuazo@.hotmail.com> wrote in message
news:O0iCVmKWGHA.752@.TK2MSFTNGP02.phx.gbl...
> Hallo everybody,
> when I create a table or a stored procedure it always becomes a system
> object instead of a user object, who can I avoid this? I just want to
> create
> user objects.
> I'm using SQL Server 2000 and I have all the service packs installed, the
> user I'm using to create the table is DBOwner of the database and is part
> of
> the role "system administrators"
> thanks for the help
> Cristian
>

Create Report with Credentials

I have a report with a data source that requires credentials to be stored in
reporting service system.
I use the CreateReport function to deploy this report but it doesn't store
the credentials and these credentials are requested when it is about to
render.
How to store these credentials in order to avoid them to be asked during
rendering'
ThanksIn our case, we use script based on the scripting samples (in the RS install
folder), but link the reports to a shared data source created with the
credentials specfied:
Public Sub CreateSQLDataSource(ByVal serverName as String, ByVal
databaseName as String, ByVal userName as String, ByVal password as String)
Dim name As String = databaseName
Dim parent As String = "/"
'Define the data source definition.
Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = "data source=" & serverName & ";initial
catalog=" & databaseName
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = "SQL"
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = True
definition.Username = userName
definition.Password = password
'Use the default prompt string.
definition.Prompt = Nothing
definition.WindowsCredentials = False
Try
rs.CreateDataSource(name, parent, False, definition, Nothing)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End Sub
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ale Dori" <AleDori@.discussions.microsoft.com> wrote in message
news:B8D09C36-79B2-4027-A6B5-E49DE21B460B@.microsoft.com...
>I have a report with a data source that requires credentials to be stored
>in
> reporting service system.
> I use the CreateReport function to deploy this report but it doesn't store
> the credentials and these credentials are requested when it is about to
> render.
> How to store these credentials in order to avoid them to be asked during
> rendering'
> Thanks
>|||Is There a way to not create a shared data source?
I would the result obtained by the Deploy of Visual Studio.
"Jeff A. Stucker" wrote:
> In our case, we use script based on the scripting samples (in the RS install
> folder), but link the reports to a shared data source created with the
> credentials specfied:
> Public Sub CreateSQLDataSource(ByVal serverName as String, ByVal
> databaseName as String, ByVal userName as String, ByVal password as String)
> Dim name As String = databaseName
> Dim parent As String = "/"
> 'Define the data source definition.
> Dim definition As New DataSourceDefinition()
> definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
> definition.ConnectString = "data source=" & serverName & ";initial
> catalog=" & databaseName
> definition.Enabled = True
> definition.EnabledSpecified = True
> definition.Extension = "SQL"
> definition.ImpersonateUser = False
> definition.ImpersonateUserSpecified = True
> definition.Username = userName
> definition.Password = password
> 'Use the default prompt string.
> definition.Prompt = Nothing
> definition.WindowsCredentials = False
> Try
> rs.CreateDataSource(name, parent, False, definition, Nothing)
> Catch e As Exception
> Console.WriteLine(e.Message)
> End Try
> End Sub
>
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Ale Dori" <AleDori@.discussions.microsoft.com> wrote in message
> news:B8D09C36-79B2-4027-A6B5-E49DE21B460B@.microsoft.com...
> >I have a report with a data source that requires credentials to be stored
> >in
> > reporting service system.
> >
> > I use the CreateReport function to deploy this report but it doesn't store
> > the credentials and these credentials are requested when it is about to
> > render.
> >
> > How to store these credentials in order to avoid them to be asked during
> > rendering'
> >
> > Thanks
> >
>
>|||Look in RS Books Online for GetReportDataSources and DataSource.Item
property
Hopefully that points you in the right direction.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ale Dori" <AleDori@.discussions.microsoft.com> wrote in message
news:8D611134-94B3-4937-9372-61A00D6F6573@.microsoft.com...
> Is There a way to not create a shared data source?
> I would the result obtained by the Deploy of Visual Studio.
> "Jeff A. Stucker" wrote:
>> In our case, we use script based on the scripting samples (in the RS
>> install
>> folder), but link the reports to a shared data source created with the
>> credentials specfied:
>> Public Sub CreateSQLDataSource(ByVal serverName as String, ByVal
>> databaseName as String, ByVal userName as String, ByVal password as
>> String)
>> Dim name As String = databaseName
>> Dim parent As String = "/"
>> 'Define the data source definition.
>> Dim definition As New DataSourceDefinition()
>> definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
>> definition.ConnectString = "data source=" & serverName & ";initial
>> catalog=" & databaseName
>> definition.Enabled = True
>> definition.EnabledSpecified = True
>> definition.Extension = "SQL"
>> definition.ImpersonateUser = False
>> definition.ImpersonateUserSpecified = True
>> definition.Username = userName
>> definition.Password = password
>> 'Use the default prompt string.
>> definition.Prompt = Nothing
>> definition.WindowsCredentials = False
>> Try
>> rs.CreateDataSource(name, parent, False, definition, Nothing)
>> Catch e As Exception
>> Console.WriteLine(e.Message)
>> End Try
>> End Sub
>>
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "Ale Dori" <AleDori@.discussions.microsoft.com> wrote in message
>> news:B8D09C36-79B2-4027-A6B5-E49DE21B460B@.microsoft.com...
>> >I have a report with a data source that requires credentials to be
>> >stored
>> >in
>> > reporting service system.
>> >
>> > I use the CreateReport function to deploy this report but it doesn't
>> > store
>> > the credentials and these credentials are requested when it is about to
>> > render.
>> >
>> > How to store these credentials in order to avoid them to be asked
>> > during
>> > rendering'
>> >
>> > Thanks
>> >
>>

Create Procedure: SQL Server 2005

Hi everyone,

I'm creating a online e-letter registration system. That saves 'temp' values to a 'temp' table, and then sends a automated email to the subscriber, from which he/she can then except, or decline the registration.

bu now i want to create a procedure, or some kind of funciton in T-SQL that will be scheduled to run every 2 or so hours, to check and see if there is any values in the temp table that is older than 24hours, and if that values is greater than 24hours, it must delete that values from the temp table... so that the temp table doesn't become too full of old, outdated data.

Please help.

Thanks

OK, thats an easy thing, but we need more input to see what you will have to do. Which SQL Server are you using ? The delete part would be the easiest, as it would turn out to be something like

DELETE FROM SomeTable Where DATEDIFF(dd,SomeDatecolumn,GETDATE()) >= 1

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||what do you mean, "what SQL server am I using?" SQl Server 2005....|||OK, it would be interesting to know which SKU you are using (in detail if you use Express or not) because with any other edition than Express you would be able to use the SQL Server agent as well to plan the execution of your procedure. You will not need to create a stored procedure as the statement above will run fine using a simple job step with an TSQL execution element. Once the job step is created you will just have to schedule the execution of the job and you are done.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||no, this is not SQL Server Express, It can be either the Developer Edition, or the Enteprise Edition...|||Then go straight ahead by creating a job, defining a schedule and inserting one step for executing the TSQL statement mentioned above.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Create new row on the fly

I have data that looks like the following

ID
=====
123456
234567
345678
456789/567890
654321/765432/876543

In some instances, the older system had to store multiple IDs in one field and used a "/" delimiter. Does anyone have any ideas on how to create new rows on the fly and split the IDs out to the new rows?

Thanks in advance.

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

Or, if you know what the maximum number of values you will ever get in a single column are then you could use a Derived Column to split them up and an Unpivot transform to convert the values into new rows.

-Jamie

|||

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

|||

Kevin Rodgers wrote:

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

-Jamie

|||

Jamie Thomson wrote:

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

I may have more output rows than input rows, so it's an asynchronous component by definition, right?

Plus, I need the code that implements the ("," occurs in Input0Buffer.Row.ICS) pseudo-code and the substring(Input0Buffer.IC, start, length) pseudo-code.

|||

Just to follow through, here's the working code:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim index As Integer = 0

If Not (Row.CSV_IsNull) Then

For Each value As String In Row.CSV.ToString().Split(","c)

'Add a row to the output buffer:

Output0Buffer.AddRow()

'Preserve columns from the input buffer:

'Output0Buffer.key = Row.key

'Output0Buffer.CSV = Row.CSV

'Add output columns:

Output0Buffer.index = index

Output0Buffer.value = value

index += 1

Next

End If

End Sub

Create new row on the fly

I have data that looks like the following

ID
=====
123456
234567
345678
456789/567890
654321/765432/876543

In some instances, the older system had to store multiple IDs in one field and used a "/" delimiter. Does anyone have any ideas on how to create new rows on the fly and split the IDs out to the new rows?

Thanks in advance.

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

Or, if you know what the maximum number of values you will ever get in a single column are then you could use a Derived Column to split them up and an Unpivot transform to convert the values into new rows.

-Jamie

|||

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

|||

Kevin Rodgers wrote:

Jamie Thomson wrote:

You will need an asynchronous script component to do this. If you type that into Google you'll find loads of resources plus loads in BOL. reply here if you have any problems.

I'm facing the same problem and found your article at http://blogs.conchango.com/jamiethomson/archive/2005/07/25/1841.aspx, but I'm stuck due to my ignorance of Visual Basic and the SSIS API.

If I understand correctly, all I need to do is override the Input0_ProcesssInputRow method. Here's my pseudo-code to generate multiple rows whose "code" column is populated from substrings of the "ICS" column, separated by a ",":

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Output0Buffer.AddRow().code = substring(Input0Buffer.IC, start, length)

End While

End Sub

What does the real code to do that?

Thanks!

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

-Jamie

|||

Jamie Thomson wrote:

That won't work but the following might:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

While ("," occurs in Input0Buffer.Row.ICS)

Row.code = substring(Input0Buffer.IC, start, length)

End While

End Sub

You don't need to call AddRow() if its a synchronous component.

I may have more output rows than input rows, so it's an asynchronous component by definition, right?

Plus, I need the code that implements the ("," occurs in Input0Buffer.Row.ICS) pseudo-code and the substring(Input0Buffer.IC, start, length) pseudo-code.

|||

Just to follow through, here's the working code:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim index As Integer = 0

If Not (Row.CSV_IsNull) Then

For Each value As String In Row.CSV.ToString().Split(","c)

'Add a row to the output buffer:

Output0Buffer.AddRow()

'Preserve columns from the input buffer:

'Output0Buffer.key = Row.key

'Output0Buffer.CSV = Row.CSV

'Add output columns:

Output0Buffer.index = index

Output0Buffer.value = value

index += 1

Next

End If

End Sub

Create Directory with a File System Task

Hi!

I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.

I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:\myprog\20060913 and move my processed file there.

Can anyone help me? Please.

Here's how I did it:

1. Create your File System task

2. Set Operation to "Create Directory"

3. Under SourceConnection, select "<New Connection>"

4. Set Usage Type to "Create Folder"

5. Select any folder, or enter a dummy value for "File" - we'll be setting this with an expression

5. Click OK, and then select your new file connection in the Connection Managers window

6. On the properties window, bring up the Property Expression Editor

7. Select the "ConnectionString" property

8. For the expression, use this:

"c:\\myprog\\" + SUBSTRING( @.[User::filename], 9, 8 )

(replace User::filename with the name of your variable).

Hope that helps!

|||Thanks Matt! I believe this is what I want to create the directory.|||

There is one additional problem I'm having with this. My filename variable is perflog_*.aud, because it goes through multiple flat files, so the SUBSTRING(@.[user::filename,9,8) is picking up *.aud. Please tell me what I'm doing wrong...

Thanks

|||

Are you using a Foreach Loop Container to go through your flat files? If your variable contains the wildcard character, it sounds like you haven't setup the container to store the current filename properly. You might want to take a look at the help page for the Foreach Loop. Step 7 covers how to map the file to a variable.

|||Thanks again Matt, that was my problem!|||

I seem to be running into another problem with this.

The create directory works fine for the first file that is moved. In the second iteration of the for loop I get a warning that the the directory exists, which is fine and I have selected use directory if it exists. The problem is that I then get an error,

Error: 0xC002F304 at Create Directory Task, File System Task: An error occurred with the following error message: "The directory is not empty.

".

What am I missing?

Thanks

|||

I'm having the same problem that you have listed here where the first create directory call works fine but the second (ie. if the directory exists) fails even though I have specified that this should not be an error (ie. Set UseDirectoryIfExists = True on the task).

I was wondering if you've found a solution to this problem.

Thanks,

Matt

Create Directory with a File System Task

Hi!

I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.

I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:\myprog\20060913 and move my processed file there.

Can anyone help me? Please.

Here's how I did it:

1. Create your File System task

2. Set Operation to "Create Directory"

3. Under SourceConnection, select "<New Connection>"

4. Set Usage Type to "Create Folder"

5. Select any folder, or enter a dummy value for "File" - we'll be setting this with an expression

5. Click OK, and then select your new file connection in the Connection Managers window

6. On the properties window, bring up the Property Expression Editor

7. Select the "ConnectionString" property

8. For the expression, use this:

"c:\\myprog\\" + SUBSTRING( @.[User::filename], 9, 8 )

(replace User::filename with the name of your variable).

Hope that helps!

|||Thanks Matt! I believe this is what I want to create the directory.|||

There is one additional problem I'm having with this. My filename variable is perflog_*.aud, because it goes through multiple flat files, so the SUBSTRING(@.[user::filename,9,8) is picking up *.aud. Please tell me what I'm doing wrong...

Thanks

|||

Are you using a Foreach Loop Container to go through your flat files? If your variable contains the wildcard character, it sounds like you haven't setup the container to store the current filename properly. You might want to take a look at the help page for the Foreach Loop. Step 7 covers how to map the file to a variable.

|||Thanks again Matt, that was my problem!|||

I seem to be running into another problem with this.

The create directory works fine for the first file that is moved. In the second iteration of the for loop I get a warning that the the directory exists, which is fine and I have selected use directory if it exists. The problem is that I then get an error,

Error: 0xC002F304 at Create Directory Task, File System Task: An error occurred with the following error message: "The directory is not empty.

".

What am I missing?

Thanks

|||

I'm having the same problem that you have listed here where the first create directory call works fine but the second (ie. if the directory exists) fails even though I have specified that this should not be an error (ie. Set UseDirectoryIfExists = True on the task).

I was wondering if you've found a solution to this problem.

Thanks,

Matt

Create Directory with a File System Task

Hi!

I'm having a bit of a problem implementing a File System Task to Create a directory and would appreciate some help if possible.

I want to create a date directory so I can move files to once they are imported successfully. The date portion for the directory comes from the import file whose name is variable and in the format of PerfLog_<yyyymmdd>.aud. So, in essence, if I am processing a file named Perflog_20060913.aud, when I am done processing it I want to create a directory c:\myprog\20060913 and move my processed file there.

Can anyone help me? Please.

Here's how I did it:

1. Create your File System task

2. Set Operation to "Create Directory"

3. Under SourceConnection, select "<New Connection>"

4. Set Usage Type to "Create Folder"

5. Select any folder, or enter a dummy value for "File" - we'll be setting this with an expression

5. Click OK, and then select your new file connection in the Connection Managers window

6. On the properties window, bring up the Property Expression Editor

7. Select the "ConnectionString" property

8. For the expression, use this:

"c:\\myprog\\" + SUBSTRING( @.[User::filename], 9, 8 )

(replace User::filename with the name of your variable).

Hope that helps!

|||Thanks Matt! I believe this is what I want to create the directory.|||

There is one additional problem I'm having with this. My filename variable is perflog_*.aud, because it goes through multiple flat files, so the SUBSTRING(@.[user::filename,9,8) is picking up *.aud. Please tell me what I'm doing wrong...

Thanks

|||

Are you using a Foreach Loop Container to go through your flat files? If your variable contains the wildcard character, it sounds like you haven't setup the container to store the current filename properly. You might want to take a look at the help page for the Foreach Loop. Step 7 covers how to map the file to a variable.

|||Thanks again Matt, that was my problem!|||

I seem to be running into another problem with this.

The create directory works fine for the first file that is moved. In the second iteration of the for loop I get a warning that the the directory exists, which is fine and I have selected use directory if it exists. The problem is that I then get an error,

Error: 0xC002F304 at Create Directory Task, File System Task: An error occurred with the following error message: "The directory is not empty.

".

What am I missing?

Thanks

|||

I'm having the same problem that you have listed here where the first create directory call works fine but the second (ie. if the directory exists) fails even though I have specified that this should not be an error (ie. Set UseDirectoryIfExists = True on the task).

I was wondering if you've found a solution to this problem.

Thanks,

Matt

create database at shared hoster site

I have an sql script that I ran on my local system using osql. It
created all the tables, views, and even inserted sample data into the
tables.

Now I need to create that same database on a shared hoster site. Only
the hoster does not allow the running of osql.

How can I create my sql server database on my shared hoster site?

Can I save and restore my database somehow?

Is there a stored procedure I can run which will run the .sql script
file?

thanks,

-SteveThat depends on how your provider expects you to execute SQL code - if
they don't allow osql.exe, then what do they allow? And how do you
connect to the hosted database - do you use Enterprise Manager? Since
osql.exe is just a client application, if you can connect to the SQL
server then you should be able to use it.

Simon|||Simon Hayes wrote:
> That depends on how your provider expects you to execute SQL code -
if
> they don't allow osql.exe, then what do they allow?

the hoster is godaddy.com You do get a panel where you can manually
config the database. There is something called "Query Analyser" which
provides a window to run sql statements. I dont think this is the same
as the Microsoft Query Analyzer that I read about yesterday. In this
godaddy qa window I cannot "run sqlscript.sql" like I think you can do
in Microsoft Query Analyzer.

What I was able to do was cut and paste large sections of my script.sql
from my PC editor into the GoDaddy "Query Analyser" window. Then I
clicked the "submit" button and it basically worked.

> And how do you
> connect to the hosted database - do you use Enterprise Manager?

> Since
> osql.exe is just a client application, if you can connect to the SQL
> server then you should be able to use it.

well my asp.net code can use the SqlConnection class to connect to the
database, then use SqlCommand to run SQL commands. Can osql be run in
that context?

I am curious to know if there is a stored procedure I can use which
will run an .sql script.

thanks,

-Steve|||You might be able to re-use this code in ASP:

http://groups-beta.google.com/group...e72b5441b?hl=en

A web interface which doesn't allow uploading scripts is quite limited
- I would definitely ask the provider if they can enhance it.

There's no stored proc to run a file, probably because the server would
need to have access to the script file somehow, which is tricky if it's
on your workstation. It's common to use xp_cmdshell to call osql.exe as
a way of executing scripts, but that's only really useful for syadmins,
because of the security implications.

Simon|||Simon Hayes wrote:
> You might be able to re-use this code in ASP:
>
http://groups-beta.google.com/group...e72b5441b?hl=en

that looks to be exactly what I am looking for.

thanks very much,

-Steve

> A web interface which doesn't allow uploading scripts is quite
limited
> - I would definitely ask the provider if they can enhance it.
> There's no stored proc to run a file, probably because the server
would
> need to have access to the script file somehow, which is tricky if
it's
> on your workstation. It's common to use xp_cmdshell to call osql.exe
as
> a way of executing scripts, but that's only really useful for
syadmins,
> because of the security implications.
> Simon