All Questions
Tagged with scripting sql-server-2016
17 questions
0
votes
1
answer
63
views
Extra `CASE WHEN` Removes Text from Dynamically Created String
I am trying to generate a CREATE TABLE statement based on a given table in my SQL-SERVER database using the INFORMATION_SCHEMA tables, but when I try and concatenate the DEFAULT portion of a field ...
- 3
1
vote
1
answer
2k
views
SQL Server Command Line Install - How to change the edition?
I am creating a script to install SQL Server via the command line.
I cannot see a way to choose the edition I want to install. The GUI on my install media offers Evaluation, Developer or Express:
(...
- 2,083
1
vote
1
answer
1k
views
how to script out proxy accounts?
I have servers where I use proxy accounts to run ETL processes or even to backup SSAS databases using powershell through a job.
I can see what AD Accounts are related to my proxies:
use msdb
go
...
- 16.6k
3
votes
2
answers
4k
views
how to add data compression to a primary key of a table?
We work with tables with over 300GB of size, a small percentage of them tables use sparse columns
although that doesn't always agree with CPU utilization as this question shows.
For the tables that ...
- 16.6k
4
votes
2
answers
5k
views
how to script out the user defined table types?
I can Get name and definition of all table types
using either of the following scripts:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF OBJECT_ID('TEMPDB..#RADHE') IS NOT NULL
DROP TABLE #...
- 16.6k
1
vote
1
answer
537
views
passing the parameters of sp_executesql to sp_ineachdb or sp_foreachdb
I am often in need to run the sp_executesql in the context of different databases
I would like to add the same parameters as the sp_executesql into the sp_ineachdb
But when I look at this:
-- Syntax ...
- 16.6k
1
vote
2
answers
711
views
sys.dm_db_stats_properties seems to be misbehaving for a small table - how to get the number of records of a table in a different way?
I have this table in one of my databases:
IF OBJECT_ID('[dbo].[repl_Gender_Type]') IS NOT NULL
DROP TABLE [dbo].[repl_Gender_Type]
GO
CREATE TABLE [dbo].[repl_Gender_Type] (
[Gender_TypeID] CHAR(...
- 16.6k
0
votes
2
answers
2k
views
How to script all the service broker elements for each database it is on, in the current server?
I have service broker running on different servers.
Just recently I had to rebuild one of the test servers and it had a complete service broker solution running there.
For the next time I need to ...
- 16.6k
0
votes
1
answer
63
views
How to format the output of this query into year...second?
I have this query that given @secs a number of seconds, it returns a table with year,months,weeks,days,hours,minutes and seconds.
take for example:
declare @secs int
select @secs = 19475298
...
- 16.6k
1
vote
1
answer
72
views
is there a way to improve this query by not re calculating functions? cross apply?
when I set up replication I like to take a count of each table in the publisher, then I go to the subscriber I do the same thing in order to compare both pictures.
this is the picture from the ...
- 16.6k
2
votes
1
answer
2k
views
How to script out the grant view on login permission?
I create the following login without any permissions.
USE [master]
GO
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'Radhe')
CREATE LOGIN [Radhe] WITH PASSWORD=N'...
- 16.6k
6
votes
1
answer
5k
views
Sqlcmd: Invalid argument - when trying to pass a variable to sqlcmd
I'm trying to use a DOS command prompt on Server 2012R2 to run a batch with the following in it. I want to run a SQL command (sqlcmd) and return the results to the command window.
This is what I'm ...
- 165
11
votes
7
answers
15k
views
How do I format the output from OBJECT_DEFINITION so that it looks the same as sp_helptext?
object_definition and sp_helptext both return the source code of a stored procedure - but how can I use object_definition and get the source code including the line breaks as the sp_helptext currently ...
- 16.6k
0
votes
2
answers
69
views
How to Repair "Orphaned" Database in SQL Server 2016
So, in my haste, I ran a script to create a database but I did not select the
full script before running it. I ended up with a bunch of tables without a "parent" database. Then I created a database ...
0
votes
1
answer
6k
views
SQL Mail Failing To Send
SQL Sever 2016 with the latest updates. Running on WS2012R2. Following error message is received:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail ...
- 47
6
votes
1
answer
3k
views
Was 'ActiveX Scripting' step type secretly removed from SQL Server 2016?
Migrating our database from SQL Server 2012 to 2016, we can't recreate an SQL Agent job that involves an ActiveX Scripting step written in VBScript.
The error message suggests that 'ACTIVESCRIPTING' ...
- 1,353
3
votes
1
answer
4k
views
How to clone a table schema (including indices) in SQL Server?
Is there a command that will completely clone the schema of a table? This would include all constraints, foreign keys, and indices on the table.
In SSMS the Script Table as | Create To menu command ...
- 723