Red Gate forums

SQL Prompt versus SSMS 2012 intellisense

freecell1 — Fri, 24 May 2013 18:31:07 GMT

Hi folks -

I just installed SSMS 2012. I've been using SQL PRompt 5 with SSMS 2008 R2 until now and like it. However in SSMS 2012 Microsoft seems to have improved the internal intellisense. Also both the intellisense and SQL Prompt are on by default so I have two layered suggestion boxes popping up. Can someone clarify how SQL Prompt differs from the built-in offering?

Thanks,

John

sql prompt fails to format forceseek index hint (sql 2012)

gokhanvarol@gmail.com — Fri, 24 May 2013 16:00:59 GMT

SQL prompt fails to format below index hint, MSDN documentation is below.
This is a major problem now, I cannot use sqlprompt period for formatting since most of my scripts uses the forceseek hint.
Thank you

WITH ( FORCESEEK(1(CntyCd, PclId, PclSeqNbr)))

SELECT CONCAT(@D, k.AddrAptNbr, @D, k.AddrCarrt, @D, k.AddrDirLeftCd, @D, k.AddrDirRightCd, @D, k.AddrHse1Nbr, @D,
k.AddrHse2Nbr, @D, k.AddrModeCd, @D, k.AddrPfx1Cd, @D, k.AddrSfx1Cd, @D, k.AddrSfx2Cd, @D, k.AddrStreetName, @D,
LTRIM(k.AddrTypCd), @D, k.CbsaCd, @D, k.CensId, @D, LTRIM(k.CSZTypInd), @D, k.DPIDCd, @D, k.DpvCd, @D,
k.GeoMatchCd, @D, k.LatDegr, @D, k.LongDegr, @D, k.MatchCd, @D, k.OrigAddr1, @D, k.OrigAddr2, @D, k.OrigAddr3, @D,
k.OrigCityName, @D, k.OrigStCd, @D, k.StdAddr1, @D, k.StdHse1Nbr, @D, k.StdHse2Nbr, @D, k.StreetNamePfx, @D,
k.StdCityName, @D, k.StdStCd, @D, k.OrigZipCd, @D, k.StdZipCd)
FROM tTrans.TransSitus AS k WITH ( FORCESEEK(1(CntyCd, BatchDt, BatchSeq)))
WHERE k.CntyCd = @CntyCd
AND k.BatchDt = t.BatchDt
AND k.BatchSeq = t.BatchSeq
AND k.SitusSeq = 1

http://msdn.microsoft.com/en-us/library/ms187373.aspx

WITH ( <table_hint> [ [, ]...n ] )

<table_hint> ::=
[ NOEXPAND ] {
INDEX ( index_value [ ,...n ] ) | INDEX = ( index_value ) | FORCESEEK [( index_value ( index_column_name [ ,... ] ) ) ]
| FORCESCAN
| FORCESEEK
| HOLDLOCK
| NOLOCK
| NOWAIT
| PAGLOCK
| READCOMMITTED
| READCOMMITTEDLOCK
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| SPATIAL_WINDOW_MAX_CELLS = integer
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}

Formatting cursor logic indents weird

Mindflux — Fri, 24 May 2013 15:20:21 GMT

Here's a kill user script I found on the web a long time ago, I've flattened it out to show what it looks like before SQL Prompt format

Code:

DECLARE @strSQL VARCHAR(255)

--PRINT 'Killing Users'
--PRINT '-----------------'
CREATE TABLE #tmpUsers (
spid INT,
eid INT,
STATUS VARCHAR(30),
loginname VARCHAR(50),
hostname VARCHAR(50),
blk INT,
dbname VARCHAR(50),
cmd VARCHAR(30),
request_id INT
)

INSERT INTO #tmpUsers
EXEC SP_WHO

DECLARE LoginCursor CURSOR READ_ONLY
FOR
SELECT spid,
dbname
FROM #tmpUsers
WHERE dbname = @dbname

DECLARE @spid VARCHAR(10)
DECLARE @dbname2 VARCHAR(40)

OPEN LoginCursor

FETCH NEXT
FROM LoginCursor
INTO @spid,
@dbname2

WHILE (@@fetch_status <> - 1)
BEGIN
IF (@@fetch_status <> - 2)
BEGIN
--PRINT 'Killing ' + @spid
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
END
FETCH NEXT
FROM LoginCursor
INTO @spid,
@dbname2
END

CLOSE LoginCursor

DEALLOCATE LoginCursor

