how to rename files before send files?

I'm using script task to rename files inside foreach loop files,code like shown below

Dim file As New System.IO.FileInfo(CStr(Dts.Variables("User::FileName").Value))

dim newname as string = Split(file.Name, "_")(0) & ".jpg"
Dts.Variables("User::outputname").Value = file.DirectoryName & "\" & newname

Then,i use ftp task to send files,but prompt "the variables User::outputname doesn't contains file path(s)"

I tried to use file system task to perform that,but failed either

Rename file operation in file system only can rename a file in a specified location,who can help me?

TIA

Have you looked at what outputname does contain? Does the file specified exist as it should for the FTP task?

The File System Task can use variables, which themselves can be used to supply expressions, see the EvaluateAsExpression property and set the Expression property. This way you can use dynamic paths in the File System Task.

sql

How to remove WITH FILLFACTOR = 100 when Generate Sql Script ?

Hello,

I'm using Entreprise Manager (for Sql Server 2000) to generate my
database's script. By mistake, i've changer FillFactor one time. And,
now I can't remove this data from generated sql script. How to remove
that ?

Thank's a lot.rabii (rabii.mail@.gmail.com) writes:
> I'm using Entreprise Manager (for Sql Server 2000) to generate my
> database's script. By mistake, i've changer FillFactor one time. And,
> now I can't remove this data from generated sql script. How to remove
> that ?

Can't you just run the file through a search/replace session in some
text editor? Then you could build a new database, and then script
from that one.

Even better, have all your SQL code under version control, so you
don't have to script from the databaes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for reply :),

I know this solutions :), but, the problem that I have a production
database. For each new version of my product, I have to compare my new
database schema and the old one (current production database). So the
tool "Sql Server Compare" (by comparing the databases script) show me
always that all the table are different although the difference is
simply the FILLFACTOR (in production database). Now I can change it
using Entreprise Manager but I can't remove it ?|||If that's Red-Gate's SQL Compare, I think that there is an option to
ignore fillfactors, among other things. Unfortunately, I'm at a machine
that doesn't have that tool installed, so I can't confirm that. I know
that you can definitely ignore some things, and I *think* that
FILLFACTOR is one of them.

Good luck,
-Tom.|||rabii (rabii.mail@.gmail.com) writes:
> I know this solutions :), but, the problem that I have a production
> database. For each new version of my product, I have to compare my new
> database schema and the old one (current production database). So the
> tool "Sql Server Compare" (by comparing the databases script) show me
> always that all the table are different although the difference is
> simply the FILLFACTOR (in production database). Now I can change it
> using Entreprise Manager but I can't remove it ?

So where is the fill factor wrong? In the development database or in
the production database? But whichever, can't you just adapt the
fillfactor of the development database to the production database?

Then again, fill factor is one of these things that could be different
from database to database, because different instances of the schema
has different data load. So you should probably see if your comparison
tool can ignore difference in fill factor.

Finally, I can't keep from saying that the whole thing of comparing
database schemas to build change scripts is not a very good idea. Do
you really want all sort of test junk in the dev database hit the
production server? If you have your code under version control,
you can build change scripts from the version-control system. This
gives you a much more solid base to stand on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks again for every one :)

I've said that I'm using the tool "Sql Server Compare" (it's a free
tool).

You're right when you said that it is better to use a version control
solution. But now the problem, that by mistake, I've added fillfactor =
x in the production database. In the dev database, I havn't did that.
When I compare the two databases, I notice that the comprator (sql
server compare) show me that all the table are different. When I
analyse the code, simply the fill factor is not the same. I can resolve
that by adding the same fillfactor in my dev database but I wan't to
know " if there is a way by script to remove fillfactor = x" ?|||rabii (rabii.mail@.gmail.com) writes:
> I've said that I'm using the tool "Sql Server Compare" (it's a free
> tool).
> You're right when you said that it is better to use a version control
> solution. But now the problem, that by mistake, I've added fillfactor =
> x in the production database. In the dev database, I havn't did that.
> When I compare the two databases, I notice that the comprator (sql
> server compare) show me that all the table are different. When I
> analyse the code, simply the fill factor is not the same. I can resolve
> that by adding the same fillfactor in my dev database but I wan't to
> know " if there is a way by script to remove fillfactor = x" ?

For an index you should be able to get rid of with with CREATE INDEX ...
WITH DROP_EXISTING. If it's a PRIMARY KEY constraint of a UNIQUE constraint,
I think you have to drop and recreate. Since you typically have FK
constraints to a PK, that can be kind of messy.

