TOP

create new table select – ms sql 2008

select * into [newTable] from [sourceTable]

example: “select * into meNewTable from meSourceTable”
example:  with linked server — millie “cadldgr” table
select * into meMain.dbo.meCadldgr from OPENQUERY (MillieTest,’SELECT * FROM cadtst.dbo.cadldgr’)
  • create new table with SELECT statement
Read More
TOP

MsSql 2008 link server ODBC sybase

exec sp_addlinkedserver @server = ‘Chron1’ ,
@srvproduct = ‘Sybase’,
@provider = ‘MSDASQL’,
@datasrc = ‘Chron1’

sp_addlinkedserver @server= server

, @srvproduct= product_name]
, @provider= provider_name]
, @datasrc= data_source]
, @location= location]
, @provstr= provider_string]
, @catalog= catalog]

The following table shows the ways that a linked server can be set up for data sources accessible through OLE DB. A linked server can be set up using more than one way for a given data source; there may be more than one row for a data source type. This table also shows thesp_addlinkedserver parameter values to be used for setting up the linked server.

Remote OLE DB data source OLE DB provider product_name provider_name data_source location provider_string catalog
SQL Server Microsoft OLE DB Provider for SQL Server SQL Server (1)
(default)
SQL Server Microsoft OLE DB Provider for SQL Server SQL Server SQLOLEDB Network name of SQL Server (for default instance) Database name (optional)
SQL Server Microsoft OLE DB Provider for SQL Server SQLOLEDB Servername\instancename (for specific instance) Database name (optional)
Oracle Microsoft OLE DB Provider for Oracle Any (2) MSDAORA SQL*Net alias for Oracle database
Access/Jet Microsoft OLE DB Provider for Jet Any Microsoft.Jet.OLEDB.4.0 Full path name of Jet database file
ODBC data source Microsoft OLE DB Provider for ODBC Any MSDASQL System DSN of ODBC data source
ODBC data source Microsoft OLE DB Provider for ODBC Any MSDASQL ODBC connection string
File system Microsoft OLE DB Provider for Indexing Service Any MSIDXS Indexing Service catalog name
Microsoft Excel Spreadsheet Microsoft OLE DB Provider for Jet Any Microsoft.Jet.OLEDB.4.0 Full path name of Excel file Excel 5.0
IBM DB2 Database Microsoft OLE DB Provider for DB2 Any DB2OLEDB See Microsoft OLE DB Provider for DB2 documentation. Catalog name of DB2 database

http://support.microsoft.com/kb/280102

Read More