Search Results

Search found 1830 results on 74 pages for 'joomla dbo'.

Page 30/74 | < Previous Page | 26 27 28 29 30 31 32 33 34 35 36 37  | Next Page >

• SSL and mod_rewrite error

  - by wnoveno

  Hi, I have https on my site. Pages with rewritten URL in my site are inaccessible while direct urls (folders). here's the htaccess ## 2009-12-17 16:52 JGC [START] ## Enable http compression for this site <IfModule mod_deflate.c> SetOutputFilter DEFLATE ## Directive "DeflateCompressionLevel" not allowed in .htaccess, only valid in server config and virtual hosts # DeflateCompressionLevel 9 # file-types indicated will not be compressed SetEnvIfNoCase Request_URI \.(?:gif|jpe?g|png)$ no-gzip dont-vary SetEnvIfNoCase Request_URI \.(?:swf|flv|pdf)$ no-gzip dont-vary SetEnvIfNoCase Request_URI \.(?:exe|t?gz|zip|gz2|sit|rar)$ no-gzip dont-vary <IfModule mod_headers.c> Header append Vary User-Agent </IfModule> </IfModule> ## 2009-12-17 16:52 JGC [END] ## 2010-03-05 16:05 JGC [START] #<IfModule mod_alias.c> #RedirectMatch 301 ^(/)$ /online-casino-poker-register.html #RedirectMatch 301 ^(/en)$ /en/online-casino-poker-register.html #RedirectMatch 301 ^(/en/)$ /en/online-casino-poker-register.html #RedirectMatch 301 ^(/en\.html)$ /en/online-casino-poker-register.html #RedirectMatch 301 ^(/sc)$ /sc/online-casino-poker-register.html #RedirectMatch 301 ^(/sc/)$ /sc/online-casino-poker-register.html #RedirectMatch 301 ^(/sc\.html)$ /sc/online-casino-poker-register.html #RedirectMatch 301 ^(/ch)$ /ch/online-casino-poker-register.html #RedirectMatch 301 ^(/ch/)$ /ch/online-casino-poker-register.html #RedirectMatch 301 ^(/ch\.html)$ /ch/online-casino-poker-register.html #</IfModule> ## 2010-03-05 16:05 JGC [END] ## # @version $Id: htaccess.txt 10492 2008-07-02 06:38:28Z ircmaxell $ # @package Joomla # @copyright Copyright (C) 2005 - 2008 Open Source Matters. All rights reserved. # @license http://www.gnu.org/copyleft/gpl.html GNU/GPL # Joomla! is Free Software ## ##################################################### # READ THIS COMPLETELY IF YOU CHOOSE TO USE THIS FILE # # The line just below this section: 'Options +FollowSymLinks' may cause problems # with some server configurations. It is required for use of mod_rewrite, but may already # be set by your server administrator in a way that dissallows changing it in # your .htaccess file. If using it causes your server to error out, comment it out (add # to # beginning of line), reload your site in your browser and test your sef url's. If they work, # it has been set by your server administrator and you do not need it set here. # ##################################################### ## Can be commented out if causes errors, see notes above. Options +FollowSymLinks # # mod_rewrite in use RewriteEngine On #RewriteLog "/var/www/html/dafa888/rewrite.log" #RewriteLogLevel 3 RewriteCond %{HTTP_COOKIE} !jfcookie\[lang\] [NC] RewriteCond %{HTTP:Accept-Language} ^zh-cn [NC] RewriteRule ^$ /sc/ [L,R=301] RewriteCond %{HTTP_COOKIE} !jfcookie\[lang\] [NC] RewriteCond %{HTTP:Accept-Language} ^zh-tw [NC] RewriteRule ^$ /ch/ [L,R=301] #RewriteCond %{HTTP_COOKIE} !jfcookie[lang] [NC] #RewriteCond %{HTTP_COOKIE} jfcookie\[lang\] [NC] #RewriteCond %{HTTP_COOKIE} jfcookie\[lang\]=([^;]+) [NC] #RewriteRule ^(.*)$ /%1/$1 [NC,QSA] ########## Begin - Rewrite rules to block out some common exploits ## If you experience problems on your site block out the operations listed below ## This attempts to block the most common type of exploit `attempts` to Joomla! # # Block out any script trying to set a mosConfig value through the URL RewriteCond %{QUERY_STRING} mosConfig_[a-zA-Z_]{1,21}(=|\%3D) [OR] # Block out any script trying to base64_encode crap to send via URL RewriteCond %{QUERY_STRING} base64_encode.*\(.*\) [OR] # Block out any script that includes a <script> tag in URL RewriteCond %{QUERY_STRING} (\<|%3C).*script.*(\>|%3E) [NC,OR] # Block out any script trying to set a PHP GLOBALS variable via URL RewriteCond %{QUERY_STRING} GLOBALS(=|\[|\%[0-9A-Z]{0,2}) [OR] # Block out any script trying to modify a _REQUEST variable via URL RewriteCond %{QUERY_STRING} _REQUEST(=|\[|\%[0-9A-Z]{0,2}) # Send all blocked request to homepage with 403 Forbidden error! RewriteRule ^(.*)$ index.php [F,L] # ########## End - Rewrite rules to block out some common exploits # Uncomment following line if your webserver's URL # is not directly related to physical file paths. # Update Your Joomla! Directory (just / for root) RewriteBase / #RewriteCond %{HTTP_HOST} ^(.*)$ [NC] #RewriteRule ^(.*)$ http://www.%1/$1 [R=301] ########## Begin - Joomla! core SEF Section # RewriteCond %{REQUEST_FILENAME} !-f RewriteCond %{REQUEST_FILENAME} !-d RewriteCond %{REQUEST_URI} !^/index.php RewriteCond %{REQUEST_URI} (/|\.php|\.html|\.htm|\.feed|\.pdf|\.raw|/[^.]*)$ [NC] RewriteRule (.*) index.php RewriteRule .* - [E=HTTP_AUTHORIZATION:%{HTTP:Authorization},L] # ########## End - Joomla! core SEF Section

  Read the article

• How do I keep a table up to date across 4 db's to be used in SQL Replication Filtering?

  - by Refracted Paladin

  I have a Win Form, Data Entry, application that uses 4 seperate Data Bases. This is an occasionally connected app that uses Merge Replication (SQL 2005) to stay in Sync. This is working just fine. The next hurdle I am trying to tackle is adding Filters to my Publications. Right now we are replicating 70mbs, compressed, to each of our 150 subscribers when, truthfully, they only need a tiny fraction of that. Using Filters I am able to accomplish this(see code below) but I had to make a mapping table in order to do so. This mapping table consists of 3 columns. A PrimaryID(Guid), WorkerName(varchar), and ClientID(int). The problem is I need this table present in all FOUR Databases in order to use it for the filter since, to my knowledge, views or cross-db query's are not allowed in a Filter Statement. What are my options? Seems like I would set it up to be maintained in 1 Database and then use Triggers to keep it updated in the other 3 Databases. In order to be a part of the Filter I have to include that table in the Replication Set so how do I flag it appropriately. Is there a better way, altogether? SELECT <published_columns> FROM [dbo].[tblPlan] WHERE [ClientID] IN (select ClientID from [dbo].[tblWorkerOwnership] where WorkerID = SUSER_SNAME()) Which allows you to chain together Filters, this next one is below the first one so it only pulls from the first's Filtered Set. SELECT <published_columns> FROM [dbo].[tblPlan] INNER JOIN [dbo].[tblHealthAssessmentReview] ON [tblPlan].[PlanID] = [tblHealthAssessmentReview].[PlanID] P.S. - I know how illogical the DB structure sounds. I didn't make it. I inherited it and was then told to make it a "disconnected app."

  Read the article

• How do I keep a table in sync across multiple SQL Databases?

  - by Refracted Paladin

  I have a Win Form, Data Entry, application that uses 4 seperate Data Bases. This is an occasionally connected app that uses Merge Replication (SQL 2005) to stay in Sync. This is working just fine. The next hurdle I am trying to tackle is adding Filters to my Publications. Right now we are replicating 70mbs, compressed, to each of our 150 subscribers when, truthfully, they only need a tiny fraction of that. Using Filters I am able to accomplish this(see code below) but I had to make a mapping table in order to do so. This mapping table consists of 3 columns. A PrimaryID(Guid), WorkerName(varchar), and ClientID(int). The problem is I need this table present in all FOUR Databases in order to use it for the filter since, to my knowledge, views or cross-db query's are not allowed in a Filter Statement. What are my options? Seems like I would set it up to be maintained in 1 Database and then use Triggers to keep it updated in the other 3 Databases. In order to be a part of the Filter I have to include that table in the Replication Set so how do I flag it appropriately. Is there a better way, altogether? SELECT <published_columns> FROM [dbo].[tblPlan] WHERE [ClientID] IN (select ClientID from [dbo].[tblWorkerOwnership] where WorkerID = SUSER_SNAME()) Which allows you to chain together Filters, this next one is below the first one so it only pulls from the first's Filtered Set. SELECT <published_columns> FROM [dbo].[tblPlan] INNER JOIN [dbo].[tblHealthAssessmentReview] ON [tblPlan].[PlanID] = [tblHealthAssessmentReview].[PlanID] P.S. - I know how illogical the DB structure sounds. I didn't make it. I inherited it and was then told to make it a "disconnected app." Go figure!

  Read the article

• Data from a table in 1 DB needed for filter in different DB...

  - by Refracted Paladin

  I have a Win Form, Data Entry, application that uses 4 seperate Data Bases. This is an occasionally connected app that uses Merge Replication (SQL 2005) to stay in Sync. This is working just fine. The next hurdle I am trying to tackle is adding Filters to my Publications. Right now we are replicating 70mbs, compressed, to each of our 150 subscribers when, truthfully, they only need a tiny fraction of that. Using Filters I am able to accomplish this(see code below) but I had to make a mapping table in order to do so. This mapping table consists of 3 columns. A PrimaryID(Guid), WorkerName(varchar), and ClientID(int). The problem is I need this table present in all FOUR Databases in order to use it for the filter since, to my knowledge, views or cross-db query's are not allowed in a Filter Statement. What are my options? Seems like I would set it up to be maintained in 1 Database and then use Triggers to keep it updated in the other 3 Databases. In order to be a part of the Filter I have to include that table in the Replication Set so how do I flag it appropriately. Is there a better way, altogether? SELECT <published_columns> FROM [dbo].[tblPlan] WHERE [ClientID] IN (select ClientID from [dbo].[tblWorkerOwnership] where WorkerID = SUSER_SNAME()) Which allows you to chain together Filters, this next one is below the first one so it only pulls from the first's Filtered Set. SELECT <published_columns> FROM [dbo].[tblPlan] INNER JOIN [dbo].[tblHealthAssessmentReview] ON [tblPlan].[PlanID] = [tblHealthAssessmentReview].[PlanID]

  Read the article

• How do I keep a table in Sync across 4 db's to be used in SQL Replication Filtering?

  - by Refracted Paladin

  I have a Win Form, Data Entry, application that uses 4 seperate Data Bases. This is an occasionally connected app that uses Merge Replication (SQL 2005) to stay in Sync. This is working just fine. The next hurdle I am trying to tackle is adding Filters to my Publications. Right now we are replicating 70mbs, compressed, to each of our 150 subscribers when, truthfully, they only need a tiny fraction of that. Using Filters I am able to accomplish this(see code below) but I had to make a mapping table in order to do so. This mapping table consists of 3 columns. A PrimaryID(Guid), WorkerName(varchar), and ClientID(int). The problem is I need this table present in all FOUR Databases in order to use it for the filter since, to my knowledge, views or cross-db query's are not allowed in a Filter Statement. What are my options? Seems like I would set it up to be maintained in 1 Database and then use Triggers to keep it updated in the other 3 Databases. In order to be a part of the Filter I have to include that table in the Replication Set so how do I flag it appropriately. Is there a better way, altogether? SELECT <published_columns> FROM [dbo].[tblPlan] WHERE [ClientID] IN (select ClientID from [dbo].[tblWorkerOwnership] where WorkerID = SUSER_SNAME()) Which allows you to chain together Filters, this next one is below the first one so it only pulls from the first's Filtered Set. SELECT <published_columns> FROM [dbo].[tblPlan] INNER JOIN [dbo].[tblHealthAssessmentReview] ON [tblPlan].[PlanID] = [tblHealthAssessmentReview].[PlanID] P.S. - I know how illogical the DB structure sounds. I didn't make it. I inherited it and was then told to make it a "disconnected app."

  Read the article

