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


7

As well as using SSMS, you can use the commercial 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 ...


6

This is not feasible in T-SQL. Not all object have a definition stored and you would have to reverse engineer CREATE scripts for basic things like tables and indexes. Rather than reinvent the wheel (in T-SQL), do the proper thing and reuse the component dedicated for this purpose the SMO Scripter class. Is already available, works, is up to date with all ...


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.


5

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


5

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


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

Your friend is sys.database_permissions to check/script out permisssions. Below is the script that I am using when doing a refresh of PROD on DEV or UAT server. I will script out before hand all the permissions and after the restore I will just run the script. /* Script DB Level Permissions v2.1 Source: ...


4

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"


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

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


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

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

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

I wrote an open source command line utility named SchemaZen that does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data. To generate scripts run: schemazen.exe script --server localhost --database db --scriptDir c:\somedir Then to recreate the database ...


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

While not disputing that the most comprehensive method is to use SMO as Remus suggested in his answer, this question does request to get the info via T-SQL and that info does exist for some objects. Just keep in mind that this info: covers a small set of object types does not include any GRANT / DENY statements. But you can get that info from ...


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

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


2

It is possible to generate DDL and Data from SQL Server Management Studio (SSMS): Object Explorer > yourServer > yourDatabase > right-click > Tasks > Generate Scripts ... Within the 'Advanced' options change 'Types of data to script' to 'Schema and Data. Also, here is a simple example of using Powershell to script data for tables I did a few years ago: ...


2

Use Profiler for a moment (or Extended Events or a server-side trace), and then produce some of the scripts you want via Management Studio. Make sure you've got the scripting options you want turned on - you may prefer to use Tasks | Generate Scripts at the database level. Anyway, the point being that you can have a look at what queries run, and get a feel ...


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

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



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