Copy maintenance plan to two different servers

SQL Server 2005:
I've created a number of maintenance plans on a development server that I'd
like to copy this to a number of different servers all running SQL Server
2005 with identical databases/structures. What is the best way to do this?
I'm not adverse to automating this with code.
Thanks,
Mark
Satya SKJ has written about this in another forum.
"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
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1203074&SiteID=1
I have developed a stored procedure based maintenance solution that is easy
to deploy.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
Ola Hallengren
http://ola.hallengren.com
"Mark" wrote:

> SQL Server 2005:
> I've created a number of maintenance plans on a development server that I'd
> like to copy this to a number of different servers all running SQL Server
> 2005 with identical databases/structures. What is the best way to do this?
> I'm not adverse to automating this with code.
> Thanks,
> Mark
>
>

Copy maintenance plan to two different servers

SQL Server 2005:
I've created a number of maintenance plans on a development server that I'd
like to copy this to a number of different servers all running SQL Server
2005 with identical databases/structures. What is the best way to do this?
I'm not adverse to automating this with code.
Thanks,
MarkSatya SKJ has written about this in another forum.
"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
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1203074&SiteID=1
I have developed a stored procedure based maintenance solution that is easy
to deploy.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
Ola Hallengren
http://ola.hallengren.com
"Mark" wrote:
> SQL Server 2005:
> I've created a number of maintenance plans on a development server that I'd
> like to copy this to a number of different servers all running SQL Server
> 2005 with identical databases/structures. What is the best way to do this?
> I'm not adverse to automating this with code.
> Thanks,
> Mark
>
>

copy data from 1 tbl to another tbl

i have a claims_tbl that i am inserting into with claim information, the tbl contains a claim_seq number that i want to copy over to a user_info_tbl at the same time insert user information below is what i have tried with no luck

insert into user_info(claim_seq)
select Max(claim_seq)
from claim_tbl

update user_info
set lname =upper(@.lname), fname =upper(@.fname), mname =upper(@.mname), personnel = @.personnel, p_position =upper(@.position)
where claim_seq = (Select Max(claim_seq) from user_info)

i am getting duplicate rows of information ........can someone help

thanks

I think you only need an INSERT statement. Perhaps your INSERT statement should look more like this?

INSERT INTO
user_info
(
claim_seq,
lname,
fname,
mname,
personnel,
p_position
)
SELECT
MAX(claim_seq),
upper(@.lname),
upper(@.fname),
upper(@.mname),
@.personnel,
upper(@.position)
FROM
claim_tbl
WHERE
NOT EXISTS (SELECT claim_seq FROM user_info WHERE claim_tbl.claim_seq = user_info.claim_seq)

This will only insert claim_seq values which do not already exists inthe user_info table, and will insert your parameter values at the sametime as the claim_seq.
|||

thanks for the help

the solution i use is below:

Select @.temp_seq = Max(incident_seq) from temp_id

insert into user_info(temp_seq, lname, fname, mname, personnel)
values(@.temp_seq, @.lname, @.fname,@.mname,@.personnel,)

Copy Access Table to SQL

Exporting an Access table and saving it as a DTS package copies the table but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, PIn SQL Server you don't have to worry about how the data is stored. All you
have to do is to, specify an ORDER BY clause in your queries, to get the
data out in your desired sort order.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Petra" <Petra@.discussions.microsoft.com> wrote in message
news:09506D78-2CFC-4482-AD02-2000131510A2@.microsoft.com...
Exporting an Access table and saving it as a DTS package copies the table
but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P

Copy Access Table to SQL

Exporting an Access table and saving it as a DTS package copies the table but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P
In SQL Server you don't have to worry about how the data is stored. All you
have to do is to, specify an ORDER BY clause in your queries, to get the
data out in your desired sort order.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Petra" <Petra@.discussions.microsoft.com> wrote in message
news:09506D78-2CFC-4482-AD02-2000131510A2@.microsoft.com...
Exporting an Access table and saving it as a DTS package copies the table
but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P

