SQLdbCopy Tech Info

by Bill Dowling 28. July 2010 06:19

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 and 2010.

 

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;td>
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 TypeDataTypeEnumProcedure TypeOleDbType
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

Tags: , ,

Access DB | SQL Coding Examples

Add comment

About the author

Bill Dowling has been a VB and .Net programmer for as long as he can remember.

Month List