How to script all Publications using SMO in 2005

How to script all Publications using SMO in 2005
Hi Paul / All,
I have used your script from you site www.replicationanswers.com to
script all publications in SQL 2000 . Work great thanks !!.
I am now testing SQL server 2005 – what is the way to work with SMO in
order to script all objects ?
Thanks in advance,
Eyal
Right click on replication node in the SQL Server Management Studio and
select Generate Scripts. Would that help?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"? ??" <nospameyalSchapira@.hotmail.com> wrote in message
news:F3E1C68F-68BC-4A17-B9C6-2A4FC7D18304@.microsoft.com...
> How to script all Publications using SMO in 2005
> Hi Paul / All,
> I have used your script from you site www.replicationanswers.com to
> script all publications in SQL 2000 . Work great thanks !!.
>
> I am now testing SQL server 2005 - what is the way to work with SMO in
> order to script all objects ?
> Thanks in advance,
> Eyal
>
|||Hi have to do it with SMO once a week triggers it in a job into a text file.
sql

How to script adding a field to a table

Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***Do it in EM, and press "Save Change Script" button before exiting the window
s, and you will get the
script served on a silver plate (almost).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl..
.
> Hi - I would like to know what sql to run in Query Analyzer to add two
> fields to an existing table (I know you can do this in Enterprise
> Manager - but I'd like to be able to send a script to someone to let it
> happen automatically).
> Column Name: IDCreated
> DataType: DateTime
> Length: 8
> Allow Nulls: False
> Default Value: (getdate())
> and
> Column Name: ChangeNum
> DataType: bigint
> Length: 8
> Allow Nulls: False
> Identity: Yes
> Identity Seed: 45
> Identity Increment: 1
> Thanks for any help,
> Mark
> *** Sent via Developersdex http://www.examnotes.net ***|||Here ya go:
create table MyTable
(
PK int primary key
)
go
alter table MyTable
add
IDCreated datetime not null
constraint DF1_Myatble default (getdate())
, ChangeNum bigint not null identity (45, 1)
go
drop table MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mark" <anonymous@.devdex.com> wrote in message
news:en82DkCyFHA.3000@.TK2MSFTNGP12.phx.gbl...
Hi - I would like to know what sql to run in Query Analyzer to add two
fields to an existing table (I know you can do this in Enterprise
Manager - but I'd like to be able to send a script to someone to let it
happen automatically).
Column Name: IDCreated
DataType: DateTime
Length: 8
Allow Nulls: False
Default Value: (getdate())
and
Column Name: ChangeNum
DataType: bigint
Length: 8
Allow Nulls: False
Identity: Yes
Identity Seed: 45
Identity Increment: 1
Thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***|||Thinking about it, go with Tom's suggestion. EM often does these things in a
less than optimal way.
Often you see EM creating a new table, copy data etc etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:%23xDANtCyFHA.1856@.TK2MSFTNGP12.phx.gbl...
> Do it in EM, and press "Save Change Script" button before exiting the wind
ows, and you will get
> the script served on a silver plate (almost).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mark" <anonymous@.devdex.com> wrote in message news:en82DkCyFHA.3000@.TK2MS
FTNGP12.phx.gbl...
>|||Like this:
ALTER TABLE table_name ADD idcreated DATETIME NOT NULL
CONSTRAINT df_table_name_idcreated
DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE table_name ADD changenum BIGINT NOT NULL
IDENTITY(45,1) ;
Usually, when you add an IDENTITY column you will want to add a unique
or primary key constraint on that column. Although that's not
mandatory, IDENTITY itself won't prevent duplicates in all
circumstances because the auto-generated value can be overridden or the
seed can be changed. Also, IDENTITY is typically referenced by a
foreign key, for which a constraint is required.
Depending on your requirements you can add a constraint like this:
ALTER TABLE table_name
ADD CONSTRAINT ak_table_name_change_num UNIQUE (changenum) ;
David Portas
SQL Server MVP
--|||> Often you see EM creating a new table, copy data etc etc.
and sometimes it is the optimal way, is it not?

How To Script

I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the table
name is dbo.item. What would my script look like to do this in SQL server
2005? The column has a silver key by it too in SQL Server Management
Studio.

ThanksBrian (b.houghtby@.eaglecrusher.com) writes:

Quote:

Originally Posted by

I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the
table name is dbo.item. What would my script look like to do this in
SQL server 2005? The column has a silver key by it too in SQL Server
Management Studio.

UPDATE dbo.item
SET IMA_GLInvAcctNbr = 11500
WHERE IMA_GLInvAcctNbr = 11283

