Does field exist in backup?

I run scripts against my db,

1) Rename table
2) Create new table (original name)
3) Create Indexes
4) Insert into new, Select from backup

My problem is that one table may have more fields than on another db, but I want to run the same script to update the tables.

What I'd like it to do, is in the insert select stuff, I want to put logic to select field from backup if it exists and insert in new.

If it doesnt exist in backup then insert null into new table, instead of having the line blow up cause the field doesnt exist in backup.

Suggestions would be appreciated. Thanks!, MitchIf it's not in the list it will automatically put in nulls...as long as it is nullable...

and you could go crazy...but it might just easier to code the dang thing...

USE Northwind
GO
sp_help Orders
GO

-- The lazy man's way to create a table

SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO

ALTER TABLE NewOrders DROP Column RequiredDate
GO

DECLARE @.x varchar(8000)

SELECT @.x = 'INSERT INTO NewOrders ('

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ') SELECT '

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ' FROM Orders'

SELECT @.x

SET IDENTITY_INSERT NewOrders ON

EXEC(@.x)

SET IDENTITY_INSERT NewOrders OFF
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO|||If you could send me a link or something, that would help me understand the logic below that would be awesome. I sort of follow the code below, but I'd like to see step by step what does what.
Thanks for your reply.
Mitch

Originally posted by Brett Kaiser
If it's not in the list it will automatically put in nulls...as long as it is nullable...

and you could go crazy...but it might just easier to code the dang thing...

USE Northwind
GO
sp_help Orders
GO

-- The lazy man's way to create a table

SELECT * INTO NewOrders FROM Orders WHERE 1=0
GO

ALTER TABLE NewOrders DROP Column RequiredDate
GO

DECLARE @.x varchar(8000)

SELECT @.x = 'INSERT INTO NewOrders ('

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ') SELECT '

SELECT @.x = @.x + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION = 1

SELECT @.x = @.x + ', '+COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'NewOrders' AND ORDINAL_POSITION > 1
ORDER BY ORDINAL_POSITION

SELECT @.x = @.x + ' FROM Orders'

SELECT @.x

SET IDENTITY_INSERT NewOrders ON

EXEC(@.x)

SET IDENTITY_INSERT NewOrders OFF
GO

SELECT * FROM NewOrders
GO

DROP TABLE NewOrders
GO|||Mitch,

Just cut and paste the code into a query analyzer window...

Just execute...I already tested it and it runs like a champ...

Does anyone use SSIS for database schema maintenance?

We currently use SSIS to build DTS packages in which we store changes
to our database schema, as well as scripts that need to be run upon
each release. This works well for small sets of changes that never
need to be updated or for architectures with only one database.

We store each of the changes included in the package in separate
files, which are tracked using version control. It is growing time
consuming to maintain parity between those files and what is in the
SSIS.

Furthermore, we have been unable to discover an easy way to load a
file's contents into a package SQL Task without opening the file and
copy-pasting the contents into a new SQL task.

ANY information at all would be extremely appreciated!On Feb 28, 2:54 pm, "Ben" <vanev...@.gmail.comwrote:

Quote:

Originally Posted by

We currently use SSIS to build DTS packages in which we store changes
to our database schema, as well as scripts that need to be run upon
each release. This works well for small sets of changes that never
need to be updated or for architectures with only one database.
>
We store each of the changes included in the package in separate
files, which are tracked using version control. It is growing time
consuming to maintain parity between those files and what is in the
SSIS.
>
Furthermore, we have been unable to discover an easy way to load a
file's contents into a package SQL Task without opening the file and
copy-pasting the contents into a new SQL task.
>
ANY information at all would be extremely appreciated!

Hi Ben,

There is a rock solid change management process for SQL Server
2000/2005 and it is provided by the DB Ghost toolset from
Innovartis.

The essence of the process is that you script out all the database
objects and lookup (static) data into individual CREATE / INSERT
scripts and put them under source control. The whole dev team then
checks these files out, makes the required changes to the CREATE
statements and checks them back in again (this can scale to thousand
of developers). Once you're ready to release the schema to the test
environment you use the DB Ghost Change Manager tool to make the
target database match the set of source scripts. If, for example, a
developer added a column to a table CREATE script then the Change
Manager would detect this and add the column to the target database
seamlessly.

Basically, DB Ghost enables you to develop in the same way as you do
for a greenfield (release 1) database for every subsequent release of
your schema without losing any data in the target database.