Converting to number: convert, cast ??

HI,
Converting to number: convert, cast '
what should I use and how'
I have looked at sql server 2000 help and until now I couldn't get sucess1
to string there is the function Str and for numbers'
Isn´t there any function like CInt, CDbl, CLng, Eval, etc..from vb,
vbscript'
see the code below,thanks
vilmar
spInserirImagem 1,'fundo_condominio_amarelo_01.jpg','amarelo',''
Drop Procedure spInserirImagem
Create Procedure spInserirImagem
@.IdImagem int,@.Descricao char(50),@.PadraoCor char(50),@.strSQL char(5000)
As
begin
Set @.IdImagem = Cast(@.IdImagem as int)
Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor)
values(' + @.IdImagem + ',' + @.Descricao + ',' + @.PadraoCor + ')'
print RTRIM(LTRIM(@.strSQL))
Exec(@.strSQL)
end> Set @.IdImagem = Cast(@.IdImagem as int)
> Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor)
> values(' + @.IdImagem + ',' + @.Descricao + ',' + @.PadraoCor + ')'
The problem here is that you are injecting an integer into a string. In
order for @.strSQL to work, you need to convert the INT to a string. This
should work fine:
Set @.IdImagem = Cast(@.IdImagem as VARCHAR(12))
Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor) values('
+ @.IdImagem + ',' + @.Descricao + ',' + @.PadraoCor + ')'
However, I'm not sure why you think you have to make a string out of this.
Can't you just do:
INSERT ImagemTopo(IdImagem,Descricao,PadraoCor)
VALUES(@.IdImagem, @.Descricao, @.PadraoCor)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi
I am not sure why think you need to cast this way, as you appending to a
string. The following should work:
Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor)
values(' + CONVERT(varchar,@.IdImagem) + ',''' + @.Descricao + ''',''' +
@.PadraoCor + ''')'
John
"news.microsoft.com" <suporte@.hitecnet.com.br> wrote in message
news:uvtN3$zuDHA.2060@.TK2MSFTNGP10.phx.gbl...
> HI,
> Converting to number: convert, cast '
> what should I use and how'
> I have looked at sql server 2000 help and until now I couldn't get sucess1
> to string there is the function Str and for numbers'
> Isn´t there any function like CInt, CDbl, CLng, Eval, etc..from vb,
> vbscript'
> see the code below,thanks
> vilmar
> spInserirImagem 1,'fundo_condominio_amarelo_01.jpg','amarelo',''
> Drop Procedure spInserirImagem
> Create Procedure spInserirImagem
> @.IdImagem int,@.Descricao char(50),@.PadraoCor char(50),@.strSQL char(5000)
> As
> begin
> Set @.IdImagem = Cast(@.IdImagem as int)
> Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor)
> values(' + @.IdImagem + ',' + @.Descricao + ',' + @.PadraoCor + ')'
> print RTRIM(LTRIM(@.strSQL))
> Exec(@.strSQL)
> end
>|||Thank you John Bell and Aaron Bertrand a lot!!
I was too forgetting about double single quotes in stored procedure besides
putting convert or cast in the way which
you showed me!!
Regards,
Vilmar
From Brazil
"John Bell" <jbellnewsposts@.hotmail.com> escreveu na mensagem
news:AK1Ab.20423$_y4.206566615@.news-text.cableinet.net...
> Hi
> I am not sure why think you need to cast this way, as you appending to a
> string. The following should work:
> Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor)
> values(' + CONVERT(varchar,@.IdImagem) + ',''' + @.Descricao + ''',''' +
> @.PadraoCor + ''')'
> John
> "news.microsoft.com" <suporte@.hitecnet.com.br> wrote in message
> news:uvtN3$zuDHA.2060@.TK2MSFTNGP10.phx.gbl...
> > HI,
> > Converting to number: convert, cast '
> > what should I use and how'
> > I have looked at sql server 2000 help and until now I couldn't get
sucess1
> > to string there is the function Str and for numbers'
> >
> > Isn´t there any function like CInt, CDbl, CLng, Eval, etc..from vb,
> > vbscript'
> > see the code below,thanks
> > vilmar
> > spInserirImagem 1,'fundo_condominio_amarelo_01.jpg','amarelo',''
> > Drop Procedure spInserirImagem
> > Create Procedure spInserirImagem
> > @.IdImagem int,@.Descricao char(50),@.PadraoCor char(50),@.strSQL char(5000)
> > As
> > begin
> > Set @.IdImagem = Cast(@.IdImagem as int)
> > Set @.strSQL = 'Insert into ImagemTopo(IdImagem,Descricao,PadraoCor)
> > values(' + @.IdImagem + ',' + @.Descricao + ',' + @.PadraoCor + ')'
> > print RTRIM(LTRIM(@.strSQL))
> > Exec(@.strSQL)
> > end
> >
> >
>

