Script to drop articles from transactional replication

--This script builds a script to drop articles from replication in
transactional publications
--It will build the necessary replication procedures and parameters to
remove articles from replication
--Instructions
--Either hard code the where clause criteria or have it look in a table you
have already populated
--Run this script on the distribution server
--Be sure to have the results output to text
--Copy the results and execute on the server where the publications exist
use distribution
go
select 'use ' + p.publisher_db + char(13) + 'go ' + char(13) + 'exec sp
dropsubscription @.publication = ''' + p.publication + ''', @.article = ''' +
a.article + ''', @.subscriber = ''' + s.srvname + ''', @.destination_db = ''' +
sub.subscriber_db + '''' + char(13) + 'exec sp_droparticle @.publication = '''
+ p.publication + ''', @.article = ''' + a.article + '''' + char(10) + char(13)
from dbo.mspublications p
inner join
dbo.msarticles a on p.publication_id = a.publication_id
inner join
dbo.msdistribution_agents ag on p.publisher_id = ag.publisher_id and
p.publisher_db = ag.publisher_db
inner join
master.dbo.sysservers s on ag.subscriber_id = s.srvid
inner join
dbo.mssubscriptions sub on ag.id = sub.agent_id and
sub.publisher_id = p.publisher_id and
sub.publisher_db = p.publisher_db and
sub.article_id = a.article_id
where a.article in(
'table1','table2','etc..'
)
and p.publisher_db = 'db_name'
--Sorry, had a typo
--This script builds a script to drop articles from replication in
transactional publications
--It will build the necessary replication procedures and parameters to
remove articles from replication
--Instructions
--Either hard code the where clause criteria or have it look in a table you
have already populated
--Run this script on the distribution server
--Be sure to have the results output to text
--Copy the results and execute on the server where the publications exist
use distribution
go
select 'use ' + p.publisher_db + char(13) + 'go ' + char(13) + 'exec
sp_dropsubscription @.publication = ''' + p.publication + ''', @.article = '''
+
a.article + ''', @.subscriber = ''' + s.srvname + ''', @.destination_db = ''' +
sub.subscriber_db + '''' + char(13) + 'exec sp_droparticle @.publication = '''
+ p.publication + ''', @.article = ''' + a.article + '''' + char(10) + char(13)
from dbo.mspublications p
inner join
dbo.msarticles a on p.publication_id = a.publication_id
inner join
dbo.msdistribution_agents ag on p.publisher_id = ag.publisher_id and
p.publisher_db = ag.publisher_db
inner join
master.dbo.sysservers s on ag.subscriber_id = s.srvid
inner join
dbo.mssubscriptions sub on ag.id = sub.agent_id and
sub.publisher_id = p.publisher_id and
sub.publisher_db = p.publisher_db and
sub.article_id = a.article_id
where a.article in(
'table1','table2','etc..'
)
and p.publisher_db = 'db_name'

script to drop all 'user' objects

Can someone point me to some sql script that drops all objects for a given
user ?
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.comMichael,
Why do you want to drop all of the objects? Maybe you'd prefer to just
change the ownership of the object? sp_changeobjectowner.
HTH
Jerry
"Michael Tissington" <mtissington@.newsgroups.nospam> wrote in message
news:exFyHU1yFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Can someone point me to some sql script that drops all objects for a given
> user ?
> --
> Michael Tissington
> http://www.oaklodge.com
> http://www.tabtag.com
>
>|||Jerry,
Thanks but I need to drop all the objects for a given users. Too complicated
to explain the details but partially to do with running DTS packages and
limitations in TRUNCATE
Michael Tissington
http://www.oaklodge.com
http://www.tabtag.com
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u37AVc1yFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Michael,
> Why do you want to drop all of the objects? Maybe you'd prefer to just
> change the ownership of the object? sp_changeobjectowner.
> HTH
> Jerry
> "Michael Tissington" <mtissington@.newsgroups.nospam> wrote in message
> news:exFyHU1yFHA.3720@.TK2MSFTNGP14.phx.gbl...
>|||Hi Michael,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to drop all objects
that related to one specified user. If I have misunderstood your concern,
please feel free to point it out.
Based on my knowledge, I am afriad we cannot do this directly. However here
are some thoughts
1. get UID from sysusers
2. get object names from sysobjects with this UID
3. according the the name column of Step 2, and delete them separately.
Admittedly, there is a lot of jobs to do. Also, let's wait to see whether
others have the same experience.
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Script Task - Am I leaving any resources open?

I've got a service that waits for a file to drop and then calls a ssis package that then gets a date from the first line of the file, sets it to a variable, then imports the rest of the file using the flat file import through a data flow task.

Occasionally the script task fails with this error message:

The script threw an exception: Access to the path 'C:\path\flatfile.txt' is denied.

Here is the code of my script:

Public Sub Main()
'
' Add your code here
'
Dim vars As Variables
Dts.VariableDispenser.LockOneForRead("User::FullFilePath", vars)

Dim filepath As String = vars("FullFilePath").Value.ToString()

vars.Unlock()

Dim file As File
Dim reader As StreamReader

