Tag Archives: mssql

MSSQL Grant Execute Stored Procedure only

Leave a reply

There are cases that we only want to grant read-write only access then also enable users to execute stored procedure with in the SQL Server database.

In order to do this we will need to:

  1. Create a new Role named, db_exec_storedprocedure

    CREATE ROLE db_exec_storedprocedure
  2. Grant that role with execute stored procedure

    GRANT EXECUTE TO db_exec_storedprocedure
  3. Then add the user as a member of that role.

That’s it you can now grant specific permission to execute stored procedure.

God Bless!
Thanks,
Thomie

Tip: MSSQL Drop all triggers

Leave a reply

Its funny that sometimes we need to drop all triggers because we inputed a logic that will prohibit a particular code. In cases like this we can drop them all and later create them again. In database with plent of tables, 100 tables, its hard if we drop them one by one. So here is a script that can help you as it just did to me.

SELECT N’DROP TRIGGER ‘ +QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N’.’ +   QUOTENAME(t.name) + N’; ‘ + NCHAR(13)

FROM sys.triggers AS t

WHERE t.is_ms_shipped = 0 AND t.parent_class_desc = N’OBJECT_OR_COLUMN’;


God Bless!

Tip: MSSQL list all SPs by its modified date

Leave a reply

In some cases we may want to get from MSSQL list all SPs by its modified date so that we know what we or our college did modified last. Here is a query you may use

SELECT name, OBJECT_DEFINITION(object_id), modify_date
FROM sys.procedures
ORDER BY modify_date DESC

This query will return the SPs from the latest modified stored procedure. Its handy specially when your migrating SPs manually.

God Bless!

MSSQL: Search all Records of a Database

Leave a reply

Hi,

There are times that we need to find something or search all records particular on an unfamiliar database in MSSQL. It is good to know how to query every item as a worst case scenario. I found this online which can help you, just like me, on looking unto a database that has no documentation.

DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_schema SYSNAME,
@column_name SYSNAME,
@sql_string VARCHAR(2000)

SET @search_string = ‘Test’

DECLARE tables_cur CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_schema, @table_name

WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @table_schema AND TABLE_NAME = @table_name AND COLLATION_NAME IS NOT NULL — Only strings have this and they always have it

OPEN columns_cur

FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = ‘IF EXISTS (SELECT * FROM ‘ + QUOTENAME(@table_schema) + ‘.’ + QUOTENAME(@table_name) + ‘ WHERE ‘ + QUOTENAME(@column_name) + ‘ LIKE ”%’ + @search_string + ‘%”) PRINT ”’ + QUOTENAME(@table_schema) + ‘.’ + QUOTENAME(@table_name) + ‘, ‘ + QUOTENAME(@column_name) + ””

EXECUTE(@sql_string)

FETCH NEXT FROM columns_cur INTO @column_name
END

CLOSE columns_cur

DEALLOCATE columns_cur

FETCH NEXT FROM tables_cur INTO @table_schema, @table_name
END

CLOSE tables_cur

DEALLOCATE tables_cur

God Bless!

Thanks,
Thomie

Signalr – OnDisconnect(bool stopCalled)

Leave a reply

Most of us who are using .Net as our Programming language use Signalr as our Real-Time Framework. And we implement OnDisconnect in most cases to catch clients that have been disconnected. Since then there were no parameter included as an Overload of the said method. Lately on the latest releases by the Signalr team they have included an Overload so that we can distinguish what triggers the disconnection and help us manage our Apps behavior to it.

With this said I have one experience that I want to share as this may also frustrate some people who are counting connected users and having a problem that when their app have multi workers (Web Garden) they app somehow always trigger OnDisconnect even if the user is still connect. So I want to share this findings on how to properly address it.

  1. Make sure that the App is using a Backplane to manage connection on your hub so that the connections are shared on all threads/servers. I use SQLServer.
  2. Make sure to use the Database on counting your list of connection and tagging who is who. Because in memory List even its static will not be shared on a different server (obviously).
  3. Make sure to trigger disconnection action when the OnDisconnect is called with the stopCalled is equal to true.

Let me highlight what is stopCalled is equal to true means.

  1. It returns true if the method on the connection to close is trigger
  2. It returns true if the browser is called
  3. It returns false if the timeout has been met

Hope this instructions will help you manage your site as it help me on our projects.

God Bless!

Thanks,
Thomie

MSSQL – Execution Plan Caching and Recompiling

Leave a reply

Hi,

