Automatically Script Out A Table && Indexes - Script Needed (2005)
Jul 10, 2007
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.
I am new to SQL server, but I need to develop a "big" application with it. Here's my application requirements:
1)Create a table, say TableA, in SQL server 7 or 2000 database,say DB1, based on another Oracle database,say DB2, on a remote server. Update TableA based on DB2 every day in some specific time. 2) If new records are inserted into the TableA,some other tables needed to update in DB1. At the same time, an e-mail will send to three receipts.
I have no idea of which function or method I can use to fulfill the requirements. Can any experts here give me some advice, detail explanation would be grateful.
I am trying to automate my index rebuild and reorg based off of the percentage of fragmentation level. The first time you run it you have to change the alter proce Sp_NCRNRecreate then change it to Alter proc. It seems to run okay no errors but it doesn't seem to actually rebuild them. Please take a look and let me know if you see a program error somewhere. I would like create this sp to run automatically
use master
GO
Alter PROC sp_NCR_RecreateIndexes @AutoRun bit=0 AS
SELECT db_name(s.database_id) as DatabaseName, schema_name(o.schema_id) as SchemaName, o.name as TableName, i.name as IndexName, s.avg_fragmentation_in_percent AS PercentFragmented
INTO #IndexesToRebuild
FROM sys.dm_db_index_physical_stats (db_id(), Null, NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id=o.object_id
INNER JOIN sys.indexes i ON s.object_id=i.object_id AND s.index_id=i.index_id
WHERE s.avg_fragmentation_in_percent > 10.0 AND s.index_id > 0
SELECT * FROM #IndexesToRebuild
DECLARE IndexCursor CURSOR FOR SELECT DatabaseName, SchemaName, TableName, IndexName, PercentFragmented FROM #IndexesToRebuild
OPEN IndexCursor
FETCH IndexCursor INTO @DatabaseName, @SchemaName, @objectName, @IndexName, @PercentFragmented
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @databaseName + '.' + @schemaname + '.' + @objectName + CASE WHEN @PercentFragmented<30 THEN ' REORGANIZE' ELSE ' REBUILD' END;
print @command
IF @AutoRun=1
EXEC(@command)
FETCH IndexCursor INTO @DatabaseName, @SchemaName, @objectName, @IndexName, @PercentFragmented
I am having a table called as status ,in that table one field is there i.e. currentstatus. the rows which are having currentstatus as "ticket closed",i want to move those rows into other table called repository which is having same table structure as status table. I can do programatically. but is there any way for every 3 months system has to check and do this action means moving to repository table automatically?
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
Hi all,We recently upsized two Microsoft Access Databases to SQL. We'reusing an ADP (2002) as the front end.All the conversion issues have been resolved, except for one:Whenever we insert a record into a table, the table isn't sorted byprimary key like I would expect. Instead, the record can be found atthe end of the table. This makes finding a particular record(especially as time goes on) very difficult.I've tried eliminating all indexes except for the primary key, andalso writing AFTER INSERT triggers, but the table still does not sortcorrectly.Any suggestions would be greatly appreciated!Matt
Once I've added Files for a database across mulitple SAN drives willSQL 2005 automatically partition data across the Files automatically?Will it atleast grow across files automatically? Or do I have to doScheme Functions, Partitions, and assign the Scheme Partition to eachtable in the database?ThanksErik
Can someone tell me if it is possible to add an index to a Table variable that is declare as part of a table valued function ? I've tried the following but I can't get it to work.
ALTER FUNCTION dbo.fnSearch_GetJobsByOccurrence ( @param1 int, @param2 int ) RETURNS @Result TABLE (resultcol1 int, resultcol2 int) AS BEGIN
I just want to display all the data of a Single table into a Data Grid, I know that we can drag and drop the table on to a form and datagrid is generated, but here I want to retrive those values through my code, how should i do that
I am getting following errors while running the program Error 1) Error No. 28037, MS SQL Server 2005 Evrywhere Edition Error: A request to send data to the computer running IIS has failed. For more information see HRESULT Error 2) Error No. 0, SQL Server 2005 Evrywhere Edition ADO.Net Data Provider Error: The specified table does not exist [ JobLists ].
Can anybody please tell me, where I went wrong ??? In this code anywhere else????
Note: While adding a Data Source of SQL Server 2005 Mobile Edition, I have added that .sdf file into my project, thats why I have written the Data Source as : .DbFile.sdf
@"Data Source = .DbDotNetCF.sdf";
The code is as follows:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlServerCe;
namespace DeviceApplication1 { public partial class Form1 : Form { string filename = @".DbDotNetCF.sdf";
private DataSet dsJobLists;
public Form1() { InitializeComponent(); }
private void DeleteDB() { if (System.IO.File.Exists(filename)) { System.IO.File.Delete(filename); } }
private void Sync() { SqlCeReplication repl = new SqlCeReplication();
if (DbDotNetCFDataSetUtil.DesignerUtil.IsRunTime()) { // TODO: Delete this line of code to remove the default AutoFill for 'dbDotNetCFDataSet.JobLists'. this.jobListsTableAdapter.Fill(this.dbDotNetCFDataSet.JobLists); } } } }
I have created a merge replication correctlly( I suppose, there were no errros) Please help
Does anyone know if the results from a query run in QA automatically go into a tempoary table? For instance if ive just run a query that took 5 minutes but it was a normal select query and after it ran i realised i wanted to query the results could i run a simple command to mke a copy of the result set rather than run the original query into a temp table that would then take a few minutes again?
When the new month comes I want the table to alter the columns automatically instead of manual how we do it now. The new Month table should delete [02-2015] and replace it with [03-2015].
hi, i have 2 tables where i require an unique ID over both. I googled a bit and discovered that i need to create an own table for this which holds the last value (because mssql unfortunately does not support sequences).
i did this but my problem now is that i want to automatically set the id of my tables with a trigger on INSERT.
hope this is possible .. or anyone have other suggestions? thanks a lot!
How to install sql server 2005 automatically with sa password
I want to know that i am creating a setup in visual studio 2005, i have set up sql server as prerequsite, by default the sql server installation is silent, it does not ask anything, in MSDE we had the facility of setyp.ini file which automatically creates the database with the settings provided in the ini file. Is there any option like this in sql server 2005 express editon.
We have just updated our sql 2005 server with sql SP 1. Should we apply this service pack to the client boxes that access this server or is that unnecessary?
I am very well versed on manipulating data using passthrough queries through access or the command object in ADO/VBA but this problem requires a sql server side solution and I have been assigned the task
Scenario
Access via an ODBC link cannot view Sql table data that uses a BigInt as a primary key. Access can update the data, insert data ands delete data to these tables - but viewing (by design) returns the actual count of the rows but each field is populated with #deleted.
I can view the table data if I use a passtyhrough query but performing row level and field level edits from there is impossible. Not all people have Studio manager so that is not an option.
We are moving away from our legacy application and the development company has already developed the schema using the BigInt so changing that to a numeric or integer is out of the question. My boss has determined that using a set of bridge tables with identical tables in a different database where we replace the bigint with int will work - we can view the data in the "bridged" database and even update it from select queries and simply by opening the table in view mode from Access.
Here is the hard part (for me) because I have never done this. I can write SQL and understand the security/roles etc, but I have never administered SQL server regarding server side triggers and stored procedures - I have always used the command object or passthrough queries to deal with sql stored procedures. I need to either
A)Create some trigger behind the tables in my bridge table that will fire a stored procedure to replicate the data to the bigint database. They are both on the same sever.
B)Somehow create a "mirror" that always replicates from my bridge database tables (5 of them) to the new database.
First of all I want to apologize if I am in the wrong forum. I am new to SQL Server. I have a table with a field containing email address from user input. Is there a build-in function or script in SQL Server 2005 that I can send a template of a email message to users? If not in what way can I acheive this?
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
Hello, I am using SQL Server 2005 and ASP.NET 2.0. We have a very simple content management system where we have to keep track of date last modified for each row in all of our content tables. I know there's a "timestamp" datatype that is used for replication scenarios, but is there anything similar that I can use to set up a date_modified column for each of my content tables that will automatically update with GETDATE() whenever anything in a given row is updated? Or do I have to create a date_modified column of smalldatetime datatype and write a trigger on update for EVERY single table of content that I have in the database? It seems there should be an easier way to do this than to write 20 triggers for my 20 content tables. Thanks!
I'm trying to update (increment) Company.SumtotalLogons from CompanyUsers.NumberOfLogons where CompanyUsers.CompanyID = Company.CompanyID
I'd like to either write a formula (if it is even possible to fire a formula from one table update/insert to increment a field in another table), or a stored procedure that triggers an auto update/append into Company.SumTotalLogons
I know this is possible in access, so i'm wondering how to go about it in ms-sql?
I have some database tables, and each one of them have a creation_date and modified_date in them. I was trying to figure out a way where when a row in one of these tables is changed using Enterprise Manager (Database -> Tables -> select table -> right click -> select all rows -> change a field in a row inside a table), is there a way apart from triggers, such that the "modified_date" column for that row get changed to 'getdate()' (rather picks up the current datetime).
I am still having problem with making View automatically updates itself when the underlying table schema changes. Running sp_recompile on the view table doesn't seem to work either, as I am still getting old format from the view (in Design mode the view returns the right info, but not when I open the View by doing Open View) even though the underlying schema has changed. Right now I find that I have to go into the View and change it a bit to force a recompilation.
And even if sp_recompile does, it would require that I manually do it each time I change a table. Any idea?
I have a question about adding a unique key column to an existing table.
what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?
I have 4 databases in the same space.My users use all of them and use the same username and password to log into these 4 databases.In each of these databases,i have put a control table to allow me to keep track of all users that have to reset their passwords.
The control table consists of the username and flag fields.When the flag is ON(1) the user is forced to reset thier password and if the flag is OFF(0) they are not.
When a user logs into any one of these databases and they have to reset thier password,how do i make sure that all the other tables in other databases are also updated to make sure that the user is not forced to reset their password again when they log into those other databases later since they are using the same username and password for all databases.
I am planning to use a stored procedure which i will put into all the four databases and when a user logs in and has to reset their password,that sproc is called and automatically updates all the other 3 tables in other 3 databases.
System.Data.SqlClient.SqlException was unhandled Class=20 ErrorCode=-2146232060 LineNumber=0 Message="An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)" Number=2 Server="" Source=".Net SqlClient Data Provider" State=0
Hi guys, I have just set up a database in SQL 2005 Express Edition. However, I have a problem accessing it with the application program created in VB 2005 EE. Before using SQL 2005, the application worked perfectly in SQL 2000. Here is my connection string used in SQL 2000:
Dim sqlConn As New SqlConnection("Data Source=(local); Database='Inventory List'; Integrated Security=yes")
Is this valid in SQL 2005 Express Edition? Or should I change the connection string instead? Thank you very much.
Hi, I'm currently migrating to Sql 2005 from 2000 and have a quick question about indexes. In 2000 i used to click on Table > All Tasks > Manage Indexes... to view and create all my table indexes. There doesn't seem to be anything similar in 2005 Management Studio. I thought it was under Table > Modify but there's nothing there for indexes. I've had to create all my indexes manually through t-sql but this doesn't help when needing to view and manage current indexes. Can anyone help me on this?
How to create insert statements of the data from a table for top 'n' rows. I know we can create using generate scripts wizard, but we can't customize the number of rows. In my scenario i got a database with 10 tables where every table got millions of records, but the requirement is to sample out only top 10000 records from each table.
I am planning to generate table CREATE statements from GENERATE Scripts wizard and add this INSERT STATEMENT at the bottom.
EX : INSERT [dbo].[table] ([SERIALID], [BATCHID] VALUES (126751, '9100278GC4PM1', )
Iam aware that Microsoft SQL 2005 Express Edition does not have agent or DTS capabilities, but how may I automate an import of flat files to the DB tables? Must I use an external VB development of is there a way to schedule an import of flat files to Microsoft SQL 2005 Express Edition ?