I played around a little, and it seems that Enterprise Manager does not
include fillfactor for primary keys, so changing your dev database may
not help.

Looks as if you either have to rebuild the production database or give
SQL Server Compare the boot...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

how to remove the sql server registry mess?

after uninstalling the sql server and related stuff, I found tons of sql
server registry entries.
How do remove them all properly? any script or tools. I tried the setup
form disk to remove but still no joyHi
You don't give the version you are trying to remove, but the following may
help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful if
it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
John
"gs" wrote:

> after uninstalling the sql server and related stuff, I found tons of sql
> server registry entries.
> How do remove them all properly? any script or tools. I tried the setup
> form disk to remove but still no joy
>
>|||thank you. I need the info the 2005
what about the express version also
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...[vbcol=seagreen]
> Hi
> You don't give the version you are trying to remove, but the following may
> help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful
> if
> it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
> John
> "gs" wrote:
>|||Hi
I would expect SQLExpress to be the same or very similar.
John
"gs" wrote:

> thank you. I need the info the 2005
>
> what about the express version also
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...
>
>sql

how to remove the sql server registry mess?

after uninstalling the sql server and related stuff, I found tons of sql
server registry entries.
How do remove them all properly? any script or tools. I tried the setup
form disk to remove but still no joy
Hi
You don't give the version you are trying to remove, but the following may
help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful if
it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
John
"gs" wrote:

> after uninstalling the sql server and related stuff, I found tons of sql
> server registry entries.
> How do remove them all properly? any script or tools. I tried the setup
> form disk to remove but still no joy
>
>
|||thank you. I need the info the 2005
what about the express version also
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...[vbcol=seagreen]
> Hi
> You don't give the version you are trying to remove, but the following may
> help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful
> if
> it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
> John
> "gs" wrote:
|||Hi
I would expect SQLExpress to be the same or very similar.
John
"gs" wrote:

> thank you. I need the info the 2005
>
> what about the express version also
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...
>
>

how to remove the sql server registry mess?

after uninstalling the sql server and related stuff, I found tons of sql
server registry entries.
How do remove them all properly? any script or tools. I tried the setup
form disk to remove but still no joyHi
You don't give the version you are trying to remove, but the following may
help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful if
it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
John
"gs" wrote:
> after uninstalling the sql server and related stuff, I found tons of sql
> server registry entries.
> How do remove them all properly? any script or tools. I tried the setup
> form disk to remove but still no joy
>
>|||thank you. I need the info the 2005
what about the express version also
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...
> Hi
> You don't give the version you are trying to remove, but the following may
> help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful
> if
> it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
> John
> "gs" wrote:
>> after uninstalling the sql server and related stuff, I found tons of sql
>> server registry entries.
>> How do remove them all properly? any script or tools. I tried the setup
>> form disk to remove but still no joy
>>|||Hi
I would expect SQLExpress to be the same or very similar.
John
"gs" wrote:
> thank you. I need the info the 2005
>
> what about the express version also
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:3AF5AC75-F591-44D5-B2FA-EC696F21B2C7@.microsoft.com...
> > Hi
> >
> > You don't give the version you are trying to remove, but the following may
> > help http://msdn2.microsoft.com/en-us/library/aa337087.aspx may be useful
> > if
> > it is SQL 2005 or http://support.microsoft.com/?id=290991 for SQL 2000.
> >
> > John
> >
> > "gs" wrote:
> >
> >> after uninstalling the sql server and related stuff, I found tons of sql
> >> server registry entries.
> >>
> >> How do remove them all properly? any script or tools. I tried the setup
> >> form disk to remove but still no joy
> >>
> >>
> >>
>
>

How to remove all non-system service broker objects

I'm looking for a tool or script that can wipe a database clean of any and all user configured service broker objects.

I've got two environments, Development and QA, that I need to have parallel service broker configurations. Each environment is hosted on a different set of servers. I need to make sure the Development environment looks exactly like the QA environment from a Service Broker perspective (other than the specific service broker instance references in the installed routes). I've got a script to build the objects that I want, but the Development environment is full of artifacts of abandoned experiments. Before running the creation script in Development, I'd like to start with a clean slate. Recreating the Development databases is not an option.

If a tool or script is not available, I know I need to adress at least the following object types, but I'm not sure if this list covers everything or in which order I need to drop which objects: routes, remote service bindings, certificates, database principals, tcp endpoints, services, queues, contracts, and message types

Thanks in advance

Lee

