Automatically script out a table & indexes - script needed (2005)

I'm trying to automate an auto-export of a table on a daily basis,
using BCP. I'm using native format for the BCP because the text in
one of the fields can encompass pretty much any ASCII characters, and
using the other options (including the null terminator, stuff like
|||, etc) hasn't worked particularly well.

So, I'm archiving out a table on a daily basis. I want to script out
the table at the same time; that way, if there are any table changes,
an import will still work.

How can I do this? I've been digging through google for scripts with
no luck. Ideally I'd like a table-creation script, along with CREATE
INDEX statements.

Anybody have a script handy for this? I know it can be done by using
the system tables, but I'm hoping to avoid reinventing the wheel.
Thanks in advance.

MichaelM Bourgon (bourgon@.gmail.com) writes:

Quote:

Originally Posted by

I'm trying to automate an auto-export of a table on a daily basis,
using BCP. I'm using native format for the BCP because the text in
one of the fields can encompass pretty much any ASCII characters, and
using the other options (including the null terminator, stuff like
>|||, etc) hasn't worked particularly well.
>
So, I'm archiving out a table on a daily basis. I want to script out
the table at the same time; that way, if there are any table changes,
an import will still work.
>
How can I do this? I've been digging through google for scripts with
no luck. Ideally I'd like a table-creation script, along with CREATE
INDEX statements.
>
Anybody have a script handy for this? I know it can be done by using
the system tables, but I'm hoping to avoid reinventing the wheel.
Thanks in advance.

If you are on SQL 2005, you would use SMO for the scripting and on SQL 2000
it would be DMO. No, I don't have any examples, I have stayed away from
both.

Personally, I would prefer the definition of the table to be under version
control and be content with that.

But why use BCP as a backup tool? Why not simply BACKUP? Or are you
trying to tell us that this is the only table in a big database that
you want to back up?

--
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|||Kalen has written this script
SELECT type_desc,object_name(ic.object_id) as object_name , index_name =
i.name,

'column' = c.name,

'column usage' = CASE ic.is_included_column

WHEN 0 then 'KEY'

ELSE 'INCLUDED'

END

FROM sys.index_columns ic JOIN sys.columns c

ON ic.object_id = c.object_id

AND ic.column_id = c.column_id

JOIN sys.indexes i

ON i.object_id = ic.object_id

AND i.index_id = ic.index_id

"M Bourgon" <bourgon@.gmail.comwrote in message
news:1184085398.736952.162110@.o61g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

I'm trying to automate an auto-export of a table on a daily basis,
using BCP. I'm using native format for the BCP because the text in
one of the fields can encompass pretty much any ASCII characters, and
using the other options (including the null terminator, stuff like
|||, etc) hasn't worked particularly well.
>
So, I'm archiving out a table on a daily basis. I want to script out
the table at the same time; that way, if there are any table changes,
an import will still work.
>
How can I do this? I've been digging through google for scripts with
no luck. Ideally I'd like a table-creation script, along with CREATE
INDEX statements.
>
Anybody have a script handy for this? I know it can be done by using
the system tables, but I'm hoping to avoid reinventing the wheel.
Thanks in advance.
>
Michael
>

Automatic Query Generator

In my final project, the biggest time consuming issue was writing a query, when there are 15 fields on the average than it is certainly not easy to write an SQL statement and run it (through code)So I want to know, is there any automatic query generator ?that could work like:1) We enter all control names 1 by 12) we enter postfix text ( like .Trim(), .ToString() ) etc3) Result should be automatically generated queryIs there any software/program for that ?Infact it is very easy to develop myself, but I dont want to waste the time if such tool (most probably) already exists

A bit of advance. I figure your in school and by "final project" you mean your senior project or something along those lines. I recommend not using any "auto-generation" code tools until you are very familiar with hand writing all the code manually yourself during the learning stages. I too liked all of the visual tools while in school and used them when I had the chance (wizards, design view, etc.) Although it does make things quite easy, in the long run, it hurts no one but yourself. I've learned from experience. When changes are needed or additions, its a lot easier to go into the source of the code, or code behind to change it and not having to deal with the designer at all.

