MS SQL Transfer Help

[psychobilly]

I need advice to move my existing MS SQL database (about 7 views and 28 tables) to my new MS SQL database that will be hosted here. Any thoughts?

My current host only allows SQL Server Web Admin to modify the database (Enterprise Manager not allowed), but I don't see any "export" feature.

Thanks

 

[Stephen]

Can DTS access the server?

 

[psychobilly]

I don't think so. Remote connections are not allowed, no Enterprise Manager.

 

[Stephen]

What SQL web admin is used, ASP Enterprise Manager.net, Microsoft SQL Webadmin, littletools webadmin?

 

[psychobilly]

It says "SQL Server Web Admin" the site is currently with a GoDaddy reseller if that helps... There is a "CVS import" but no export.

 

[psychobilly]

I guess I jumped the gun, there is an export to csv. How can I import a CSV file into MS SQL Server?

 

[hatton]

It isn't going to be pretty if you don't have an application to put it together but you can use some queries to grab what you're looking for:

SELECT SO.Name as ItemName, SC.Name as ColumnName, ST.Name as ColumnType, 
CASE WHEN ST.Status = 2 THEN '(' + CONVERT(VarChar(6), SC.Length) + ')' ELSE CASE WHEN SC.AutoVal IS NOT NULL THEN 'IDENTITY' END END As ColumnTypeExtras,
CASE WHEN Left(SysIndexLookup.IndexType, 2) = 'PK' THEN 'PRIMARY KEY' ELSE '' END as KeyText, SCom.Text as Contents, SO.XType, 'DEFAULT ' + SD.Text AS DefaultValue, SCom.ColID
FROM SysObjects SO LEFT OUTER JOIN SysColumns SC ON
	SO.ID = SC.ID LEFT OUTER JOIN SysTypes ST ON
	SC.XType = ST.XType LEFT OUTER JOIN
		(SELECT SI.ID, SI.Name as IndexType, SIK.ColID
		 FROM SysIndexes SI INNER JOIN SysIndexKeys SIK ON
		 	SI.ID = SIK.ID AND
			SI.IndID = SIK.IndID) SysIndexLookup ON
	SO.ID = SysIndexLookup.ID AND SC.ColID = SysIndexLookup.ColID LEFT OUTER JOIN SysComments SCom ON
		SO.ID = SCom.ID LEFT OUTER JOIN
		(SELECT sysobjects.id, sysobjects.name, sysobjects.parent_obj, syscomments.text
		 FROM SysObjects INNER JOIN SysComments ON
			SysObjects.id = SysComments.id AND SysObjects.xType = 'D') SD ON
	SC.CDefault = SD.ID
WHERE SO.XType NOT IN ('S','PK','F','D') AND 
	Left(SO.Name, 2) NOT LIKE 'dt%'	
ORDER BY SO.ID

This will give you a query that will contain the strucure of your tables along with the contents of any views, stored procedures or triggers. I am actually in the process of building a ColdFusion application to turn the output of that into a SQL script file.