I would suggest to create a script that drops all types of objects (services, contracts, message types, queues, routes, remote service bindings). One easy criteria to choose wheter is a 'system' object or not is to look if a similar object (same type and name) exists in [master]. If it does, then is 'system'. Each of these types has a coresponding metadata view: sys.services, sys.service_contracts, sys.service_message_types, sys.service_queues, sys.routes, sys.re:mote_service_bindings.

Attempting to drop a true 'system' object would fail. But there is one object that is implicitly created, but is not trully system: the 'AutoCreatedLocal' route in each database. this route can be altered and dropped, it is not a system object.

HTH,
~ Remus

How to release large unused/allocated space

Hi,

I have 6 filegroups that contain over 30 gigs of data. I manage the transaction log space efficiently by using a script and 'shrink database'. But, the allocated unused space of the data files are very large, over 30 gigs.

The configuration is standard for all files:

- Automatically grow file by percent

- Maximum file size - Unrestricted

How do I release the unused space?

Thanks,

- gshaf

Hi Gshaf,

You can issue the DBCC SHRINKDATABASE statement with TRUNCATEONLY. This will shrink the database the maximum amount possible, and release the space back to windows. If you want to control how much space in the db left for growth, you'll need to issue DBCC SHRINKFILE and specify the amount (MB) of space you want left:

DBCC SHRINKFILE(MyDataFileNameHere, 15000)

The above command will attempt to shrink the datafile "MyDataFileNameHere" to 15GB.

Cheers,

Rob

sql

How to release large unused/allocated space

Hi,

I have 6 filegroups that contain over 30 gigs of data. I manage the transaction log space efficiently by using a script and 'shrink database'. But, the allocated unused space of the data files are very large, over 30 gigs.

The configuration is standard for all files:

- Automatically grow file by percent

- Maximum file size - Unrestricted

How do I release the unused space?

Thanks,

- gshaf

Hi Gshaf,

You can issue the DBCC SHRINKDATABASE statement with TRUNCATEONLY. This will shrink the database the maximum amount possible, and release the space back to windows. If you want to control how much space in the db left for growth, you'll need to issue DBCC SHRINKFILE and specify the amount (MB) of space you want left:

DBCC SHRINKFILE(MyDataFileNameHere, 15000)

The above command will attempt to shrink the datafile "MyDataFileNameHere" to 15GB.

Cheers,

Rob

How to reference a shared schedule by Name ?

I'm generating a rss script for a report that will be depolyed to a new
server. If the report uses a shared schedule for its history snapshot then
in the script I need to pass a ScheduleReference to SetReportHistoryOptions.
What's the easiest way of interogating the new server to see if a Shared
Schedule with the same name exists and get a reference to it.
GetScheduleProperties needs a ScheduleID but this will be different between
the 2 servers. Since Shared Schedule names have to be unique why can't we
create a ScheduleReference based on the name of the Shared schedule? The
only other way I can think of is to call ListSchedules and then loop through
looking for one with the same name but this seems inefficient on a per
report basis.
--
Cheers,
JasYes, it is inefficient to have to loop through all the schedules,
unfortunately it is the only way to do it. By the way, how do you guarantee
that schedules with the same name will have the same reoccurrence pattern?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%230j1zQEFFHA.1836@.tk2msftngp13.phx.gbl...
> I'm generating a rss script for a report that will be depolyed to a new
> server. If the report uses a shared schedule for its history snapshot then
> in the script I need to pass a ScheduleReference to
> SetReportHistoryOptions. What's the easiest way of interogating the new
> server to see if a Shared Schedule with the same name exists and get a
> reference to it. GetScheduleProperties needs a ScheduleID but this will be
> different between the 2 servers. Since Shared Schedule names have to be
> unique why can't we create a ScheduleReference based on the name of the
> Shared schedule? The only other way I can think of is to call
> ListSchedules and then loop through looking for one with the same name but
> this seems inefficient on a per report basis.
> --
> Cheers,
> Jas
>|||By having standards and trying to stick to them :-) Shared schedules would
be set up by a DBA so as a project moves throught the environments, if a
need for a schedule is identified, the DBA should check to see if a suitable
shared schedule exists and get the developers to use that. If it doesn't
then create one and include it as part of the deployment package.
If I want to be ultra careful then when scripting a report on Server A that
reference a shared schedule I'd include the shared schedule definition in
the script so that if I do find one on Server B with the same name (which I
always should - in theory!) I can check the definitions are the same or
issue a warning/abort report creation. I could also optionally create the
schedule if it doesn't exist (but I'd rather not do that in the report
script)
At the simplest level i.e. just the report it's fairly easy to transport
across environments but as I keep adding all the various options and
properties (especially the scheduling stuff) to my scripting tool it's
getting more and more complex :-)
--
Cheers,
Jas
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:eR24d$TFFHA.2600@.TK2MSFTNGP09.phx.gbl...
> Yes, it is inefficient to have to loop through all the schedules,
> unfortunately it is the only way to do it. By the way, how do you
> guarantee that schedules with the same name will have the same
> reoccurrence pattern?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:%230j1zQEFFHA.1836@.tk2msftngp13.phx.gbl...
>> I'm generating a rss script for a report that will be depolyed to a new
>> server. If the report uses a shared schedule for its history snapshot
>> then in the script I need to pass a ScheduleReference to
>> SetReportHistoryOptions. What's the easiest way of interogating the new
>> server to see if a Shared Schedule with the same name exists and get a
>> reference to it. GetScheduleProperties needs a ScheduleID but this will
>> be different between the 2 servers. Since Shared Schedule names have to
>> be unique why can't we create a ScheduleReference based on the name of
>> the Shared schedule? The only other way I can think of is to call
>> ListSchedules and then loop through looking for one with the same name
>> but this seems inefficient on a per report basis.
>> --
>> Cheers,
>> Jas
>>
>

