Recently at my day-job were were instructed that any comments regarding our stored procedures MUST NOT exist inside the stored procedure and rather Extended Properties must be used. In the past we used something like this. /* * NOTE: Auto-Generated Procedure DO NOT MODIFY */ CREATE PROCEDURE dbo.MyProc AS SELECT * FROM MyTable GO T...
In Access 2003 VBA (Used Immediate Window) ? CDate(39870) Returns: 2/26/2009 In SQL Server 2005 (SQL Server Management Studio) SELECT CONVERT(DATETIME, 39870) Returns: 2009-02-28 00:00:00.000 Do they always differ by 2 or is there some other date setting I'm missing? Server Collation = SQL_Latin1_General_CP1_CI_AS ...
Hi! I have a legacy data table in SQL Server 2005 that has a PK with no identity/autoincrement and no power to implement one. As a result, I am forced to create new records in ASP.NET manually via the ole "SELECT MAX(id) + 1 FROM table"-before-insert technique. Obviously this creates a race condition on the ID in the event of simulta...
I am working in SQL 2005 (I think), SQL Query Analyzer Version SQL 8.00.760. I would like to write a query that returns a count into a Crystal Report from a table only if the due date exceeds 14 days based on the end date in the report search. Based on my very limited understanding of SQL, I have come up with the following, which has p...
I have a client who has a SQL Server 2000 database linked to an Oracle 8i database. They have dozens of views in the SQL Server 2000 database which reference the Oracle database, often with simple syntax such as: SELECT * FROM SERVER..DB.TABLE These views (and the sprocs which reference them) have worked for YEARS without issue. Sudd...
This is not http://stackoverflow.com/questions/279401/sql-connection-pooling-and-audit-login-logout. I've got a C# .NET 3.5 app that updates about 30K records on SQL 2008 on a local database. The logic is it first checks to see if the record exists SingleOrDefault(p => p.stock=stock && p.number=number) and either adds the record or upd...
I have a table-value function that takes an ID number of a person and returns a few rows and columns. In another query, I am creating a SELECT that retrieves a lot of information about many people. How can I pass an id number from my main query to my function to sum a column and join it to my main query? I wish I didn't have a table valu...
I have a select statement which will return me 5 values as val1,val3,val5,val2,val4 I have another select statement which is going to return a set of records with one of the column having values from the above set (val1 to val5) Is it possible to sort the second select statement with the result of the first select statement? I mean i...
In SQL (specifically MySQL, but the question is generic enough), what is the most efficient way to query time-series data when I have multiple tables across disjoint time ranges? For example, if my tables are as follows: router1_20090330( unixtime integer unsigned, iface1_in integer unsigned, iface1_...
Say I have a million-row table [mytable] in my SQL Server 2005 database, which has columns: ID SomeField AnotherField Url A very common query in this system is: select * from [mytable] where url = 'http://www.somesite.com/some/really/long/url' Which will give me better performance: a) Add an index to the Url column. or b) Add an...
We are about to embark on a major project which requires an Occasionally Connected Application. The MS Sync Framework seems to offer a good solution with Sync Services for ADO.NET. Has anyone used this in a production app, ideally with tens of thousands of users, and can you comment on how well it scales? Any other pitfalls or gotcha's...
I have this error which happen many times on my site: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error:...
When the SQL Server (2000/2005/2008) is running sluggish, what is the first command that you run to see where the problem is? The purpose of this question is that, when all the answer is compiled, other users can benefit by running your command of choice to segregate where the problem might be. There are other troubleshooting posts rega...
I've been searching for some time for a good solution to implement the idea of managing schema on an SQL Server Compact 3.5 database. I know of several ways of managing schema on SQL Server Express, SQL Server Standard, SQL Server Enterprise, but the Compact Edition doesn't support the necessary tools required to use the same methodolog...
I've been looking at the SqlPubWiz.exe command to write a batch file so that I can keep my script up to date in my source control. But what I need is for the command line tool to allow me to pick specific tables to include (and that I can exclude others). I think SqlPubWiz.exe won't do that for me (let me know if I'm wrong) but if someon...
Some sample data: DECLARE @TARGET TABLE ( ID INT, value INT ) ; DECLARE @SOURCE TABLE ( ID INT, value INT ) INSERT INTO @TARGET VALUES ( 1, 213 ) INSERT INTO @TARGET VALUES ( 2, 3 ) INSERT INTO @TARGET VALUES ( 3, 310 ) INSERT INTO @TARGET VALUES ( 4, 43 ) INSERT INTO @SOURCE...
In an extract I am dealing with, I have 2 fields. One field stores the dates and another the times as shown. How can I query the table to combine these two fields into 1 column of type date? Dates 2009-03-12 00:00:00.000 2009-03-26 00:00:00.000 2009-03-26 00:00:00.000 Times 1899-12-30 12:30:00.000 1899-12-30 10:00:00.000 1899-12-30...
I am trying to replicate a database from SQL server 2000 to 2005 they are located on two different servers both running Windows Server 2003 R2. Im am using SERVER1(SQL2000) as the Transactional publisher and distributor and SERVER2(SQL2005) is the subscriber. I can set up the publication and subscription but when I try to syncronize them...
I need to update a row in a table, and get a column value from it. I can do this with UPDATE Items SET Clicks = Clicks + 1 WHERE Id = @Id; SELECT Name FROM Items WHERE Id = @Id This generates 2 plans/accesses to the table. Is possibile in T-SQL to modify the UPDATE statement in order to update and return the Name column with 1 plan/ac...
I am working with MS SQL 2005. I have defined a tree structure as: 1 |\ 2 3 /|\ 4 5 6 I have made a SQL-function Subs(id), that gets the id, and returns the subtree table. So, Subs(3) will return 4 rows with 3,4,5,6, while Subs(2) will return one row, with 2. I have a select statement that returns the above Ids (joining this t...