Converting Text to number

Hello there
I have table with field which is a text field that provide numbers and text
I would like to drop all the records that on the current field has text and
leave only the records with the number on the field
For this i've tried to use the convertion process but it gave me an error
when the data is text
Is there a way to know if the data is text or number so i can deal with it?
any help would be useful
roy@.atidsm.co.il
' 050-7709399"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in
news:#Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl:

> Hello there
> I have table with field which is a text field that provide numbers and
> text
> I would like to drop all the records that on the current field has text
> and leave only the records with the number on the field
> For this i've tried to use the convertion process but it gave me an
> error when the data is text
> Is there a way to know if the data is text or number so i can deal with
> it?
Try ISNUMERIC(expression)|||Although ISNUMERIC will provide the desired results in many cases, it will
also consider some obscure numeric values to be numeric. You can use LIKE
to test for integer digits. The example shows some differences between
ISNUMERIC and LIKE.
CREATE TABLE MyTable
(
MyNumber varchar(10)
)
GO
INSERT INTO MyTable VALUES('')
INSERT INTO MyTable VALUES(' ')
INSERT INTO MyTable VALUES('1')
INSERT INTO MyTable VALUES(' 2')
INSERT INTO MyTable VALUES('3 ')
INSERT INTO MyTable VALUES('1D')
INSERT INTO MyTable VALUES('0D1')
INSERT INTO MyTable VALUES('0E1')
SELECT *
FROM MyTable
WHERE ISNUMERIC(MyNumber) = 0
SELECT *
FROM MyTable
WHERE MyNumber LIKE '%[^0-9]%' OR
DATALENGTH(MyNumber) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl...
> Hello there
> I have table with field which is a text field that provide numbers and
> text
> I would like to drop all the records that on the current field has text
> and
> leave only the records with the number on the field
> For this i've tried to use the convertion process but it gave me an error
> when the data is text
> Is there a way to know if the data is text or number so i can deal with
> it?
> any help would be useful
> --
>
> roy@.atidsm.co.il
> ' 050-7709399
>|||ISNUMERIC also interprets '.', '-' and '+' as numeric.
Dan Guzman wrote:
> Although ISNUMERIC will provide the desired results in many cases, it
> will also consider some obscure numeric values to be numeric. You
> can use LIKE to test for integer digits. The example shows some
> differences between ISNUMERIC and LIKE.
> CREATE TABLE MyTable
> (
> MyNumber varchar(10)
> )
> GO
> INSERT INTO MyTable VALUES('')
> INSERT INTO MyTable VALUES(' ')
> INSERT INTO MyTable VALUES('1')
> INSERT INTO MyTable VALUES(' 2')
> INSERT INTO MyTable VALUES('3 ')
> INSERT INTO MyTable VALUES('1D')
> INSERT INTO MyTable VALUES('0D1')
> INSERT INTO MyTable VALUES('0E1')
> SELECT *
> FROM MyTable
> WHERE ISNUMERIC(MyNumber) = 0
> SELECT *
> FROM MyTable
> WHERE MyNumber LIKE '%[^0-9]%' OR
> DATALENGTH(MyNumber) = 0
>
> "Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
> news:%23Ok5SqLIFHA.2564@.tk2msftngp13.phx.gbl...