How to reference a dataset passed to script

Hi, I have a dataset created via the Execute SQL Statement control and wish to pass it into a script control that within the vb.net script I wish to perform different functions to it.

The main goal is to take the dataset loop through it and record values into an array then take that array and loop through that so I can send an email containing the information within the array.

Can anyone help?

If inside a script component the common method is to use row

So you can do row.xxx where xxx is the field name... then using row.next row etc you can loop and such.

|||Ok, how do we reference the dataset? I appreciate your answer Dave but its very vague.

So I have the execute SQL statement create a dataset named "dataset1" using an ado.net object type.

Then within the script component how to we initialize, and work with "dataset1"? I wish to loop through "dataset1" and perform various functions on it. Sure the row access sounds great for the looping part but what of the other parts?|||Use a foreach loop to open that ADO recordset and loop through it. There are plenty of examples in this forum.|||I know that method and that won't work for what i'm trying to do, or at least i don't think so.

All I want to know is how to reference a dataset from an execute sql statement within a script component. I wish to reference this dataset within the script component and loop through and create an array with specific information. Then I want to use the array as loop through that to create a body of an email. So PLEASE answer the question of ->>>>>

How do I reference a dataset after it being populated via an Execute SQL Statement?

<<<<<--
I tried
dim results as dataset
dim reader as datatablereader
reader = results.tables(0).createdatareaders
do while reader.read
execute statements
loop

I get the error that reader is referenced to no table.|||

I misunderstood your initial question, I thought you needed basic access. I was thinking you used the execute sql command component to create the data set. So if you execute the sql, and then have the output of that component connect to a script component you can just use the Row call I mentioned, because it automatically ties to the "input" dataset from the sql execute component.

|||Here is my code. I have OBJ_NULL_SET as the result set object from the execute sql components output.

Public Sub Main()
'Assume success

Try
Dim results As DataSet

results = CType(ReadVariable("OBJ_NULL_SET"), DataSet)
For Each tbl As DataTable In results.Tables
System.Windows.Forms.MessageBox.Show(tbl.TableName)
Next

Catch ex As Exception
Dts.Events.FireError(-1, "N/A", ex.ToString(), "", 0)
End Try
Dts.TaskResult = Dts.Results.Success

End Sub

Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object

Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try

Return result
End Function

When the msgbox pops up it says Table with nothing else. It does not display the name of the dataset so i'm sure that i've done something wrong where the dataset is not being referenced properly.|||

You are already referencing the dataset by iterating through the DataTables. The first table's name so happens to be "Table", and that's what the message box call shows.

So, for the only DataTable of the DataSet, iterate through the rows using standard ADO.NET. For example, call this function right after you're first MsgBox() call, and it will display the column names and stringify the data. You're refencing the data from the Execute SQL task at this point, and what is done beyond that is up to your imagination.

Private Sub DisplayTable(ByVal tbl As DataTable)

Dim dr As DataRow, dc As DataColumn

Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()

For colNum As Integer = 0 To tbl.Columns.Count - 1

dc = tbl.Columns(colNum)

sb.Append(dc.ColumnName).Append(",")

Next

Dts.Events.FireInformation(1, "Columns", sb.ToString(), "", 0, True)

For rowNum As Integer = 0 To tbl.Rows.Count - 1

sb.Remove(0, sb.Length)

dr = tbl.Rows(rowNum)

For itemOffset As Integer = 0 To dr.ItemArray.Length - 1

sb.Append(dr.ItemArray(itemOffset)).Append(",")