• SQL SERVER – Securing TRUNCATE Permissions in SQL Server

  - by pinaldave

  Download the Script of this article from here. On December 11, 2010, Vinod Kumar, a Databases & BI technology evangelist from Microsoft Corporation, graced Ahmedabad by spending some time with the Community during the Community Tech Days (CTD) event. As he was running through a few demos, Vinod asked the audience one of the most fundamental and common interview questions – “What is the difference between a DELETE and TRUNCATE?“ Ahmedabad SQL Server User Group Expert Nakul Vachhrajani has come up with excellent solutions of the same. I must congratulate Nakul for this excellent solution and as a encouragement to User Group member, I am publishing the same article over here. Nakul Vachhrajani is a Software Specialist and systems development professional with Patni Computer Systems Limited. He has functional experience spanning legacy code deprecation, system design, documentation, development, implementation, testing, maintenance and support of complex systems, providing business intelligence solutions, database administration, performance tuning, optimization, product management, release engineering, process definition and implementation. He has comprehensive grasp on Database Administration, Development and Implementation with MS SQL Server and C, C++, Visual C++/C#. He has about 6 years of total experience in information technology. Nakul is an member of the Ahmedabad and Gandhinagar SQL Server User Groups, and actively contributes to the community by actively participating in multiple forums and websites like SQLAuthority.com, BeyondRelational.com, SQLServerCentral.com and many others. Please note: The opinions expressed herein are Nakul own personal opinions and do not represent his employer’s view in anyway. All data from everywhere here on Earth go through a series of  four distinct operations, identified by the words: CREATE, READ, UPDATE and DELETE, or simply, CRUD. Putting in Microsoft SQL Server terms, is the process goes like this: INSERT, SELECT, UPDATE and DELETE/TRUNCATE. Quite a few interesting responses were received and evaluated live during the session. To summarize them, the most important similarity that came out was that both DELETE and TRUNCATE participate in transactions. The major differences (not all) that came out of the exercise were: DELETE: DELETE supports a WHERE clause DELETE removes rows from a table, row-by-row Because DELETE moves row-by-row, it acquires a row-level lock Depending upon the recovery model of the database, DELETE is a fully-logged operation. Because DELETE moves row-by-row, it can fire off triggers TRUNCATE: TRUNCATE does not support a WHERE clause TRUNCATE works by directly removing the individual data pages of a table TRUNCATE directly occupies a table-level lock. (Because a lock is acquired, and because TRUNCATE can also participate in a transaction, it has to be a logged operation) TRUNCATE is, therefore, a minimally-logged operation; again, this depends upon the recovery model of the database Triggers are not fired when TRUNCATE is used (because individual row deletions are not logged) Finally, Vinod popped the big homework question that must be critically analyzed: “We know that we can restrict a DELETE operation to a particular user, but how can we restrict the TRUNCATE operation to a particular user?” After returning home and having a nice cup of coffee, I noticed that my gray cells immediately started to work. Below was the result of my research. As what is always said, the devil is in the details. Upon looking at the Permissions section for the TRUNCATE statement in Books On Line, the following jumps right out: “The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.“ Now, what does this mean? Unlike DELETE, one cannot directly assign permissions to a user/set of users allowing or revoking TRUNCATE rights. However, there is a way to circumvent this. It is important to recall that in Microsoft SQL Server, database engine security surrounds the concept of a “securable”, which is any object like a table, stored procedure, trigger, etc. Rights are assigned to a principal on a securable. Refer to the image below (taken from the SQL Server Books On Line). urable”, which is any object like a table, stored procedure, trigger, etc. Rights are assigned to a principal on a securable. Refer to the image below (taken from the SQL Server Books On Line). SETTING UP THE ENVIRONMENT – (01A_Truncate Table Permissions.sql) Script Provided at the end of the article. By the end of this demo, one will be able to do all the CRUD operations, except the TRUNCATE, and the other will only be able to execute the TRUNCATE. All you will need for this test is any edition of SQL Server 2008. (With minor changes, these scripts can be made to work with SQL 2005.) We begin by creating the following: 1.       A test database 2.        Two database roles: associated logins and users 3.       Switch over to the test database and create a test table. Then, add some data into it. I am using row constructors, which is new to SQL 2008. Creating the modules that will be used to enforce permissions 1.       We have already created one of the modules that we will be assigning permissions to. That module is the table: TruncatePermissionsTest 2.       We will now create two stored procedures; one is for the DELETE operation and the other for the TRUNCATE operation. Please note that for all practical purposes, the end result is the same – all data from the table TruncatePermissionsTest is removed Assigning the permissions Now comes the most important part of the demonstration – assigning permissions. A permissions matrix can be worked out as under: To apply the security rights, we use the GRANT and DENY clauses, as under: That’s it! We are now ready for our big test! THE TEST (01B_Truncate Table Test Queries.sql) Script Provided at the end of the article. I will now need two separate SSMS connections, one with the login AllowedTruncate and the other with the login RestrictedTruncate. Running the test is simple; all that’s required is to run through the script – 01B_Truncate Table Test Queries.sql. What I will demonstrate here via screen-shots is the behavior of SQL Server when logged in as the AllowedTruncate user. There are a few other combinations than what are highlighted here. I will leave the reader the right to explore the behavior of the RestrictedTruncate user and these additional scenarios, as a form of self-study. 1.       Testing SELECT permissions 2.       Testing TRUNCATE permissions (Remember, “deny by default”?) 3.       Trying to circumvent security by trying to TRUNCATE the table using the stored procedure Hence, we have now proved that a user can indeed be assigned permissions to specifically assign TRUNCATE permissions. I also hope that the above has sparked curiosity towards putting some security around the probably “destructive” operations of DELETE and TRUNCATE. I would like to wish each and every one of the readers a very happy and secure time with Microsoft SQL Server. (Please find the scripts – 01A_Truncate Table Permissions.sql and 01B_Truncate Table Test Queries.sql that have been used in this demonstration. Please note that these scripts contain purely test-level code only. These scripts must not, at any cost, be used in the reader’s production environments). 01A_Truncate Table Permissions.sql /* ***************************************************************************************************************** Developed By          : Nakul Vachhrajani Functionality         : This demo is focused on how to allow only TRUNCATE permissions to a particular user How to Use            : 1. Run through, step-by-step through the sequence till Step 08 to create a test database 2. Switch over to the "Truncate Table Test Queries.sql" and execute it step-by-step in two different SSMS windows, one where you have logged in as 'RestrictedTruncate', and the other as 'AllowedTruncate' 3. Come back to "Truncate Table Permissions.sql" 4. Execute Step 10 to cleanup! Modifications         : December 13, 2010 - NAV - Updated to add a security matrix and improve code readability when applying security December 12, 2010 - NAV - Created ***************************************************************************************************************** */ -- Step 01: Create a new test database CREATE DATABASE TruncateTestDB GO USE TruncateTestDB GO -- Step 02: Add roles and users to demonstrate the security of the Truncate operation -- 2a. Create the new roles CREATE ROLE AllowedTruncateRole; GO CREATE ROLE RestrictedTruncateRole; GO -- 2b. Create new logins CREATE LOGIN AllowedTruncate WITH PASSWORD = 'truncate@2010', CHECK_POLICY = ON GO CREATE LOGIN RestrictedTruncate WITH PASSWORD = 'truncate@2010', CHECK_POLICY = ON GO -- 2c. Create new Users using the roles and logins created aboave CREATE USER TruncateUser FOR LOGIN AllowedTruncate WITH DEFAULT_SCHEMA = dbo GO CREATE USER NoTruncateUser FOR LOGIN RestrictedTruncate WITH DEFAULT_SCHEMA = dbo GO -- 2d. Add the newly created login to the newly created role sp_addrolemember 'AllowedTruncateRole','TruncateUser' GO sp_addrolemember 'RestrictedTruncateRole','NoTruncateUser' GO -- Step 03: Change over to the test database USE TruncateTestDB GO -- Step 04: Create a test table within the test databse CREATE TABLE TruncatePermissionsTest (Id INT IDENTITY(1,1), Name NVARCHAR(50)) GO -- Step 05: Populate the required data INSERT INTO TruncatePermissionsTest VALUES (N'Delhi'), (N'Mumbai'), (N'Ahmedabad') GO -- Step 06: Encapsulate the DELETE within another module CREATE PROCEDURE proc_DeleteMyTable WITH EXECUTE AS SELF AS DELETE FROM TruncateTestDB..TruncatePermissionsTest GO -- Step 07: Encapsulate the TRUNCATE within another module CREATE PROCEDURE proc_TruncateMyTable WITH EXECUTE AS SELF AS TRUNCATE TABLE TruncateTestDB..TruncatePermissionsTest GO -- Step 08: Apply Security /* *****************************SECURITY MATRIX*************************************** =================================================================================== Object                   | Permissions |                 Login |             | AllowedTruncate   |   RestrictedTruncate |             |User:NoTruncateUser|   User:TruncateUser =================================================================================== TruncatePermissionsTest  | SELECT,     |      GRANT        |      (Default) | INSERT,     |                   | | UPDATE,     |                   | | DELETE      |                   | -------------------------+-------------+-------------------+----------------------- TruncatePermissionsTest  | ALTER       |      DENY         |      (Default) -------------------------+-------------+----*/----------------+----------------------- proc_DeleteMyTable | EXECUTE | GRANT | DENY -------------------------+-------------+-------------------+----------------------- proc_TruncateMyTable | EXECUTE | DENY | GRANT -------------------------+-------------+-------------------+----------------------- *****************************SECURITY MATRIX*************************************** */ /* Table: TruncatePermissionsTest*/ GRANT SELECT, INSERT, UPDATE, DELETE ON TruncateTestDB..TruncatePermissionsTest TO NoTruncateUser GO DENY ALTER ON TruncateTestDB..TruncatePermissionsTest TO NoTruncateUser GO /* Procedure: proc_DeleteMyTable*/ GRANT EXECUTE ON TruncateTestDB..proc_DeleteMyTable TO NoTruncateUser GO DENY EXECUTE ON TruncateTestDB..proc_DeleteMyTable TO TruncateUser GO /* Procedure: proc_TruncateMyTable*/ DENY EXECUTE ON TruncateTestDB..proc_TruncateMyTable TO NoTruncateUser GO GRANT EXECUTE ON TruncateTestDB..proc_TruncateMyTable TO TruncateUser GO -- Step 09: Test --Switch over to the "Truncate Table Test Queries.sql" and execute it step-by-step in two different SSMS windows: --    1. one where you have logged in as 'RestrictedTruncate', and --    2. the other as 'AllowedTruncate' -- Step 10: Cleanup sp_droprolemember 'AllowedTruncateRole','TruncateUser' GO sp_droprolemember 'RestrictedTruncateRole','NoTruncateUser' GO DROP USER TruncateUser GO DROP USER NoTruncateUser GO DROP LOGIN AllowedTruncate GO DROP LOGIN RestrictedTruncate GO DROP ROLE AllowedTruncateRole GO DROP ROLE RestrictedTruncateRole GO USE MASTER GO DROP DATABASE TruncateTestDB GO 01B_Truncate Table Test Queries.sql /* ***************************************************************************************************************** Developed By          : Nakul Vachhrajani Functionality         : This demo is focused on how to allow only TRUNCATE permissions to a particular user How to Use            : 1. Switch over to this from "Truncate Table Permissions.sql", Step #09 2. Execute this step-by-step in two different SSMS windows a. One where you have logged in as 'RestrictedTruncate', and b. The other as 'AllowedTruncate' 3. Return back to "Truncate Table Permissions.sql" 4. Execute Step 10 to cleanup! Modifications         : December 12, 2010 - NAV - Created ***************************************************************************************************************** */ -- Step 09A: Switch to the test database USE TruncateTestDB GO -- Step 09B: Ensure that we have valid data SELECT * FROM TruncatePermissionsTest GO -- (Expected: Following error will occur if logged in as "AllowedTruncate") -- Msg 229, Level 14, State 5, Line 1 -- The SELECT permission was denied on the object 'TruncatePermissionsTest', database 'TruncateTestDB', schema 'dbo'. --Step 09C: Attempt to Truncate Data from the table without using the stored procedure TRUNCATE TABLE TruncatePermissionsTest GO -- (Expected: Following error will occur) --  Msg 1088, Level 16, State 7, Line 2 --  Cannot find the object "TruncatePermissionsTest" because it does not exist or you do not have permissions. -- Step 09D:Regenerate Test Data INSERT INTO TruncatePermissionsTest VALUES (N'London'), (N'Paris'), (N'Berlin') GO -- (Expected: Following error will occur if logged in as "AllowedTruncate") -- Msg 229, Level 14, State 5, Line 1 -- The INSERT permission was denied on the object 'TruncatePermissionsTest', database 'TruncateTestDB', schema 'dbo'. --Step 09E: Attempt to Truncate Data from the table using the stored procedure EXEC proc_TruncateMyTable GO -- (Expected: Will execute successfully with 'AllowedTruncate' user, will error out as under with 'RestrictedTruncate') -- Msg 229, Level 14, State 5, Procedure proc_TruncateMyTable, Line 1 -- The EXECUTE permission was denied on the object 'proc_TruncateMyTable', database 'TruncateTestDB', schema 'dbo'. -- Step 09F:Regenerate Test Data INSERT INTO TruncatePermissionsTest VALUES (N'Madrid'), (N'Rome'), (N'Athens') GO --Step 09G: Attempt to Delete Data from the table without using the stored procedure DELETE FROM TruncatePermissionsTest GO -- (Expected: Following error will occur if logged in as "AllowedTruncate") -- Msg 229, Level 14, State 5, Line 2 -- The DELETE permission was denied on the object 'TruncatePermissionsTest', database 'TruncateTestDB', schema 'dbo'. -- Step 09H:Regenerate Test Data INSERT INTO TruncatePermissionsTest VALUES (N'Spain'), (N'Italy'), (N'Greece') GO --Step 09I: Attempt to Delete Data from the table using the stored procedure EXEC proc_DeleteMyTable GO -- (Expected: Following error will occur if logged in as "AllowedTruncate") -- Msg 229, Level 14, State 5, Procedure proc_DeleteMyTable, Line 1 -- The EXECUTE permission was denied on the object 'proc_DeleteMyTable', database 'TruncateTestDB', schema 'dbo'. --Step 09J: Close this SSMS window and return back to "Truncate Table Permissions.sql" Thank you Nakul to take up the challenge and prove that Ahmedabad and Gandhinagar SQL Server User Group has talent to solve difficult problems. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Best Practices, Pinal Dave, Readers Contribution, Readers Question, SQL, SQL Authority, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• Stored Procedures with SSRS? Hmm… not so much

  - by Rob Farley

  Little Bobby Tables’ mother says you should always sanitise your data input. Except that I think she’s wrong. The SQL Injection aspect is for another post, where I’ll show you why I think SQL Injection is the same kind of attack as many other attacks, such as the old buffer overflow, but here I want to have a bit of a whinge about the way that some people sanitise data input, and even have a whinge about people who insist on using stored procedures for SSRS reports. Let me say that again, in case you missed it the first time: I want to have a whinge about people who insist on using stored procedures for SSRS reports. Let’s look at the data input sanitisation aspect – except that I’m going to call it ‘parameter validation’. I’m talking about code that looks like this: create procedure dbo.GetMonthSummaryPerSalesPerson(@eomdate datetime) as begin     /* First check that @eomdate is a valid date */     if isdate(@eomdate) != 1     begin         select 'Please enter a valid date' as ErrorMessage;         return;     end     /* Then check that time has passed since @eomdate */     if datediff(day,@eomdate,sysdatetime()) < 5     begin         select 'Sorry - EOM is not complete yet' as ErrorMessage;         return;     end         /* If those checks have succeeded, return the data */     select SalesPersonID, count(*) as NumSales, sum(TotalDue) as TotalSales     from Sales.SalesOrderHeader     where OrderDate >= dateadd(month,-1,@eomdate)         and OrderDate < @eomdate     group by SalesPersonID     order by SalesPersonID; end Notice that the code checks that a date has been entered. Seriously??!! This must only be to check for NULL values being passed in, because anything else would have to be a valid datetime to avoid an error. The other check is maybe fair enough, but I still don’t like it. The two problems I have with this stored procedure are the result sets and the small fact that the stored procedure even exists in the first place. But let’s consider the first one of these problems for starters. I’ll get to the second one in a moment. If you read Jes Borland (@grrl_geek)’s recent post about returning multiple result sets in Reporting Services, you’ll be aware that Reporting Services doesn’t support multiple results sets from a single query. And when it says ‘single query’, it includes ‘stored procedure call’. It’ll only handle the first result set that comes back. But that’s okay – we have RETURN statements, so our stored procedure will only ever return a single result set.  Sometimes that result set might contain a single field called ErrorMessage, but it’s still only one result set. Except that it’s not okay, because Reporting Services needs to know what fields to expect. Your report needs to hook into your fields, so SSRS needs to have a way to get that information. For stored procs, it uses an option called FMTONLY. When Reporting Services tries to figure out what fields are going to be returned by a query (or stored procedure call), it doesn’t want to have to run the whole thing. That could take ages. (Maybe it’s seen some of the stored procedures I’ve had to deal with over the years!) So it turns on FMTONLY before it makes the call (and turns it off again afterwards). FMTONLY is designed to be able to figure out the shape of the output, without actually running the contents. It’s very useful, you might think. set fmtonly on exec dbo.GetMonthSummaryPerSalesPerson '20030401'; set fmtonly off Without the FMTONLY lines, this stored procedure returns a result set that has three columns and fourteen rows. But with FMTONLY turned on, those rows don’t come back. But what I do get back hurts Reporting Services. It doesn’t run the stored procedure at all. It just looks for anything that could be returned and pushes out a result set in that shape. Despite the fact that I’ve made sure that the logic will only ever return a single result set, the FMTONLY option kills me by returning three of them. It would have been much better to push these checks down into the query itself. alter procedure dbo.GetMonthSummaryPerSalesPerson(@eomdate datetime) as begin     select SalesPersonID, count(*) as NumSales, sum(TotalDue) as TotalSales     from Sales.SalesOrderHeader     where     /* Make sure that @eomdate is valid */         isdate(@eomdate) = 1     /* And that it's sufficiently past */     and datediff(day,@eomdate,sysdatetime()) >= 5     /* And now use it in the filter as appropriate */     and OrderDate >= dateadd(month,-1,@eomdate)     and OrderDate < @eomdate     group by SalesPersonID     order by SalesPersonID; end Now if we run it with FMTONLY turned on, we get the single result set back. But let’s consider the execution plan when we pass in an invalid date. First let’s look at one that returns data. I’ve got a semi-useful index in place on OrderDate, which includes the SalesPersonID and TotalDue fields. It does the job, despite a hefty Sort operation. …compared to one that uses a future date: You might notice that the estimated costs are similar – the Index Seek is still 28%, the Sort is still 71%. But the size of that arrow coming out of the Index Seek is a whole bunch smaller. The coolest thing here is what’s going on with that Index Seek. Let’s look at some of the properties of it. Glance down it with me… Estimated CPU cost of 0.0005728, 387 estimated rows, estimated subtree cost of 0.0044385, ForceSeek false, Number of Executions 0. That’s right – it doesn’t run. So much for reading plans right-to-left... The key is the Filter on the left of it. It has a Startup Expression Predicate in it, which means that it doesn’t call anything further down the plan (to the right) if the predicate evaluates to false. Using this method, we can make sure that our stored procedure contains a single query, and therefore avoid any problems with multiple result sets. If we wanted, we could always use UNION ALL to make sure that we can return an appropriate error message. alter procedure dbo.GetMonthSummaryPerSalesPerson(@eomdate datetime) as begin     select SalesPersonID, count(*) as NumSales, sum(TotalDue) as TotalSales, /*Placeholder: */ '' as ErrorMessage     from Sales.SalesOrderHeader     where     /* Make sure that @eomdate is valid */         isdate(@eomdate) = 1     /* And that it's sufficiently past */     and datediff(day,@eomdate,sysdatetime()) >= 5     /* And now use it in the filter as appropriate */     and OrderDate >= dateadd(month,-1,@eomdate)     and OrderDate < @eomdate     group by SalesPersonID     /* Now include the error messages */     union all     select 0, 0, 0, 'Please enter a valid date' as ErrorMessage     where isdate(@eomdate) != 1     union all     select 0, 0, 0, 'Sorry - EOM is not complete yet' as ErrorMessage     where datediff(day,@eomdate,sysdatetime()) < 5     order by SalesPersonID; end But still I don’t like it, because it’s now a stored procedure with a single query. And I don’t like stored procedures that should be functions. That’s right – I think this should be a function, and SSRS should call the function. And I apologise to those of you who are now planning a bonfire for me. Guy Fawkes’ night has already passed this year, so I think you miss out. (And I’m not going to remind you about when the PASS Summit is in 2012.) create function dbo.GetMonthSummaryPerSalesPerson(@eomdate datetime) returns table as return (     select SalesPersonID, count(*) as NumSales, sum(TotalDue) as TotalSales, '' as ErrorMessage     from Sales.SalesOrderHeader     where     /* Make sure that @eomdate is valid */         isdate(@eomdate) = 1     /* And that it's sufficiently past */     and datediff(day,@eomdate,sysdatetime()) >= 5     /* And now use it in the filter as appropriate */     and OrderDate >= dateadd(month,-1,@eomdate)     and OrderDate < @eomdate     group by SalesPersonID     union all     select 0, 0, 0, 'Please enter a valid date' as ErrorMessage     where isdate(@eomdate) != 1     union all     select 0, 0, 0, 'Sorry - EOM is not complete yet' as ErrorMessage     where datediff(day,@eomdate,sysdatetime()) < 5 ); We’ve had to lose the ORDER BY – but that’s fine, as that’s a client thing anyway. We can have our reports leverage this stored query still, but we’re recognising that it’s a query, not a procedure. A procedure is designed to DO stuff, not just return data. We even get entries in sys.columns that confirm what the shape of the result set actually is, which makes sense, because a table-valued function is the right mechanism to return data. And we get so much more flexibility with this. If you haven’t seen the simplification stuff that I’ve preached on before, jump over to http://bit.ly/SimpleRob and watch the video of when I broke a microphone and nearly fell off the stage in Wales. You’ll see the impact of being able to have a simplifiable query. You can also read the procedural functions post I wrote recently, if you didn’t follow the link from a few paragraphs ago. So if we want the list of SalesPeople that made any kind of sales in a given month, we can do something like: select SalesPersonID from dbo.GetMonthSummaryPerSalesPerson(@eomonth) order by SalesPersonID; This doesn’t need to look up the TotalDue field, which makes a simpler plan. select * from dbo.GetMonthSummaryPerSalesPerson(@eomonth) where SalesPersonID is not null order by SalesPersonID; This one can avoid having to do the work on the rows that don’t have a SalesPersonID value, pushing the predicate into the Index Seek rather than filtering the results that come back to the report. If we had joins involved, we might see some of those being simplified out. We also get the ability to include query hints in individual reports. We shift from having a single-use stored procedure to having a reusable stored query – and isn’t that one of the main points of modularisation? Stored procedures in Reporting Services are just a bit limited for my liking. They’re useful in plenty of ways, but if you insist on using stored procedures all the time rather that queries that use functions – that’s rubbish. @rob_farley

  Read the article