Converting Rows into Columns MS SQL 2K

I have a SP that returns the information I want but it returns it in 2
separate queries.

Example:
Query 1
Name, Number, Class
Row 1- Mike Phillips, 154AA, AA
and
Query 2
Time, Manual
Row 1 -12:45:22,0
Row 2 -13:04:56,0

What I want it to look like is:
Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2
Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0

Here is the query I'm using:
DECLARE Class cursor

FOR
--here we get a list of distinct classes to pass to the Class cursor
select Distinct(class_ID) from kt_member_lap
where Race_ID = 83
order by Class_ID;

OPEN Class;

DECLARE @.RaceID int
DECLARE@.RacerCount int
DECLARE @.ClassID char(50)
DECLARE @.classcount
DECLARE @.Racer char(50)
DECLARE @.i int

SET @.RaceID = 83
--this is where we loop through the classes
FETCH NEXT FROM Class INTO @.ClassID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN

IF (@.@.FETCH_STATUS <> -2)

DECLARE Lap cursor

FOR
Select DISTINCT(Member_ID) from KT_MEMBER_LAP
Where class_ID = @.classID and race_id = @.RaceID

OPEN Lap;

--this is to begin counting from the first lap
SET @.i = 1;

FETCH NEXT FROM Lap INTO @.Racer
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN

IF (@.@.FETCH_STATUS <> -2)
SELECT KT_MEMBER.MEMBER_FNAME + ' ' +
KT_MEMBER.MEMBER_LNAME As MemberName,
CONVERT(nvarchar(3),
KT_MEMBER_CLASS.MEMBER_CLASS_BIKE_NUM) + KT_CLASS.CLASS_LETTER As
BikeNumber,
KT_CLASS.CLASS_DESC
FROM KT_CLASS INNER JOIN
KT_MEMBER_CLASS ON KT_CLASS.CLASS_ID =
KT_MEMBER_CLASS.CLASS_ID INNER JOIN
KT_MEMBER ON KT_MEMBER_CLASS.MEMBER_ID =
KT_MEMBER.MEMBER_ID
WHERE KT_MEMBER.MEMBER_ID = @.Racer and KT_CLASS.CLASS_ID =
@.ClassID

--SELECT @.Racer, @.ClassID

Select MEMBER_LAP_TIME_REAL, member_lap_manual from KT_MEMBER_LAP
Where Member_ID = @.Racer and class_ID = @.classID and race_id =
@.RaceID
ORDER BY MEMBER_LAP_TIME_REAL

--here I count up for the next lap
SET @.i = @.i + 1;

FETCH NEXT FROM Lap INTO @.Racer
END

CLOSE Lap;
DEALLOCATE Lap;

FETCH NEXT FROM Class INTO @.ClassID
END

CLOSE Class;
DEALLOCATE Class;

Any help would be appreciated.[posted and mailed, please reply in news]

dare197 (daniel.white@.perceptivetech.com) writes:
> I have a SP that returns the information I want but it returns it in 2
> separate queries.
> Example:
> Query 1
> Name, Number, Class
> Row 1- Mike Phillips, 154AA, AA
> and
> Query 2
> Time, Manual
> Row 1 -12:45:22,0
> Row 2 -13:04:56,0
> What I want it to look like is:
> Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2
> Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0

Could there be any number of Time, Manual rows or is there never more
than two? I will assume that you always have two. Then you can try
this query:

