Hi there, I'm going to get some real-world data the best I can for this query I'm having a hard time with. Table HierarchySet: HierarchySetId ClientId 22 1866 23 1866 Table User: UserId UserName ClientId 76 TestUser 1866 Table LinkTable: LinkId UserId OrganisationId Hierar...
Hi guys, I have a little DB challenge for you. Hopefully you can help. The Problem: I need a solution that will allows me to "handle" changes to primary keys/ composite keys. By "handle", I mean I should still be able to perform CRUD operations with little or no code changes. It needs to have minimum hassle from an application/ databa...
I'm looking at some legacy VB6 code (years+years old, before my time) which runs a query against an SQL 2005 db. It supplies a date restriction in the WHERE clause - where the date is given as an integer value as a result of a CLng() on the Date in VB6. e.g. ... WHERE SomeDateField >= 40064 40064 is what VB6 converts today's date to ...
I'd like to be able to retrieve programmatically the command strings that generate the views on our SQL Server. I though the ADOX collection, used together with an ADODB connection, will allow us to access this through the catalog/view/command property. Unfortunately, the 'views' collection is not available when connecting from an MS-A...
Hi All, I'm selecting some rows from a table valued function but have found an inexplicable massive performance difference by putting SELECT TOP in the query. SELECT col1, col2, col3 etc FROM dbo.some_table_function WHERE col1 = @parameter is taking upwards of 5 or 6 mins to complete. However SELECT TOP 6000 col1, col2, col3...
Hi, i need to create a dynamic SQL statement that selects fields based on an XML parameter. Say i have a stored proc with 1 param - [@FIELDS XML] that contains field names. eg... Field 1 = Name Field 2 = Address etc... ..in reality there would be up to 50 fields and i only want to report on the ones in the XML parameter. how can i m...
Hello! I'm in the process to migrate a datebase. New is that a part of the data's home is in an external database (ERP-System). I have to modify a large number of queries. How can i check all queries & SP's, if they still run successfuly? If i have a query 'A' which depends on query 'B', and i change a column in query 'B' i do not get...
I have a loooooong stored procedure that runs about 15 select statements across different tables in a database - I am inserting all the results of the selects into a temporary table. However - that table has some rows that are exact duplicates. The temporary table looks something like this: DocID | VisitDate | DocName 824...
I have the SQL statement (SQL Server ) SELECT COUNT(ActionName) AS pageCount FROM tbl_22_Benchmark WHERE DATEPART(dw,CreationDate)>1 AND DATEPART(dw,CreationDate)<7 GROUP BY dateadd(dd,0, datediff(dd,0,CreationDate)) which produces the output pageCount 27 19 59 Now I would like to get the average of all those figures using SQ...
When I try to retrieve the contents of a Text field from mssql in php I only get back part of the contents. I think its about the first 4000 characters. How do I stop this. ...
I know that an index seek is better than an index scan, but which is preferable in SQL Server explain plans: Index seek or Key Lookup (Bookmark in SQL Server 2000)? Please tell me they didn't change the name again for SQL Server 2008... ...
I need to create a function that returns a table of continuous dates. I would pass in a min & max date. I expect it to be able to be called like this: SELECT * FROM GetDates('01/01/2009', '12/31/2009') I currently have a stored proc that does this, but requirements changed and now I need to do include the returned data from within a ...
Ok, firstly I've seen this thread. But none of the solutions are very satisfactory. The nominated answer looks like NULLs would break it, and the highest-rated answer looks nasty to maintain. So I was wondering about something like the following : CREATE FUNCTION GetMaxDates ( @dte1 datetime, @dte2 datetime, @dte3 datetime, ...
Hi, When creating indexes for an SQL table,if i had an index on 2 columns in the table and i changed the index to be on 4 columns in the table, what would be a reasonable increase the time taken to save say 1 million rows to expect? I know that the answer to this question will vary depending on a lot of factors, such as foreign keys, ...
I'm trying to flip a bit field in SQL Server using an update query, that is, I want to make all the 0's into 1's and vice versa. What's the most elegant solution? There doesn't seem to be a bitwise NOT operator in TSQL (unless I'm missing something obvious) and I haven't been able to find any other way of performing the update. ...
I have two stored procedures, one of which is called a lot and is thus very simple and executes well. Additionally, these actually work very nicely in paralell even though they are serializable. However I have a second query that is only called in certain circumstances which needs to do many more checks before touching the same tables....
Trying to create Database as follows: USE Master GO IF NOT EXISTS(SELECT [Name] FROM sys.databases WHERE [name] = 'QAudit') CREATE DATABASE [QAudit] ON PRIMARY ( NAME = N'QAuditData', FILENAME = N'<filePath,nvarchar(300),C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\>QAuditData.mdf' , SIZE = 921600KB , FILEGROW...
This is a follow-on question from the one I asked here. Can constraints in a DB have the same name? Say I have: CREATE TABLE Employer ( EmployerCode VARCHAR(20) PRIMARY KEY, Address VARCHAR(100) NULL ) CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, EmployerCode VARCHAR(20) N...
Hi, I have a schema called application. Is there a way that, rather than using the syntax SELECT * FROM application.table I can set the default schema so that I can just use SELECT * FROM table It would be the same idea as a using statement I suppose. ...
I am having trouble calling the following stored procedure. When I call ExecuteReader, I am getting the error 'Incorrect syntax near 'GetAverages2'. I can create the stored procedure and call it from TSQL. I can't seem to get it working from ADO: CREATE PROCEDURE GetAverages2 @CompanySize INT, @Q1 FLOAT OUT, @Q2 FLOAT...