What is the syntax to specify a Left Join using pre ANSI-92 syntax (i.e. *=) when part of the Where clause has an 'equal to some constant' condition ? (in this case 100 is the constant) Example: SELECT t1.ID, t.* FROM (select * from SybaseTable where ID=1) t, SqlServerTable t1 WHERE t1.ID *= 100 and t1.SeqNo *= t.SeqNo In this ca...
I have several assemblies for SQL Server 2005. If invalid data is entered to be run by my SP function (using C#), I want to pass back an error to sql server. For example, if someone enters something I don't want them to then I want to output something back to SQL Server prior to executing any query. Any ideas? ...
I have worked on several applications that saved XML data into a database. To date all of them simply overwrote the current XML contents with the new XML contents. I would like to save the changes to the XML to the database but not overwrite what is currently in the DB. Short of just creating new rows for changes and leaving the old...
I'm in the process of designing a fairly complex system. One of our primary concerns is supporting SQL Server peer-to-peer replication. The idea is to support several geographically separated nodes. A secondary concern has been using a modern ORM in the middle tier. Our first choice has always been Entity Framework, mainly because th...
What is the best practical way of learning index tuning while writing tsql queries? I have VS2008 SQL Express. Could someone please provide me examples, etc? I have already found online articles and they are great in theory, but I still fail to see index tuning in real life action. Are there small easy to create examples out there? ...
I am using an ASP.Net and C# front end to run some reports. I want to open an SQL Connection to the data source used by the report. When the report uses integrated security it is easy enough to create a connection, however I want to create a connection when the user name and password are stored by the reporting server. I can get SQL Se...
Indexes and Constraints in MS SQL Server are local to the table they are defined in. So you can create the same index or constraint name in more than one table with no trouble. Foreign Key Constraints (which as everyone should know are NOT keys, but constraints) will appear in the Keys subfolder and their names appear to be globally sc...
Hi, I have a union all query in a stored procedure. WHat I would like to do is Sum a column and return that query to the client How would I do this? Malcolm ...
hi all, Recently I think of shifting from oledb data provider to sqlclient as I am using sql server 2005 express edition as my app's backend which contain millions of records.. Can anybody give me concrete reasons for the same please reply sooon ...
Hi, In some SQL query performance tuning, I noticed that the following query was running slowly but it wasn't thrashing the CPU and there appeared to be no other system bottlenecks to cause it to run slowly. In fact the CPU average was 15% whilst it ran: UPDATE: The query in question runs in a cursor loop which contains 800 records: c...
I need to replace all iframe tags, stored as nvarchar in my database. I can find the entries using the following sql-question: SELECT * FROM databasename..VersionedFields WHERE Value LIKE '%<iframe%' Say I want to replace the following code segment: code before iframe <iframe src="yadayada"> </iframe> code after iframe With this: ...
I need to synchronize an SQL Server database to Oracle through an Oracle Transparent Gateway. The synchronization is performed in batches, so I need to get the next set of data from the point where I left off. The problem I'm having is that the only field I have in the source, to help me, is a GUID. If it were a number I could just orde...
I think I have the same problem as kcrumley describes in the question "Problem calling stored procedure from another stored procedure via classic ASP". However his question does not really include an solution, so I'll give it another shot, adding my own observations: I have two stored procedures: CREATE PROCEDURE return_1 AS BEGIN ...
I have a records of events with starttime and endtime for a calendar control. I want to get the events done on a particular date say 2/28/2009 But the db table has date data in form 2/28/2009 10:00:00, 2/28/2009 12:00:00. I tried this query in a sproc created in VS 2005 IDE but it didn't work ALTER PROCEDURE dbo.spSelectBenchEvent (...
How can data be imported from a Microsoft Works database (*.wdb file) into a SqlServer2005 database. Would SSIS be able to do this? ...
I have a data object (let's say it's called 'Entry') that has a set of potential states that look something like this: 1 - Created 2 - File added 3 - Approved 4 - Invalid This is represented in the database with a 'Status' table with an autonumber primary key, then a 'StatusId' field in the main table, with the appropriate relationship...
I believe my server has been the ongoing target of a brute-force attack on SQL Server 2005 (SQLExpress). My Event Viewer log is filled with "Failure Audit" messages for SQL Server. The attempts usually last for an hour or two, with 1-2 seconds between attempts. Login failed for user 'sa' [CLIENT: 222.169.224.163] Event ID: 18456. B...
my data looks like this in the table: ID Name Parent ID --- ---- --------- 1 Mike null 2 Steve 1 3 George null 4 Jim 1 I can't figure out how to write a linq to sql query that will return the results with the parent rows grouped with their child rows. So for example this is the result I...
Is there a way to fix the problem of the difference in the number of rows in the destination and the source table. I check the replication monitor and it show that replication is working fine but then the is a difference of 300 tables between the source and destination. ...
I'm working on my MSSQL index defragmentation script. Certain kinds of indexes can be rebuilt online, and other kinds can't. For clustered indexes, it's easy enough to see if the table contains any LOB columns, but for a non-clustered index I need to specifically know if there is any LOB columns covered by that specific index. I used ...