reader = file.OpenText(filepath)

Dim line As String
line = reader.ReadLine()

reader.Close()

'MsgBox(line.Substring(29))

Dim asofdate As String
asofdate = line.Substring(29, 5)
asofdate = asofdate + "20" + line.Substring(34)

'MsgBox(asofdate)

Dim writeVars As Variables
Dts.VariableDispenser.LockOneForWrite("User::AsOfDate", writeVars)
writeVars(0).Value = asofdate

'MsgBox("done")

writeVars.Unlock()

Dts.TaskResult = Dts.Results.Success
End Sub

Does anyone see anything that I'm not. I believe i'm closing all necessary resources and streams. Does anyone have suggestions.

Thank you very much in advance.

Is it possible that your service calls the package multiple times simultaneously, or that the package could be executed before the drop is completed? The access violation in either of those cases would be due to the file still being locked by something else (e.g. the process writing the file, or the first package processing the file).

If neither are the case, then you could try a small re-write to see if it helps:

Replace:

Dim file As File
Dim reader As StreamReader

reader = file.OpenText(filepath)

Dim line As String
line = reader.ReadLine()

reader.Close()

With:

Dim line As String

Using reader as new StreamReader(filepath)

line = reader.Readline()

reader.Close()

End Using

HTH,

Patrik

|||Thanks for your response.

After banging my head against the table for a day i realized that the reason I was having issues is because the package in question was running as a service. The service was running under a particular user or usergroup. This usergroup was not one that had access to the files being run through my package, and therefore the package had its access deined by the OS.

In the interest of helping others, I'll leave this post up, even though it should have been one of the first things I tried.

Script SQL 2005 database to individual files?

I am trying to script the DROP(IF EXISTS) and CREATE for all of my
tables, views, stored procs, and functions to individual SQL text files
(one per object). This was trivially done in SQL 2000 with Enterprise
Manager, but when I try in SQL 2005 through Management Studio my only
"script mode" options are:

- Script to file (which is one huge file with everything)
- Script to Clipboard
- Script to New Query Window

FYI, I get to this screen through Management Studio by right clicking
on a database and selecting Tasks > Generate Scripts... > Next (doesn't
seem to matter what combo of objects I select to script or what other
options). I am using SQL Server 2005 Developer (which is Microsoft SQL
Server Management Studio 9.00.1399.00).

Any solution to this (i.e. via Management Studio, command line, etc.)
would be greatly appreciated.

Thanks.
TedMSDN/BOL indicates that this is possible here
http://msdn2.microsoft.com/en-us/library/ms191299.aspx. However, it
doesn't give any clue on how to accomplish it.

Quote from that page: "The schema for generated objects can be saved in
a single SQL Script file, or in several files with each file containing
the schema of just one object."|||Ted O'Connor (toconnor@.gmail.com) writes:
> I am trying to script the DROP(IF EXISTS) and CREATE for all of my
> tables, views, stored procs, and functions to individual SQL text files
> (one per object). This was trivially done in SQL 2000 with Enterprise
> Manager, but when I try in SQL 2005 through Management Studio my only
> "script mode" options are:
> - Script to file (which is one huge file with everything)
> - Script to Clipboard
> - Script to New Query Window
> FYI, I get to this screen through Management Studio by right clicking
> on a database and selecting Tasks > Generate Scripts... > Next (doesn't
> seem to matter what combo of objects I select to script or what other
> options). I am using SQL Server 2005 Developer (which is Microsoft SQL
> Server Management Studio 9.00.1399.00).
> Any solution to this (i.e. via Management Studio, command line, etc.)
> would be greatly appreciated.

Rather than hackiong SMO on your own, I don't think there is one.
Note that there are really two features from SQL 2000 you are missing:
1) One object per file.
2) A script that performs both DROP and CREATE.

There is this suggestion on MSDN Product Feedback Centre,
http://lab.msdn.microsoft.com/produ...px?feedbackid=9
eb6c773-2dbb-4a27-b9d8-225d6ed4385a
the by far most voted-on item for SQL Server.

I did actually only find one item that brings up one file per object,
http://lab.msdn.microsoft.com/produ...px?feedbackid=2
7695db7-cef6-42c3-9cb0-ac30583bbee9
there are no votes here, beside the submitted, but that is because the
bug has not been validated.

In any case, that would be better as a suggestion, as it is not a bug
that you can't script per object, just a poor design. So if you can't
find a suggestion on that theme - submit one.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

SCPTXFR.EXE-- Problem with Drop procedure

hi there
I am using SCPTXFR.EXE to generate script of the database(for backup),using following command.

SCPTXFR.EXE /s abc /d aaa /P abc12345 /f D:\HSE0607\SCHEMA.sql /q/r/T

Now the script is generating fine, but what i find that there is no statment like
"if not exists(...) "
for the Stored Procedure( i had applied the /r parameter), for the Create Tables it is there.

Why there is no statment IF NOT EXISTS(...) for Stored Procedure?
I need this Line, how can i do this?
Regards,
Thanks.
Gurpreet S. Gill

hey nobody help me here, but i find the solution at

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73290