IF OBJECT_ID(‘temdb..#tempoffer’) IS NOT NULL
DROP TABLE #tempoffer;
ELSE
CREATE TABLE #tempoffer (offerID BIGINT)
DECLARE @intLimit AS BIGINT = 51237999999 –Ending Range( specify end range)
DECLARE @intCounter AS BIGINT = 51237000000 –Starting Range(specify start range)
WHILE (@intCounter <= @intLimit)
BEGIN
INSERT INTO #tempoffer VALUES(@intCounter)
SET @intCounter = @intCounter + 1
END
–Checking the resultset
Select * from #tempoffer
The below stored procedure using a dynamic sql with cursor to loop all the tablenames present under the numeric schema and get the rowcount and min datetimestamp of all the tables present under the database. you can change your logic differently as per your requirement as this is the basic syntax of how to loop all the tablenames present under the database with a different condition and insert in to table variable.
CREATE PROCEDURE [dbo].[ROWCOUNT_MINCREATETIME]
AS
BEGIN
DECLARE @tableName NVARCHAR(600);
DECLARE @cmd nvarchar(max);SET NOCOUNT ON;
–exec ROWCOUNT_MINCREATETIME
Declare @MainTableinsert TABLE
( Tablename varchar(100), CountofRows bigint, MinCreatedDate Datetime)
DECLARE CUR_TABLE CURSOR FOR SELECT QUOTENAME(t1.name) +’.’+ QUOTENAME(t2.name) as tablename FROM sys.schemas t1INNER JOIN sys.tables t2 ON t2.schema_id = t1.schema_id where ISNUMERIC(t1.name)=1
OPEN CUR_TABLE
FETCH NEXT FROM CUR_TABLE INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd=’Select ”’+ @tablename +”’ as tablename, count(*) as ”Count of rows” , min(CreatedDate) as ” min created date” from ‘+ @tablename +’ (nolock) where CreatedDate < DATEADD(m, -6, current_timestamp) ‘
INSERT INTO @MainTableinsert
EXEC [dbo].[sp_executesql] @cmd;
FETCH NEXT FROM CUR_TABLE INTO @tableName;
END;
CLOSE CUR_TABLE
DEALLOCATE CUR_TABLE
SELECT * from @MainTableinsert ORDER BY CountofRows DESC
END
GO
Below is the powershell script that you can use to backup the sql database to azure URL and I have commented accordingly in the script. Please make sure you have the credential created with name conventions like azurebackup simiarily and you also need have the storageuri to be updated correctly in the below script.
************************************************************************************
Powershell Script Started
**************************************************************************************
# 1. Prepare database name and Azure related Name
$ServerInstance = “******” ##Enter here the server instance name
$DatabaseName = “****” ## Provide your relevant Databasename
$extension= “BAK” ##extension for the backup file
$BackupFileName = “******” ##Provide the name of the backupfile that you want to be stored
$now = [datetime]::now.ToString(‘yyyy-MM-dd’) ## This is to get the timestamp for dynamic timestamp
Try {$ErrorActionPreference = ‘Stop’
# 2. Get Azure Storage Container Uri
$StorageUri =”https://xxxxxx.blob.core.windows.net:123/Storageaccount/Foldername” ## This you should have already if you use azure url account
$BackupUri = “$StorageUri/$BackupFileName.$now.$extension” ##Creating Dynamic timestamp for the backup files that being created with extension
# 3. Connect SqlServer cmdlet
Import-Module Sqlps -DisableNameChecking 3>$null#
#$test=”BACKUP DATABASE [$DatabaseName] TO URL=’$BackupUri’ WITH CREDENTIAL =’*****’,Compression;” ##Checking the syntax of the backup command thats being fired.
# 4. Start backup Main Script that executes the backup script
Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $DatabaseName -Query ” BACKUP DATABASE [$DatabaseName] TO URL=’$BackupUri’ WITH CREDENTIAL =’*****’,Compression;” -QueryTimeout 20000
Write-Host “Finished with Backup”} Catch { Throw} ## Main backup command that get executed when you run this powershell script.
Sometimes you end up with forgetting the password to decrypt the database incase if you want to move or restore in to another server for migration etc, in that case the only option that will be left to you will be decrypting the database. First sql query is use to show the certificate details present for that database in the server. Here first 2 steps under Steps to perform decryption you can run one after another there will be no issue after that pls check my note if the database which you are trying to decrypt is more than 100 GB it will take serveral hours for the entire decryption process to complete which will start from 1% to 100% you need to monitor the status of the same using DMV sys.dm_database_encryption_keys, Below is the screenshot for the same with the sql query required to check. You need to wait until percent_complete of the DMV sys.dm_database_encryption_keys goes to 100% and then run from step3 under Steps to Perform Decryption which is to drop the certificate. You wont be able to drop the certificate until decryption progress goes to 100% . Even if you try dropping the certificate before it goes to 100 % you will get errors saying that you cant drop the certificate as it will be still protected by the Master key.
Confirmation of the certificate name using SQL Query:
select * from master.sys.certificates
Steps to perform decryption:
USE master;
Note: Here you need to wait for sometime until the status in encryption_state of in DMV sys.dm_database_encryption_keys
changes from 5 to sys.dm_database_encryption_keys
0 = No database encryption key present, no encryption
1 = Unencrypted
2 = Encryption in progress
3 = Encrypted
4 = Key change in progress
5 = Decryption in progress
6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)
SQL Query to check the decryption progress:
select database_id,encryption_state,percent_complete from sys.dm_database_encryption_keys
USE <Database_name>
USE master;
When Iam calling SSIS packages one inside another I was getting the error and warnings as below, After few hrs of checking the source and destination data I finally tried AlwaysUseDefaultCodePage” property of oledb source as true, It worked. If you are internally calling SSIS packages one inside another this error may occur at OLEDB source level of the dataflow task.
Solution:AlwaysUseDefaultCodePage” property of oledb source as true.
Error and warning:
[OLE DB Source [1]] Error: The column “UserName” cannot be processed because more than one code page (936 and 1252) are specified for it.
OLE DB Source [1]] Warning: The external columns for component “OLE DB Source” (1) are out of synchronization with the data source columns. The external column “UserName” needs to be updated.
I have faced the below error when trying to rename the cube, This is due to Dim XML file which is 0 KB in size in the data folder change the zero kb size dim file to Dim.Test extension and try renaming the cube again. This will resolve the issue.
XML parsing failed at line 1, a document must contain exactly one root element,an error occured when instantiating a metadata object from the file s:\MSAS10.MSSQLSERVER\OLAP\data\SASFolder.o.db\Dim.XML
Hi all,
I wrote this beta version of the paper, This paper consists of entire 54 questions, with 3 case studies with it.Each case study has around 9 questions that would be 27 questions from the case studies and the remaining questions 27. Overall the exam helped me to understand as what is new in SQL Server 2012.
Some of the questions as what i remembered was one question on SSIS package deployment models. As you all are already aware there is new deployment model in 2012 which is project deployment model there were some questions like in which scenario u will go for this new deployment model. Proactive caching , Degenerate dimensions,defining the relationship between fact and dimension models, Improve the query performance which one u would like to go for Mdx VS Dax, Columnstore indexes.
Welcome to WordPress.com. After you read this, you should delete and write your own post, with a new title above. Or hit Add New on the left (of the admin dashboard) to start a fresh post.
Here are some suggestions for your first post.
soumyasamprathi 