Showing posts with label studio. Show all posts
Showing posts with label studio. Show all posts

Thursday, March 29, 2012

Automating deployment of maintenance plans

Hello,

I have created a Maintenance Plan on our development SQL Server 2005 Standard using the designer in SQL Server Management Studio. The plan backs up databases and transaction logs to a hard disk and does some cleanup too. It is scheduled to run nightly. This plan needs to be deployed to 13 production sites by someone else not familiar with SQL Server.

Can I use some combination of a SQL script, an export of the maintenance plan, and/or a batch file to automate the deployment of this plan and it's schedule to servers at several different sites? The deployment team will have admin remote desktop access to the production SQL Servers, which also have SQL Management Studio installed but we cannot expect the team to recreate the plan manually on each site.

I haven't been able to find much documentation on doing this automatically. Any help will be appreciated.

Thank you,

- Jason

Create a SSIS package to perform this maintenance plan task and use DTUTIL to deploy on multiple servers.

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE fyi.

sql

Sunday, March 25, 2012

Automatically reformat SQL Keywords to UPPER CASE

Is there a way to get SQL 2000 Query Analyzer or Visual Studio 2003 to
automatically re-format SQL keywords to upper case as you type?
I can do this in Ultra-Edit by editing an external file that contains all of
the keywords.
Thanks.Not uppercase specifically, you probably know this , but you can customise
keywords through the tools|options|fonts
For uppercase , check this:
http://www.aquafold.com/docs-qw-sqlformatter.html
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"SOG1" <SOG1@.discussions.microsoft.com> wrote in message
news:E3AB7C4C-7F22-4C42-9ABB-40D80AF5E820@.microsoft.com...
> Is there a way to get SQL 2000 Query Analyzer or Visual Studio 2003 to
> automatically re-format SQL keywords to upper case as you type?
> I can do this in Ultra-Edit by editing an external file that contains all
of
> the keywords.
> Thanks.|||I recently bought a license for PromptSQL, a tool that does just that along
with Intellisense, and works with several tools including Query Analyzer and
SQL Server Management Studio. A couple of ws ago, Red Gate Software bough
t
the product, renamed to SQL Prompt, and is currently in beta for release 3.
I'm not sure what you can get right now, but you can try www.promptsql.com,
or Red Gates site, www.red-gate.com to see.
HTH
Vern
"SOG1" wrote:

> Is there a way to get SQL 2000 Query Analyzer or Visual Studio 2003 to
> automatically re-format SQL keywords to upper case as you type?
> I can do this in Ultra-Edit by editing an external file that contains all
of
> the keywords.
> Thanks.

automatically expand identity specification node?

subject says it all -- is it possible to automatically expand the identity specification node in table properties for the Management Studio or VS2005 diagram mode? when creating a DB, it seems ridiculous that for EVERY table I have to add an extra click to get to just one more clickable item that ought to be exposed by default.Yeah, that's kind of a pain. If you have a lot of design to do at once, it's easier to use T-SQL than the GUI tools. I don't know of a method to auto-expand that.|||

thx for the reply ... even if it's a year later

sure, T-SQL would be easier for the identity aspect ... but when I'm modelling a DB, it's too organic of a process to do in script. One of SQL Server's strengths has always been the DB modeller. I don't need to create an ERD and work off of that because the SQL Server diagram IS my initial ERD. Implementing ideas visually at the conceptual stage of DB design is critical IMO. Making the identiy attribute more accessible, whether by default, by choice, or by rearranging that portion of the GUI, would be a big value add, again, IMO ...

|||Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.|||

Buck Woody - MSFT wrote:

Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.

Where at? Connections?

|||That's right - here is the link:

http://connect.microsoft.com/SQLServer

|||thanks for the link, hadn't used Connect in a while.

automatically expand identity specification node?

subject says it all -- is it possible to automatically expand the identity specification node in table properties for the Management Studio or VS2005 diagram mode? when creating a DB, it seems ridiculous that for EVERY table I have to add an extra click to get to just one more clickable item that ought to be exposed by default.Yeah, that's kind of a pain. If you have a lot of design to do at once, it's easier to use T-SQL than the GUI tools. I don't know of a method to auto-expand that.|||

thx for the reply ... even if it's a year later

