What's the difference between peer-to-peer and merge replication with SQL Server?
What's the difference between peer-to-peer replication and merge replication using SQL Server? ...
sql-server
LINQ to SQL complex query problem
Hi all, I have 3 tables: Principal (Principal_ID, Scale), Frequency (Frequency_ID, Value) and Visit (Visit_ID, Principal_ID, Frequency_ID). I need a query which returns all principals (in the Principal table), and for each record, query the capacity required for that principal, calculated as below: Capacity = (Principal.Scale == 0 ? 0 ...
SQL order results by number of fields matched.
Bit of a complicated SQL question here. I currently have a SELECT statement which matches several fields, like this. SELECT field1, field2, field3, field4, field5 FROM table WHERE field1 = 'variable 1' AND field2 = 'variable 2' AND field3 = 'variable 3' AND field4 = 'variable 4' AND field5 = 'variable 5' I would like to modify t...
Alphabetize list from query with specific associated row selected in dropdown
Hey all, I'm trying to write an SQL query that would populate a dropdown box of locations. In addition, I need the query to select the location associated with a user automatically (ie: be the first in the list results.) I had the following, but recently realized that the list isn't completely alphabetized. To add another level to this...
SQL Text Filtering
I want to filter out several key phrases out of a dataset. Unfortunately, the only algorithm I've been able to come up with thus far is nested replace statements, such as: SELECT REPLACE( REPLACE(FIELDNAME,'</u>','') ,'<u>','') where FIELDNAME is raw HTML code stored in a table. As you can see, this is hideous. Any be...
Performance effect of using TOP 1 in a SELECT query
Hi All, I have a User table where there are a Username and Application columns. Username may repeat but combination of Username + Application is unique, but I don't have the unique constraint set on the table (for performance) Question: will there be any difference (performance-wise) between : SELECT * FROM User where UserName='myuse...
Inserting rows into a table with a predefined identity
I am transfering data from an old database to a new one, and I need to keep the ID from the old database when inserting them into the new database. However, it's not working. This is my Query: SET IDENTITY_INSERT RentalEase.dbo.tblTenant ON INSERT INTO RentalEase.dbo.tblTenant SELECT [ID] ,1 ,[PropertyID] ,0 ,[UnitI...
How to decrypt encrypted Oracle data that was replicated to SQL Server 2008?
I have a case where Oracle data (phone numbers) was encrypted using the Oracle DBMS_OBFUSCATION_TOOLKIT DESEncrypt function with a unique user generated hash key. SQL Example: update Phone set encrypted_phone = WEBX_ENCRYPT_DECRYPT.ENCRYPT( '212-555-1201', '8IcrEuOdDjRT5iDjqHLcsA==') where person_id = 120000...
How to get the logical name of the transaction log in SQL Server 2005
I am trying to write a T-SQL routine that shrink the transaction log file using DBCC SHRINKFILE based on the logical name of the database. The DB_NAME() function gives you the logical name of the database. Is there an equivalent one for the transaction log? If not, is there some other way to get this information? The default name for...
SQL Union problem
Here is my query: SELECT publicationId AS PublicationID FROM dbo.PublicationOwner WHERE ownerId = 31331 UNION SELECT AreaBuy.AreaBuyID AS PublicationID FROM AreaBuy JOIN PublicationAreaBuy ON AreaBuy.AreaBuyID = PublicationAreaBuy.AreaBuyID WHERE PublicationAreaBuy.PublicationID IN (SELECT publicationId ...
MySQL: Selecting data from MS SQL Server
How do I select data from a MS SQL Server from MySQL? We have a system of linked server in SQL Server. I have also heard that you can do a linked server to Mysql from sql server. But I want to know the reverse. The mssql server is what we use mainly for production. So one main reason we'd want to do this is simply to get the exact mssql...
SQL Server Agent job fails with "The step was cancelled (stopped) as the result of a stop job request"
Hello! Since 3 days ago, an SQL Server 2005 Agent job that's used to retrieve data from an odbc data source (Microsoft XAL) fails with the following error: "Executed as user: NT AUTHORITY\SYSTEM. The step was cancelled (stopped) as the result of a stop job request.". As far as I can tell, we didn't change anything in the configuration...
Is there a lightweight SQL gui that allows exploring databases and running SQL?
SQL Express is installed on the machines that I'm going to, but I'm not going to install the clunky SQL Management Studio. I just want an EXE I can run, so preferably I'd like it to be install-less. Current options are: 1) Write one myself - not too hard, but I thought I'd check here first 2) Use the command line Requirements: 1) Bro...
Sql Connection Problem
Hi there. I have a sqlconnection problem. i have perfectly connection to sql server when i m using sqldatasource. but when i m try use SqlConnection object throw an exception. string qstring = "Data Source=****;Initial Catalog=**;User ID=**;Password=**"; SqlConnection con = new SqlConnection(qstring); con.Open(); (exception thr...
How can I round a column in a single SQL request without changing the overall sum ?
I've got a table defined like this : create table #tbFoo (bar float) And I'm looking for a way to round every value contained in column bar without changing the total sum (which is known to be an integer, or very close to an integer because of float number precision). Rounding every value to the nearest integer won't work (ex : 1,5;...
How to test two datetimes for equality in T-SQL (ignoring their time components)?
How can I test two datetimes (not including their time components) for equality? ...
Increase performance on DELETE/INSERT on remote server
In this scenario there are two database servers: one is in our LAN (Server A) and one on our remote location (Server B). We want to transfer the data of our projects from Server A to Server B. To do this we first remove the existing data on Server B for a certain ProjectID and after that simply insert the data from Server A to Server B....
How do I drop all foreign-key constraints on a table in Sql Server 2000?
How do I drop all foreign-key constraints on a table in SQL Server 2000 using T-SQL? ...
JDBC-driver / SQL Server recompiles my prepared statements all the time when they contain ROW_NUMBER()
I've noticed that prepared statements containing ROW_NUMER() code often gets recompiled although their SQL-code hasn't changed. (example from book Inside Microsoft SQL Server 2008: T-SQL Querying): WITH SalesRN AS ( SELECT ROW_NUMBER() OVER (ORDER BY qty, empid) AS rownum, empid, mgrid, qty FROM dbo.SalesOrder ) SELECT r...
Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:1681) with latch type SH. sysindexes failed.
Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:1681) with latch type SH. sysindexes failed. What does this mean ? is it that DB is currupted? ...