sql-server

How do I grow a database file?

I am trying to grow a database using the following the code below. However I get the following error. How do I check for size (3600MB) and grow it if necessary? USING MyDatabase ALTER DATABASE MyDatabase MODIFY FILE (NAME = MyDatabase_data, SIZE = 3600MB) Error: MODIFY FILE failed. Specified size is less than or equal to current si...

What is the best SQL Server conference?

Which SQL Server conference has the most performance, tuning, advanced feature presentations that never fail to inform and amuse and has great food at lunch. EDIT: Can you talk a little about your experience there? ...

What cannot be done in SQL Server 2008 Web Edition?

What can you do in SQL Server 2008 Standard Edition that you cannot do in SQL Server 2008 Web Edition? I've seen the Microsoft feature lists, but I am wondering from a very practicle standpoint where I am going to run into limitations. ...

Where can I find a good introduction on SQL locking and transaction strategies

I'm using locks and transactions a bit like a VBA excel programmer trying to write a multithreaded c++ server for the first time... I've tried to ask my coworkers for advice, but while we're all quite good (or so we think) at designing complex databases, writing fast and efficient queries, using index and constraints when it's needed, a...

How do I include empty rows in a single GROUP BY DAY(date_field) SQL query?

I'm using MS SQL Server but welcome comparitive solutions from other databases. This is the basic form of my query. It returns the number of calls per day from the 'incidentsm1' table: SELECT COUNT(*) AS "Calls", MAX(open_time), open_day FROM ( SELECT incident_id, opened_by, open_time - (9.0/24) AS open_time, DATEPART(dd...

@“Data Source=(local)\ SIRSYSTEM \ HEY;” – can’t connect if a string includes SIRSYSTEM

Hi The name of my MS Sql server instance is HEY, but when trying to log on to Sql server via Microsoft Sql management studio, the login window displays as the name of a server "SIRSYSTEM\HEY". What is SIRSYSTEM? A name of default server instance? If so, then why can’t I connect to HEY using the following connection string ( in Ado.n...

WMI Configuration Error on SQL 2005 install

SQL Server 2005 is giving me the following error during the "System Check" portion of the initial install: "The SQL Server Configuration Checker cannot be executed due to WMI configuration on the machine Error:2147749907 (0x80041013)." Machine is a Dell D620 running XP SP2. I found a batch command online that is supposed to fix the...

How to pass parameter values to a T-SQL query

I am using the following T-SQL query in SQL server 2005 (Management Studio IDE): DECLARE @id int; DECLARE @countVal int; DECLARE @sql nvarchar(max); SET @id = 1000; SET @sql = 'SELECT COUNT(*) FROM owner.myTable WHERE id = @id'; EXEC (@sql) AT oracleServer -- oracleServer is a lined server to Oracle I am not sure how to pass the input...

What triggers cache removal in SqlServer 2000

Hi, I'm rewriting a stored procedure at work, primarily to stop it from doing dirty reading and have not made any significant structural changes. However, running the new version against the current version I have found the new version to run almost twice as long on a dev database which doesn't have a lot of activity! Following the adv...

MS SQL 2000 or MySQL 5.0 ?

Which one would you choose for a small ASP.NET 2.0 web site with little traffic? I have no experience with either of them, but my provider wants me to choose one. In fact, I have no experience in ASP.NET too, I am just starting to learn, using VS2008 Professional. Thank you, Petr ...

Dynamic tables in sql

Is there any method for creating dynamic tables in sql server 2000? ...

C# CLR Assembly Query

I am creating a C# assembly for MS SQL Server 2005 for encryption/decryption. I need to query the database in this assembly, and was wondering what the preferred method of connecting to the database is? I believe we would not want to have a username/password in the connection string. Since the assembly is registered in MS SQL does it h...

LINQ to SQL - Updating Data Context Objects in Partial Classes

I have created an extensibility method for deleting one of my Linq To Sql objects called Reservation. Well, in this partial method I created, I want to update some other objects. I can't seem to get the update to be persisted in the database. Here is my partial method for deleting the reservation. public partial class LawEnforcementDat...

EXEC(query) AT linkedServer With Oracle DB

I am using Microsoft SQL server 2005. I need to sync data between SQL server and an Oracle db. First thing I need is to find out if the count of data on Oracle side with certain filters(here I use ID as a simple example). SELECT COUNT(*) FROM oracleServer..owner.table1 WHERE id = @id; The problem I have is that the table on the lined ...

How can I compare two tables and delete the duplicate rows in SQL?

I have two tables and I need to remove rows from the first table if an exact copy of a row exists in the second table. Does anyone have an example of how I would go about doing this in MSSQL server? ...

What SYSTEM stored procedures are available in SQL Server Compact Edition?

I'm trying to script the changes to a SQL Server Compact Edition database running on windows mobile 6 and could really use: EXECUTE sp_rename 'MyTable.SomeColumn', 'BrandNewName', 'COLUMN' What other system stored procedures are available? What are the differences to the non compact version? Edit: There ARE system stored procedures...

How to display relevant record first using FULLTEXT in SQL ??

I am using the FULLTEXT in my sql query to search the records. SELECT * FROM (SELECT d.DataId, (SELECT [Name] FROM Category WHERE CategoryId = d.CategoryId) AS 'Category', d.Description, d.CompanyName, d.City, d.CategoryId, d.CreatedOn, d.Rank, d.voteCount, d.commentCount, d.viewCount FROM Data d ...

Last Run Date on a Stored Procedure in MS SQL

We starting to get a lot of stored procedures in our application. Many of them are for custom reports many of which are no longer used. Does anyone know of a query we could run on the system views in SQL Server 2005 that would tell us the last date a stored procedure was executed? ...

MDX equivalent to SQL subqueries with aggregation

I'm new to MDX and trying to solve the following problem. Investigated calculated members, subselects, scope statements, etc but can't quite get it to do what I want. Let's say I'm trying to come up with the MDX equivalent to the following SQL query: SELECT SUM(netMarketValue) net, SUM(CASE WHEN netMarketValue > 0 THEN netMarketV...

CLR Assembly for Encryption/Decryption

We recently implemented some symmetric keys in MS SQL 2005 for encrypting and decrypting credit card, check routing/account numbers. Ideally, we would like a user defined function to be able to perform the encryption and decryption, however, its not possible since the encryptbykey and decryptbykey functions cannot be used within user de...