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. ...
So I have SQL Server datetime field, and in PHP I want to use this as a UNIX TIMESTAMP, because I want to use it with strtotime, in order to add 14 days into it. Eg. If it is stored in the SQL Server database as: 2010-07-27 13:12:22.040 I want to add 14 days into it to become 2010-08-10 13:12:22.040 How would I do that using PHP wi...
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 ...
Does anyone know how to write a script in stored proc to run the table based on the variable (or will it possible to do so?)? for example: I have 3 tables name called customer, supplier, and support when user input 1, then run table customer, 2 table supplier and 3 table support declare @input int; if @input =1 begin declare @table ...
Hi, I am studing SQL, and I would like your advice, to see if my code is written properly, or better way to do it. This script create: a table to store IPs address that are Black Listed a sproc to allow spitting an IP address in 4 octet a SPROC that allow to check if a IP is Black Listed or not Please let me know! thanks! -- Black Lis...
I got a two tables tblGoals -name -url -siteID tblUrlCollection -url -siteID How do I count how many times every "url" from tblGoals is in tblUrlCollection using "url" and "siteID" as input? Would like to use like or something so I can add wildcards to the end of the "url" input parameter. Please help me out - thanks a lot. Perfor...
Hello, can I somehow call a stored proc without params, even though it normally has params? Thanks :-) ...
I've heard that in some programming languages it is faster to check if the length of a string is 0, than to check if the content is "". Is this also true for T-SQL? Sample: SELECT user_id FROM users WHERE LEN(user_email) = 0 vs. SELECT user_id FROM users WHERE user_email = '' ...
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...
I have a problem regarding this query. I want to get the applicant latest position (please see below query). The result should be like this: aId Position Startdate Enddate 154 Web Developer 2008-04-07 NULL 155 Analyst 2008-06-12 2009-06-12 156 Quality Controller ...
I am trying to sum INTERVAL. E.g. SELECT SUM(TIMESTAMP1 - TIMESTAMP2) FROM DUAL Is it possible to write a query that would work both on Oracle and SQL Server? If so, how? Edit: changed DATE to INTERVAL ...
Here is the SQL: CREATE TABLE dbo.TmpFeesToRules1(Name varchar, LookupId int) INSERT INTO dbo.TmpFeesToRules1(Name, LookupId) SELECT DISTINCT Name, 0 FROM Lending.Fee UNION SELECT DISTINCT Name, 0 FROM Lending.ApplicationFee INSERT INTO dbo.tblLookup (LookupType, LookupCode, LookupDesc, EditFlag, DeletedFlag, DefaultFlag) SELECT 'FEE_...
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 ...
I have a one row table returned from a query that looks something like this [Date1] [Date2] [Date3] [Date4] [Date5] [Date6] and I want all the Dates to stack up like this [Date1] [Date2] [Date3] [Date4] [Date5] [Date6] How would I go about doing this without a bunch of separate queries and union statements? I have tried playing a...
I have an procedure that runs on a control's AfterUpdate event: Private Sub cmdDelCountry_AfterUpdate() Dim strID As String strID = Me.ID Dim strCase As String strCase = 1 Dim cmdCommand As New ADODB.Command With cmdCommand .ActiveConnection = CurrentProject.Connection .CommandType = adCmdStoredProc .CommandText = "uspSal...
How can I convert a day [1-31] and a month [1-12] and a year (all int), to a date serial IN SQL (without converting to varchar)? ...
Hello. I am inserting a web form into the database and so using parameterized queries. I have a CheckBoxList. How can I iterate over CheckBoxList, create an insert statement for every checked thing (many-to-many), and keep this parameterized and executed in one swoop? I have this right now: string query = "INSERT INTO resources (url, s...
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...
Using SQL Server and T-SQL, how can I update ALL tables tables with a specific name but different schemas under the same database? :Update: The updates include adding/deleting/modifying columns as well as changing specific values of rows. I want to be able to deploy changes to a table across all schemas. Using the SQL Server Managemen...