SELECT m.MEMBER_FNAME + ' ' + m.MEMBER_LNAME As MemberName,
CONVERT(nvarchar(3), mc.MEMBER_CLASS_BIKE_NUM) +
c.CLASS_LETTER As BikeNumber,
c.CLASS_DESC,
ml1.MEMBER_LAP_TIME_REAL AS "Time 1",
ml1.member_lap_manual AS "Manual 1",
ml2.MEMBER_LAP_TIME_REAL AS "Time 2",
ml2.member_lap_manual AS "Manual 2",
FROM KT_CLASS c
JOIN KT_MEMBER_CLASS mc ON c.CLASS_ID = mc.CLASS_ID
JOIN KT_MEMBER m ON mc.MEMBER_ID = m.MEMBER_ID
JOIN KT_MEMBER_LAP ml1 ON ml1.MEMBER_ID = m.MEMBER_ID
AND mll.CLASS_ID = mc.CLASS_ID
JOIN KT_MEMBER_LAP ml2 ON ml2.MEMBER_ID = m.MEMBER_ID
AND ml2.CLASS_ID = mc.CLASS_ID
AND ml2.member_lap_manual > ml1.member_lap_manual
WHERE m.MEMBER_ID = @.Racer
AND c.CLASS_ID = @.ClassID
ORDER BY c.CLASS_ID, m.MEMBER_ID

Here I have collapsed everything into one query, without any cursor, as
I could see no need for a cursor. Cursors can be a magnitude slower than
set-based statements, so there all reasons to avoid them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Converting report input dates to UTC

I have created a report in reporting services and it takes a number of
DateTime values as input parameters. These values will be passed on to
queries in my database; the problem is that my database stores DateTime
values as UTC.
Is there a way I can convert them to UTC before the values are sent to the
query?Hello,
You could try this:
DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @.LocalDate)
http://geekswithblogs.net/ewright/archive/2004/09/14/11180.aspx
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.
>Thread-Topic: Converting report input dates to UTC
>thread-index: AcYtdvuTa+Zu+2dZQC6M3qdl1xmC3Q==>X-WBNR-Posting-Host: 195.139.24.170
>From: "=?Utf-8?B?Q2hyaXN0b3BoZXIgS2ltYmVsbA==?="
<c_kimbell@.newsgroup.nospam>
>Subject: Converting report input dates to UTC
>Date: Thu, 9 Feb 2006 04:47:27 -0800
>Lines: 7
>Message-ID: <920BBD92-A5C6-4A43-B965-8D1AE0151687@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:68388
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I have created a report in reporting services and it takes a number of
>DateTime values as input parameters. These values will be passed on to
>queries in my database; the problem is that my database stores DateTime
>values as UTC.
>Is there a way I can convert them to UTC before the values are sent to the
>query?
>

Converting of Decimal digits to string

Hi .,
I am facing a problem in converting a number with a maximum decimal digit
of Six to a Comma Separated String.
For Ex.
if the number is 123456.36987 , my expected result is 123,456.36987
if the number is 123456789.36 , my expected result is 123,456,789.36
Please give valuable suggestion...
With Regards,
R.RamaKrishnanGo here:
http://msdn.microsoft.com/library/d...br />
2f3o.asp
...and look for "style values for float or real".
ML
http://milambda.blogspot.com/|||Hello,
If you do not need more than 4 decimal places, you can use the
conversion style 1 for a money value. See:
http://msdn.microsoft.com/library/e..._ca-co_2f3o.asp
However, this is usually handled in the front-end and it's probably
easier to specify a format in the application, not on the server-side.
Razvan

COnverting Numeric data type (Oracle) to Date Data type using SSIS

We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.

I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.

On Error, If I fail the component, then the error is :

There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Regards

RH

If you are using a query to go against Oracle, you'll likely want to cast that as a varchar and then work with it in SSIS. Not sure that YYYYMMDD will cast to DT_DATE. You'll likely have to substring pieces of that to get it into a date. There are plenty of examples here on this forum for doing that. Just search for "YYYYMMDD."|||

Thanks for the reply. I used SQL on the OLEDB Source and added a TO_DATE expression in the SQL and use that column as input to my destination column.

This worked fine.

RH