• SQL Server &ndash; Undelete a Table and Restore a Single Table from Backup

  - by Mladen Prajdic

  This post is part of the monthly community event called T-SQL Tuesday started by Adam Machanic (blog|twitter) and hosted by someone else each month. This month the host is Sankar Reddy (blog|twitter) and the topic is Misconceptions in SQL Server. You can follow posts for this theme on Twitter by looking at #TSQL2sDay hashtag. Let me start by saying: This code is a crazy hack that is to never be used unless you really, really have to. Really! And I don’t think there’s a time when you would really have to use it for real. Because it’s a hack there are number of things that can go wrong so play with it knowing that. I’ve managed to totally corrupt one database. :) Oh… and for those saying: yeah yeah.. you have a single table in a file group and you’re restoring that, I say “nay nay” to you. As we all know SQL Server can’t do single table restores from backup. This is kind of a obvious thing due to different relational integrity (RI) concerns. Since we have to maintain that we have to restore all tables represented in a RI graph. For this exercise i say BAH! to those concerns. Note that this method “works” only for simple tables that don’t have LOB and off rows data. The code can be expanded to include those but I’ve tried to leave things “simple”. Note that for this to work our table needs to be relatively static data-wise. This doesn’t work for OLTP table. Products are a perfect example of static data. They don’t change much between backups, pretty much everything depends on them and their table is one of those tables that are relatively easy to accidentally delete everything from. This only works if the database is in Full or Bulk-Logged recovery mode for tables where the contents have been deleted or truncated but NOT when a table was dropped. Everything we’ll talk about has to be done before the data pages are reused for other purposes. After deletion or truncation the pages are marked as reusable so you have to act fast. The best thing probably is to put the database into single user mode ASAP while you’re performing this procedure and return it to multi user after you’re done. How do we do it? We will be using an undocumented but known DBCC commands: DBCC PAGE, an undocumented function sys.fn_dblog and a little known DATABASE RESTORE PAGE option. All tests will be on a copy of Production.Product table in AdventureWorks database called Production.Product1 because the original table has FK constraints that prevent us from truncating it for testing. -- create a duplicate table. This doesn't preserve indexes!SELECT *INTO AdventureWorks.Production.Product1FROM AdventureWorks.Production.Product   After we run this code take a full back to perform further testing.   First let’s see what the difference between DELETE and TRUNCATE is when it comes to logging. With DELETE every row deletion is logged in the transaction log. With TRUNCATE only whole data page deallocations are logged in the transaction log. Getting deleted data pages is simple. All we have to look for is row delete entry in the sys.fn_dblog output. But getting data pages that were truncated from the transaction log presents a bit of an interesting problem. I will not go into depths of IAM(Index Allocation Map) and PFS (Page Free Space) pages but suffice to say that every IAM page has intervals that tell us which data pages are allocated for a table and which aren’t. If we deep dive into the sys.fn_dblog output we can see that once you truncate a table all the pages in all the intervals are deallocated and this is shown in the PFS page transaction log entry as deallocation of pages. For every 8 pages in the same extent there is one PFS page row in the transaction log. This row holds information about all 8 pages in CSV format which means we can get to this data with some parsing. A great help for parsing this stuff is Peter Debetta’s handy function dbo.HexStrToVarBin that converts hexadecimal string into a varbinary value that can be easily converted to integer tus giving us a readable page number. The shortened (columns removed) sys.fn_dblog output for a PFS page with CSV data for 1 extent (8 data pages) looks like this: -- [Page ID] is displayed in hex format. -- To convert it to readable int we'll use dbo.HexStrToVarBin function found at -- http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-convert-hex-string-to-varbinary.aspx -- This function must be installed in the master databaseSELECT Context, AllocUnitName, [Page ID], DescriptionFROM sys.fn_dblog(NULL, NULL)WHERE [Current LSN] = '00000031:00000a46:007d' The pages at the end marked with 0x00—> are pages that are allocated in the extent but are not part of a table. We can inspect the raw content of each data page with a DBCC PAGE command: -- we need this trace flag to redirect output to the query window.DBCC TRACEON (3604); -- WITH TABLERESULTS gives us data in table format instead of message format-- we use format option 3 because it's the easiest to read and manipulate further onDBCC PAGE (AdventureWorks, 1, 613, 3) WITH TABLERESULTS   Since the DBACC PAGE output can be quite extensive I won’t put it here. You can see an example of it in the link at the beginning of this section. Getting deleted data back When we run a delete statement every row to be deleted is marked as a ghost record. A background process periodically cleans up those rows. A huge misconception is that the data is actually removed. It’s not. Only the pointers to the rows are removed while the data itself is still on the data page. We just can’t access it with normal means. To get those pointers back we need to restore every deleted page using the RESTORE PAGE option mentioned above. This restore must be done from a full backup, followed by any differential and log backups that you may have. This is necessary to bring the pages up to the same point in time as the rest of the data.  However the restore doesn’t magically connect the restored page back to the original table. It simply replaces the current page with the one from the backup. After the restore we use the DBCC PAGE to read data directly from all data pages and insert that data into a temporary table. To finish the RESTORE PAGE  procedure we finally have to take a tail log backup (simple backup of the transaction log) and restore it back. We can now insert data from the temporary table to our original table by hand. Getting truncated data back When we run a truncate the truncated data pages aren’t touched at all. Even the pointers to rows stay unchanged. Because of this getting data back from truncated table is simple. we just have to find out which pages belonged to our table and use DBCC PAGE to read data off of them. No restore is necessary. Turns out that the problems we had with finding the data pages is alleviated by not having to do a RESTORE PAGE procedure. Stop stalling… show me The Code! This is the code for getting back deleted and truncated data back. It’s commented in all the right places so don’t be afraid to take a closer look. Make sure you have a full backup before trying this out. Also I suggest that the last step of backing and restoring the tail log is performed by hand. USE masterGOIF OBJECT_ID('dbo.HexStrToVarBin') IS NULL RAISERROR ('No dbo.HexStrToVarBin installed. Go to http://sqlblog.com/blogs/peter_debetta/archive/2007/03/09/t-sql-convert-hex-string-to-varbinary.aspx and install it in master database' , 18, 1) SET NOCOUNT ONBEGIN TRY DECLARE @dbName VARCHAR(1000), @schemaName VARCHAR(1000), @tableName VARCHAR(1000), @fullBackupName VARCHAR(1000), @undeletedTableName VARCHAR(1000), @sql VARCHAR(MAX), @tableWasTruncated bit; /* THE FIRST LINE ARE OUR INPUT PARAMETERS In this case we're trying to recover Production.Product1 table in AdventureWorks database. My full backup of AdventureWorks database is at e:\AW.bak */ SELECT @dbName = 'AdventureWorks', @schemaName = 'Production', @tableName = 'Product1', @fullBackupName = 'e:\AW.bak', @undeletedTableName = '##' + @tableName + '_Undeleted', @tableWasTruncated = 0, -- copy the structure from original table to a temp table that we'll fill with restored data @sql = 'IF OBJECT_ID(''tempdb..' + @undeletedTableName + ''') IS NOT NULL DROP TABLE ' + @undeletedTableName + ' SELECT *' + ' INTO ' + @undeletedTableName + ' FROM [' + @dbName + '].[' + @schemaName + '].[' + @tableName + ']' + ' WHERE 1 = 0' EXEC (@sql) IF OBJECT_ID('tempdb..#PagesToRestore') IS NOT NULL DROP TABLE #PagesToRestore /* FIND DATA PAGES WE NEED TO RESTORE*/ CREATE TABLE #PagesToRestore ([ID] INT IDENTITY(1,1), [FileID] INT, [PageID] INT, [SQLtoExec] VARCHAR(1000)) -- DBCC PACE statement to run later RAISERROR ('Looking for deleted pages...', 10, 1) -- use T-LOG direct read to get deleted data pages INSERT INTO #PagesToRestore([FileID], [PageID], [SQLtoExec]) EXEC('USE [' + @dbName + '];SELECT FileID, PageID, ''DBCC TRACEON (3604); DBCC PAGE ([' + @dbName + '], '' + FileID + '', '' + PageID + '', 3) WITH TABLERESULTS'' as SQLToExecFROM (SELECT DISTINCT LEFT([Page ID], 4) AS FileID, CONVERT(VARCHAR(100), ' + 'CONVERT(INT, master.dbo.HexStrToVarBin(SUBSTRING([Page ID], 6, 20)))) AS PageIDFROM sys.fn_dblog(NULL, NULL)WHERE AllocUnitName LIKE ''%' + @schemaName + '.' + @tableName + '%'' ' + 'AND Context IN (''LCX_MARK_AS_GHOST'', ''LCX_HEAP'') AND Operation in (''LOP_DELETE_ROWS''))t');SELECT *FROM #PagesToRestore -- if upper EXEC returns 0 rows it means the table was truncated so find truncated pages IF (SELECT COUNT(*) FROM #PagesToRestore) = 0 BEGIN RAISERROR ('No deleted pages found. Looking for truncated pages...', 10, 1) -- use T-LOG read to get truncated data pages INSERT INTO #PagesToRestore([FileID], [PageID], [SQLtoExec]) -- dark magic happens here -- because truncation simply deallocates pages we have to find out which pages were deallocated. -- we can find this out by looking at the PFS page row's Description column. -- for every deallocated extent the Description has a CSV of 8 pages in that extent. -- then it's just a matter of parsing it. -- we also remove the pages in the extent that weren't allocated to the table itself -- marked with '0x00-->00' EXEC ('USE [' + @dbName + '];DECLARE @truncatedPages TABLE(DeallocatedPages VARCHAR(8000), IsMultipleDeallocs BIT);INSERT INTO @truncatedPagesSELECT REPLACE(REPLACE(Description, ''Deallocated '', ''Y''), ''0x00-->00 '', ''N'') + '';'' AS DeallocatedPages, CHARINDEX('';'', Description) AS IsMultipleDeallocsFROM (SELECT DISTINCT LEFT([Page ID], 4) AS FileID, CONVERT(VARCHAR(100), CONVERT(INT, master.dbo.HexStrToVarBin(SUBSTRING([Page ID], 6, 20)))) AS PageID, DescriptionFROM sys.fn_dblog(NULL, NULL)WHERE Context IN (''LCX_PFS'') AND Description LIKE ''Deallocated%'' AND AllocUnitName LIKE ''%' + @schemaName + '.' + @tableName + '%'') t;SELECT FileID, PageID , ''DBCC TRACEON (3604); DBCC PAGE ([' + @dbName + '], '' + FileID + '', '' + PageID + '', 3) WITH TABLERESULTS'' as SQLToExecFROM (SELECT LEFT(PageAndFile, 1) as WasPageAllocatedToTable , SUBSTRING(PageAndFile, 2, CHARINDEX('':'', PageAndFile) - 2 ) as FileID , CONVERT(VARCHAR(100), CONVERT(INT, master.dbo.HexStrToVarBin(SUBSTRING(PageAndFile, CHARINDEX('':'', PageAndFile) + 1, LEN(PageAndFile))))) as PageIDFROM ( SELECT SUBSTRING(DeallocatedPages, delimPosStart, delimPosEnd - delimPosStart) as PageAndFile, IsMultipleDeallocs FROM ( SELECT *, CHARINDEX('';'', DeallocatedPages)*(N-1) + 1 AS delimPosStart, CHARINDEX('';'', DeallocatedPages)*N AS delimPosEnd FROM @truncatedPages t1 CROSS APPLY (SELECT TOP (case when t1.IsMultipleDeallocs = 1 then 8 else 1 end) ROW_NUMBER() OVER(ORDER BY number) as N FROM master..spt_values) t2 )t)t)tWHERE WasPageAllocatedToTable = ''Y''') SELECT @tableWasTruncated = 1 END DECLARE @lastID INT, @pagesCount INT SELECT @lastID = 1, @pagesCount = COUNT(*) FROM #PagesToRestore SELECT @sql = 'Number of pages to restore: ' + CONVERT(VARCHAR(10), @pagesCount) IF @pagesCount = 0 RAISERROR ('No data pages to restore.', 18, 1) ELSE RAISERROR (@sql, 10, 1) -- If the table was truncated we'll read the data directly from data pages without restoring from backup IF @tableWasTruncated = 0 BEGIN -- RESTORE DATA PAGES FROM FULL BACKUP IN BATCHES OF 200 WHILE @lastID <= @pagesCount BEGIN -- create CSV string of pages to restore SELECT @sql = STUFF((SELECT ',' + CONVERT(VARCHAR(100), FileID) + ':' + CONVERT(VARCHAR(100), PageID) FROM #PagesToRestore WHERE ID BETWEEN @lastID AND @lastID + 200 ORDER BY ID FOR XML PATH('')), 1, 1, '') SELECT @sql = 'RESTORE DATABASE [' + @dbName + '] PAGE = ''' + @sql + ''' FROM DISK = ''' + @fullBackupName + '''' RAISERROR ('Starting RESTORE command:' , 10, 1) WITH NOWAIT; RAISERROR (@sql , 10, 1) WITH NOWAIT; EXEC(@sql); RAISERROR ('Restore DONE' , 10, 1) WITH NOWAIT; SELECT @lastID = @lastID + 200 END /* If you have any differential or transaction log backups you should restore them here to bring the previously restored data pages up to date */ END DECLARE @dbccSinglePage TABLE ( [ParentObject] NVARCHAR(500), [Object] NVARCHAR(500), [Field] NVARCHAR(500), [VALUE] NVARCHAR(MAX) ) DECLARE @cols NVARCHAR(MAX), @paramDefinition NVARCHAR(500), @SQLtoExec VARCHAR(1000), @FileID VARCHAR(100), @PageID VARCHAR(100), @i INT = 1 -- Get deleted table columns from information_schema view -- Need sp_executeSQL because database name can't be passed in as variable SELECT @cols = 'select @cols = STUFF((SELECT '', ['' + COLUMN_NAME + '']''FROM ' + @dbName + '.INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = ''' + @tableName + ''' AND TABLE_SCHEMA = ''' + @schemaName + '''ORDER BY ORDINAL_POSITIONFOR XML PATH('''')), 1, 2, '''')', @paramDefinition = N'@cols nvarchar(max) OUTPUT' EXECUTE sp_executesql @cols, @paramDefinition, @cols = @cols OUTPUT -- Loop through all the restored data pages, -- read data from them and insert them into temp table -- which you can then insert into the orignial deleted table DECLARE dbccPageCursor CURSOR GLOBAL FORWARD_ONLY FOR SELECT [FileID], [PageID], [SQLtoExec] FROM #PagesToRestore ORDER BY [FileID], [PageID] OPEN dbccPageCursor; FETCH NEXT FROM dbccPageCursor INTO @FileID, @PageID, @SQLtoExec; WHILE @@FETCH_STATUS = 0 BEGIN RAISERROR ('---------------------------------------------', 10, 1) WITH NOWAIT; SELECT @sql = 'Loop iteration: ' + CONVERT(VARCHAR(10), @i); RAISERROR (@sql, 10, 1) WITH NOWAIT; SELECT @sql = 'Running: ' + @SQLtoExec RAISERROR (@sql, 10, 1) WITH NOWAIT; -- if something goes wrong with DBCC execution or data gathering, skip it but print error BEGIN TRY INSERT INTO @dbccSinglePage EXEC (@SQLtoExec) -- make the data insert magic happen here IF (SELECT CONVERT(BIGINT, [VALUE]) FROM @dbccSinglePage WHERE [Field] LIKE '%Metadata: ObjectId%') = OBJECT_ID('['+@dbName+'].['+@schemaName +'].['+@tableName+']') BEGIN DELETE @dbccSinglePage WHERE NOT ([ParentObject] LIKE 'Slot % Offset %' AND [Object] LIKE 'Slot % Column %') SELECT @sql = 'USE tempdb; ' + 'IF (OBJECTPROPERTY(object_id(''' + @undeletedTableName + '''), ''TableHasIdentity'') = 1) ' + 'SET IDENTITY_INSERT ' + @undeletedTableName + ' ON; ' + 'INSERT INTO ' + @undeletedTableName + '(' + @cols + ') ' + STUFF((SELECT ' UNION ALL SELECT ' + STUFF((SELECT ', ' + CASE WHEN VALUE = '[NULL]' THEN 'NULL' ELSE '''' + [VALUE] + '''' END FROM ( -- the unicorn help here to correctly set ordinal numbers of columns in a data page -- it's turning STRING order into INT order (1,10,11,2,21 into 1,2,..10,11...21) SELECT [ParentObject], [Object], Field, VALUE, RIGHT('00000' + O1, 6) AS ParentObjectOrder, RIGHT('00000' + REVERSE(LEFT(O2, CHARINDEX(' ', O2)-1)), 6) AS ObjectOrder FROM ( SELECT [ParentObject], [Object], Field, VALUE, REPLACE(LEFT([ParentObject], CHARINDEX('Offset', [ParentObject])-1), 'Slot ', '') AS O1, REVERSE(LEFT([Object], CHARINDEX('Offset ', [Object])-2)) AS O2 FROM @dbccSinglePage WHERE t.ParentObject = ParentObject )t)t ORDER BY ParentObjectOrder, ObjectOrder FOR XML PATH('')), 1, 2, '') FROM @dbccSinglePage t GROUP BY ParentObject FOR XML PATH('') ), 1, 11, '') + ';' RAISERROR (@sql, 10, 1) WITH NOWAIT; EXEC (@sql) END END TRY BEGIN CATCH SELECT @sql = 'ERROR!!!' + CHAR(10) + CHAR(13) + 'ErrorNumber: ' + ERROR_NUMBER() + '; ErrorMessage' + ERROR_MESSAGE() + CHAR(10) + CHAR(13) + 'FileID: ' + @FileID + '; PageID: ' + @PageID RAISERROR (@sql, 10, 1) WITH NOWAIT; END CATCH DELETE @dbccSinglePage SELECT @sql = 'Pages left to process: ' + CONVERT(VARCHAR(10), @pagesCount - @i) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + CHAR(13), @i = @i+1 RAISERROR (@sql, 10, 1) WITH NOWAIT; FETCH NEXT FROM dbccPageCursor INTO @FileID, @PageID, @SQLtoExec; END CLOSE dbccPageCursor; DEALLOCATE dbccPageCursor; EXEC ('SELECT ''' + @undeletedTableName + ''' as TableName; SELECT * FROM ' + @undeletedTableName)END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage IF CURSOR_STATUS ('global', 'dbccPageCursor') >= 0 BEGIN CLOSE dbccPageCursor; DEALLOCATE dbccPageCursor; ENDEND CATCH-- if the table was deleted we need to finish the restore page sequenceIF @tableWasTruncated = 0BEGIN -- take a log tail backup and then restore it to complete page restore process DECLARE @currentDate VARCHAR(30) SELECT @currentDate = CONVERT(VARCHAR(30), GETDATE(), 112) RAISERROR ('Starting Log Tail backup to c:\Temp ...', 10, 1) WITH NOWAIT; PRINT ('BACKUP LOG [' + @dbName + '] TO DISK = ''c:\Temp\' + @dbName + '_TailLogBackup_' + @currentDate + '.trn''') EXEC ('BACKUP LOG [' + @dbName + '] TO DISK = ''c:\Temp\' + @dbName + '_TailLogBackup_' + @currentDate + '.trn''') RAISERROR ('Log Tail backup done.', 10, 1) WITH NOWAIT; RAISERROR ('Starting Log Tail restore from c:\Temp ...', 10, 1) WITH NOWAIT; PRINT ('RESTORE LOG [' + @dbName + '] FROM DISK = ''c:\Temp\' + @dbName + '_TailLogBackup_' + @currentDate + '.trn''') EXEC ('RESTORE LOG [' + @dbName + '] FROM DISK = ''c:\Temp\' + @dbName + '_TailLogBackup_' + @currentDate + '.trn''') RAISERROR ('Log Tail restore done.', 10, 1) WITH NOWAIT;END-- The last step is manual. Insert data from our temporary table to the original deleted table The misconception here is that you can do a single table restore properly in SQL Server. You can't. But with little experimentation you can get pretty close to it. One way to possible remove a dependency on a backup to retrieve deleted pages is to quickly run a similar script to the upper one that gets data directly from data pages while the rows are still marked as ghost records. It could be done if we could beat the ghost record cleanup task.

  Read the article

