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


6

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


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

It's been a while since I used Red Gate, but the VS2010 has it matched from what I remember, with options to include or exclude by object types, and generate scripts to match the two schemas; the VS tools takes a while to run, I remember the Redgate to be pretty quick.


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.


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

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


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


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


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 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

Why are you using ##global temp tables instead of #local? It is possible that when you are changing the procedure there is already a global temp table in existence with the same name - they're global so it could have been created by anyone. Use a local temp table and that problem will go away. Also, please don't use the sp_ prefix. Stored procedures ...


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

$(...) denotes a variable in SQLCMD, so it's interpreting your data as a variable. Use the -x command-line option to disable variables.


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

Use the standard SSMS scripting feature: Right click on database in object explorer->Tasks->Generate Scripts Then choose what tables do you want to script and not forget to check "Script Data"


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

You can also remove the PRINT command from the generate scripts wizard by setting "Include Descriptive Headers" to false. Generate SQL Server Scripts Wizard (Choose Script Options Page)


1

would this script use a temporary table? Then the table will have the server default collation. If it just a quick one-time solution, then I would modify the source query to provide the data in the correct collation, here Hebrew_CI_AS


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

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

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

For what redgate does that VS dosn't its really not worth paying all that extra money. Redgate tools are seriously over priced. Redgate is a little nicer to use but you would have to use it an awful lot to justify paying that much on an extra tool.


1

I've used both and I thought that Red Gate's was a slightly better interface and easier to use (that probably meant I was overlooking something in VS). They both produce reasonably similar scripts.


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