I am trying to write some integration tests for some SQL server stored procedures and functions. I'd like to have a database that has a known set of test data in it, and then wrap each test in a transaction, rolling it back when complete so that tests are effectively independent. The stored procedures/functions do anything from fairly ...
Whenever I want to add a column to a table it usually goes something like this: Fire up SQL Server Management Studio (SSMS) Select "Design" on the table I want to add the column to Add the new column to the table Save Get an error that SSMS can't save because it would need to drop the table (and it can't because the the table has forei...
I couldn't find anything that rejects or confirms whether SQL Server 'MONEY' data type is a decimal floating point or binary floating point. In the description it says that MONEY type range is from -2^63 to 2^63 - 1 so this kind of implies that it should be a binary floating point. But on this page it lists MONEY as "exact" numeric. Wh...
Our SQL Server 2008 mirroring was setup and working a few weeks ago with a 3rd server as the witness. However, we started getting the following error message recently: Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://COMPUTER-...
Good afternoon everybody, I am in a bit over my head and facing some tight deadlines, so hopefully someone can offer some advice. My starting point will be a table in a SQL Server database, two of whose fields are x,y coordinates obtained from a gps unit. I will be responsible for geocoding (getting physical street address) these loca...
What is the best way to access sql server from python is it DB-API ? Also could someone provide a such code using the DB-API how to connect to sql server from python and excute query ? ...
I have a SQL Server table named DeficiencyTag and its PK is an integer named DeficiencyTagID. It will have a couple hundred records. There's another table named Deficiency that references a particular DeficiencyTagID. There will be millions of these eventually. What I need to do is make a list of the top 10 DeficiencyTag records based ...
I have a SQL Server 2008 database with records like the following 001 CAT 1 2 3 002 DOG 3 3 1 003 DOG 2 1 1 004 DOG 2 1 3 005 CAT 1 3 4 I want to take 1 row for each unique value in column 2 (cat and dog) so this would get me (one possible answer): 001 CAT 1 2 3 002 DOG 3 3 1 Column 1 is the PK and is a string. Column 2 is a stri...
Is it possible to work with the returned datasets from a stored procedure? Basically I have a stored procedure (lets call it SP_1) and it calls another stored procedure (lets call it SP_2). SP_2 has 5 or so select statements. What I want to do is handle each select statement within SP_1. Basically to manipulate the data or whatever, but ...
Hello, I can't get SqlCacheDependency to work with a simple stored proc (SQL Server 2008): create proc dbo.spGetPeteTest as set ANSI_NULLS ON set ANSI_PADDING ON set ANSI_WARNINGS ON set CONCAT_NULL_YIELDS_NULL ON set QUOTED_IDENTIFIER ON set NUMERIC_ROUNDABORT OFF set ARITHABORT ON select Id, Artist, Album from dbo.PeteTest And h...
We are looking at hosting our client's data on our own system and using the MS Sync Framework to replicate data with a SQL Server instance on each client's site. Each client might have on average about 500MB of data and there could be up to 1,000 client sites, se we would have about 500GB of data overall. Data changes will need to be rep...
I have a table columns Id and EmployeeID. The table data has the following peculiarity: in some parts (where the Id is consecutive), the same EmployeeID can sometimes be found, for example Id | EmployeeID --------------- 1 | 1 2 | 1 3 | 2 4 | 5 5 | 1 6 | 1 I want to build a query to find blocks of data co...
In on-premises Microsoft SQL Server, when I issue this query: SELECT * FROM sys.dm_exec_sessions I get one record per window in SSMS. If I open several query windows in SSMS, I get a record for each of those, even if they're not doing anything. In Azure, when I issue that same query, I only get one record back. Here's the kicker - I...
I am trying to decide on which approach to take in a database I am designing. I will be adding a ProcessedDate datetime null column in a table. It will be nullable for when the record has not been processed. So, is it worth having a Processed bit not null default 0 column as well? With the following queries: select * from tablename whe...
Hi everyone! I am trying to put full text search working on SQL Server 2008, however the table i am trying to index is a table with a composite primary key, something like this: EXEC sp_fulltext_catalog 'My_Catalog', 'create' EXEC sp_fulltext_table 'Message', 'create', 'My_Catalog', 'PK__MESSAGES__C87C0C9C0EC32C7A' // PK__MESSAGES__C87...
I have an MS SQL 2000 database that was backed up from a public server and restored at a test location for an upgrade test. The problem is that the user that had access permission on the public server does not exist on the testing server, and now all tables are prefixed with that username (which requires ALL queries against those tables ...
Here is the T-SQL code that I coded, which I imagined to work but didn't work: DECLARE @Local nvarchar(20) SET @Local = 'Yes' SELECT (CASE WHEN @Local = 'Yes' THEN ID ELSE COUNT(ID) END) FROM myTable Is there something I am doing wrong or is there any other way to do that? Thanks. ...
I have a client that currently uses a local Advantage Database on their PC along with an application. They are thinking of upscaling their setup to have multiple applications running communicating with a database server i.e/a client-server environment. They are now considering the best database for this approach. They are looking at t...
Hey, I'm trying to convert some SQL I've patched together into a LINQ query. The context is a simple quiz app. A quiz has quizrounds, contestants are one to one attached to quizrounds, rounds have registered answers. The case is that I have a one to many relationship between tables 'quizround' and 'answer'. I want to do a ranking of a...
I've captured a trace (template: tsql_replay) on our production server for a specific database (filtered by database name). I'd like to replay on it on our test server, but of course the database id is different on the test server. So far, I've tried loading the trace file into a table and modifying the databaseid with an update query ...