Next

Dts.Events.FireInformation(1, "Data", sb.ToString(), "", 0, True)

Next

End Sub

sql

How to recreate replication system objects?

Does anyone know what script I need to run to recreate the
replication system objects in MSSQL?
I believe it's in the Install Directory after installing SQL?
Will it destroy any existing publications/subscriptions on the server?
I have 2 other publications that are running I want to keep.
Thanks
RClick Replication component/ Generate SQL Script.
"Chris" <chriswinstone@.aol.com> wrote in message
news:1125399221.478160.216810@.o13g2000cwo.googlegr oups.com...
> Does anyone know what script I need to run to recreate the
> replication system objects in MSSQL?
> I believe it's in the Install Directory after installing SQL?
> Will it destroy any existing publications/subscriptions on the server?
> I have 2 other publications that are running I want to keep.
> Thanks
>
|||I'm guessing here, but did you have a failed installation? If so, then
reapplying sp4 should do the trick.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
No I didn't have a failed installation as such. The replication was working
fine for about 18 months then all of a sudden I started getting errors from
the merge agent after executing sp_MSfetchidentityrange saying it could not
retrieve identity range resource for the table [TABLENAME].
A post from Hilary Cotter pointed me in the direction of the
msrepl_identity_range tables, however I decided to script the publication
for backup and try removing it. So I ran sp_removedreplication (probably too
hasty on that one!) then tried to run the puplication gen script thats when
I started getting the invalid object name 'syspublications' This is where I
am at at the moment. no replication at all! I can't even remove replication
manually - documented in Q324401!
I'm really stuck! Can't go forward or back! So I thought maybe recreating
the replication system objects would help me go in at least one direction!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uyQXnTZrFHA.464@.TK2MSFTNGP15.phx.gbl...
> I'm guessing here, but did you have a failed installation? If so, then
> reapplying sp4 should do the trick.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Chris,
what about if you disable replication then reenable, then execute your
scripts?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul
How do you do this? In QA or EM?
Thanks
Paul Ibison wrote:
> Chris,
> what about if you disable replication then reenable, then execute your
> scripts?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||Chris,
in EM - 'Tools, Replication, Disable Publishing'.
HTH,
Paul Ibison
|||Thanks Paul
Thats worked!
I now have a problem when creating my scripted publication again! See later
post.
Thanks again!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%236JW0YjrFHA.3060@.TK2MSFTNGP09.phx.gbl...
> Chris,
> in EM - 'Tools, Replication, Disable Publishing'.
> HTH,
> Paul Ibison
>

How to recover Master database table data

Hi all, I am working with SQL Server2000 and I have done a horrible thing here.
We here have an script that delete all data from all user tables of a database, and I run it in the master DATABASE.
As we don't made backups of this database, now somethings of the database aren't working.

Here is the script:

declare @.table_name sysname
declare @.alter_table_statement varchar(256)
declare @.delete_statement varchar(256)

-- definindo o cursor...
declare table_name_cursor cursor local fast_forward for
select
name
from
sysobjects
where
xtype = 'U'
and
name <> 'dtproperties'

-- desligando os vínculos...
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
end
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor

deallocate table_name_cursor

I have tried to restore master table with the restore function, but it doesn't work. When I try to do this I received a message informing that it can't copy the data because one file was in use. The server was in a single user mode.

Is there anyway to recover the data that I have lost?

Help me understand this.

At the beginning, you say that you don't have backups of the master database.

At the end you say that you tried to restore the master database, but had problems.

You should be able to restore the master database if you have a backup of it, and this would be the best method to use.

Can you clarify, and also let us know what method (T-SQL commands etc.) you used to attempt the restore?

|||Kevin don't have any backup of this database, I tried to rebuild it, not restore it, I used the wrong word for it.|||

There isn't any easy answer to this. If you have already rebuilt master then you should have SQL Server up and running with a clean master. Realize that by rebuilding the master database all your jobs and maintenance plans in MSDB have been lost, unless you have a backup of msdb or scripts to recreate the jobs.

The datafiles for your user databases should still be on the disk, you will need to attach those using sp_attach_db.

Once you have the databases attached you still won't have the logins. You will need to recreate the logins, then remap those new logins using the procedure outlined in http://support.microsoft.com/kb/274188/en-us

|||Ok thanks... one more explanation
In fact my real problem isn't on the databases and user logins, becaus this I doesn't lost. I lost the access to table metadata (via jdbc driver) and the permission to create diagrams. Probably this can be done by reconfiguring the server, but I doesn't know how to do it.

How to recover deleted data from master database