• Usage of ||, OR in PHP

  - by shin

  I have the following code which redirect pages depends on $path. ... $path = $this->uri->segment(3); $pathid = $this->uri->segment(4); if($path=='forsiden'){ redirect('','refresh'); }elseif($path =='contact'){ redirect('welcome/kontakt','refresh'); }elseif($path =='illustration'){ $this->_gallery($path,$pathid); }elseif($path =='webdesign'){ redirect('welcome/webdesign','refresh'); }elseif($path==('web_tjenester' || 'webdesigndetails' || 'vismahjemmeside' || 'joomla' || 'vismanettbutikk' || 'vpasp' || 'artportfolio')){ ... CODE A ... }else{ ... CODE B ... } I am not getting right results with $path==('web_tjenester' || 'webdesigndetails' || 'vismahjemmeside' || 'joomla' || 'vismanettbutikk' || 'vpasp' || 'artportfolio') contact, illustration, gallery and webdesign are redirected and working alright. However all other pages are added CODE A. I am expecting CODE A only when $path is web_tjenester', 'webdesigndetails', 'vismahjemmeside', 'joomla', 'vismanettbutikk', 'vpasp' or 'artportfolio'. Could anyone point out my mistake and correct me please? Thanks in advance. --UPDATE-- The following works, but is there any ways shorten the code? I am repeating ($path==..). elseif(($path=='web_tjenester') || ($path=='webdesigndetails') || ($path=='vismahjemmeside') || ($path=='joomla') || ($path=='vismanettbutikk') || ($path=='vpasp') || ($path=='artportfolio')){

  Read the article