--
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|||What does the silver key mean?

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9947EFB6764B9Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Brian (b.houghtby@.eaglecrusher.com) writes:

Quote:

Originally Posted by

>I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the
>table name is dbo.item. What would my script look like to do this in
>SQL server 2005? The column has a silver key by it too in SQL Server
>Management Studio.

>
UPDATE dbo.item
SET IMA_GLInvAcctNbr = 11500
WHERE IMA_GLInvAcctNbr = 11283
>
>
--
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

|||What does the silver key mean?

It means that it is in a foreign key relationship to another table.

On Jun 7, 9:51 am, "Brian" <b.hough...@.eaglecrusher.comwrote:

Quote:

Originally Posted by

>
"Erland Sommarskog" <esq...@.sommarskog.sewrote in message
>
news:Xns9947EFB6764B9Yazorman@.127.0.0.1...
>
>
>

Quote:

Originally Posted by

Brian (b.hough...@.eaglecrusher.com) writes:

Quote:

Originally Posted by

I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the
table name is dbo.item. What would my script look like to do this in
SQL server 2005? The column has a silver key by it too in SQL Server
Management Studio.

>

Quote:

Originally Posted by

UPDATE dbo.item
SET IMA_GLInvAcctNbr = 11500
WHERE IMA_GLInvAcctNbr = 11283

>

Quote:

Originally Posted by

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

>

Quote:

Originally Posted by

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ons/books.mspx- Hide quoted text -

>
- Show quoted text -

How to scheule job to run last day of the month

What are the settings to get a sql server job to always run the last day of the month? This should be quite easy!

Thanks!

SQL2000?

if so,

on the schedule tab of the job, edit the schedule, or create a new one... on the schedule type, select recurring, then click the change button on the lower right hand side.

In the Occurs section, change to monthly. Then in the monthly section that you will see to the right select the lower radio button and change the first drop down list box to "Last", the second to "Day" of every 1 month.

Set the time, and away you go...

|||Thanks! This would make a nice addition to EM Help!

How to scheduling Report

Hi Friends

How to generate the Report every month automatically.with out endusers explicitly .

Regards

Raju

Hello Raju,

Take a look at Subscriptions in Books Online.

Hope this helps.

Jarret

How to schedule Usage Based Optimization on a regular basis?

I want to perform Usage Based Optimization on given partitions automatically (not using the wizard). Is it possible to schedule this task and how?

I don't know of anyway "out of the box" that this can be done. You could write your own program using AMO to facilitate this, but there are a couple of issues that come to mind.

1) You should not need to continuously re-run the usage based optimizations unless the useage patterns are changing significantly. Optimizing a cube for a given usage pattern only needs to be done once. Once the aggregations have been designed they will be used every time the cube is reprocessed (or specifically when the index processing is done)

2) There is an issue with UBO in that it overwrites previously designed aggregations (see this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=806335&SiteID=1) so your performance could possibly regress, rather than incrementally improve, if you ran it unattended.

|||Thanks a lotsql

How to schedule to run Integration Packages using SQL Agent/Job?

Hi Folks,

I deploying a couple of integration packages which needs to be run sequentially. Currently, I quite puzzled how come i cannot run them as a SQL Agent job. It always fail with "Executed as user: Servername\Administrator. The package execution failed. " But if I had put import these integration packages into the SQL Server 2005 Integration Services FileSystem, it does run but i need promptly as there is a dialog requesting whether to execute the packages. Is there a way to automate the running of these packages through SQL agent. ? Pls help need to get it up and running asap. Thanks first.

Yes there is. Most problems experienced when using SQL Agent to run packages are down to the package being run as a different user. Is that what you are doing?

-Jamie

|||Hi Garynkill23,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey is most likely flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy

|||

Andy Leonard wrote:

Hi Garynkill23,

There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Andy,

Why do you think that makes most sense? To me, it makes more sense to set ProtectionLevel=DontSaveSensitive and then store all your connection strings in configurations. Personally I think this should be considered best practice - its a matter of opinion of course.

-Jamie

|||

Hi Jamie,

I think the method you advocate (DontSaveSensitive) is great, and perhaps best.

My experience with this question drives my copy-and-paste answer about using a password. Here's what I've seen: Someone starts using SSIS and works through some issues as they scale the learning curve (reading good books and a great blog over at Conchango for help as they go).

Then they move it to a test or integration server, experiment with the job step type until they get a job to run when they right-click the job and click Start Job at Step... So they schedule it to run and wait. The SQL Agent scheduler fires the job and it fails with a "cannot acquire connection" (or similar) error. We know why but it seems completely baffling to them. (It certainly did to me when it first happened!)