sure, T-SQL would be easier for the identity aspect ... but when I'm modelling a DB, it's too organic of a process to do in script. One of SQL Server's strengths has always been the DB modeller. I don't need to create an ERD and work off of that because the SQL Server diagram IS my initial ERD. Implementing ideas visually at the conceptual stage of DB design is critical IMO. Making the identiy attribute more accessible, whether by default, by choice, or by rearranging that portion of the GUI, would be a big value add, again, IMO ...

|||Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.|||

Buck Woody - MSFT wrote:

Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.

Where at? Connections?

|||That's right - here is the link:

http://connect.microsoft.com/SQLServer

|||thanks for the link, hadn't used Connect in a while.sql

automatically expand identity specification node?

subject says it all -- is it possible to automatically expand the identity specification node in table properties for the Management Studio or VS2005 diagram mode? when creating a DB, it seems ridiculous that for EVERY table I have to add an extra click to get to just one more clickable item that ought to be exposed by default.Yeah, that's kind of a pain. If you have a lot of design to do at once, it's easier to use T-SQL than the GUI tools. I don't know of a method to auto-expand that.|||

thx for the reply ... even if it's a year later

sure, T-SQL would be easier for the identity aspect ... but when I'm modelling a DB, it's too organic of a process to do in script. One of SQL Server's strengths has always been the DB modeller. I don't need to create an ERD and work off of that because the SQL Server diagram IS my initial ERD. Implementing ideas visually at the conceptual stage of DB design is critical IMO. Making the identiy attribute more accessible, whether by default, by choice, or by rearranging that portion of the GUI, would be a big value add, again, IMO ...

|||Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.|||

Buck Woody - MSFT wrote:

Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.

Where at? Connections?

|||That's right - here is the link:

http://connect.microsoft.com/SQLServer

|||thanks for the link, hadn't used Connect in a while.

Saturday, February 25, 2012

Auto-Increasement field?

Hi!

I'm using Microsoft SQL Server Management Studio to design a table with two fields:

id (int)

file (text)

I set 'id' to be primary. I try to add a row to this table but it asks me for a custom value for 'id'. I want it simply to auto-assign a uniqe value for it. How to do this please?

In the table designer, set the Identity Specification to Is_Identity = Yes.

Also, I recommend NOT using [ID] as the column name. A good standard is to use the TableName and ID, so a table named MyTable would have it's IDENTITY column named MyTableID.

Monday, February 13, 2012

Auto Print

I am using Visual studio 2005. This may be a vb question but it deals with reporting services.

I have a local report using the reportviewer control. (rdcl 2005 fmt)

It displays perfectly, and when i click the print button it prints flawlessly.

How do i print the document to the default printer automatically?

This seems like a basic function that i can't seem to find the answer to.

Thanx

Jerry Cicierega

I found the answer. Its not as bad as it looks. Thois is a form called "slip.vb" I am printing to a slip printer. The form has a rpt viewer on it. The form nevers shows. It is just used as a containor for the report. I created the report as an RDL report. I coppied it into the program folder and re-named it to a RDCL extension. I then established the datasource. and it worked interactivly. I then added the following code and presto. Auto Print. I also asume the default printer is the printer of choice.

Imports System.IO

Imports System.Data

Imports System.Text

Imports System.Drawing.Imaging

Imports System.Drawing.Printing

Imports System.Collections.Generic

Imports Microsoft.Reporting.WinForms

Public Class Slip

Private m_currentPageIndex As Integer

Private m_streams As IList(Of Stream)

Public Sub Print_Slip()

Me.TxnHistoryTableAdapter.Connection.ConnectionString = My.Utilities.ConnectionString

Me.TxnHistoryTableAdapter.FillBy(Me.PCZonDataSet.TxnHistory, My.GlobalVariables.ComputerData.ComputerName, My.GlobalVariables.ComputerData.Sequencenumber)

Export(Me.ReportViewer1.LocalReport)

m_currentPageIndex = 0

Print()

End Sub

Private Function CreateStream(ByVal name As String, _

ByVal fileNameExtension As String, _

ByVal encoding As Encoding, ByVal mimeType As String, _

ByVal willSeek As Boolean) As Stream

Dim stream As Stream = New FileStream(name + "." + fileNameExtension, FileMode.Create)

