|
|
|
|
|
|
Question of the Day |
Today's question (by Steve Jones - SSC Editor): | |
Numeric and Decimal | |
What's the difference between the numeric and decimal datatypes? | |
Think you know the answer? Click here, and find out if you are right. |
Yesterday's Question of the Day (by Steve Jones - SSC Editor) |
Getting the Options I run this command in Management Studio: select @@options I get the result of 5496 back in the Results tab. What does this mean? Answer: This is the value of the bitmap of option values Explanation: All the SET options are stored in a bitmap. If you bitmap and with the value of @@OPTIONs, you will see the value of each item. As an example: DECLARE @OptionValues INT SELECT @OptionValues = @@Options IF ( (1 & @OptionValues) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' IF ( (2 & @OptionValues) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' IF ( (4 & @OptionValues) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' Ref: @@Options - https://docs.microsoft.com/en-us/sql/t-sql/functions/options-transact-sql?view=sql-server-ver15 |
Database Pros Who Need Your Help |
Here's a few of the new posts today on the forums. To see more, visit the forums. |
SQL Server 2017 - Administration |
migrate package siis from 2008 to 2107 - good evening I need to migrate packages ssis 2008 to 2017 I am looking for a method to export my packages that are on the 2008 server on the net I find this command who has a feedback experience USE MSDB SELECT 'EXEC XP_CMDSHELL ''DTUTIL /SQL ' + NAME +' /COPY FILE; D:\SQLDBAExperts\Packages\' + NAME+'.DTSX' […] |
Whose account the job runs under? - I have a sql agent job that is called by a cmd file remotely using an admin account for example I will call it OrchestrorService account. In SQL agent job history I can see the job was invoked by that account. But the job step actually is a SSIS package. And the step is run […] |
Unable to Join Databases to AG - I have a stand alone SQL 2008 R2 SQL Server that I'm migrating to a 3 node AG. At various times during the testing of the restore process to get the databases onto the new SQL Server 2017 servers that are on CU16 I run into a scenario where one or more databases will join […] |
Using Storage DeDupe for AlwaysOn Availability Groups - I have a Sr. Sysadmin here who wants to turn on storage dedupe for the three nodes that will be host AG's because he really wants space savings. The setup is that I actually have 3 sets of 3 nodes. Each set of three nodes will be hosting databases from what is now a single […] |
SQL Server 2017 - Development |
sqlcmd - Unable to pass in exact file path as an input variable to :out - Hi there I have a routine which outputs an ID generated from a stored procedure into a text file as follows: :!!if exist \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt del \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt --:connect localhost\SQL2008R2 :out \\LONDEVSQL01\Staging\SEG_DEV_H01\Import\SupplementaryImport\StoredProcedureRunTrackerID.txt select Ltrim(Rtrim(@StoredProcedureRunTrackerID)) Now i wanted to change this , so that the path for :out is not hardcoded and passed in as a variable, ie […] |
SQL Server 2016 - Administration |
Package error - when i ran the package through sql agent job it got failed but i did not find any information in job history Executed as user: . Started: 11:59:55 AM Finished: 12:00:43 PM Elapsed: 47.281 seconds. The package execution failed. The step failed. How can we trouble shoot |
Extended events - MY EXTENDED EVENT SCRIPT: CREATE EVENT SESSION [CLogins] ON SERVER ADD EVENT sqlserver.sql_statement_completed(SET collect_statement=(1) ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username) WHERE (([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%SELECT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ALTER%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DELETE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INSERT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CREATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%DROP%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%RENAME%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TRUNCATE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%COMMENT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%MERGE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%CALL%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%EXPLAIN PLAN%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%LOCK TABLE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%GRANT%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%REVOKE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INDEXES%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%TABLE%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%INDEX%')) […] |
SQL Server 2016 - Development and T-SQL |
The data types varchar and datetime2 are incompatible in the add operator. - In the procedure i have date column as datetime2 declare @date datetime2 = null, @CaptureError varchar(100) when i am logging this variable like below SET @CaptureError= @CaptureError + ' ,Date: ' + coalesce(@Date, 'NULL') this is getting error as data types varchar and datetime2 are incompatible in the add operator. what is the workaround for […] |
SSIS For each Loop not finding files when deployed - Hi, I have an issue with a SSIS package deployed to the catalog. This package includes a For Each container that scans a folder for CSV files. I can run the package from Visual Studio on my development box using my credentials and it completes successfully finds the files targeted by the for each loop […] |
Administration - SQL Server 2014 |
Problem with calculating an approximate space required for online index rebuild - I have a database called TestDB which is in simple recovery model. I performed index rebuild on my Test Server which has no other user transactions except me. Before an online index rebuild the sizes of mdf and ldf files were as below: total size […] |
SQL 2012 - General |
Join Query, two tables, two databases? - Is that possible? To have two databases with one query? If so the query below should populate some data! If not, how can I accomplish the task? SELECT JobSheet.dbo.JobHrs.JobNo, JobSheet.dbo.JobHrs.ActualHrs, JobSheet.dbo.JobHrs.ReworkHrs, TimeReporting.dbo.TimeData.Time, TimeReporting.dbo.TimeData.Rework FROM JobSheet.dbo.JobHrs INNER JOIN TimeReporting.dbo.TimeData ON JobSheet.dbo.JobHrs.JobNo = TimeReporting.dbo.TimeData.JobNo WHERE JobSheet.dbo.JobHrs.JobNo = '19-0228B' |
General Cloud Computing Questions |
Archival Options due to Regulation - Hi Guys, I have an ongoing need to provide a archival solution for an ex-production system for Regulatory reasons. In a nutshell: active database is around 30GB, but there will be others pending of varying sizes there may be the need for a couple of queries a year initially, tapering off potentially need to […] |
SSDT |
Using variables in multiple packages - I have a SSIS Solution built in Visual Studio Community 2019 using SSDT. The solution contains about 15 packages. I am using 5 variables (created in the first package) that I would like to use in all of the other packages. Is there a way to make a variable usable in other packages within the […] |
Expression won't evaluate for property - I am attempting to parameterize the three properties for checkpoints in a SSDT 2017 SSIS project and keep receiving this error for the CheckpointUsage property: Error loading: The result of the expression "@[$Project::parCheckpointUsage]" on property cannot be written to the property. The expression was evaluated, but cannot be set on the property. I have the […] |
Events |
PASS Local Group/Virtual Group/SQL Saturday organizers corner - A dedicated place for PASS Local Group/Virtual Group/SQL Saturday organizers to share marketing and promotional content as well as discuss what is working and what is not (credit for the idea goes to Cecilia Brusatori) |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |