sql-server

how to use between in sql server

ID NAME TIME --------------------------------------------- 1 United Arab Emirates 3:00 2 Sri Lanka 2:00 3 Turkey 4:00 4 Yemen 4:00 5 Us 11:00 6 Sweden 5:00 7 England 6:00 8 Sin...

Is there a DB deployment tool that will read from subversion?

I would like to have a single application that handles all production database changes. Ideally I would like all changes to be scripted and created in a SVN repository. I would like the deployment tool to take a tag name, extract the scripts from the repository and run them on the production database. Ideally keeping a log of who did wh...

SELECT TOP Scan the whole table

If I did a command like: SELECT TOP(10000) ProductId from ProductInfo WHERE ProductId IN ( SELECT ProductId from DeletedProduct ) Will it scan the whole table or break out as soon as it finds 10000? ...

Block a row from reads whilst sp executes

Hi, I have many .NET processes reading messages from an SQL Server 2008 DB table and processing them one at a time. I implement a simple SP to 'lock' the row that is being read by any one process, to avoid any two processes processing the same row. BEGIN TRAN SELECT @status = status FROM t WHERE t.id = @id IF @status = 'L' BEGIN ...

How do you use GetSchema(), ReportSchema() or SMO to determine the method a constraint was created in Sql Server 2008?

In this previous question I asked I found that constraints created with Create Index do not exist in sys.objects. I'm already using ReportSchema() and looping through Indexes and IndexColumns collections. Is there a way to determine the method a constraint was created by using the collections available to GetSchema() or ReportSchema()...

SQL Server Management Studio 2008 -- Differences between what is seen and what is really in DB?

I altered a stored procedure in SQL Server Management Studio database and everything appeared as expected. When my ASP.NET website called that stored procedure, it complained of invalid arguments. After running the alter procedure and then drop/create procedure the website continued to complain of invalid arguments. Furthermore, after re...

SQL Server replication drop vs truncate of target tables

The sp_addarticle stored procedure, used to create publication articles in SQL Server speak, has a parameter @pre_creation_cmd through which dropping the table or truncating data at the target server can be specified for a snapshot. What circumstances are appropriate for each? ...

using different data connection in visual studio

It's possible to use 2 or more data connections in visual studio and using them in a single query, like we can do in sql server management (while we use mysql and mssql dbms same time): SELECT * INTO testMySQL.dbo.shoutbox FROM openquery(MYSQL, 'SELECT * FROM tigerdb.shoutbox')...etc ... etc... ...

Which datatype for base64 encoded image ?

Question: In order to store logos in a database and display them dynamically in ms-reporting service, I need to base64 encode the image. It doesn't work with binary saved images, due to MS limitations... Now my question: Which datatype do I use? Varchar, nvarchar or text? I guess varchar would be good enough, since base64 encoded, but ...

Incorrect syntax near the keyword 'with' (SQL)

Hi Guys, I have a little bit of an odd issue. When I run this SQL: with ID_Table as ( select row_number() over (order By SS_ID) As row_id, ss_id from slide_show ) select t0.* from ID_Table as t1 inner Join slide_show as t0 on t1.ss_id = t0.ss_id where t1.row_id between 0 and 1 order by t...

sql connection pool

I am making multiple queries to a database over a short period continiously. In the past I have always closed the connection and reopened when I needed to talk to sqlserver again - this served me well. But now when I try to do this over a very short period I get no connections availible messages. I then thought I would just try and keep ...

How to compare two databases?

I have two databases that are similar, but not the same. DB 1 is the old one and DB2 is the updated one with lots of new tables, columns, procs, constraint etc. I need to write an update script in order to update DB1 database. These databases have lots of tables and stored procedures. Is there any way to get the differences in two datab...

SQL Server DateTime Query Issue

I am using SqlServer Compact 3.5 (2008) and am trying to return rows that have a datetime field that is today or previous. This is the code I am trying, but it is throwing an exception. (in the Where clause) I'm new to SQL so I'm having trouble with this one. Any ideas? If you need more data, let me know. string selectStatement = "S...

SQL Server Database Change Listener C#

I want to listen for changes to data in a SQL Server database from C#. I was hoping that there would be some sort of listener which I could use to determine if data that I have is stale. Despite being a fairly common scenario I can't find any solutions which aren't to simply poll the database. I use Linq-To-SQL to access the data and he...

Using single quote in an Exec statement in SQL

I am having issues executing a SQL statement from a stored proc having single quotes. Here is my query from a stored procedure that I am executing. EXEC('UPDATE myTABLE SET myCOLUMN = (SELECT Replace('OSINGLEQUOTEJOHN DOE','SINGLEQUOTE','''')') I am trying to update table "myTABLE" column "myCOLUMN" with a value "O'John Doe"...

What is the lowest impact method for profiling SQL Server for a specific client?

My local friendly DBA does not like the idea of allowing profiling on the SQL servers (even non-production ones) due to the impact on performance they have. Ironically, I'm trying to reduce the need for a whole bunch of SQL queries, and would like to be able to check in a fairly non-invasive manner what queries are being sent over the w...

Converting SQL Server to MySQL

Hi, is it possible to convert a SQL Server database file to a MySQL Database file and if so what would be the best way to go about doing this. Thanks. ...

Update a table that has an identity column with SqlCommandBuilder

I have a DataGridView corresponding to a table which has an identity column and the primary key is also set to this column and a button (a SQL database). I've added the dataset of database to project. There is a empty table (DataGridView) in front of user and he/she can edit (update) this DataGridView; for example edit just one cell and...

how to create utility stored procedure and run it like other sp_... utilities

I need to create a stored procedure one time and in one place only on my database server, but be able to run it from any database. However, I don't want to have to type database.schema.procedure_name every time I run it. I'd like to be able to use it like the built in procedures: sp_... is there any way to do this? here is the proced...

Building a demo account which will reset itself periodically

Hi: We are building a simple CMS with asp.net 3.5 and mssql2005 We need to build a demo account for our customers and this will be published to public. So, we want to reset the content of the demo account on every 2 hours. What is the best way of building this kind of demo account? Thanks for all responses for now. Best Regards ...