Piotr Rodak has a great article on Security – How To Assign Rights To SQL Server Database Objects and why you should not assign rights to the public role and why assigning rights to roles instead of users gives you much more flexibility. He also covers why you should only grant permissions to your stored […]
You are browsing archives for
Category: SQL Server / Microsoft SQL Server / MSSQL
SQL Server / Microsoft SQL Server / MSSQL
Searching SQL Stored Procedure Source Code in Information_Schema.Routines
— Here’s a little line of code I use a lot to find out what SQL stored procedures / functions use a particular table SELECT * FROM information_schema.routines WHERE routine_definition LIKE ‘%GetThemeColours%’ –&nnbsp;INFORMATION_SCHEMA.ROUTINES is similar to sys.routines but has all the text names instead of objectid’s with the added bonus the full stored procedure text is searchable (its in ROUTINE_DEFINITION column) – its like a super powered sp_helptext — Also check out these helpful views: — View all the columns in tables + views with full table/view name SELECT * FROM information_schema.columns — stored procedure / function parameters SELECT * FROM information_schema.parameters — output columns for table valued functions (NOT stored procedures) SELECT * FROM information_schema.routine_columns — list of tables SELECT * FROM information_schema.tables — list of views SELECT * FROM information_schema.tables — 195cac785d844883be7a61f2b54189c6
Queensland SQL Server User Group Meeting Tonight
QSSUG Meeting This Thursday – 26th of November The emerging field of "Location Intelligence" brings the worlds of Geographic Information Systems (GIS) and Business Intelligence (BI) together. These capabilities are now available with SQL Server in a no-coding, user-driven environment provided by the Integeo (www.integeo.com) Map Intelligence product. SQL Server 2008 also introduces a […]
SQL SNIPPET: Instant Print a Message from within a Batch
Following on from Leo Pasta’s lead, I’ve created two stored procedures to make printing out a message without waiting for a batch or transaction to completed. InstantPrint and InstantPrintTime
Answer: How do you create a byte with the value of 00000001 in SQL Server?
My team mate Pi asked me today: how do you create a byte with the value 00000001 in SQL Server? The answer is to create an integer representation of the byte’s value, and cast it to binary. SELECT CAST(CAST(1 AS TINYINT) AS BINARY(1)) The output in Management Studio is: 0x01 (Management Studio outputs binary fields […]
Hey Yo! SQLCMD may truncate your XML!
While trying to use SQLCMD to produce an app.config file, I encountered problems with the output being truncated. The script was similar to: DECLARE @appconfig XML SET @appconfig = ‘<configuration></configuration>’ … Insert configuration nodes — Output the config SELECT @appconfig as ApplicationConfiguration The default options for SQLCMD produced output like: ApplicationConfiguration ————————- <configuration><connecti (the value […]
How to Connect to a SQL Server Compact Edition Database with Management Studio
Open a new connection by choosing File menu -> Connect Object Explorer in SQL Server Management Studio. Change the Server Type to SQL Server Compact Edition. For Database file: put the full path to an existing SQL Server Compact Edition .sdf file; click the drop-down button, and select New Database or Browse to locate a […]
Updated SQL Server 2005 Books Online (help) Available Now (Feb 2007)
As SQL Server 2005 continues to grow and change, the Books Online / help files are also updated. Keep your help files up-to-date by downloading the latest copy. Download SQL Server 2005 Books Online Feb 2007 version.
SQL Server 2005 – Service Pack 2a (SP2a) Update
Downloaded and installed Service Pack 2 for SQL Server 2005? Well here’s a reason to download and apply it again. A few days after releasing Service Pack 2 for SQL Server 2005, Microsoft has re-released it with an additional fix for maintenance plans. If you have already applied SP2, you will need to apply this […]
Suppressing Warnings in Data Dude
I’ve been working on migrating existing SQL Server database projects into Visual Studio for Database Professional projects. Most of the existing databases have imported without major problems, but there are a few that have warnings about cross-database joins in old unit tests. TSD3025: The following cross-database dependencies could not be verified: … Your database application […]