Here's the scenario: You have an ASP.Net application supported by a Microsoft SQL Server database, and for this example there won't be any caching. For each page in your web application, what's more efficient: Attempting to condense everything you need into one (or a few) stored procedure calls that return multiple tables with all of ...
I need to alter a trigger in sql server 2005 and I want to do it using a table adapter and a sql statement, not a stored proc. I know I could run this in the query browser and execute it but I need to deploy it across several databases and use a table adapter in the update. Is this possible? Doing Add Query -> Update -> pasting the belo...
Are hints really necessary for every sql statement? We have a dba who is anal about it and asks us to put hints on every select and update statements in our stored procs. Is this really necessary? ...
When @RadioServiceGroup is set to NULL, I want to return all the records from the sbi_l_radioservicecodes table which has about 120 records. However, when I execute the following procedure and set the @RadioServiceGroup to NULL, it returns no records. Here is the stored proc: CREATE PROCEDURE [dbo].[GetRadioServiceCodes] @RadioServic...
I have a stored procedure that creates and opens some cursors. It closes them at the end, but if it hits an error those cursors are left open! Then subsequent runs fail when it tries to create cursors since a cursor with the name already exists. Is there a way I can query which cursors exists and if they are open or not so I can close...
I added the columns in the select list to the order by list, but it is still giving me the error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Here is the stored proc: CREATE PROCEDURE [dbo].[GetRadioServiceCodesINGroup] @RadioServiceGroup nvarchar(1000) = NULL AS BEGIN SET NOCOUNT ON; SELECT DISTIN...
Hey all, I'm trying to create an ASP page that has a bridged connection with an SQL Server 2005 database (separate sever from the ASP's server). For this I am trying to use a Windows Authentication setup. I have my name with full rights to the SQL server yet I am still getting the error 'Login failed for user COMPANY\name'. To see if th...
For better or worse, we have a solution that relies on multiple databases that all reference a common administration database. Databases ship as part of modules, and not all modules are required for an installation (probably why we have multiple databases in the first place). The admin database is required, however ... so it will alway...
I prefer to keep our SSIS packages in a solution on the server, not in sql. By default, sensitive data is encrypted with a user key. Since the sql server agent uses a service account to run jobs, we have to change this encryption method to something else. I like encrypting with a password. The problem is whenever I setup a job in a step...
I am trying to convert a date with individual parts such as 12, 1, 2007 into a datetime in SQL Server 2005. I have tried the following: CAST(DATEPART(year, DATE)+'-'+ DATEPART(month, DATE) +'-'+ DATEPART(day, DATE) AS DATETIME) but this results in the wrong date. What is the correct way to turn the three date values into a proper date...
Sql Server Management Studio 2008 is not scripting table permissions even when I select the option to script object level permissions. Is this a bug or is there another way to do this? It is creating permissions for stored procs, but not for tables. I am a sysadmin. If it doesn't work, are there free sql server tools to script the permi...
What is the best option for a windows application that uses SQL server authentication? Should I create a single SQL account and manage the users inside the application (using a users table). Or should I create a SQL server account for each user. What is your experience? Thank you! ...
As part of the way we handle support for our clients for an enterprise application, we have them backup their database, I restore it on our server, do some maintenance, backup it up, and have them restore it on their server. This works very well, but I usually have to reset the users permissions for that database. But at the moment I h...
Hi Folks, I'm trying to figure out the best way to insert a record into a single table but only if the item doesn't already exist. The KEY in this case is an NVARCHAR(400) field. For this example, lets pretend it's the name of a word in the Oxford English Dictionary / insert your fav dictionary here. Also, i'm guessing i will need to ma...
I have this sql ce database which I would like to convert to SQL 2008 because I want it to be accessible through the network. I am using SQL 2008 express; and VB9. Any idea? ...
Is it possible to make efficient queries that use the complete regular expression feature set. If not Microsoft really should consider that feature. ...
In a table I have the following schema table1: playerID int primary key, nationalities nvarchar table2: playerID int, pubVisited nvarchar Now, I want to set all the players' playedVisited to null, for player whose nationality is "England", any idea on how to do this? ...
We've just been given the following code as a solution for a complicated search query in a new application provided by offshore developers. I'm skeptical of the use of dynamic SQL because I could close the SQL statement using '; and then excute a nasty that will be performed on the database! Any ideas on how to fix the injection attack?...
Why doesn't this work? DECLARE @temp table (ShipNo int, Supplier varchar(10) ) INSERT INTO @temp VALUES (1,'CFA') INSERT INTO @temp VALUES (1, 'TFA') INSERT INTO @temp VALUES (2, 'LRA') INSERT INTO @temp VALUES (2, 'LRB') INSERT INTO @temp VALUES (3, 'ABC') INSERT INTO @temp VALUES (4, 'TFA') Declare @OrderBy varchar(255) sET @OrderB...
I have some update or something that tries to run every night, and ends up leaving the SQL Server service stopped. When the machine restarts, the service restarts properly. How do I go about diagnosing and fixing what is breaking my SQL Server service? I'm running SQL 2005 on Vista Business. ...