What's worse is there's little or nothing in the log (the package cannot make that connection either).

While EncryptWithPassword method works, it also requires folks to keep track of a(nother) password.

I start here mainly because it's the quickest way I can think of to get that package up and running. Everyone understands passwords. I don't claim to be right or even advocating a best practice (at least not yet) - I'm just trying to help SSIS developers get that package running.

I welcome any feedback - from anyone. I'm still learning too! :)

Thanks,

Andy

|||

That all sounds fair enough to me Andy. I'll hold my hand up and say that I always try and drive people towards best practice - and to me that means configurations. As I aluded to, its all subjective.

Good discussion.

-Jamie

|||

Jamie Thomson wrote:

I'll hold my hand up and say that I always try and drive people towards best practice - and to me that means configurations.

I cannot argue with you. I advocate 2-pass (minimum) configurations to consulting clients. But I'm also right there to either implement it myself or show them how to implement it.

Jamie Thomson wrote:

Good discussion.

I couldn't agree more!

Thanks,

Andy

|||

As regards to your first message, I am not sure which account i was using Windows Authentication to SQL Server 2005 to run the packages which i kept under the SQL Server 2005 Integration Services - File System section. And I had to run it manually.

Then what should i do so that i can run it under SQL Agent as from what i read it requires a username/password but the packages had been saved under this option "Don't Save any sensitive data". One more question, is there any impact if i save it using this option and put it under maintenance under SQL Server 2005 Integration Services.

|||

garynkill23 wrote:

As regards to your first message, I am not sure which account i was using Windows Authentication to SQL Server 2005 to run the packages which i kept under the SQL Server 2005 Integration Services - File System section. And I had to run it manually.

Then what should i do so that i can run it under SQL Agent as from what i read it requires a username/password but the packages had been saved under this option "Don't Save any sensitive data". One more question, is there any impact if i save it using this option and put it under maintenance under SQL Server 2005 Integration Services.

As discussed above, if you have Protectionlevel=DontSaveSensitive then you will more than likely have to use configurations.

What do you mean by "put it under maintenance"?

-Jamie

|||

Hi garynkill23,

This is complicated. It touches a lot of moving parts in your SSIS package, SQL Servers, and enterprise domain. There's just no simple and easy explanation. But it is this way for good reason and that reason is to provide security.

I wrote a blog entry that talks about connections between SSIS and SQL Server - if you use a SQL Login to connect to SQL Server. This should also apply to storing credentials for any provider that requires a username and password.

Windows Authentication is simpler and safer provided your SQL Servers use one or more domain accounts for the SQL Agent service. (It also offloads SQL Server connectivity account maintenance to the Help Desk - at no extra charge.) If you use a domain account for the SQL Agent service, your SSIS connection managers can all be configured to use Windows Authentication to connect, and you simply grant the SQL Agent service domain account the access it needs in SQL Server. Does this make sense?

For example: I have a domain account named MyDomain\Andy. I log in as MyDomain\Andy and write an SSIS package that connects to a Dev SQL Server. I use the default ProtectionLevel: EncryptSensitiveWithUserKey. MyDomain\Andy has sufficient privileges on the Dev SQL Server. When I'm done, I can manually execute the package and it succeeds in connecting to the Dev SQL Server.

Next, I deploy the SSIS package to a Prod SQL Server. I create a SQL Agent job with an SSIS step that calls the SSIS package. I schedule the job to run at 2:00 AM. Again, MyDomain\Andy has sufficient privileges in the Prod SQL Server. When I right-click the SQL Agent job I just created, I can select "Start Job at Step..." and the job executes, calls the SSIS package, and both succeed.

What just happened? From the security context point-of-view, MyDomain\Andy just executed this job. This is important. The SQL Agent scheduler did not execute the job. In this scenario, that won't happen until 2:00 AM. Although this was a good test, it was incomplete.

To continue the example, let's assume the SQL Agent service on the Prod SQL Server runs under a domain account named MyDomain\SQLAgentService. When the scheduler fires the job at 2:00 AM, MyDomain\SQLAgentService will try to log into the Prod SQL Server and perform the operations specified in your SSIS package. If it lacks permissions to connect, the SSIS package will fail, causing the SQL Agent job to fail. You will get an error message similar to "failed to acquire connection". The error varies because the permissions granted MyDomain\SQLAgentService vary along with the configuration of the SSIS package:

If Logging is enabled in the SSIS package, it will probably attempt to connect to the logging provider before attempting to connect to a SQL Server - unless you're using a SQL Server logging provider. If it cannot connect to the Logging provider, it cannot log the fact that it encountered an error connecting to the Logging provider.