This page has moved to our new blog at SQLdbCopy Tech.
Different SQL Server statements need to be performed depending on what version of SQL Server you are running. Below are some of the statements used in SQLdbCopy to perform specific tasks, both for SQL Server and Visual Basic with Visual Studio 2005.
| Sql Server 2000 | Version 8 |
| Sql Server 2005 | Version 9 |
| Sql Server 2008 | Version 10 |
| OleDB Provider statement for Access | Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; [{0} = Full Filename] |
| .Net Date/Time Formatting | MM/dd/yyyy HH:mm:ss |
| .Net List of SQL Servers | Dim dt As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources |
| 2000 List Databases | SELECT * FROM dbo.sysdatabases; |
| 2005 List Databases | SELECT * FROM sys.sysdatabases; |
| 2000 List Tables | SELECT u.name AS SchemaName, o.name AS TableName , SUM(CASE WHEN (c.status & 0x80 = 0x80) THEN 1 ELSE 0 END) AS SetIdentity FROM sysobjects AS o INNER JOIN sysusers AS u ON o.uid = u.uid INNER JOIN syscolumns AS c ON o.id = c.id WHERE (o.xtype = 'U') AND (o.name NOT LIKE 'dtproperties') AND (o.name NOT LIKE 'tmp%') AND (o.name NOT LIKE 'sys%') GROUP BY u.name, o.name ORDER BY u.name, o.name; |
| 2005 List Tables | SELECT s.name AS SchemaName, t.name AS TableName, SUM(CASE WHEN (c.is_identity = 1) THEN 1 ELSE 0 END) AS SetIdentity FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.columns AS c on t.object_id = c.object_id WHERE (t.type = 'U') AND (t.name NOT LIKE 'dtproperties') AND (t.name NOT LIKE 'tmp%') AND (t.name NOT LIKE 'sys%') GROUP BY s.name, t.name ORDER BY s.name, t.name; |
| 2000 List Primary Key | SELECT u.name AS SchemaName, t.name AS TableName, ix.name AS IndexName, ik.keyno AS ColumnNum, c.name AS ColumnName FROM dbo.sysobjects AS t INNER JOIN dbo.sysindexes AS ix ON t.id = ix.id INNER JOIN dbo.sysindexkeys AS ik ON ix.id = ik.id AND ix.indid = ik.indid INNER JOIN dbo.syscolumns AS c ON ik.id = c.id AND ik.colid = c.colid INNER JOIN dbo.sysusers AS u ON t.uid = u.uid WHERE ((ix.status & 2048) <> 0) AND (u.name = '{0}') AND (t.name = '{1}') ORDER BY t.name, ix.name, ik.keyno; [{0}=SchemaName, {1}=TableName] |
| 2005 List Primary Key | SELECT s.name AS SchemaName, t.name AS TableName, ix.name AS IndexName, ic.index_column_id AS ColumnNum, c.name AS ColumnName FROM sys.tables AS t INNER JOIN sys.indexes AS ix ON t.object_id = ix.object_id INNER JOIN sys.index_columns AS ic ON ix.object_id = ic.object_id AND ix.index_id = ic.index_id INNER JOIN sys.columns AS c ON ic.object_id = c.object_id AND ic.column_id = c.column_id INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id WHERE (ix.is_primary_key = 1) AND (s.name = '{0}') AND (t.name = '{1}') ORDER BY t.name, ix.name, ic.index_column_id; [{0}=SchemaName, {1}=TableName] |
| 2000 List Columns | SELECT u.name AS SchemaName, t.name AS TableName, c.colid AS ColumnID, c.name AS ColumnName, dt.name AS DataType, c.length AS MaxLength, c.prec AS ColumnPrec, c.scale AS ColumnScale FROM dbo.syscolumns AS c INNER JOIN dbo.sysobjects AS t ON c.id = t.id INNER JOIN dbo.systypes AS dt On c.xusertype = dt.xusertype INNER JOIN dbo.sysusers AS u ON t.uid = u.uid WHERE (u.name = '{0}') AND (t.name = '{1}') AND (dt.name NOT LIKE 'sys%') ORDER BY t.name, c.colid; [{0}=SchemaName, {1}=TableName] |
| 2005 List Columns | SELECT s.name AS SchemaName, t.name AS TableName, c.column_id AS ColumnID, c.name AS ColumnName, dt.name AS DataType, c.max_length AS MaxLength, c.precision AS ColumnPrec, c.scale AS ColumnScale FROM sys.tables AS t INNER JOIN sys.columns AS c ON t.object_id = c.object_id INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.types AS dt ON c.system_type_id = dt.user_type_id WHERE (s.name = '{0}') AND (t.name = '{1}') AND (dt.name NOT LIKE 'sys%') ORDER BY t.name, c.column_id; [{0}=SchemaName, {1}=TableName] |
We have added a SetIdentity field in the "List Tables" code to identify tables that have a column with "IDENTITY" set. This allows us to identify which tables need "SET IDENTITY_INSERT [schemaname].[tablename] On/Off" statements when inserting data.
For Access Database output, we currently process the following SQL data types and what ADO format we convert them to. If a character (adVarWChar) based field is larger then 255 characters, it will be converted to an Access Memo (adLongVarWChar) field.
| SQL Type | DataTypeEnum | Procedure Type | OleDbType |
|---|---|---|---|
| char | adVarWChar | TEXT | VarWChar |
| nchar | adVarWChar | TEXT | VarWChar |
| text | adLongVarWChar | MEMO | LongVarWChar |
| ntext | adLongVarWChar | MEMO | LongVarWChar |
| varchar | adVarWChar | TEXT | VarWChar |
| nvarchar | adVarWChar | TEXT | VarWChar |
| bigint | adNumeric | NUMERIC | Numeric |
| int | adInteger | LONG | Integer |
| smallint | adSmallInt | INTEGER | SmallInt |
| tinyint | adUnsignedTinyInt | NUMERIC | UnsignedTinyInt |
| bit | adBoolean | BIT | Boolean |
| decimal | adNumeric | NUMERIC | Numeric |
| numeric | adNumeric | NUMERIC | Numeric |
| money | adCurrency | CURRENCY | Currency |
| smallmoney | adCurrency | CURRENCY | Currency |
| float | adDouble | DOUBLE | Double |
| real | adDouble | DOUBLE | Double |
| date | adDate | DATE | Date |
| datetime | adDate | DATE | Date |
| smalldatetime | adDate | DATE | Date |
| binary | adLongVarBinary | BINARY | LongVarBinary |
| varbinary | adLongVarBinary | LONGBINARY | LongVarBinary |
| image | adLongVarBinary | LONGBINARY | LongVarBinary |
| timestamp | adVarBinary | BINARY | VarBinary |
| uniqueidentifier | adGUID | GUID | Guid |
| cursor | not translated | ||
| sql_variant | not translated | ||
| table | not translated | ||
| xml | adLongVarWChar | MEMO | LongVarWChar |