Have you every experience that you have a stored procedure(SP) that have plenty of parameters that when 1 set of parameter is executed for the very first time the said SPwas created, its fast. Then after you have a new set of parameter passed to the same SP you’ll end up a long execution process?

Then that is because of a so called Execution Plan Caching that MSSQL is doing on your query. In an overview MSSQL check if there is an existing execution plan for the SP. If there is none, it will create an optimized execution plan, compile, and then cache it on memory. So when the same SP is executed with the same number of parameter it will be reused.

So in cases that you have the same number of parameter every time you use the said stored procedure and you know that you have a logic that will change the query result then this optimization may not be on our side.

When does this caching is cleared? It is only cleared when either there are no memory allocation for MSSQL or you force MSSQL to recompile the SP.

How to Recompile your specific SP? Just add OPTION (RECOMPILE) at the end of your query, before the END statement, and you are ready to go. In some cases you may want to force all cache to be removed, you need to use another statement to do that and that is DBCC FREEPROCCACHE.

That’s it. Hope it helped you as it helped me on my project.

God Bless!
Thanks,
Thomie

MSSQL – Drop all SP and Table on a Database

Leave a reply

Execute this with Precaution

USE YourDataBaseName
declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where type = ‘p’
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec(‘drop procedure [‘ + @procName+’]’)
fetch next from cur into @procName
end
close cur
deallocate cur
EXEC sp_MSforeachtable @command1 = “DROP TABLE [?]”

 

 

MSSQL – Count Number of Records on Each Table

Leave a reply

Here is the script to count all the number of records on each table on a MSSQL Server:

 

SELECT sc.name +’.’+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC

Hope it helps.

God Bless!
Thanks,
Thomie

A potentially dangerous Request.Form value was detected from the client

Leave a reply

Have you ever experience the following error in ASP .NET?

A potentially dangerous Request.Form value was detected from the client 

Then you are one like me. Then you should now that one of the values of your elements (<inputs> or <button> or <textarea>) has html elements on it. Example is bellow:

<input type=’text’ name=’content’ value='<script language=”javascript”>alert(“Hello World!”);</script>’/>

Then you should also know that this error is persisting because the values given above might be an XSS attack. If you are sure that you want to accept this type of values on your dabatase you may explicitly remove this validation by adding this tag to your page’s masterpage or 1st line of tag.

validateRequest=”false”

If you are on ASP .NET MVC you can add this to your model, assuming that your model has the content variable.

[AllowHtml]
public string content = String.Empty;

Thanks hope it help you allot. Also if you are not familiar with XSS attacks, I will try to make an article for that so you can create your own script that is hack-able, be able to prevent it, and know what are it’s crons.

God Bless!

Don’t Ever use Inline Database in MSSQL SP and Query

Leave a reply

Hi there!

I have been modifying a source code from my work and it has been a little difficult to test the system in multiple instances of database. This is due the previous developers of the said source code used Inline Query Codes and Connection Strings. In addition to that this Inline Queries and even their Stored Procedure use Inline Database, an example is [Database1].[dbo].[tbl_tableName] which should not be in practice if it is using the same database on the whole coding system.

This kind of practice limits the system to use different database name. For an instance you have a web server that serves both Development and Production Environment. You have a project that needs to have Dev and Prod on the said server. If this practice is used you are limited to one Environment at a time nevertheless you modify each script to change that inline database name. So its better to use [dbo].[tbl_tableName]  than [Database1].[dbo].[tbl_tableName] 

Thanks,

Thomie

MS SQL Generate Script with Data

2 Replies

Here is a step on how you can make MS SQL Generate Script with Data.

  1. Open your Sql Server Management Studio.
  2. Select your database and right click on it follow just like what is in the image below. Refer to image 1
  3. Click Next until you when thru to the image below and click Advance. Refer to image 2
  4. Scroll down and until this row and update the value just like what is below. Refer to image 3
  5. Click Ok and next until you have generated the said script. Refer to image 4
  6. Now you have a create script that includes the schema at it’s data.

SQL Server Report Service

5 Replies

Time to remember an old friend on creating a report on ASP .Net, the Sql Server Report Service also known as SSRS. SSRS utilizes the MS SQL Server that simplifies the creation of report. The Concept is that you separate the location where your report is located so that even if your website is down you can still provide report and use it on other web programming language by putting it in an iFrame.

Upon reviewing here are the sites that made me remember:

In addition, SSRS need SQL Business Intelligence Development Studio (BIDS) in order to create reports and deploy directly these reports to the server.