• Different ways to query this search in SQL?

  - by Bart Terrell

  I am teaching myself MS-SQL and I am trying to find different ways to find the Count of Paid and Unpaid Claims for 2012 grouped by Region from these 3 tables. If there is a returned date, the claim is unpaid if the returned date is null then the claim is paid. I will attach the code I have ran, but I am not sure if there are better ways to do it. Thanks. Here is the code: SET dateformat ymd; CREATE TABLE Claims ( ClaimID INT, SubID INT, [Claim Date] DATETIME ); CREATE TABLE Phoneship ( ClaimID INT, [Shipping Number] INT, [Claim Date] DATETIME, [Ship Date] DATETIME, [Returned Date] DATETIME ); CREATE TABLE Enrollment ( SubID INT, Enrollment_Date DATETIME, Channel NVARCHAR(255), Region NVARCHAR(255), Status FLOAT, Drop_Date DATETIME ); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (102, 201, '2011-10-13 00:00:00', '2011-10-14 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (103, 202, '2011-11-02 00:00:00', '2011-11-03 00:00:00', '2011-11-20 00:00:00'); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (103, 203, '2011-11-02 00:00:00', '2011-11-22 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (105, 204, '2012-01-16 00:00:00', '2012-01-17 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (106, 205, '2012-02-15 00:00:00', '2012-02-16 00:00:00', '2012-02-26 00:00:00'); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (106, 206, '2012-02-15 00:00:00', '2012-02-27 00:00:00', '2012-03-06 00:00:00'); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (107, 207, '2012-03-12 00:00:00', '2012-03-13 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (108, 208, '2012-05-11 00:00:00', '2012-05-12 00:00:00', NULL); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (109, 209, '2012-05-13 00:00:00', '2012-05-14 00:00:00', '2012-05-28 00:00:00'); INSERT INTO [Phoneship] ([ClaimID], [Shipping Number], [Claim Date], [Ship Date], [Returned Date]) VALUES (109, 210, '2012-05-13 00:00:00', '2012-05-30 00:00:00', NULL); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (101, 12345678, '2011-03-06 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (102, 12347190, '2011-10-13 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (103, 12348723, '2011-11-02 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (104, 12349745, '2011-11-09 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (105, 12347190, '2012-01-16 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (106, 12349234, '2012-02-15 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (107, 12350767, '2012-03-12 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (108, 12350256, '2012-05-11 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (109, 12347701, '2012-05-13 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (110, 12350256, '2012-05-15 00:00:00'); INSERT INTO [Claims] ([ClaimID], [SubID], [Claim Date]) VALUES (111, 12350767, '2012-06-30 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12345678, '2011-01-05 00:00:00', 'Retail', 'Southeast', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12346178, '2011-03-13 00:00:00', 'Indirect Dealers', 'West', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12346679, '2011-05-19 00:00:00', 'Indirect Dealers', 'Southeast', 0, '2012-03-15 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12347190, '2011-07-25 00:00:00', 'Retail', 'Northeast', 0, '2012-05-21 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12347701, '2011-08-14 00:00:00', 'Indirect Dealers', 'West', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12348212, '2011-09-30 00:00:00', 'Retail', 'West', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12348723, '2011-10-20 00:00:00', 'Retail', 'Southeast', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12349234, '2012-01-06 00:00:00', 'Indirect Dealers', 'West', 0, '2012-02-14 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12349745, '2012-01-26 00:00:00', 'Retail', 'Northeast', 0, '2012-04-15 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12350256, '2012-02-11 00:00:00', 'Retail', 'Southeast', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12350767, '2012-03-02 00:00:00', 'Indirect Dealers', 'West', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12351278, '2012-04-18 00:00:00', 'Retail', 'Midwest', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12351789, '2012-05-08 00:00:00', 'Indirect Dealers', 'West', 0, '2012-07-04 00:00:00'); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12352300, '2012-06-24 00:00:00', 'Retail', 'Midwest', 1, NULL); INSERT INTO [Enrollment] ([SubID], [Enrollment_Date], [Channel], [Region], [Status], [Drop_Date]) VALUES (12352811, '2012-06-25 00:00:00', 'Retail', 'Southeast', 1, NULL); And Query1 SELECT Count(ClaimID) AS 'Paid Claim', (SELECT Count(ClaimID) FROM dbo.phoneship WHERE [returned date] IS NOT NULL) AS 'Unpaid Claim' FROM dbo.Phoneship WHERE [Returned Date] IS NULL GROUP BY claimid Query2 SELECT Count(*) AS 'Paid Claims', (SELECT Count(*) FROM dbo.Phoneship WHERE [Returned Date] IS NOT NULL) AS 'Unpaid Claims' FROM dbo.Phoneship WHERE [Returned Date] IS NULL; Query3 Select Distinct(C.[Shipping Number]), Count(C.ClaimID) AS 'COUNT ClaimID', A.Region, A.SubID From dbo.HSEnrollment A Inner Join dbo.Claims B On A.SubId = B.SubId Inner Join dbo.Phoneship C On B.ClaimID = C.ClaimID Where C.[Returned Date] IS NULL Group By A.Region, A.Subid, C.ClaimID, C.[Shipping Number] Order By A.Region

  Read the article

• Deleted one membership table. Possible to import without breaking relationship?

  - by superexsl

  Hey, I hope this isn't going to be tricky/time consuming, so fingers crossed. I'm working with the ASP.NET membership table. However, I've got quite a few other tables that I've built, and most of them have a relationship with the dbo.aspnet_Membership table. I've accidentally deleted the dbo.aspnet_Membership table and can't get it back. There was no major data on it, (as it's on my local machine), but I would really like to copy and paste that one table from another database I have, mainly for the sake of not breaking the schema. Is this possible? I'm worried if I run the Aspnet_regsql.exe tool, it's going to break the schema and remove all data from the tables as well the relationships (which would take a while to re-establish). Is there any way I can import just the dbo.aspnet_Membership table into my current database? Thanks for the any advice!

  Read the article

• Entity Framework many-to-many using VB.Net Lambda

  - by bgs264

  Hello, I'm a newbie to StackOverflow so please be kind ;) I'm using Entity Framework in Visual Studio 2010 Beta 2 (.NET framework 4.0 Beta 2). I have created an entity framework .edmx model from my database and I have a handful of many-to-many relationships. A trivial example of my database schema is Roles (ID, Name, Active) Members (ID, DateOfBirth, DateCreated) RoleMembership(RoleID, MemberID) I am now writing the custom role provider (Inheriting System.Configuration.Provider.RoleProvider) and have come to write the implementation of IsUserInRole(username, roleName). The LINQ-to-Entity queries which I wrote, when SQL-Profiled, all produced CROSS JOIN statements when what I want is for them to INNER JOIN. Dim query = From m In dc.Members From r In dc.Roles Where m.ID = 100 And r.Name = "Member" Select m My problem is almost exactly described here: http://stackoverflow.com/questions/553918/entity-framework-and-many-to-many-queries-unusable I'm sure that the solution presented there works well, but whilst I studied Java at uni and I can mostly understand C# I cannot understand this Lambda syntax provided and I need to get a similar example in VB. I've looked around the web for the best part of half a day but I'm not closer to my answer. So please can somebody advise how, in VB, I can construct a LINQ statement which would do this equivalent in SQL: SELECT rm.RoleID FROM RoleMembership rm INNER JOIN Roles r ON r.ID = rm.RoleID INNER JOIN Members m ON m.ID = rm.MemberID WHERE r.Name = 'Member' AND m.ID = 101 I would use this query to see if Member 101 is in Role 3. (I appreciate I probably don't need the join to the Members table in SQL but I imagine in LINQ I'd need to bring in the Member object?) UPDATE: I'm a bit closer by using multiple methods: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim count As Integer Using dc As New CBLModel.CBLEntities Dim persons = dc.Members.Where(AddressOf myTest) count = persons.Count End Using System.Diagnostics.Debugger.Break() End Sub Function myTest(ByVal m As Member) As Boolean Return m.ID = "100" AndAlso m.Roles.Select(AddressOf myRoleTest).Count > 0 End Function Function myRoleTest(ByVal r As Role) As Boolean Return r.Name = "Member" End Function SQL Profiler shows this: SQL:BatchStarting SELECT [Extent1].[ID] AS [ID], ... (all columns from Members snipped for brevity) ... FROM [dbo].[Members] AS [Extent1] RPC:Completed exec sp_executesql N'SELECT [Extent2].[ID] AS [ID], [Extent2].[Name] AS [Name], [Extent2].[Active] AS [Active] FROM [dbo].[RoleMembership] AS [Extent1] INNER JOIN [dbo].[Roles] AS [Extent2] ON [Extent1].[RoleID] = [Extent2].[ID] WHERE [Extent1].[MemberID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=100 SQL:BatchCompleted SELECT [Extent1].[ID] AS [ID], ... (all columns from Members snipped for brevity) ... FROM [dbo].[Members] AS [Extent1] I'm not certain why it is using sp_execsql for the inner join statement and why it's still running a select to select ALL members though. Thanks. UPDATE 2 I've written it by turning the above "multiple methods" into lambda expressions then all into one query, like this: Dim allIDs As String = String.Empty Using dc As New CBLModel.CBLEntities For Each retM In dc.Members.Where(Function(m As Member) m.ID = 100 AndAlso m.Roles.Select(Function(r As Role) r.Name = "Doctor").Count > 0) allIDs &= retM.ID.ToString & ";" Next End Using But it doesn't seem to work: "Doctor" is not a role that exists, I just put it in there for testing purposes, yet "allIDs" still gets set to "100;" The SQL in SQL Profiler this time looks like this: SELECT [Project1].* FROM ( SELECT [Extent1].*, (SELECT COUNT(1) AS [A1] FROM [dbo].[RoleMembership] AS [Extent2] WHERE [Extent1].[ID] = [Extent2].[MemberID]) AS [C1] FROM [dbo].[Members] AS [Extent1] ) AS [Project1] WHERE (100 = [Project1].[ID]) AND ([Project1].[C1] > 0) For brevity I turned the list of all the columns from the Members table into * As you can see it's just ignoring the "Role" query... :/

  Read the article

• openDatabase Hello World - 2

  - by cf_PhillipSenn

  This is a continuation from a previous stackoverflow question. I've renamed some variables so that I can tell what are keywords and what are names that I can control. Q: Why is the deleteRow function not working? <html> <head> <title>html5 openDatabase Hello World</title> <script src="http://www.google.com/jsapi"></script> <script type="text/javascript"> google.load("jquery", "1"); google.setOnLoadCallback(OnLoadCallback); function OnLoadCallback() { var dbo; dbo = openDatabase('HelloWorld'); dbo.transaction( function(T1) { T1.executeSql( 'CREATE TABLE IF NOT EXISTS myTable ' + ' (myTableID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, ' + ' Field1 TEXT NOT NULL );' ); } ); dbo.transaction(function(T2) { T2.executeSql('SELECT * FROM myTable',[], function (T6, result) { for (var i=0; i < result.rows.length; i++) { var row = result.rows.item(i); $('#savedData').append('<li id="'+row.myTableID+'">' + row.Field1 + '</li>'); } }, errorHandler); }); $('form').submit(function() { var xxx = $('#xxx').val(); dbo.transaction( function(T3) { T3.executeSql( 'INSERT INTO myTable (Field1) VALUES (?);', [xxx], function(){ $('#savedData').append('<li id="ThisisWhereIneedHELP">' + xxx + '</li>'); $('#xxx').val(''); }, errorHandler ); } ); return false; }); $('#savedData > li').live('click', function (){ deleteRow(this.id); $(this).remove(); }); } function deleteRow(myTableID) { alert('trying to delete'); dbo.transaction(function(T4) { T4.executeSql('DELETE FROM myTable WHERE myTableID = ?', [myTableID], function(){ alert('Deleted!'); }, errorHandler); }); } function errorHandler(T5, error) { alert('Oops. Error was '+error.message+' (Code '+error.code+')'); // T5.executeSql('INSERT INTO errors (code, message) VALUES (?, ?);', // [error.code, error.message]); return false; } </script> </head> <body> <form method="post"> <input name="xxx" id="xxx" /> <p> <input type="submit" name="OK" /> </p> <ul id="savedData"> </ul> </form> </body> </html>

  Read the article

• SQL SERVER – Create Primary Key with Specific Name when Creating Table

  - by pinaldave

  It is interesting how sometimes the documentation of simple concepts is not available online. I had received email from one of the reader where he has asked how to create Primary key with a specific name when creating the table itself. He said, he knows the method where he can create the table and then apply the primary key with specific name. The attached code was as follows: CREATE TABLE [dbo].[TestTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](100) NULL) GO ALTER TABLE [dbo].[TestTable] ADD  CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED ([ID] ASC) GO He wanted to know if we can create Primary Key as part of the table name as well, and also give it a name at the same time. Though it would look very normal to all experienced developers, it can be still confusing to many. Here is the quick code that functions as the above code in one single statement. CREATE TABLE [dbo].[TestTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](100) NULL CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED ([ID] ASC) ) GO Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, Readers Question, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• Denali Paging–Key seek lookups

  - by Dave Ballantyne

  In my previous post “Denali Paging – is it win.win ?” I demonstrated the use of using the Paging functionality within Denali.  On reflection,  I think i may of been a little unfair and should of continued always planned to continue my investigations to the next step. In Pauls article, he uses a combination of ctes to first scan the ordered keys which is then filtered using TOP and rownumber and then uses those keys to seek the data.  So what happens if we replace the scanning portion of the code with the denali paging functionality. Heres the original procedure,  we are going to replace the functionality of the Keys and SelectedKeys ctes : CREATE  PROCEDURE dbo.FetchPageKeySeek         @PageSize   BIGINT,         @PageNumber BIGINT AS BEGIN         -- Key-Seek algorithm         WITH    Keys         AS      (                 -- Step 1 : Number the rows from the non-clustered index                 -- Maximum number of rows = @PageNumber * @PageSize                 SELECT  TOP (@PageNumber * @PageSize)                         rn = ROW_NUMBER() OVER (ORDER BY P1.post_id ASC),                         P1.post_id                 FROM    dbo.Post P1                 ORDER   BY                         P1.post_id ASC                 ),                 SelectedKeys         AS      (                 -- Step 2 : Get the primary keys for the rows on the page we want                 -- Maximum number of rows from this stage = @PageSize                 SELECT  TOP (@PageSize)                         SK.rn,                         SK.post_id                 FROM    Keys SK                 WHERE   SK.rn > ((@PageNumber - 1) * @PageSize)                 ORDER   BY                         SK.post_id ASC                 )         SELECT  -- Step 3 : Retrieve the off-index data                 -- We will only have @PageSize rows by this stage                 SK.rn,                 P2.post_id,                 P2.thread_id,                 P2.member_id,                 P2.create_dt,                 P2.title,                 P2.body         FROM    SelectedKeys SK         JOIN    dbo.Post P2                 ON  P2.post_id = SK.post_id         ORDER   BY                 SK.post_id ASC; END; and here is the replacement procedure using paging: CREATE  PROCEDURE dbo.FetchOffsetPageKeySeek         @PageSize   BIGINT,         @PageNumber BIGINT AS BEGIN         -- Key-Seek algorithm         WITH    SelectedKeys         AS      (                 SELECT  post_id                 FROM    dbo.Post P1                 ORDER   BY post_id ASC                 OFFSET  @PageSize * (@PageNumber-1) ROWS                 FETCH NEXT @PageSize ROWS ONLY                 )         SELECT  P2.post_id,                 P2.thread_id,                 P2.member_id,                 P2.create_dt,                 P2.title,                 P2.body         FROM    SelectedKeys SK         JOIN    dbo.Post P2                 ON  P2.post_id = SK.post_id         ORDER   BY                 SK.post_id ASC; END; Notice how all i have done is replace the functionality with the Keys and SelectedKeys CTEs with the paging functionality. So , what is the comparative performance now ?. Exactly the same amount of IO and memory usage , but its now pretty obvious that in terms of CPU and overall duration we are onto a winner.    

  Read the article

• SQL SERVER – DMV sys.dm_exec_describe_first_result_set_for_object – Describes the First Result Metadata for the Module

  - by pinaldave

  Here is another interesting follow up blog post of SQL SERVER – sp_describe_first_result_set New System Stored Procedure in SQL Server 2012. While I was writing earlier blog post I had come across DMV sys.dm_exec_describe_first_result_set_for_object as well. I found that SQL Server 2012 is providing all this quick and new features which quite often we miss  to learn it and when in future someone demonstrates the same to us, we express our surprise on the subject. DMV sys.dm_exec_describe_first_result_set_for_object returns result set which describes the columns used in the stored procedure. Here is the quick example. Let us first create stored procedure. USE [AdventureWorks] GO ALTER PROCEDURE [dbo].[CompSP] AS SELECT [DepartmentID] id ,[Name] n ,[GroupName] gn FROM [HumanResources].[Department] GO Now let us run following two DMV which gives us meta data description of the stored procedure passed as a parameter. Option1: Pass second parameter @include_browse_information as a 0. SELECT * FROM sys.dm_exec_describe_first_result_set_for_object ( OBJECT_ID('[dbo].[CompSP]'),0) AS Table1 GO Option2: Pass second parameter @include_browse_information as a 1. SELECT * FROM sys.dm_exec_describe_first_result_set_for_object ( OBJECT_ID('[dbo].[CompSP]'),1) AS Table1 GO Here is the result of Option1 and Option2. If you see the result, there is absolutely no difference between the results. Both of the resultset are returning column names which are aliased in the stored procedure. Let us scroll on the right side and you will notice that there is clear difference in some columns. You will see in second resultset source_database, Source_schema as well few other columns are reporting original table instead of NULL values. When @include_browse_information result is set to 1 it will provide the columns details of the underlying table. I have just discovered this DMV, I have yet to use it in production code and find out where exactly I will use this DMV. Do you have any idea? Does any thing comes up to your mind where this DMV can be helpful. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL DMV, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• Tale of an Encrypted SSIS Package in msdb and a Lost Password

  - by Argenis

    Yesterday a Developer at work asked for a copy of an SSIS package in Production so he could work on it (please, dear Reader – withhold judgment on Source Control – I know!). I logged on to the SSIS instance, and when I went to export the package… Oops. I didn’t have that password. The DBA who uploaded the package to Production is long gone; my fellow DBA had no idea either - and the Devs returned a cricket sound when queried. So I posed the obligatory question on #SQLHelp and a bunch of folks jumped in – some to help and some to make fun of me (thanks, @SQLSoldier @crummel4 @maryarcia and @sqljoe). I tried their suggestions to no avail…even ran some queries to see if I could figure out how to extract the package XML from the system tables in msdb:   SELECT CAST(CAST(p.packagedata AS varbinary(max)) AS varchar(max)) FROM msdb.dbo.sysssispackages p WHERE p.name = 'LePackage'   This just returned a bunch of XML with encrypted data on it:  I knew there was a job in SQL Agent scheduled to execute the package, and when I tried to look at details on the job step I got the following: Not very helpful. The password had to be saved somewhere, but where?? All of a sudden I remembered that there was a system table I hadn’t queried yet: SELECT sjs.command FROM msdb.dbo.sysjobs sj JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id WHERE sj.name = 'Run LePackage' The result: “Well, that’s really secure”, I thought to myself. Cheers, -Argenis

  Read the article

• Create MSDB Folders Through Code

  You can create package folders through SSMS, but you may also wish to do this as part of a deployment process or installation. In this case you will want programmatic method for managing folders, so how can this be done? The short answer is, go and look at the table msdb.dbo. sysdtspackagefolders90. This where folder information is stored, using a simple parent and child hierarchy format. To add new folder directly we just insert into the table - INSERT INTO dbo.sysdtspackagefolders90 ( folderid ,parentfolderid ,foldername) VALUES ( NEWID() -- New GUID for our new folder ,<<Parent Folder GUID>> -- Lookup the parent folder GUID if a child or another folder, or use the root GUID 00000000-0000-0000-0000-000000000000 ,<<Folder Name>>) -- New folder name There are also some stored procedures - sp_dts_addfolder sp_dts_deletefolder sp_dts_getfolder sp_dts_listfolders sp_dts_renamefolder To add a new folder to the root we could call the sp_dts_addfolder to stored procedure - EXEC msdb.dbo.sp_dts_addfolder @parentfolderid = '00000000-0000-0000-0000-000000000000' -- Root GUID ,@name = 'New Folder Name The stored procedures wrap very simple SQL statements, but provide a level of security, as they check the role membership of the user, and do not require permissions to perform direct table modifications.

  Read the article

• Is there any way to send a column value from outer query to inner sub query? [closed]

  - by chetan

  'Discussions' table schema title description desid replyto upvote downvote views browser used a1 none 1 1 12 - bad topic b2 a1 2 3 14 sql database a3 none 4 5 34 - crome b4 a3 3 4 12 The above table has two types of content types Main Topics and Comments. Unique content identifier 'desid' used to identify that its a main topic or a comment. 'desid' starts with 'a' for Main Topic and for comment 'desid' starts with 'b'. For comment 'replyto' is the 'desid' of main topic to which this comment is associated. I like to find out the list of the top main topics that are arranged on the basis of (upvote+downvote+visits+number of comments to it) addition. The following query gives top topics list in order of (upvote+downvote+visits) select * with highest number of upvote+downvote+views by query "select * from [DB_user1212].[dbo].[discussions] where desid like 'a%' order by (upvote+downvote+visited) desc For (comments+upvote+downvote+views ) I tried select * from [DB_user1212].[dbo].[discussions] where desid like 'a%' order by ((select count(*) from [DB_user1212].[dbo].[discussions] where replyto = desid )+upvote+downvote+visited) desc but it didn't work because its not possible to send desid from outer query to inner subquery. How to solve this? Please note that I want solution in query language only.

  Read the article

• Query for server DefaultData & DefaultLog folders

  - by jamiet

  Do you ever need to query for the DefaultData & DefaultLog folders for your SQL Server instance? Well, I just did and the following script enabled me to do that: DECLARE @HkeyLocal NVARCHAR(18),@MSSqlServerRegPath NVARCHAR(31),@InstanceRegPath SYSNAME; SELECT @HkeyLocal=N'HKEY_LOCAL_MACHINE' SELECT @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer' SELECT @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer' DECLARE @SmoDefaultFile NVARCHAR(512) EXEC MASTER.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultData', @SmoDefaultFile OUTPUT DECLARE @SmoDefaultLog NVARCHAR(512) EXEC MASTER.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultLog', @SmoDefaultLog OUTPUT SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile],ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]' I haven’t done any rigorous testing or anything like that, all I can say is…it worked for me (on SQL Server 2012). Use as you see fit. Doubtless this information exists in a multitude of other places but nevertheless I’m putting it here so I know where to find it in the future. Just for fun I thought I’d try this out against SQL Azure Windows Azure SQL Database. Unsurprisingly it didn’t work there: Msg 40515, Level 15, State 1, Line 16 Reference to database and/or server name in 'MASTER.dbo.xp_instance_regread' is not supported in this version of SQL Server. @Jamiet

  Read the article

• SQL Server 2008: FileStream Insertion Failure w/ .NET 3.5SP1

  - by James Alexander

  I've configured a db w/ a FileStream group and have a table w/ File type on it. When attempting to insert a streamed file and after I create the table row, my query to read the filepath out and the buffer returns a null file path. I can't seem to figure out why though. Here is the table creation script: /****** Object: Table [dbo].[JobInstanceFile] Script Date: 03/22/2010 18:05:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[JobInstanceFile]( [JobInstanceFileId] [int] IDENTITY(1,1) NOT NULL, [JobInstanceId] [int] NOT NULL, [File] [varbinary](max) FILESTREAM NULL, [FileId] [uniqueidentifier] ROWGUIDCOL NOT NULL, [Created] [datetime] NOT NULL, CONSTRAINT [PK_JobInstanceFile] PRIMARY KEY CLUSTERED ( [JobInstanceFileId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [JobInstanceFilesGroup], UNIQUE NONCLUSTERED ( [FileId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] FILESTREAM_ON [JobInstanceFilesGroup] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[JobInstanceFile] ADD DEFAULT (newid()) FOR [FileId] GO Here's my proc I call to create the row before streaming the file: /****** Object: StoredProcedure [dbo].[JobInstanceFileCreate] Script Date: 03/22/2010 18:06:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create proc [dbo].[JobInstanceFileCreate] @JobInstanceId int, @Created datetime as insert into JobInstanceFile (JobInstanceId, FileId, Created) values (@JobInstanceId, newid(), @Created) select scope_identity() GO And lastly, here's the code I'm using: public int CreateJobInstanceFile(int jobInstanceId, string filePath) { using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConsumerMarketingStoreFiles"].ConnectionString)) using (var fileStream = new FileStream(filePath, FileMode.Open)) { connection.Open(); var tran = connection.BeginTransaction(IsolationLevel.ReadCommitted); try { //create the JobInstanceFile instance var command = new SqlCommand("JobInstanceFileCreate", connection) { Transaction = tran }; command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@JobInstanceId", jobInstanceId); command.Parameters.AddWithValue("@Created", DateTime.Now); int jobInstanceFileId = Convert.ToInt32(command.ExecuteScalar()); //read out the filestream transaction context to stream the file for storage command.CommandText = "select [File].PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() from JobInstanceFile where JobInstanceFileId = @JobInstanceFileId"; command.CommandType = CommandType.Text; command.Parameters.AddWithValue("@JobInstanceFileId", jobInstanceFileId); using (SqlDataReader dr = command.ExecuteReader()) { dr.Read(); //get the file path we're writing out to string writePath = dr.GetString(0); using (var writeStream = new SqlFileStream(writePath, (byte[])dr.GetValue(1), FileAccess.ReadWrite)) { //copy from one stream to another byte[] bytes = new byte[65536]; int numBytes; while ((numBytes = fileStream.Read(bytes, 0, 65536)) 0) writeStream.Write(bytes, 0, numBytes); } } tran.Commit(); return jobInstanceFileId; } catch (Exception e) { tran.Rollback(); throw e; } } } Can someone please let me know what I'm doing wrong. In the code, the following expression is returning null for the file path and shouldn't be: //get the file path we're writing out to string writePath = dr.GetString(0); The server is different then the computer the code is running on but the necessary shares appear to be in order and I have also run the following: EXEC sp_configure filestream_access_level, 2 Any help would be greatly appreciated. Thanks!

  Read the article

• Invisible .htaccess Redirect from /public_html/ to /public_html/folder

  - by dosboy

  I need to point the root domain of my hosting account to a subdirectory (joomla). I want this to be invisible (i.e. browser address bar doesn't change). Also, I need this to work when a user hits the root or a subfile/subfolder. I've tried the following rules, which work individually, but I can't get them to work together. This one works when no subfile/subfolder is specified: RewriteEngine On RewriteRule ^$ /joomla/ [L] And this one works when a subfile/subfolder IS specified: RewriteEngine On RewriteCond %{REQUEST_FILENAME} !-f RewriteRule (.+)$ /joomla/$1 [L] I just can't figure out how to combine them.

  Read the article

• If Then Statement Condition Being Ignored With Optimisations On

  - by Matma

  I think im going mad but can some show me what im missing, it must be some stupidly simple i just cant see the wood for the trees. BOTH side of this if then else statement are being executed? Ive tried commenting out the true side and moving the condition to a seperate variable with the same result. However if i explicitly set the condition to 1=0 or 1=1 then the if then statement is operating as i would expect. i.e. only executing one side of the equation... The only time ive seen this sort of thing is when the compiler has crashed and is no longer compiling (without visible indication that its not) but ive restarted studio with the same results, ive cleaned the solution, built and rebuilt with no change? please show me the stupid mistake im making using vs2005 if it matters. Dim dset As DataSet = New DataSet If (CboCustomers.SelectedValue IsNot Nothing) AndAlso (CboCustomers.SelectedValue <> "") Then Dim Sql As String = "Select sal.SalesOrderNo As SalesOrder,cus.CustomerName,has.SerialNo, convert(varchar,sal.Dateofpurchase,103) as Date from [dbo].[Customer_Table] as cus " & _ " inner join [dbo].[Hasp_table] as has on has.CustomerID=cus.CustomerTag " & _ " inner join [dbo].[salesorder_table] as sal On sal.Hasp_ID =has.Hasp_ID Where cus.CustomerTag = '" & CboCustomers.SelectedValue.ToString & "'" Dim dap As SqlDataAdapter = New SqlDataAdapter(Sql, FormConnection) dap.Fill(dset, "dbo.Customer_Table") DGCustomer.DataSource = dset.Tables("dbo.Customer_Table") Else Dim erm As String = "wtf" End If EDIT: i have found that this is something to do with the release config settings im using, i guesing its the optimisations bit. does anyone know of any utils/addons for vs that show if a line has been optimised out. delphi, my former language showed blue dots in the left margin to show that it was a compiled line, no dot meaning it wasnt compiled in, is there anything like that for vs? alternatively can someone explain how optimisations would affect this simple if statement causeing it to run both sides? EDIT2: using this thread as possible causes/solutions : http://stackoverflow.com/questions/2135509/bug-only-occurring-when-compile-optimization-enabled. It does the same with release = optimisations on, x86, x64 and AnyCPU Goes away with optimisations off. Im using V2005 on a x64 win7 machine, if that matters. Thanks

  Read the article

• SQL SERVER – A Puzzle Part 4 – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value

  - by pinaldave

  It seems like every weekend I get a new puzzle in my mind. Before continuing I suggest you read my previous posts here where I have shared earlier puzzles. A Puzzle – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value  A Puzzle Part 2 – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value A Puzzle Part 3 – Fun with SEQUENCE in SQL Server 2012 – Guess the Next Value After reading above three posts, I am very confident that you all will be ready for the next set of puzzles now. First execute the script which I have written here. Now guess what will be the next value as requested in the query. USE TempDB GO -- Create sequence CREATE SEQUENCE dbo.SequenceID AS DECIMAL(3,0) START WITH 1 INCREMENT BY -1 MINVALUE 1 MAXVALUE 3 CYCLE NO CACHE; GO SELECT next value FOR dbo.SequenceID; -- Guess the number SELECT next value FOR dbo.SequenceID; -- Clean up DROP SEQUENCE dbo.SequenceID; GO Please note that Starting value is 1, Increment value is the negative value of -1 and Minimum value is 3. Now let us first assume how this will work out. In our example of the sequence starting value is equal to 1 and decrement value is -1, this means the value should decrement from 1 to 0. However, the minimum value is 1. This means the value cannot further decrement at all. What will happen here? The natural assumption is that it should throw an error. How many of you are assuming about query will throw an ERROR? Well, you are WRONG! Do not blame yourself, it is my fault as I have told you only half of the story. Now if you have voted for error, let us continue running above code in SQL Server Management Studio. The above script will give the following output: Isn’t it interesting that instead of error out it is giving us result value 3. To understand the answer about the same, carefully observe the original syntax of creating SEQUENCE – there is a keyword CYCLE. This keyword cycles the values between the minimum and maximum value and when one of the range is exhausted it cycles the values from the other end of the cycle. As we have negative incremental value when query reaches to the minimum value or lower end it will cycle it from the maximum value. Here the maximum value is 3 so the next logical value is 3. If your business requirement is such that if sequence reaches the maximum or minimum value, it should throw an error, you should not use the keyword cycle, and it will behave as discussed. I hope, you are enjoying the puzzles as much as I am enjoying it. If you have any interesting puzzle to share, please do share with me and I will share this on blog with due credit to you. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology

  Read the article

• Indexed view deadlocking

  - by Dave Ballantyne

  Deadlocks can be a really tricky thing to track down the root cause of.  There are lots of articles on the subject of tracking down deadlocks, but seldom do I find that in a production system that the cause is as straightforward.  That being said,  deadlocks are always caused by process A needs a resource that process B has locked and process B has a resource that process A needs.  There may be a longer chain of processes involved, but that is the basic premise. Here is one such (much simplified) scenario that was at first non-obvious to its cause: The system has two tables,  Products and Stock.  The Products table holds the description and prices of a product whilst Stock records the current stock level. USE tempdb GO CREATE TABLE Product ( ProductID INTEGER IDENTITY PRIMARY KEY, ProductName VARCHAR(255) NOT NULL, Price MONEY NOT NULL ) GO CREATE TABLE Stock ( ProductId INTEGER PRIMARY KEY, StockLevel INTEGER NOT NULL ) GO INSERT INTO Product SELECT TOP(1000) CAST(NEWID() AS VARCHAR(255)), ABS(CAST(CAST(NEWID() AS VARBINARY(255)) AS INTEGER))%100 FROM sys.columns a CROSS JOIN sys.columns b GO INSERT INTO Stock SELECT ProductID,ABS(CAST(CAST(NEWID() AS VARBINARY(255)) AS INTEGER))%100 FROM Product There is a single stored procedure of GetStock: Create Procedure GetStock as SELECT Product.ProductID,Product.ProductName FROM dbo.Product join dbo.Stock on Stock.ProductId = Product.ProductID where Stock.StockLevel <> 0 Analysis of the system showed that this procedure was causing a performance overhead and as reads of this data was many times more than writes,  an indexed view was created to lower the overhead. CREATE VIEW vwActiveStock With schemabinding AS SELECT Product.ProductID,Product.ProductName FROM dbo.Product join dbo.Stock on Stock.ProductId = Product.ProductID where Stock.StockLevel <> 0 go CREATE UNIQUE CLUSTERED INDEX PKvwActiveStock on vwActiveStock(ProductID) This worked perfectly, performance was improved, the team name was cheered to the rafters and beers all round.  Then, after a while, something else happened… The system updating the data changed,  The update pattern of both the Stock update and the Product update used to be: BEGIN TRAN UPDATE... COMMIT BEGIN TRAN UPDATE... COMMIT BEGIN TRAN UPDATE... COMMIT It changed to: BEGIN TRAN UPDATE... UPDATE... UPDATE... COMMIT Nothing that would raise an eyebrow in even the closest of code reviews.  But after this change we saw deadlocks occuring. You can reproduce this by opening two sessions. In session 1 begin transaction Update Product set ProductName ='Test' where ProductID = 998 Then in session 2 begin transaction Update Stock set Stocklevel = 5 where ProductID = 999 Update Stock set Stocklevel = 5 where ProductID = 998 Hop back to session 1 and.. Update Product set ProductName ='Test' where ProductID = 999 Looking at the deadlock graphs we could see the contention was between two processes, one updating stock and the other updating product, but we knew that all the processes do to the tables is update them.  Period.  There are separate processes that handle the update of stock and product and never the twain shall meet, no reason why one should be requiring data from the other.  Then it struck us,  AH the indexed view. Naturally, when you make an update to any table involved in a indexed view, the view has to be updated.  When this happens, the data in all the tables have to be read, so that explains our deadlocks.  The data from stock is read when you update product and vice-versa. The fix, once you understand the problem fully, is pretty simple, the apps did not guarantee the order in which data was updated.  Luckily it was a relatively simple fix to order the updates and deadlocks went away.  Note, that there is still a *slight* risk of a deadlock occurring, if both a stock update and product update occur at *exactly* the same time.

  Read the article

< Previous Page | 26 27 28 29 30 31 32 33 34 35 36 37  | Next Page >