So now, I work about 95% from the source view and I stopped using the designer view. Since then I have really started to understand and learn A LOT more than the simple auto-generation or wizard methods. I mean, when there are time constraints and other variables involved, it could be benefical to use the easy methods, but if you really dont understand wants going on and the code in the back-end, any changes that are needed could take twice as long to figure out and change in the long-run. And most of the time there are always changes to a project, so understanding it key. Also, many of these auto-generated tools add a lot of "junk" in your code that you dont use and makes changes that much harder to understand.

Just a bit of advice, ive learned from doing it the "Wrong" way and then re-learning the stuff and doing it the "Right" way. Writing clean and understandable code is a must and best practice.

Anyone can write code, but if its not readable and maintainable to another person, its pretty much crap, in my eyes at least. Just keep this in mind while you develop.

Sorry if I got a little off the topic of your question. You might know a lot more than myself and are just looking for a tool to save yourself some time, but this is just a handy tip to consider, for I graduated a little over a year ago and matured a lot as a developer by trying to follow "best practices".

automatic number problem

Hi, i have imported an access database into sqlserver 2000. In access,
i have some table fields which were using automatic number as
datatype. But i see that there is not a datatype in sqlserver like
automatic no. I have to enter id numbers to my tables for each records
automatically. Would anybody help me about solving this problem? Any
idea? Thanksfatih kayaalp (kaya_alp@.hotmail.com) writes:
> Hi, i have imported an access database into sqlserver 2000. In access,
> i have some table fields which were using automatic number as
> datatype. But i see that there is not a datatype in sqlserver like
> automatic no. I have to enter id numbers to my tables for each records
> automatically. Would anybody help me about solving this problem? Any
> idea? Thanks

You can assign a column the IDENTITY property:

