Hi,
I have a "master" table that holds the names of data tables (one record in the "master" table for each "data" table).
Can I create a ForeignKey constraint that will prevent the "master" table records from being removed if the cooresponding "data" table exists? Is the way to do this to use INFORMATION_SCHEMA.TABLES as the PrimaryKeyTable for the ForeignKey?
Thanks!
Hi:
I am new to SQLSERVER, so I am trying to learn from all these database views, in Oracle it use 'desc all_tables ' to database dictionary view, can some one tell me how to view SQLserver dictionary view like INFORMATION_SCHEMA.TABLES, or sys.tables?
IF I login as sa, but I only want to view the table list one schema at a time? how do I do that?
I tried :
select table_name from INFORMATION_SCHEMA.TABLES
where table_schema='CIT'....
select table_name from INFORMATION_SCHEMA.TABLES
where table_schema='CIT.DBO'.... give 0 result too.
it give me 0 result, but if I login as CIT, then
'select table_name from INFORMATION_SCHEMA.TABLES' will give 14 tables.
Plus, When I log into Query analyzer, all the tables has dbo. prefix. , why is that?
Thanks a lot
how do i return only the tables created by the user?
in three of my databases i am inserting one record per 5 secs. in all the tables. how good is using triggers for 'insert instead of' for these tables?
The following code example to returns table space usage in my databases, except for AdventureWorks.
When I "USE AdventureWorks", I get the following message. "Invalid object name 'INFORMATION_SCHEMA.tables'."
Why is AdventureWorks different? Thanks. Bill
USE AdventureWorks
-- Declare variables
DECLARE @AWTables TABLE (SchemaTable varchar(100))
DECLARE @TableName varchar(100)
--Insert Table names into the TABLE variable
INSERT @AWTables
(SchemaTable)
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME
-- Report on each table using sp_spaceused
WHILE (SELECT COUNT(*) FROM @AWTables) > 0
BEGIN
SELECT TOP 1 @TableName = SchemaTable
FROM @AWTables
ORDER BY SchemaTable
EXEC sp_spaceused @TableName
DELETE @AWTables
WHERE SchemaTable = @TableName
END
Hi,
we've just installed SQL2005 and, as expected, are hitting problems with collation settings.
We have made sure that the collation of our new server is the same as the old (working) SQL2000 server (both are Latin1_General_CI_AS) but a function that works fine on the 2000 box just doesn't work on the 2005 box.
The problem is this...
Code Snippet
select *
from information_schema.[tables] as t
left join :: fn_ListExtendedProperty('MS_Description','user','dbo','table',null,null,null) as xp on ( xp.objname = t.table_name )
...where that string comparison on "table_name" results in...
"Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_CI_AI" in the equal to operation."
Now... I can't see where the "Latin1_General_CI_AI" is coming from. The two data sources are both "system" sources, so should, I would have thought, been the same. If I do...
execute sp_help "information_schema.tables"
...all the columns are Latin1_General_CI_AS. If I do...
execute sp_help fn_ListExtendedProperty
...the same collation is returned for all columns.
I've checked everything I can think of (server, master, tempdb, etc.), and everything seems to be "AS".
Where might this "AI" collation be defined ? We have only just installed 2005, so a re-install, while a pain, is not a massive problem, but what should we check/change ?
Thanks
Hi,
I need to create a table (Named as C) with a foreignkey column. That column should references with a primarykey column in table A and a primarykey column in table B. Is this possible?
Thanks in advance.
ramesh.p
Hi, I want know how can I to build a query to get all the foreignkey contrains exist between tables using the sys tables, for example if the user select this two tables:
dbo.cat_states -> with this fields -> id_state & desc
dbo.cat_universities -> with this fields -> id_state, id_university & desc_university
I want get something like this:
dbo.universities.id_state = dbo.states.id_state
tks 4 help
Leo
I have written the Query in which i am getting TableName,Columns,Precision but how can i get Table related Foreign key and Constraints
SELECT DISTINCT
QUOTENAME(SCHEMA_NAME(tb.[schema_id])) AS 'Schema',
QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) AS 'Table',
C.NAME AS 'Column',
T.NAME AS 'DataType',
C.max_length,
C.is_nullable,
c.precision,
c.scale
[code]...
Hi All,
I have a table [Vendor] in first database SAMPLEDB. And i have another table [Contract] in another database TESTDB. I need a foreign key in the [Contract] table (which is in TESTDB database) is reference to the other table [Vendor] which is present in the SAMPLEDB.
When i try to creating them through SQL, i received the following error message :
Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key 'SAMPLEDB..VENDOR'.
Is that possible to refer foreign key in external database or not?
If possible, please give solution for this. If not possible, please suggest some alternate way.
Thanks in Advance
Hi,
I'm trying to figure out how to setup a Constraint (Foreign Key?) from a lookup table.
For the following example, I want to constrain to constrain so that a Car.ParkingSpotNumber is unique for a given ParkingFacility.
Any thoughts?
Thanks!
Andy
Here's the scenario:
CAR table:
Id (PrimaryKey)
LicensePlateNumber
ParkingSpotNumber
PARKINGFACILITY__CAR__LOOKUP table:
Id (PrimaryKey)
CarId (Foreign Key to Car.Id)
ParkingFacilityId (Foreign Key to ParkingFacility)
PARKINGFACILITY table:
Id (PrimaryKey)
PARKINGLOT table:
Id (Foreign Key to ParkingFacility.Id) (PrimaryKey)
Address
PARKINGGARAGE table:
Id (Foreign Key to ParkingFacility.Id) (PrimaryKey)
Address
Here's the T-SQL code to make a SQLServer2005 database for this scenario:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Car]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Car](
[Id] [bigint] NOT NULL,
[CustomerName] [nvarchar](50) NOT NULL,
[ParkingSpotNumber] [int] NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingFacility]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingFacility](
[Id] [bigint] NOT NULL,
CONSTRAINT [PK_ParkingFacility] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingFacility__To__Car_Lookup](
[Id] [bigint] NOT NULL,
[CarId] [bigint] NOT NULL,
[ParkingFacilityId] [bigint] NOT NULL,
CONSTRAINT [PK_ParkingFacility__To__Car_Lookup] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingLot]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingLot](
[Id] [bigint] NOT NULL,
[Address] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ParkingLot] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingGarage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ParkingGarage](
[Id] [bigint] NOT NULL,
[Address] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ParkingGarage] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingFacility__To__Car_Lookup_Car]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]'))
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car] FOREIGN KEY([CarId])
REFERENCES [dbo].[Car] ([Id])
GO
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingFacility__To__Car_Lookup_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]'))
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility] FOREIGN KEY([ParkingFacilityId])
REFERENCES [dbo].[ParkingFacility] ([Id])
GO
ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingLot_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingLot]'))
ALTER TABLE [dbo].[ParkingLot] WITH CHECK ADD CONSTRAINT [FK_ParkingLot_ParkingFacility] FOREIGN KEY([Id])
REFERENCES [dbo].[ParkingFacility] ([Id])
GO
ALTER TABLE [dbo].[ParkingLot] CHECK CONSTRAINT [FK_ParkingLot_ParkingFacility]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingGarage_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingGarage]'))
ALTER TABLE [dbo].[ParkingGarage] WITH CHECK ADD CONSTRAINT [FK_ParkingGarage_ParkingFacility] FOREIGN KEY([Id])
REFERENCES [dbo].[ParkingFacility] ([Id])
GO
ALTER TABLE [dbo].[ParkingGarage] CHECK CONSTRAINT [FK_ParkingGarage_ParkingFacility]
Hi,
We have a DB with a heirarchy of tables each connected via Guids and controlled for Delete Operations by Foreign Key Constraints. However, under certain privileged conditions we would like CERTAIN users to be able to perform a Cascade Delete all the way down.
Thus by example we have tables A,B,C,D - Typically once a record is inserted into Table A then we can add multiple records in Table B referencing that new record in Table A...and so on through records in Table C referencing Table B...records in Table D referencing table C. Now when we try and delete that ONE Record in Table A we 'could' implement CASCADE DELETES which would delete all those records in Table B,C,D.
We decided that was too dangerous to implement as Whooosh....all records could be deleted by a non-privileged user deleteing that ONE records in Table A, so we changed the Cascade DELETE to NO ACTION. However, there are times (hah...particularly in Testing !) when we would like a privilegd user to be able to DO that task...i.e. perform a CASCADE delete.
Has anybody got any suggestions on how this would be best, easily and securely implemented.
I have had a brief look at INSTEAD OF Triggers but am not sure about the pitfalls of using this...
ANy help appreciated,
Cheers,
Desmond.
Hi to all,
I'm using SQL Server 2005. May I know where the INFORMATION_SCHEMA
is found, can we view this by object explorer.
Hello,
When the procedure : sp_tables is executed (master db), the table owners are either dbo or INFORMATION_SCHEMA,
any detail about this last ? (to be precise, table_type is view and not table).
Thanks
Hi:
I need to change a column default to '' and not null for 1500 databases accross 10 servers.
if exists (select * from information_schema.columns
where table_name = 'tblABC'
and column_name = 'columnX'
and data_type = 'VARCHAR'
and is_nullable = 'No'
and column_default = '('')'
)
begin
--do something to implement
end
Here, there is a problem for and column_default = '('')'
I have tried ''''+'('')' + '''' or "'('')'", neither works.
Do you have any idea to deal with the ('') ?
thanks
-D
I'm having a really hard time displaying the structure of my database using INFORMATION_SCHEMA. Can this be used with Access? I've used it before for sql databases but when I try it with this access one I get an error message telling me: Can't find file c:...INFORMATION_SCHEMA.mdb
I'm hoping maybe there's another command that works just as well or some other way to get around it.
I'm using Visual Web Developer 2005 Express
and my .aspx pages are coded using VB so any insights would be awesome!
Thanks much,
~UNI
Hi I need to see all the indexes in a database. The ID has dbo rightsto the database, but not to the master. I can't see anything inINFORMATION_SCHEMA.CHECK_CONSTRAINTS orINFORMATION_SCHEMA.KEY_COLUMN_USAGEAn sa ID for the master sees everything however.Thanks for your helpPachydermitis
View 5 Replies View RelatedHi,
I'd like to get information about tables defined in the SqlCe20 database. So I tried to use the following which doesn't work:
string sql = "SELECT COLUMN_NAME AS CN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?"cmd = new SqlCeCommand(sql, connection);cmd.Parameters.Add(newSqlCeDataParameter("A", DbTypes.NVarChar);cmd.Prepare();//later in codecmd.Parameters[0].Value = tableName;reader = cmd.ExecuteReader();while (reader.Read())....
Here the reader.Read() returns always false indepent of the table name.
In contrast doing the following without parameter works as expected:
reader = cmd.ExecuteReader(string.Format("SELECT COLUMN_NAME AS CN FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", tableName));
while (reader.Read())
Because I have to execute this statemen often I have to use prepared statement with parameters.
How can I get it working?
Miroslaw
Hiya,
I'm trying to run reports, some of which have UserEntered as a criteria. So I'm filling up a list with (SELECT * FROM SysUsers WHERE IsLogin = 1), which is also pulling up INFORMATION_SCHEMA as a login. I'm not really sure why this would be an islogin = 1 or issqluser = 1. Is this a server default login,and if yes,why, and what can I do about it?
Thanks,
Sarah
Hi:
from SQL2005 information_schema, I don't see anything related to default constraint.
So, I still need the old way to use old stuff such as
from sysobjects sobj inner join syscolumns scolumn.
I need to check existing 3000 databases to drop any possible default for tableA.columnB's default and then add a new one.
I thought SQL2005 will stop let us to query system table to retrieve table structure info including default, but only via information_schema? :rolleyes:
thanks
David
Hi there!
I'm trying to find how can I get the information_schema for a temp table.
I'm trying to find all columns of a temp table.
So it will be something like this SELECT * FROM information_schema.columns
But it doesn't work for temp table, I tried tempdb.dbo.information_schema.columns .... nada....
Please help!
Thanks,
Or Tho
Hi,
I'm working with MSSQL2K+SP3a, Standard Edition. I defined linked server (MSDE).
On the local server, I can do
select * from [testDB].[INFORMATION_SCHEMA].[TABLES] -- local
select * from [testSRV].[testDB_far].[dbo].[sysobjects] -- linked
but not
select * from [testSRV].[testDB_far].[INFORMATION_SCHEMA].[TABLES]
How can I access the INFORMATION_SCHEMA on the linked server ?
Thanks,
Helena
Hi Folks:I'm a little new to SQLServer, so please pardon my ignorance!I've found the INFORMATION_SCHEMA views for TABLES, COLUMNS, andTABLE_CONSTRAINTS. I'm looking for the views that will give me the list ofcolumns by constraint.For instance, if Table1 has a unique key called Table1_UK01, I can find thatunder INFORMATION_SCHEMA.TABLE_CONSTRAINTS. But I also need to know thecolumns in that UK constraint. I've triedINFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE andINFORMATION_SCHEMA.KEY_COLUMN_USAGE, but the UK I have defined for this usertable doesn't seem to show up in either of those views.Can anyone point me in the right direction? Any sample queries would betremendously appreciated. I'm going to be using this meta-data toautomatically generate quite a bundle of stored procs that do updates basedon finding rows via unique keys...TIA,Dave
View 3 Replies View RelatedHi
I'm building a data warehouse - my end users connect using Access via ODBC Microsoft SQL Server driver (2000.85.1117.00).
However, whenever they connect using Access via ODBC they get a huge list of sys and INFORMATION_SCHEMA views, in addition to the data warehouse tables they need to access.
How can I remove these sys and INFORMATION_SCHEMA views from the list of tables/views presented to the end user?
I've tried denying access by changing permissions to deny in the public role of the master database - I have also changed permissions in the public role in the data warehouse database. When I do this, the ODBC connection fails to retrieve any objects because it doesn't have access to sys.databases (and various other unspecified objects).
I'm stuck - help!
We have a legacy app where some of our web page urls were hardcoded into the stored procs (SQL 2000 SP4 database). We have changed the system and so changed the hardcoded strings with a value stored in a config table. We used the following query to identify the hardcoded urls (say LegacyPage.asp) €“
select routine_name
from information_schema.routines
where routine_definition like '%LegacyPage.asp?%'
However, even after this we keep getting issues with LegacyPage.asp being referenced. Tracing the code, I found that there is at least one SP (say spHardCoded) which does not turn up in the query, but does have the string LegacyPage.asp? in the routine definition. When I run the following query €“
select routine_name
from information_schema.routines
where
routine_name = 'spHardCoded'
and routine_definition like '%LegacyPage.asp?%'
0 rows are returned!
Am I missing something obvious here or is INFORMATION_SCHEMA.Routines metadata not always updated? Is there any way to force the metadata to be updated, before we query it? Is there a better system catalog view which lets me do the same thing? Any help would be really appreciated.
I ran the following query in Query Analyzer for a 7 column table.SELECT c.name,c.colid FROM syscolumns c WHERE c.id=925962375 ORDER BYc.colidThe results were:I_CSD 1X_STE_XML2I_USR_LCK4T_CRT_RCD5I_USR_CRT_RCD6T_UDT_RCD7I_USR_UDT_RCD8If I use the information_schema view (SELECT column_name,ordinal_position FROM information_schema.columns WHERE table_name ='CSD_XML') I get the same results.The problem is that the colids go from 2 to 4 and the colids gothrough 8 when there are only 7 columns.At one time there was another column in the table, but it has sincebeen dropped and isn't there anymore. It seems that the colids insyscolumns did not update when the column was dropped.Is this because of the way I dropped the column? Is there anything Ican do now that it has happened?
View 3 Replies View RelatedI'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
We have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process
View 3 Replies View RelatedI am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
From Newbie to Newbie,
Add reference to:
'Microsoft ActiveX Data Objects 2.8 Library
'Microsoft ADO Ext.2.8 for DDL and Security
'Microsoft Jet and Replication Objects 2.6 Library
--------------------------------------------------------
Imports System.IO
Imports System.IO.File
Code Snippet
'BACKUP DATABASE
Public Shared Sub Restart()
End Sub
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String
With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName
'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With
'Notify user
MsgBox("Data restored successfully")
Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table
'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog
Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")
'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)
'User notification )
MsgBox("A new empty database was created successfully")
'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing
'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification
MsgBox("The database was compacted successfully")
End Sub
End Class
I'm working on an ASP.Net project where I want to test code on a localmachine using a local database as a back-end, and then export it tothe production machine where it uses the hosting provider's SQL Serverdatabase on the back-end. Is there a way to export tables from oneSQL Server database to another in such a way that if a table alreadyexists in the destination database, it will be updated to reflect thechanges to the local table, without existing data in the destinationtable being lost? e.g. suppose I change some tables in my localdatabase by adding new fields. Can I "export" these changes to thedestination database so that the new fields will be added to thedestination tables (and filled in with default values), without losingdata in the destination tables?If I run the DTS Import/Export Wizard that comes with SQL Server andchoose "Copy table(s) and view(s) from the source database" and choosethe tables I want to copy, there is apparently no option *not* to copythe data, and since I don't want to copy the data, that choice doesn'twork. If instead of "Copy table(s) and view(s) from the sourcedatabase", I choose "Copy objects and data between SQL Serverdatabases", then on the following options I can uncheck the "CopyData" box to prevent data being copied. But for the "CreateDestination Objects" choices, I have to uncheck "Drop destinationobjects first" since I don't want to lose the existing data. But whenI uncheck that and try to do the copy, I get collisions between theproperties of the local table and the existing destination table,e.g.:"Table 'wbuser' already has a primary key defined on it."Is there no way to do what I want using the DTS Import/Export Wizard?Can it be done some other way?-Bennett
View 3 Replies View RelatedI have a trade data tables (about 10) and I need to retrieve information based on input parameters. Each table has about 3-4 million rows.
The table has columns like Commodity, Unit, Quantity, Value, Month, Country
A typical query I use to select data is "Select top 10 commodity , sum(value), sum(quantity) , column4, column5, column6 from table where month=xx and country=xxxx"
The column4 = (column2)/(total sum of value) and column 5=(column3)/(total sum of quantity). Column6=column5/column4.
It takes about 3-4 minutes for the query to complete and its a lot of time specially since I need to pull this information from a webpage.
I wanted to know if there is an alternate way to pull the data from server ?
I mean can I write a script that creates tables for all the input combinations i.e month x country (12x228) and save them in table (subtable-table) with a naming convention so from the web I can just pull the table with input parameters mapped to name convention and not running any runtime queries on database ??
OR
Can I write a script that creates a html files for each table for all input combinations save them ?
OR
Is there exists any other solution ?
Hi all,
I have a large Excel file with one large table which contains data, i've built a SQL Server DataBase and i want to fill it with the data from the excel file.
How can it be done?
Thanks, Michael.
Hi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.