Hi all,
I made a horrible thing this week. I have an script that delete all data from all user tables, and I run it in the master database.
After this I couldn't access the metadata from the tables of my databases using a JDBC connection.
My script runs over all sysobject that are different from dtproperties.
I don't have a back up of master table. I have tried to copy the data from another master database (from another machine) but I did not work. I tried to copy from a xls, but it did'nt work too. I try do recover the master database (as it is explained in the online books) but it did not work either.

Someone know how can I recover this data?Here is the script:

declare @.table_name sysname
declare @.alter_table_statement varchar(256)
declare @.delete_statement varchar(256)

declare table_name_cursor cursor local fast_forward for
select
name
from
sysobjects
where
xtype = 'U'
and
name <> 'dtproperties'

open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
end
close table_name_cursor

-- ligando os vnculos...
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor

deallocate table_name_cursor

And here is the names of the tables of master database that this script had deleted all the containing data:
spt_monitor
spt_values
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_provider_types
spt_datatype_info_ext
MSreplication_options
spt_datatype_info
spt_server_info
spt_server_info

How to record if a Linked Server is offline?

I created the below script to track my linked servers. I set it up that it
would record a record for each server along with all its corresponding
databases. Unfortunately, I have not been able to successfully track a
server which is offline.
Currently, I am using cmdshell to ping linked servers and return any that
have a 'timed out' string. I feel that there must be a better way to see if
a linked server is offline via TSQL.
Thanks in Advance.
Drake
DECLARE @.DatabaseName VARCHAR(50)
,@.DatabaseNameString VARCHAR(200)
,@.DBString VARCHAR(200)
,@.Server VARCHAR(100)
,@.SQLcmd NVARCHAR(500)
,@.Error int
,@.Errormsg varchar(100)
--BEGIN TRAN
--SET NOCOUNT ON
DECLARE SERVERCRSOR CURSOR FOR
select srvname from sysservers order by srvname
OPEN SERVERCRSOR
FETCH NEXT FROM SERVERCRSOR INTO @.Server
WHILE @.@.FETCH_STATUS = 0
BEGIN
BEGIN
set @.SQLcmd = 'IF EXISTS(select name from [' + @.Server +
'].master.dbo.sysdatabases)
DECLARE STATUSCRSOR CURSOR FOR SELECT Name FROM [' + @.Server +
'].master.dbo.sysdatabases'
EXEC sp_executesql @.SQLcmd ,N'@.Server varchar(255)' ,@.Server
select @.Error = @.@.Error
if @.Error <> 0
begin
set @.Errormsg = 'Linked SQL Server does not exist or access denied.'
Insert into SQLMONITOR.dbo.tblSQLServerStatus (szLinkedServer_nm,
szDatabases_nm, dtStatusdate_dt)
Select @.Server, '(' + @.Errormsg + ')', getdate() DateStatus
raiserror ('test',1,1)
end
OPEN STATUSCRSOR
FETCH NEXT FROM STATUSCRSOR INTO @.DatabaseName
Select @.DBString = @.DatabaseName
WHILE @.@.FETCH_STATUS = 0
BEGIN
Select @.DBString = @.DBString + ',' + @.DatabaseName
FETCH NEXT FROM STATUSCRSOR INTO @.DatabaseName
Select @.DatabaseNameString = @.DBString
END
Insert into SQLMONITOR.dbo.tblSQLServerStatus (szLinkedServer_nm,
szDatabases_nm, dtStatusdate_dt)
Select @.Server, '(' + @.DatabaseNameString + ')', getdate() DateStatus
END
FETCH NEXT FROM SERVERCRSOR INTO @.Server
CLOSE STATUSCRSOR
DEALLOCATE STATUSCRSOR
END
CLOSE SERVERCRSOR
DEALLOCATE SERVERCRSOR
--COMMIT
GOYou could try to do a select count(*) from <<linked
server>>.pubs.dbo.authors and see if anything comes back.|||I need to clarify my above response - I have a job that contains 3
steps. Step #1 contains the SELECT statement against the linked
server. Step #1, on success, goes to step #2 which handles some
processing on the remote server. Step #1, on failture, goes to step
#3 which informs me that the linked server is not available.
Make sure that step #2 skips step #3.

How to record if a Linked Server is offline?

I created the below script to track my linked servers. I set it up that it
would record a record for each server along with all its corresponding
databases. Unfortunately, I have not been able to successfully track a
server which is offline.
Currently, I am using cmdshell to ping linked servers and return any that
have a 'timed out' string. I feel that there must be a better way to see if
a linked server is offline via TSQL.
Thanks in Advance.
Drake

DECLARE @.DatabaseName VARCHAR(50)
,@.DatabaseNameString VARCHAR(200)
,@.DBString VARCHAR(200)
,@.Server VARCHAR(100)
,@.SQLcmd NVARCHAR(500)
,@.Error int
,@.Errormsg varchar(100)
--BEGIN TRAN
--SET NOCOUNT ON
DECLARE SERVERCRSOR CURSOR FOR
select srvname from sysservers order by srvname
OPEN SERVERCRSOR
FETCH NEXT FROM SERVERCRSOR INTO @.Server
WHILE @.@.FETCH_STATUS = 0
BEGIN
BEGIN
set @.SQLcmd = 'IF EXISTS(select name from [' + @.Server +
'].master.dbo.sysdatabases)
DECLARE STATUSCRSOR CURSOR FOR SELECT Name FROM [' + @.Server +
'].master.dbo.sysdatabases'
EXEC sp_executesql @.SQLcmd ,N'@.Server varchar(255)' ,@.Server
select @.Error = @.@.Error
if @.Error <> 0
begin
set @.Errormsg = 'Linked SQL Server does not exist or access denied.'
Insert into SQLMONITOR.dbo.tblSQLServerStatus (szLinkedServer_nm,
szDatabases_nm, dtStatusdate_dt)
Select @.Server, '(' + @.Errormsg + ')', getdate() DateStatus
raiserror ('test',1,1)
end
OPEN STATUSCRSOR
FETCH NEXT FROM STATUSCRSOR INTO @.DatabaseName
Select @.DBString = @.DatabaseName
WHILE @.@.FETCH_STATUS = 0
BEGIN
Select @.DBString = @.DBString + ',' + @.DatabaseName
FETCH NEXT FROM STATUSCRSOR INTO @.DatabaseName
Select @.DatabaseNameString = @.DBString
END
Insert into SQLMONITOR.dbo.tblSQLServerStatus (szLinkedServer_nm,
szDatabases_nm, dtStatusdate_dt)
Select @.Server, '(' + @.DatabaseNameString + ')', getdate() DateStatus
END
FETCH NEXT FROM SERVERCRSOR INTO @.Server
CLOSE STATUSCRSOR
DEALLOCATE STATUSCRSOR
END
CLOSE SERVERCRSOR
DEALLOCATE SERVERCRSOR
--COMMIT
GO

You could try to do a select count(*) from <<linked
server>>.pubs.dbo.authors and see if anything comes back.
|||I need to clarify my above response - I have a job that contains 3
steps. Step #1 contains the SELECT statement against the linked
server. Step #1, on success, goes to step #2 which handles some
processing on the remote server. Step #1, on failture, goes to step
#3 which informs me that the linked server is not available.
Make sure that step #2 skips step #3.

How to read/write global package variables in an active x script?

Is this possible. How can one do it? Thanks for any info you have.

Are you using DTS or SSIS? This is an SSIS only forum, if you're using DTS.

Stay away from Active X scripts in SSIS as that is a deprecated technology.|||Using SSIS. I know I shouldn't, but I have some legacy DTS code I need to quickly move over. Is it possible to read variables from an active x script? Thanks.

How to read/select data from a text file in TLSQL

Hello All,
I wanna write a TSQl script which will read the data from a text file(having table like structure). Not with DTS, I wanna do this programatically as sql batch file. Kindly help me this is very urgent.
Thanks in advance
Thazul:)Why is dts not an option ? Please provide a sample your data from the text file - is it delimited or fixed ?|||Is it possible to read a "csv" file?

It is (as I've understood) via ODBC, but I guess ODBC won't help in this particular case?|||Originally posted by thazanm
Hello All,
I wanna write a TSQl script which will read the data from a text file(having table like structure). Not with DTS, I wanna do this programatically as sql batch file. Kindly help me this is very urgent.
Thanks in advance
Thazul:)

Thanks for ur reply.
I wanna transfer data offline, so i cant use DTS. I am doing this offline data transfer using bcp(getting data from source database and making files then read from files and put it into target database). This is helpfull for me when the target database is fresh, when the target databse is having data, it need some validation for duplication, parent child relationship based on identity columns.

All I want is, I am creating few file for each table using bcp with -c or
-n option. After creating this I wanna read the data from created files to do some validation in TSQL and then load into target database.

I mentioned offline mean, I am not sure what will be the target database and I am not sure about their database informations. This script has to go as a pre install script before our product get installed.

I wanna put this in a simple way. In oracle there is Pl/SQL package available to open, read and write data from files to table and vice versa like UTL_FILE.<procedures>. Is there any functions or procedures available in SQLServer TSQL.

Thanks in advance
Thazul|||You may have already mentioned why you can't do this - in situations like this I have a holding database and work with the data using tsql this way. Massage the data with a stored procedure and then output the data to the destination database/table.|||Alrigth, I am sorry for the improper information provided.
Well I am already having a TSQL script which will transfer data from one database to another database after doing all kind of validations. The isssue is doing it offline. I cant connect two database at the client place. Some client places the dont agree to connect with our database.
I this facility(reading from files having table like data) is available then I can go for complete batch file.
I really appricate ur suggestion, if anyone can provide me the syntax or comman, it will be great.

Thanks in advance,
Thazul|||Are you looking for a way to read in a file, run validations, then output the data to a file ?|||RE:The isssue is doing it offline. I cant connect two database at the client place. Some client places the dont agree to connect with our database.

In some situations one may attach an MDF file (pre-populated with the necessary data in tables), and subsequently use it as the staging area database for validation, transfer, etc. (to other DB targets). In this way the stageing area DB is effectively populated "off-line" (from a client perspective).|||Originally posted by rnealejr
Are you looking for a way to read in a file, run validations, then output the data to a file ?

Hi,
I am looking for reading the data from file and run validation and then store it into database tables.

Thanks in advance|||I am confused as to why you can't use dts within your tsql - but you can accomplish this task by using bcp within your tsql, have your tsql validate and spit it out to a table(s).
If this does not satisify your requirements, please let me know - a step by step detail of the entire process would be helpful- offline is a little vague at this point, since tsql implies online.

How to read parent Package in Script Task

Hi,
In SQL2000 DTS, I set a variable to DTSGlobalVariables.Parent. Then I can manipulate the package.
What is the equivalent way in SQL 2005 SSIS?
Thanks for all the help!
TTSQL Server 2005 Integration Services has no exact eqivalent. However, you can use package configurations and property expressions to accomplish the task that you describe. I especially recommend that you take a look at the Parent package variable configuration type.
Books Online includes a number of topics about package configurations and property expressions. You might find the following topics useful:
Package Configurations
Creating Package Configurations
Using Property Expressions in Packages
dtexec Utility
Configuration Class

Marianne
SQL Server User Education

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Still - how can I read "Description" property of the current package in the "Script Task"?|||

In addition to Marianne's reply, check out this article for more information regarding the "DTSGlobalVariables.Parent" equivalent in SSIS.

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/SSIS_3A00_-Dynamic-modification-of-SSIS-packages.aspx

How to read parent Package in Script Task

Hi,
In SQL2000 DTS, I set a variable to DTSGlobalVariables.Parent. Then I can manipulate the package.
What is the equivalent way in SQL 2005 SSIS?
Thanks for all the help!
TT
SQL Server 2005 Integration Services has no exact eqivalent. However, you can use package configurations and property expressions to accomplish the task that you describe. I especially recommend that you take a look at the Parent package variable configuration type.
Books Online includes a number of topics about package configurations and property expressions. You might find the following topics useful:
Package Configurations
Creating Package Configurations
Using Property Expressions in Packages
dtexec Utility
Configuration Class

Marianne
SQL Server User Education

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Still - how can I read "Description" property of the current package in the "Script Task"?|||

In addition to Marianne's reply, check out this article for more information regarding the "DTSGlobalVariables.Parent" equivalent in SSIS.

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/SSIS_3A00_-Dynamic-modification-of-SSIS-packages.aspx

How to read parent Package in Script Task

Hi,
In SQL2000 DTS, I set a variable to DTSGlobalVariables.Parent. Then I can manipulate the package.
What is the equivalent way in SQL 2005 SSIS?
Thanks for all the help!
TT
SQL Server 2005 Integration Services has no exact eqivalent. However, you can use package configurations and property expressions to accomplish the task that you describe. I especially recommend that you take a look at the Parent package variable configuration type.
Books Online includes a number of topics about package configurations and property expressions. You might find the following topics useful:
Package Configurations
Creating Package Configurations
Using Property Expressions in Packages
dtexec Utility
Configuration Class

Marianne
SQL Server User Education

This posting is provided "AS IS" with no warranties, and confers no rights.

|||Still - how can I read "Description" property of the current package in the "Script Task"?|||

In addition to Marianne's reply, check out this article for more information regarding the "DTSGlobalVariables.Parent" equivalent in SSIS.

http://blogs.conchango.com/jamiethomson/archive/2005/02/28/SSIS_3A00_-Dynamic-modification-of-SSIS-packages.aspx