DROP TABLE #tmpUsers
--PRINT 'Done'

Here's the same script after formatting it with SQL Prompt:

Code:

DECLARE @strSQL VARCHAR(255)

--PRINT 'Killing Users'
--PRINT '-----------------'
CREATE TABLE #tmpUsers
(
spid INT ,
eid INT ,
STATUS VARCHAR(30) ,
loginname VARCHAR(50) ,
hostname VARCHAR(50) ,
blk INT ,
dbname VARCHAR(50) ,
cmd VARCHAR(30) ,
request_id INT
)

INSERT INTO #tmpUsers
EXEC SP_WHO

DECLARE LoginCursor CURSOR READ_ONLY
FOR
SELECT spid ,
dbname
FROM #tmpUsers
WHERE dbname = @dbname

DECLARE @spid VARCHAR(10)
DECLARE @dbname2 VARCHAR(40)

OPEN LoginCursor

FETCH NEXT
FROM LoginCursor
INTO @spid, @dbname2

WHILE ( @@fetch_status <> -1 )
BEGIN
IF ( @@fetch_status <> -2 )
BEGIN
--PRINT 'Killing ' + @spid
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
END
FETCH NEXT
FROM LoginCursor
INTO @spid, @dbname2
END

CLOSE LoginCursor

DEALLOCATE LoginCursor

DROP TABLE #tmpUsers
--PRINT 'Done'

The "Fetch next" are the parts I find odd. Fetch Next gets indented away from FROM LoginCursor INTO ....

then the second fetch next before the END code gets formatted even further out, I guess because it's part of the indent logic for the WHILE BEGIN/END BLOCK?

RE: sql monitor alert level not changing down a level

priyasinha — Fri, 24 May 2013 14:44:06 GMT

My mistake Steven. I assumed that you are talking about built-in Fragmented indexes alert. I have received your email. Replying you in few minutes.

Thanks,
Priya

Changeset Comments Policy Error

flyer299 — Fri, 24 May 2013 14:26:11 GMT

I have Team Foundation server setup to require a Check In Comment on all check ins. I was excited to see that SQL Source was going to support this feature.

When we upgraded to the new version, even if we enter a comment we get an error.

"This Commit doesn't meet the server's policy requirements, or the policy isn't configured on your machince. The Server returned the follwing "Internal error in ChangeShet commetns Policy. Error loading the changeset comments Policy policy."

It does reference a CheckForComments.CS with instructions. Please help? We are dead in the water and can't check code in right now!

RE: sql monitor alert level not changing down a level

stevenhoban — Fri, 24 May 2013 13:45:09 GMT

ive got all the screen shots exce[t the 'frgmented indexe' tab - there isnt one !

Hopefully an easy question

jessay — Fri, 24 May 2013 13:39:40 GMT

Good morning all!

Resolved my issue which was unable to locate triggers pertaining to certain tables after decryption. Realized the code is just all tied to the table and I have to pull the trigger code specifically and then mail it off to the developer for review.

RE: sql monitor alert level not changing down a level

priyasinha — Fri, 24 May 2013 13:28:55 GMT

Hi Steven,

Could you please email screen shot of the following at priya.sinha@red-gate.com:
- Details tab for the alert.
- Fragmented indexes tab for the alert.
- Alert history tab for the alert.
- Click on 'configure alert' link on the alert details page and then send screen shot of the Alert settings page.

Thanks,
Priya

sql monitor alert level not changing down a level

stevenhoban — Fri, 24 May 2013 13:01:16 GMT

I have the alert "Increased % of fragmented indexes" set up on sql monitor. The medium level alert is set when the value reaches 25 and the alert was fired at value 29. I last night ran some index maintenance jobs to rebuild if over 30% fragmentation and reorganise if over 5%.
The alert collects the data once a day @ 13:35 and i can see the alert has now been 'downgraded' from medium to Low when i click on the 'alert history'
How is it then, that the actual alert on the inbox is still showing as 'Medium' - should this not have changed to a blue 'low'.?
Also if i set to re-organise my indexes with 5% or above fragmentation level, howcome this is still showing as a downgraded leve. Shouldnt it be set to 'ended'
Thanks
Steven

RE: Use migration script with Snapshots

rickjr82 — Fri, 24 May 2013 12:42:16 GMT

Sounds good, I sent you a PM with contact and more general information.

Once we get this finalized, I will provide a write up and share with the group on a solid CI->automated deploy process.