Our customers rave about DB Ghost and can't believe the cost savings
it brings - have a look for yourself :)

www.dbghost.com
Kind regards,

Malcolm

Documenting SQL Jobs

Does anyone know any tools or scripts to use for
documenting SQL Jobs. I need to take the results and
create an excel or word file for management. I would need
it to list the job name, schedule, frequency...so on.
Trying to script this is not easy in that the sysjobs and
sysjobschedules tables use numbers to represent everything
about the schedule and all.
Any help is appreciated.
Thanks,
Van JonesHow about a simple SQL Query, and put the output in Excel? The tables you
need to read from aren't that difficult to work with. Only mess is the
dateformat and frequency stuff, but there is some good stuff at
www.SQLDev.Net that help you with this.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:0dbd01c3a485$acb49fb0$a401280a@.phx.gbl...
> Does anyone know any tools or scripts to use for
> documenting SQL Jobs. I need to take the results and
> create an excel or word file for management. I would need
> it to list the job name, schedule, frequency...so on.
> Trying to script this is not easy in that the sysjobs and
> sysjobschedules tables use numbers to represent everything
> about the schedule and all.
> Any help is appreciated.
> Thanks,
> Van Jones|||Hey thanks for the reply Tibor. I've been down that road
before a couple years ago and didn't have a great deal of
luck. I got it to work, but it wasn't very clean. I was
just hoping for a tool to do it for me. However, I saw a
reply from Sue under dbforums to the same question that
had the following article listed:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15560
It has a sp called usp_Scheduled_Jobs that will do it all
for you....extremely nice and useful.
Thanks,
Van Jones
MCDBA, MCSE, MCSA, MCAD
>--Original Message--
>How about a simple SQL Query, and put the output in
Excel? The tables you
>need to read from aren't that difficult to work with.
Only mess is the
>dateformat and frequency stuff, but there is some good
stuff at
>www.SQLDev.Net that help you with this.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote
in message
>news:0dbd01c3a485$acb49fb0$a401280a@.phx.gbl...
>> Does anyone know any tools or scripts to use for
>> documenting SQL Jobs. I need to take the results and
>> create an excel or word file for management. I would
need
>> it to list the job name, schedule, frequency...so on.
>> Trying to script this is not easy in that the sysjobs
and
>> sysjobschedules tables use numbers to represent
everything
>> about the schedule and all.
>> Any help is appreciated.
>> Thanks,
>> Van Jones
>
>.
>|||OK, I just wanted to point the option of using these tables, in case you
haven't used them already. Yes, some things aren't that easy to deal with
(mostly the freq- things, IMO). Hopefully Sue's proc will help you :-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:0ded01c3a48e$787439a0$a601280a@.phx.gbl...
> Hey thanks for the reply Tibor. I've been down that road
> before a couple years ago and didn't have a great deal of
> luck. I got it to work, but it wasn't very clean. I was
> just hoping for a tool to do it for me. However, I saw a
> reply from Sue under dbforums to the same question that
> had the following article listed:
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15560
> It has a sp called usp_Scheduled_Jobs that will do it all
> for you....extremely nice and useful.
> Thanks,
> Van Jones
> MCDBA, MCSE, MCSA, MCAD
> >--Original Message--
> >How about a simple SQL Query, and put the output in
> Excel? The tables you
> >need to read from aren't that difficult to work with.
> Only mess is the
> >dateformat and frequency stuff, but there is some good
> stuff at
> >www.SQLDev.Net that help you with this.
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Van Jones" <anonymous@.discussions.microsoft.com> wrote
> in message
> >news:0dbd01c3a485$acb49fb0$a401280a@.phx.gbl...
> >> Does anyone know any tools or scripts to use for
> >> documenting SQL Jobs. I need to take the results and
> >> create an excel or word file for management. I would
> need
> >> it to list the job name, schedule, frequency...so on.
> >> Trying to script this is not easy in that the sysjobs
> and
> >> sysjobschedules tables use numbers to represent
> everything
> >> about the schedule and all.
> >>
> >> Any help is appreciated.
> >>
> >> Thanks,
> >>
> >> Van Jones
> >
> >
> >.
> >|||It's a reference to an article in SQL mag a couple of years
or so ago that has a pretty nice script to grab the job,
schedule, avg run time, max run time, some other properties.
It's pretty good - I've used it quite a bit with just a
couple modifications.
-Sue
On Thu, 6 Nov 2003 18:58:09 +0100, "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote:
>OK, I just wanted to point the option of using these tables, in case you
>haven't used them already. Yes, some things aren't that easy to deal with
>(mostly the freq- things, IMO). Hopefully Sue's proc will help you :-)

