Is there a way to get the xml-safe version of an xml column in sql server ? By xml-Safe i mean escaping special characters like <,>,', &, etc. I'd like to avoid doing the replacements myself. Is there a build in function in sql server. What I want to achieve is to store the xml content into another xml attribute. ...
Hi I have an "Employee" table with an "EmployeeID" column and a column representing Employee's Boss (BossID) which in turn is an employee in the "Employee" table. How can I trace the hierarchy from a given "EmployeeID" to the top most Boss. I do not want a self join approach in this, also I am using SQL Server 2005. Thank you Man...
I have to create a temp table (#temp) from a variable or stored procedure (i.e) My stored procedure contains ...... set @sql='select ...' set @sql=@sql+'..join..' set @sql=@sql+'....join..' when i execute it (i.e) exec (@sql) it returns some rows,i want to store that rows in a temp table.How to acheieve it? i tried something lik...
Hi guys, I am working with VS 2010, Entity framework, SQl-Server 2005, ASP.Net web forms. Currently, I am working on the Data access layer library which soon will be a web service, using Entity Framework collaboration with different design patterns like repository pattern and some best practices that posts in different blogs. I am also ...
Hi there I have the following query and I need to put OPTION (MAXRECURSION 1000) on the CTE section cause that place is where is the problem is (UNION ALL). So I put next to that line and compiled and not working and try different place is not working either (Incorrect syntax near the keyword 'OPTION'.). My question where I should put ...
I've got a LINQ to Entities app and a database project to manage the schema in a VS 2010 solution targetting .NET 4.0. The entity model is currently reverse engineered from the database. One of the tables is defined with a column of type datetime. The database project is configured to use SQL Server 2005 compatability mode and so it all ...
i have the following key: ALTER TABLE dbo.Table ADD CONSTRAINT PK_ID PRIMARY KEY CLUSTERED ( ID ASC ) so i have clustered index and primary key on ID column. Now i need to drop clustered index (i want to create new clustered index on another column), but retain primary key. Is it possible? ...
Dear Friends , How can i retrive Microsoft SQL Analysis Server Properties using SQL Query? Thanks ...
I have an issue where a variable is not getting set by a select statement. The select joins a table variable @contracts (which is used to control a loop) and then joins to the real contract and contract line tables. my select is: select top 1 @contract_id = c.contract_id , @account = ch.account , @service = c...
Hi There, am using SQL server 2005 , i have a requirement to get the Creation datetime of all the rows in a particular table, unfortunately the table do not have any "rowverion" or datetime column ( i know this is a major design flaw). so , i was wondering if SQL server maintains datetime for each row inserts. comments suggestions app...
requirement is, both field must be equal, what would you do declare @var datetime set @var = getdate() update table set f1=@var,f2=@var or simply update table set f1=getdate(),f2=getdate() ...
Hi All, I'm trying to find and replace all instances of 'this phrase' with 'that phrase' in an xml type column using T-SQL. The xml column contains fields that are more than 8000 characters long. I tried using the Replace function but it doesn't like the XML data type, and the data can't be squeezed into a varchar. Phil ...
When I try EXEC sp_refreshsqlmodule [myschema.myprocedure] it throws Could not find object 'myschema.myprocedure' or you do not have permission. ...
I'm analyzing some code that utilizes empty OVER clauses in the contest of Count(). Example: SELECT ROW_NUMBER() OVER (ORDER BY Priority DESC) AS RowID, CAST((COUNT(*) OVER() / @pagesize) AS Int) AS TotalPages, I'm trying to understand why the empty OVER clause is being used here. There are other standard select ele...
I'm having this problem: The SQL Server holds a said value, let's say 990.30, the Reporting Services Report show a different value (948.33) when viewed online, in the brower and a third value (912.22) when the report is exported to Excel. There are no formulas on Excel, just plain values. Also, there's no formula on the layout of the r...
There appears to be several stored procedures that were renamed with sp_rename, causing the syscomments to be left with the old proc name instead of the new proc name. Thus, my db transfers are failing to transfer correctly. Is there a way to find which procs (or any objects) have their names out of synch? Or better, a simple way to f...
I've got a view that's a union of two tables that have overlapping keys and I want to uniquely identify the rows for later retrieval. How can I add an identity or identifier column to the view rows so I can retrieve the rows later by that value? ...
I have a table that i use for current semesters (currentSemester) with an id that is an identity. I have a table I archive to called pastSemester with an id that is an identity and a legacyID column that carries the key to currentSemester when I archive. The problem is that I have to report on both current and past records simultaneousl...
Hi, I have SQL 2005 databases. I have deleted a row from one of them and want to get it back from another database that was a backup of the row. How do isnert it while preserving its id primary key identity field? Can you give TSQL to do this assume databases are called "tbrPdata" and "tbr0910" which is the backup? Malcolm ...
I've created a trigger in ms sql server 2005. The trigger is supposed to update orderdata. Example: table id order 1 1 2 2 3 3 4 4 Update so that id 4 has order 2 would create. table id order 1 1 2 2 3 3 4 2 With my trigger, after this it would automatically create. table id order 1 1 2 3 3 4 4 2 My table obviously l...