CREATE TABLE a (a int IDENTITY(1, 1) NOT NULL,

To get idenity value the most recently inserted row, use scope_identity().

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I have some table fields [sic] which were using automatic number as
datatype [sic]. <<

You do not understand SQL or data modeling. Fields are not columns;
tables are not file; rows are not records. The entire concept of a
physical numbering of rows is not relational. Your original design
was wrong and you want to copy it into SQL. Why do you want to do
that again??

Take a course and take the time to learn to do it right.|||Joe, go back in the hole you came from, take an attitude course and then
come back. Not earlier.

"--CELKO--" <joe.celko@.northface.edu> wrote in message
news:a264e7ea.0401132113.38222a3a@.posting.google.c om...
> >> I have some table fields [sic] which were using automatic number as
> datatype [sic]. <<
> You do not understand SQL or data modeling. Fields are not columns;
> tables are not file; rows are not records. The entire concept of a
> physical numbering of rows is not relational. Your original design
> was wrong and you want to copy it into SQL. Why do you want to do
> that again??
> Take a course and take the time to learn to do it right.|||Martin Feuersteiner (theintrepidfox@.hotmail.com) writes:
> Joe, go back in the hole you came from, take an attitude course and then
> come back. Not earlier.

Joe on an attitude course? What a waste of time and money! That man is a
hopeless case!

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> Joe on an attitude course? What a waste of time and money! That man
is a hopeless case! <<

I was going to take a Dale Carnegie course, but the restraining order is
still in effect.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Automatic Fields

Hi,

it's possibile to define table fields for automatic Last_Update_Date and for
Creation_date using column formula??

Thanks !!!hmario wrote:

Quote:

Originally Posted by

it's possibile to define table fields for automatic Last_Update_Date and for
Creation_date using column formula??

You can create regular columns, and write (fairly simple) insert and
update triggers to populate them appropriately. I don't know a simpler
way to do it.|||Hi.

Ok, I do it with triggers, Thanks !!

"Ed Murphy" <emurphy42@.socal.rr.comha scritto nel messaggio
news:PqH6h.705$Fg.383@.tornado.socal.rr.com...

Quote:

Originally Posted by

hmario wrote:
>

Quote:

Originally Posted by

>it's possibile to define table fields for automatic Last_Update_Date and
>for Creation_date using column formula??

>
You can create regular columns, and write (fairly simple) insert and
update triggers to populate them appropriately. I don't know a simpler
way to do it.

Auto-incremented fields error

I am trying to insert into a SQL Server table from an Oracle database. This table has an auto-incremented field, and when I try to insert into this table I get the following error:

Code Snippet

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL
into column '<column_name>', table '<my_table>'; column does not allow nulls.
UPDATE fails.[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been terminated. (SQL State: 23000; SQL Code: 515)

When I turn off all the triggers in the database, I don't get this error. But that is just a test environment, the production environment will need to have those triggers activated.

Any ideas on what is going on here?

Thanks.

Have a look at whether the following KB article (PRB: Guarantee @.@.IDENTITY Value on a Per Table Basis) is relevant to your case.|||

The most common cause of this issue is partial insertion into a table with non-null columns. If you have a table with (not null) columns, you must specify a value for that column on insert or it will generate this error. Make sure that either your inserts always apply values to these columns or the columns are nullable. Autoincrementing columns and columns with default values should not cause this issue, so you can ignore those as well.

Hope that helps,

John

Auto-increment fields when moving a base from Access to SQL server

Hello all,

I'm a total newbie with SQL Server 2000 and I have a little problem when
moving a database form Access 2000 to SQL Server 2000.
In the Access database, each table has an auto-increment field.
After importing the tables in SQL Server, all the auto-increment fields
are turned into "int" type fields.
Does anybody have an explanation for that mystery?

Thanks in advance,
YanYou might want to change the field type from int to identity. The identity
field type in SQL Server is analogous to the Autonumber field type in
Access.

good luck

CJ

"Yan Roosens" <yan.roosens@.skynet.be> wrote in message
news:3F7C4258.6C6B428E@.skynet.be...
> Hello all,
> I'm a total newbie with SQL Server 2000 and I have a little problem when
> moving a database form Access 2000 to SQL Server 2000.
> In the Access database, each table has an auto-increment field.
> After importing the tables in SQL Server, all the auto-increment fields
> are turned into "int" type fields.
> Does anybody have an explanation for that mystery?
> Thanks in advance,
> Yan|||Hi CJ,

> You might want to change the field type from int to identity. The identity
> field type in SQL Server is analogous to the Autonumber field type in
> Access.

Thank you, the error message related to that problem is no longer displayed, I
will now care about the next one :-(

Yan|||On Thu, 02 Oct 2003 17:20:57 +0200, Yan Roosens
<yan.roosens@.skynet.be> wrote:

>Hello all,
>I'm a total newbie with SQL Server 2000 and I have a little problem when
>moving a database form Access 2000 to SQL Server 2000.
>In the Access database, each table has an auto-increment field.
>After importing the tables in SQL Server, all the auto-increment fields
>are turned into "int" type fields.
>Does anybody have an explanation for that mystery?
>Thanks in advance,
>Yan
Because that isn't a data type. Look up identity for an explanation of
how sql server can provide that sort of functionality.

AutoIncrement Fields

I am trying to upsize a Microsoft Access database.
I have used Autoincrement/random to generate some key fields
Is there a way to duplicate this fuctionality in MSDE? ( I can find the
ability to increment starting with a seed)
thanks
Ed Warren.
Hi,
See IDENTITY property in SQL Server books online. Usage is
CREATE TABLE TESTTABLE(i int IDENTITY(1,1), Name Varchar(10))
So the value for i start with 1 and increment by 1
Thanks
Hari
SQL Server MVP
"Ed Warren" <eowarren@.fakeaddress.zzz> wrote in message
news:exV1d5rNFHA.1500@.TK2MSFTNGP09.phx.gbl...
>I am trying to upsize a Microsoft Access database.
> I have used Autoincrement/random to generate some key fields
> Is there a way to duplicate this fuctionality in MSDE? ( I can find the
> ability to increment starting with a seed)
> thanks
> Ed Warren.
>
|||Ed,
You can use a trigger to give you a random auto increment - the upsizing
wizard can do this for you or you can add your own similar to
CREATE TRIGGER [StudentsTrig] ON dbo.Students
FOR INSERT
AS
SET NOCOUNT ON
Declare @.randc int, @.newc int
SET @.randc=0
WHILE @.randc=0
BEGIN
SELECT @.randc = (SELECT convert(int,(rand()*4294967295)-2147483648))
END
SELECT @.newc = (SELECT [Stud ID] FROM inserted)
UPDATE Students SET [Stud ID]=@.randc WHERE [Stud ID]=@.newc
You can modify the select statement if you want to allow negative numbers
(like Access).
Russ Stevens
|||That's what I'm looking for, thanks a lot
Ed Warren
"Russell Stevens" <rustyprogrammer@.online.nospam> wrote in message
news:e3FZJAtNFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Ed,
> You can use a trigger to give you a random auto increment - the upsizing
> wizard can do this for you or you can add your own similar to
> CREATE TRIGGER [StudentsTrig] ON dbo.Students
> FOR INSERT
> AS
> SET NOCOUNT ON
> Declare @.randc int, @.newc int
> SET @.randc=0
> WHILE @.randc=0
> BEGIN
> SELECT @.randc = (SELECT convert(int,(rand()*4294967295)-2147483648))
> END
> SELECT @.newc = (SELECT [Stud ID] FROM inserted)
> UPDATE Students SET [Stud ID]=@.randc WHERE [Stud ID]=@.newc
> You can modify the select statement if you want to allow negative numbers
> (like Access).
> Russ Stevens
>

Auto scripting SPs, perhaps concatenating text fields...

Hi,

I'm trying to get the text of all my SPs saved into text (*.sql) files. My first thought was to use sp_helptext and bcp the table to a text file but this isn't working (see my other post) so thought I'd try another method.

I can get the code from syscomment.text and concatenate the varchar(8000) field together in a text field. Unfortunately this isn't as easy as just text = text + newtext, how is this done?

Or am I doing it all comletely the wrong way? BTW, I have over 150 SPs so I can't save them individually.

Thanks!

Nick

Why don't you use EM or SSMS for that? Both utilities have feature to scrip database objects.|||

I've just found the "create one file per object" option. Thanks gavrilenko_s!

Nick

Auto populating the fields box.

After I hook up a datasource with a stored procedure, I
run the query and expect the fields box to populate with
the retun query columns. This does not happen. Are there
certain conditions that the query has to meet for SQL RS
to do this, for example, does the query have to return a
permanent table?
Thanks,Yes.
For your query, in order to update the fields list, you will need to click
on the "Refresh Fields" button in the Data Set toolbar in Data View.
The report designer has two methods available to update the fields list.
1. Automatic (fast, low-cost): The Schema is queried by calling
IDbCommand.ExecuteReader(SchemaOnly), which returns schema information
without executing the query (parameter values are not needed). This method
is automatically invoked when you switch from Data to Layout View. It is
fast because the query doesn't have to be executed. However, there are
queries whose schemas can only be discovered by running the query. The
queries which don't return a schema include stored procedures that either
use dynamic SQL or use temporary tables. Most SQL command text and stored
procedure calls with a fixed schema work fine here.
2. Manual (slow, higher-cost): The schema is queried by calling
IDbCommand.ExecuteReader(SingleResult), which executes the query returning
the 1st row and also schema information. This method is invoked when you
click on the "Refresh Fields" button in the Data View (query designer). For
stored procedures, this method requires asking the user for sample parameter
values and then running the query with those values.
--
Jerry Povse
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"stevec" <anonymous@.discussions.microsoft.com> wrote in message
news:27dae01c4635a$b1d41810$a301280a@.phx.gbl...
> After I hook up a datasource with a stored procedure, I
> run the query and expect the fields box to populate with
> the retun query columns. This does not happen. Are there
> certain conditions that the query has to meet for SQL RS
> to do this, for example, does the query have to return a
> permanent table?
> Thanks,

Auto increment help

Is there an auto increment feature for integer key fields. I'm using VS2005 to configure my DB and I do not see a way to set this.

Thanks in Advance
MoonWa

CREATE TABLE tblArtists
(
artistID int identity primary key,
artistName nvarchar(50) NOT NULL
)

this SQL creates a table with two fields, the keyword 'identity' makes a field auto increment. In the UI there's a property 'identity specification', or something similar. If you open this, you can check 'is identity'.

|||

DataColumn type has a property AutoIncrement:

dtEmployees.Columns[0].AutoIncrement = true;
dtEmployees.Columns[0].AutoIncrementSeed = -1;
dtEmployees.Columns[0].AutoIncrementStep = -1;

SQL Server table may also have one identity column, as Christian mentioned. But you should be aware of some points:

1. SQL Server by default doesn't allows inserting in Identity columns (and never allows updates)
2. Your DataColumn instance knows nothing about current SQL Server Identity value and will possibly generate duplicate values (which will fail on insert if column is constrained via Primary Key or Unique, as usually).

So, if you let SQL Server to generate values, you also should update your column' values with generated one from stored proc output parameters or from output from FOR INSERT trigger on your table (or so) using DataAdapter. For example:

daEmployees.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

WBR, Evergray
--
Words mean nothing...

|||Thanks for your help.

MoonWa