Posts Tagged ‘sqlserver’
October 6th, 2014
Honor those who should.
Setting up a new Web site and then connecting it to Sqlserver is a breeze if one uses the development web server.
But if one wants to use the real McCoy, i.e. IIS, there is more work.
iis apppool: http://www.selfelected.com/iis-7-iisapppool-lightly-explained/
Why use the real IIS when it is so cumbersome? Because it looks more like the real production environment.
How do I debug? Like this:
Start Visual studio i in administrator mode. (if one doesn’t, VS is helpful with this)
Then ctrl-alt-p (menu:debug:attach to process) and choose the w3wp.exe process, with the right user name. Attach. Debug.
February 4th, 2013
A simple solution copied from here is
1
| sp_msforeachtable "sp_spaceused '?'" |
an almost as simple is the one below copied from here:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
| SET NOCOUNT ON
CREATE TABLE #TBLSize
(Tblname VARCHAR(80),
TblRows INT,
TblReserved VARCHAR(80),
TblData VARCHAR(80),
TblIndex_Size VARCHAR(80),
TblUnused VARCHAR(80))
DECLARE @DBname VARCHAR(80)
DECLARE @tablename VARCHAR(80)
SELECT @DBname = DB_NAME(DB_ID())
PRINT 'User Table size Report for (Server / Database): ' + @@ServerName + ' / ' + @DBName
PRINT ''
PRINT 'By Size Descending'
DECLARE TblName_cursor CURSOR FOR
SELECT NAME
FROM sysobjects
WHERE xType = 'U'
OPEN TblName_cursor
FETCH NEXT FROM TblName_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused)
EXEC Sp_SpaceUsed @tablename
-- Get the next author.
FETCH NEXT FROM TblName_cursor
INTO @tablename
END
CLOSE TblName_cursor
DEALLOCATE TblName_cursor
SELECT CAST(Tblname AS VARCHAR(30)) 'Table',
CAST(TblRows AS VARCHAR(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) AS INT) 'Total Space (KB)',
CAST(TblData AS VARCHAR(14)) 'Data Space',
CAST(TblIndex_Size AS VARCHAR(14)) 'Index Space',
CAST(TblUnused AS VARCHAR(14)) 'Unused Space'
FROM #tblSize
ORDER BY 'Total Space (KB)' DESC
PRINT ''
PRINT 'By Table Name Alphabetical'
SELECT CAST(Tblname AS VARCHAR(30)) 'Table',
CAST(TblRows AS VARCHAR(14)) 'Row Count',
CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) AS INT) 'Total Space (KB)',
CAST(TblData AS VARCHAR(14)) 'Data Space',
CAST(TblIndex_Size AS VARCHAR(14)) 'Index Space',
CAST(TblUnused AS VARCHAR(14)) 'Unused Space'
FROM #tblSize
ORDER BY 'Table'
DROP TABLE #TblSize |
February 3rd, 2012
Testing that transactions do as they should is probably easiest done right in Sqlserver management studio. The trick is to fire up two of them.
You can set breakpoints as with your usual Visual studio and use the same shortcuts for stepping through.
Transactions are complex, do not think otherwise. Similarly to multi threaded programming it requires knowledge beforehand and just not the usual intellisense-and-see-which-methods-pops-up we have learned to use.
Playing around with transactions before implementing them is crucial for understanding and correct behaviour.
Just understanding that the transaction does what it should is probably not enough for any solution that has more than a trickle of traffic. Resources I have used a lot are Inside Sqlserver and Sqlserver Books online (BOL). The latter is the same as the help.
December 5th, 2011
This is an old item. Scope_Identity in Sqlserver doesn’t return an Int but a Decimal. So in Dapper the call is
var id = conn.Query<Decimal>(...
Honour those who should and here.
September 16th, 2011
The title says it all.
For example when one restores a database one has to disconnect all users. Here is a script for that.
USE master
GO
DECLARE @dbname varchar(30), @spid varchar(10), @start datetime
SELECT @start = current_timestamp, @dbname = 'dbname'
-- Timeout after 5 mts
while(exists(Select * FROM sysprocesses WHERE dbid = db_id(@dbname)) AND
datediff(mi, @start, current_timestamp) < 5)
begin
DECLARE spids CURSOR FOR
SELECT convert(varchar, spid) FROM sysprocesses
WHERE dbid = db_id(@dbname)
OPEN spids
while(1=1)
BEGIN
FETCH spids INTO @spid
IF @@fetch_status < 0 BREAK
exec('kill ' + @spid)
END
DEALLOCATE spids
END
GO
I recently forgot to close the connection and looped through records so the connection pool was exhausted and people couldn’t log on to the system. Typically something one finds out in a live system where many use your web at once. At least that was the case for me. Below is the code I ran over and over again while looping to make sure my updated code didn’t use too many connections in the pool.
USE MASTER
SELECT * FROM sysprocesses WHERE dbid = DB_ID('dbname')
Honor those who should.
There is also a longer version of the latter here.
May 17th, 2011
I don’t know for how long this has been possible but I guess my years of whining about lack of data scripting capabilities in Sqlserver management studio is moot.
I found an article which describes the hidden setting. I wonder for how long the possibility has been there.
http://www.kodyaz.com/articles/how-to-script-data-in-sql-server-2011.aspx
I remember Sqlbase scripting its own data back in last century. I remember myself writing sprocs for the same for Sqlserver. I remember myself hunting scripts for the same on the web. I now foresee a future with lots of clicketyclick – the GUI of MS’s Sqlserver tools is way too fiddly for my taste.
August 18th, 2010
When working with CLR inside the SQLserver database one doesn’t have to deploy the DLL through Visual Studio. Instead use the Create Assembly command.
There are several reasons for this command to fail and
CREATE ASSEMBLY failed because method 'AAA' on type 'BBB' in safe assembly 'CCC' is storing to a static field.
Storing to a static field is not allowed in safe assemblies.
is one of them.
Unless your assembly is very complex the diagnosis is easy. The AAA is a static something. If it is a property
public static Colour{ get; set; }
AAA will be
set_Colour
CCC is the assembly that refuses to install.
Find AAA. Rewrite to be not static. Compile. Run create assembly again.
June 8th, 2010
It is, at least in MSSqlserver, possible to annotate fields. Please do.
There are people coming after you to maintain the code base. One day it might be you who come after someone. Start paying it forward.
The field explanation might be in the Documentation but the Documentation is too often faulty, not accessible and almost always not up to date. The database is closer and probably easer to keep in sync with reality.
Don’t think a field name is self explanatory. The field CustomerID in table Customer might be. But the same field in Region is not. Is it the region master or the primary customer or what? By the time the field was created the meaning of this field was clear but not a year afterwards.
The reason I write this is that I presently work in a project where the habile predecessor has written down the meaning of the fields and saved me lots of time and lots more of confidence of what the figures mean and tons of debugging time.
October 18th, 2009
If one uses trusted connection to a sqlserver database under Windows 7 and II7 one might get the error message “Login failed for user ‘IIS APPPOOL\DefaultAppPool’.” when trying to run a web application. There are several articles for this but all points to a different GUI than mine (earlier versions of Windows7/IIS7?).
Update: as I lightly noted, to give the apppool SystemLocal rights is Not the right way to go.
Set up the application pool in IIS
Fire up Internet Information Services Manager. (windowbutton-iis-return)
Find the Application Pools item in the Connections toolbar. One can reuse the default application pool or create a new one. Go for the latter since it is slightly more complicated and hence more fun.
or in Win8:
(I don’t grok what Managed pipleline mode does.)
Select your newly created application pool and the advanced settings. Change the Process model to LocalSystem.
(I had planned to create a “better” account with the proper limited rights but ran out of time. Someone else…?)
Update: Don’t change to LocalSystem, instead add the user to allowed SQLServer logins as noted in the bottom here. I suggest “IIS AppPool\MySite” for now.
The recommended Microsoft solution is to create a separate account. However, if your solution is small, you can instead add the ‘IIS APPPOOL\DefaultAppPool’ user as a database user in your SQL instance, then providing the proper ‘User Mappings’ to the databases you need the DefaultAppPool user to access. You may not necessarily be able to search for this user, but you can still enter it in the ‘Login name’ field in the “Login – New” window as “IIS APPPOOL\DefaultAppPool” (without the quotes).
Follow this link as a reference and pay attention to the last post: http://social.msdn.micro…-4a71-a448-3e3eef9ee404/
Application pool settings
Getting to advanced settings.
Application pool advanced settings
Site settings
Now change your web application to use this application pool. This could be made simpler by moving the menu for this to the same place as the other menus. To make the story short – select your site and look to the right of the IIS manager.
Site advanced settings.
Site advanced setting IIS7.
Application pool.
Application pool in IIS7.
Set up Sqlserver
Honour those who should.
To add a new user to SQLServer go to your database->security and add user connected to IIS APPPOOL\DefaultAppPool. This way the user can log in to the database.
Sqlserver -> new login.
New login to database. Don’t forget to change default database to appropriate.
Then go to the SQLServer->security and add this user to be allowed to login to the server through the user and properties.
The users of a database.
That is all there is to it.