Tag Info

Hot answers tagged

8

You'd use the sp_OA% stored procs for this. Or CLR since SQL Server 2005. It can't be done via T-SQL directly because T-SQL is for data manipulation. So you have to use one of the 2 methods above. Unless you want to use xp_cmdshell to run a powershell script. This also brings up one limitation of T-SQL: how to get an object definition to disk? And I guess ...


5

I tried to use the VS tool yesterday with my production SQL 2000 instance, comparing to my dev 2008 instance, and it refused to work with anything prior to SQL 2005. Red Gate definitely does not have such a restriction. It even works reasonably well (not 100%) with another database we have that runs in 6.5 Compatibility Mode.


5

There's nothing built-in from the command line. If you have Red Gate SQL Compare you can do it: sqlcompare /s1:MySQLInstance /db1:MyDB /mkscr:MyDB_Schema /q SSMS scripting functions are just wrappers for SMO. I know you mention it, but you could write a powershell script to use SMO. This is adapted from code found on this Simple Talk post. ...


5

As well as using SSMS, you can use SSMS Tools Pack I prefer it: more options, batching, cleverer all round. Insert statements for the whole database are generated by the order of PK-FK relationships. Top tables with no FK's are scripted first. Binary data is by default fully scripted. If you wish you can also set the scripting data limit between 0 and ...


5

In theory you could do something as like wrap the script execution in a transaction. You can execute the .sql batch files from an application library like dbutilssqlcmd or SMO's ServerConnection.ExecuteNonQuery which handles the GO batch separator or sqlcmd extensions in the script. However in practice this is nearly impossible to do. Wrapping a script in a ...


4

Even in Management Studio that ships with SQL Server 2012 (BTW please stop using SSMS 2005 to manage 2008+ instances), the Generate Scripts option will not offer Service Broker objects, whether you select "script all" or select individual objects. You'll need to use other methods - SMO, PowerShell, or right-clicking within the Service Broker node of Object ...


4

To just feed off of squillman's answer this is to show a sample of what SQLPS can do for you...You can browse each "directory" under the database and just do a get-member -MemberType Method, looking for Script(). Most of the directories have it I believe. Add-PSSnapin *SQL* # Note my hostname of the server is "SQLSERVER" # To show object names to be ...


4

I'm personally using only Visual Studio with Database Edition GDR, because it's better for my needs. What I like about it mostly is the fact that I'm able to compare db projects with the actual databases. The tool can also compare databases, not only projects. It's true that the schema comparison tool itself is not very fast and has some quirks, but I ...


3

I don't know of any setting which will do this in SSMS. Microsoft probably does it so that they don't need to check the data type name for special characters when generating the script (user defined data type names could have anything in them) so they just box everything. You could submit that as a feature request to the tools team via ...


3

You can use the SQL Server Database Publishing Wizard. If you don't have it installed then you can grab version 1.2 from here The installer doesn't give any indication that it has installed but if you open up a command window and navigate to: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2 the SqlPubWiz.exe should be there. You can ...


3

I wrote a command line utility named schemazen that does this. It's much faster than scripting from management studio and it's free and open source. To generate scripts run: schemazen.exe script --server localhost --database db --scriptDir c:\somedir Then to recreate the database from scripts run: schemazen.exe create --server localhost --database db ...


3

I would not remove the GO statements that break up large batches. There are various reasons for breaking large inserts size (duration) of transactions memory requirements transaction log management mirror synchronicity error isolation (by batch) etc... As for removing the status updates, that can be done easily. Press Ctrl-H or from the menu, Edit ...


3

I would personally just do a backup for each database, and then attach them to the new server. Just remember to use EXEC sp_change_users_login 'Auto_Fix', 'username' for fixing user accounts. The Wizard approach sounds doable. I you need another approach for scripting DDL, the scptxfr.exe tool that comes with earlier version of SQL Server still works with ...


2

You will need to script permissions in two steps, roles and objects. As Kin alludes to, you can use sys.database_permissions for the objects, but you will want to use sys.database_principals and sys.database_role_members for the role membership. The following SQL will work for SQL 2012 only (previous versions should use sp_addrolemember for roles) and ...


2

You can do it using powershell. $serverName = "servername\instanceName" $outputFolder = "D:\data\" ## load the AMO and XML assemblies into the current runspace [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null [System.Reflection.Assembly]::LoadWithPartialName("System.Xml") > $null $dateStamp = ...


2

What your script is doing is explicitly attempting to insert data into an IDENTITY column. You need to do one of two things: Either set IDENTITY_INSERT to ON for that table prior to the insert, or rework the INSERT statement so that it doesn't have the IDENTITY column specified with a value.


2

You could try the following: set pagesize 0 set long 90000 exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false); exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false); SELECT ...


1

Instead of one massive update statement, use multiple update statements one for each where clause. UPDATE [DatabeseOne].[dbo].[TableOne] SET [KeyWord] = 'X Ray' WHERE [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- X-Ray -%') UPDATE [DatabeseOne].[dbo].[TableOne] SET [KeyWord] = 'BMD' WHERE [ReviewNote] COLLATE Latin1_General_CS_AS LIKE('%- bone ...


1

You have three different types of problems from the looks of it. Cannot insert explicit value for identity column in table 'MyTableName' when IDENTITY_INSERT is set to OFF. You have identity columns. For a table that has an identity column you have three choices. First when you do your insert exclude the identity column. Second remove the identity ...


1

Usually the way you'd go about this is to have a Script component and enter your VB / C# code to handle this type of input. If you have a variable number of columns than you should forget about SSIS being able to handle this since it doesn't work well with non-finite number of columns (being that tables have a finite number of columns on them). Having said ...


1

It seems [] is one of internal features. Just do macros in any truth editor to replace [INT], [VARCHAR]... to []less version or just replace all [ and ] with empty char. ADDED: select '[' + UPPER(name) + ']' from sys.types



Only top voted, non community-wiki answers of a minimum length are eligible