Documenting existing stored procedures

Does anyone have any scripts, or know of any tools, that can scan through all of the SPs that I have inherited, documenting details?
ThxThis will retrieve the name and text of all the stored procedures.

SELECT a.name, b.text
FROM sysobjects a INNER JOIN syscomments b ON
a.id = b.id
WHERE
a.xtype = 'P'

This will retrieve the input parameters for all stored procedures in a database:

SELECT a.name, b.*
FROM sysobjects a INNER JOIN syscolumns b ON
a.id = b.id
WHERE
a.xtype = 'P'

What else are you looking to do?|||thanks! :o :o|||Did you use Enterprise Manager to script out the stored procedures and the like?

You can use this to Search your database (http://weblogs.sqlteam.com/brettk/archive/2004/02/05/841.aspx)

Good Luck

documentation on SQL OLE methods...Where to find?

I've seen a few examples in BOL and various websites where sp_OAMethod is
used to generates scripts, return rows, etc. For example:
SET @.exec_str = 'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
'+ @.filename +'")'
EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
I can't seem to find any documentation that explains the possible methods
(like databases().Tables().Script).
Can someone point me to where I could find a list of these methods?
Thanks in advancesp_OACreate invokes an extermal program that has a COM OO interface.
The documentation you're looking for will reside with whichever external COM
application you're referring to.
You haevn't given us the name of the particular application you're looking
for in your post, so it's a bit hard to answer this. Your post only gives us
information on a method - perhaps if you go back to whever you got that code
snippet from & give us the part that has "sp_OACreate" in it, we may be able
to help you further.
Regards,
Greg Linwood
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:#$OwJjrsDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I've seen a few examples in BOL and various websites where sp_OAMethod is
> used to generates scripts, return rows, etc. For example:
> SET @.exec_str =>
'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
> '+ @.filename +'")'
> EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
> I can't seem to find any documentation that explains the possible methods
> (like databases().Tables().Script).
> Can someone point me to where I could find a list of these methods?
> Thanks in advance
>|||Hi Greg,
Thanks for the reply. Here's the sp_OACreate statement:
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
Thanks,
Tom
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:uIDLvwrsDHA.2416@.TK2MSFTNGP10.phx.gbl...
> sp_OACreate invokes an extermal program that has a COM OO interface.
> The documentation you're looking for will reside with whichever external
COM
> application you're referring to.
> You haevn't given us the name of the particular application you're looking
> for in your post, so it's a bit hard to answer this. Your post only gives
us
> information on a method - perhaps if you go back to whever you got that
code
> snippet from & give us the part that has "sp_OACreate" in it, we may be
able
> to help you further.
> Regards,
> Greg Linwood
> SQL Server MVP
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:#$OwJjrsDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I've seen a few examples in BOL and various websites where sp_OAMethod
is
> > used to generates scripts, return rows, etc. For example:
> > SET @.exec_str => >
>
'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"
> > '+ @.filename +'")'
> > EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
> >
> > I can't seem to find any documentation that explains the possible
methods
> > (like databases().Tables().Script).
> >
> > Can someone point me to where I could find a list of these methods?
> >
> > Thanks in advance
> >
> >
>|||Most COM (or Automation) objects come with their own
documentation. For instance, the object hierarchy of SQL-
DMO can be found in SQL Server Books Online. If you need
to interact with an object hierarchy that doesn't seem to
have online/printed documentation, you can try tools such
as OLE-COM Object Viewer that comes with NT Resource kit.
The object viewer allows you browse the object hierarchy
and see all the objects, methods, and proerties.
By the way, if you can stay away from sp_OAxxx stuff, stay
away from it. It's pretty ugly and you can't import the
symbolic constants.
Linchi
>--Original Message--
>I've seen a few examples in BOL and various websites
where sp_OAMethod is
>used to generates scripts, return rows, etc. For example:
>SET @.exec_str =>'Databases("'+@.dbname+'").Tables("'+RTRIM(UPPER(@.tbname))
+'").Script(74077,"
>'+ @.filename +'")'
>EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
>I can't seem to find any documentation that explains the
possible methods
>(like databases().Tables().Script).
>Can someone point me to where I could find a list of
these methods?
>Thanks in advance
>
>.
>