m_streams.Add(stream)

Return stream

End Function

Private Sub Export(ByVal report As LocalReport)

Dim deviceInfo As String = _

"<DeviceInfo>" + _

" <OutputFormat>EMF</OutputFormat>" + _

" <PageWidth>9in</PageWidth>" + _

" <PageHeight>11in</PageHeight>" + _

" <MarginTop>0.0in</MarginTop>" + _

" <MarginLeft>0.0in</MarginLeft>" + _

" <MarginRight>0.25in</MarginRight>" + _

" <MarginBottom>0.25in</MarginBottom>" + _

"</DeviceInfo>"

Dim warnings() As Warning = Nothing

m_streams = New List(Of Stream)()

report.Render("Image", deviceInfo, _

AddressOf CreateStream, warnings)

Dim stream As Stream

For Each stream In m_streams

stream.Position = 0

Next

End Sub

Private Sub Print()

If m_streams Is Nothing Or m_streams.Count = 0 Then

Return

End If

Dim printDoc As New PrintDocument()

AddHandler printDoc.PrintPage, AddressOf PrintPage

printDoc.Print()

If Not (m_streams Is Nothing) Then

Dim stream As Stream

For Each stream In m_streams

stream.Close()

Next

m_streams = Nothing

End If

End Sub

Private Sub PrintPage(ByVal sender As Object, ByVal ev As PrintPageEventArgs)

Dim pageImage As New Metafile(m_streams(m_currentPageIndex))

ev.Graphics.DrawImage(pageImage, ev.PageBounds)

m_currentPageIndex += 1

ev.HasMorePages = (m_currentPageIndex < m_streams.Count)

End Sub

End Class

Auto populated field

Hello,

I have SQL Server Server Man Studio Express 2005, currently having a problem with an auto populated field.

Basically I have a number populated everytime a new asset is added to my database, but at the moment the firled does not increment by 1 as I would like it to. Seems to assign the same number as a item already in the database and I have to go into the back end and change it manually.

Anyone know how this is easly sorted, the asset ID is not the primary key. Just for your info at the moment 'Identity Spec' is set to 'NO'.

Many thanks, Andrew

How are you currently trying to auto populate the number if Identity is set to No?

--Uncle Pete

|||This is a good question, sorry i only recently started using this software as inherited it of another person so very new to it.|||I have just checked and it will not allow me to change the Identity Spec to 'Yes'? Default value or binding is set to ((0)).|||

What is the datatype of the field?

If it is set to INT then you should be able to set the identity to yes.

|||

Hello, yes it is set to INT but dosen't seem that I can alter it?

|||

If you look at the identity field, you will see a plus sign, expand that. There you will be able to select Yes and set the seed value and increment. Be sure to set the seed value higher than what ever the highest current value is.

Also I see you said that default was set to (0), delete that, as it will conflict with the indentity.

|||

Thank you for your reply but I still cannot change the Identity Spec field?

It is setup the follwoing way:

Allow Nulls: Yes

Datat Type: int

Value or binding: ((0))

Condensed data type: int

Deterministic: Yes

Indexable: Yes

Full text Spec: No

Identity Spec: NO

Size: 4

everything else set to No or blank.

Many thanks, Andrew

Friday, February 10, 2012

Auto generated CRUD in Sql 2005 issue

Here is our problem. If you right click on a table in Management studio it gives you the option of creating The Delete, insert, select and Update stored procedures for any table. The problem is that the auto generation script includes the database name in the stored procedures. So if we have a database called DB_DEV and we move the stored procedures over to database DB_QA these stored procedures are now trying to access the wrong database. Is there a way to make sure that the database name is not included?

If I am reproducing your steps correctly, I see that SSMS will 'auto-magically' write a query for one of the CRUD actions -but it's not a stored procedure.

You may wish to combine that action with using a Stored Procedure template -but as far as I can determine, you'll have to manually remove the dbname.

|||

Could you please post the script you see, and the version of SQL Server you are using?

You can try it on a simple table and not necessary your primary one.

When I try to reproduce your problem, the CRUD script created has a "use [<dbname>]" at the beginning of it. If this is the case for you, you can simply remove this line from the script and it will be applicable to any database.

|||Why not use Edit / Find and Replace after you generate the script?