TSQL Reverse FOR XML Encoding
I am using FOR XML in a query to join multiple rows together, but the text contains quotes, "<", ">", etc. I need the actual character instead of the encoded value like """ etc. Any suggestions? ...
tsql
SQL Server - Adding a string to a text column (concat equivalent)
How do you add a string to a column in SQL Server? UPDATE [myTable] SET [myText]=' '+[myText] That doesn't work: The data types varchar and text are incompatible in the add operator. You would use concat on MySQL, but how do you do it on SQL Server? ...
An object or column name is missing or empty.
I am getting the following error An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. for the query show below: CREATE PROC [dbo].[Sp_T...
With AS error in SQL
i am getting the error as shown below : Msg 156, Level 15, State 1, Procedure Sp_Table1, Line 22 Incorrect syntax near the keyword 'AS'. while executing this SQL: WITH myCTE AS (Select mci.* from view_name AS si JOIN merch_catalog_ipt_view_name AS mci ON mci.view_id = si.view_id AND mci.resolved_view_name_id = si.view_name_id A...
The multi-part identifier "si.shi" could not be bound.
i am getting the error as shown below : The multi-part identifier "si.shi" could not be bound. while executing this SQL: ;WITH myCTE AS (Select mci.* from view_name AS si JOIN merch_ctlg_ipt_view_name AS mci ON mci.view_id = si.view_id AND mci.resolved_view_name_id = si.view_name_id AND mci.ctg_ipt_event_id = @ctg_ipt_event_id ...
Format a Date into Mai 2010 in T-SQL
Dear interested reader, how do I format a date (smalldatetime) in Mai 2010 (i.e. 06.05.2010 => Mai 2010 or 06.10.2009 => October 2009) thx a lot... greetings ...
TSQL granting read and write permissions to all tables
Is setting EXEC sp_addrolemember N'db_datareader', N'User' EXEC sp_addrolemember N'db_datawriter', N'User' Is the same as granting SELECT/INSERT/DELETE/UPDATE on all tables to User GRANT SELECT ON [Schema].[Table] TO [User] GRANT INSERT ON [Schema].[Table] TO [User] GRANT DELETE ON [Schema].[Table] TO [User] GRANT UPDATE ON [Sch...
Is it possible to get the SQL single column result as a comma separated string?
Possible Duplicate: How do I Create a Comma-Separated List using a SQL Query? I am working on a web application. I need the SQL query of single column selection like select recordid from device where accountid in (1,2)) I need this result to be formatted comma separated string from SQL. ...
TSQL, help with small query about user's age
I have a table with registered users, in which i save year as varchar values simply because i take just an year. I want to create pie chart with ages to show which users are more likely to register. Query below gives me count of user ages which appear more than 5 times in Table to avoid small results. While these small results, below "...
Find high and low prices in sql
In this sample database there are two tables, products and prices. The goal is to find the highest and the lowest price for each product. The price table can have either zero, one or two rows per product. create table products( id int, name nvarchar(50) ) create table prices( productId int, price int ) insert into pr...
TSQL help with statistics query
I've made a post here a link , asking some question about how to make statistics for number of users with from same age group so i can show pie chart. I want to take query in older post to higher level with percentages along with values... Means i to have count(userID) of certain age group / count(userID) of all * 100 to get percentage....
Date range in vb.net
I have following code and I can filter data on grid but when I pick same date in 2 datepicker it shows nothing. How can I fix the problem. Any help will be appreciated. con = New SqlCeConnection(constring) con.Open() cmd = New SqlCeCommand("SELECT * FROM tblMeter WHERE (Date >= @startDate) AND (Date < @endDate)", con) Dim p...
Title: CTE error
I am getting these errors while executing the following SQL please help me ;WITH myCTE AS (Select mcisi.* from coke_packaged_item AS spi JOIN coke_item AS si ON si.coke_id = spi.coke_id AND si.coke_item_id = spi.coke_item_id AND si.shipper_flag = 'n' JOIN merch_cat_import_coke_item AS mcisi ON mcisi.coke_id = si.coke_id AND m...
How can I use two different databases with Linq to SQL in Linqpad?
I'm starting out with Linq To SQL, fiddling around with Linqpad and I'm trying to duplicate a SQL script which joins on tables in separate databases on the same server (SQL Server 2008). The TSQL query looks approximately like this: using MainDatabase go insert Event_Type(code, description) select distinct t1.code_id, t2.desc from O...
Query Total Amounts for Orders where the amount of each item was greater than >100
Using the following two tables on SQL Server 2005, how would I write a query to return the First_Name, Last_Name, Order_Date and total amount of an order where any order line item amounts for any order (OD_Amount) are greater than 100. Orders Order_ID First_Name Last_Name Order_Date Order_Details Order_Detail_ID Order_ID OD_Item_N...
SQL Server: Is there an "IF EXISTS" test for a linked server?
I want to be able to programmatically (in T-SQL) check if a specific linked server already exists for my current server and database (so that if the link doesn't exist yet, I can create it). I tried stuff like this: IF OBJECT_ID('myserver\devdb_1') IS NULL BEGIN PRINT 'Does not exist, need to create link' EXEC master.dbo.sp_addlinke...
Join Unlike Tables
I have 2 unlike tables and a large set of subqueries that have a key for each of those two tables. I need to join the two tables to each subquery. Table 1 Table1ID Table 2 Table2ID Subqueries Table1ID Table2ID Is there any way to join everything together? I have tried something similar to SELECT Table1.Table1ID, Table2.Table2ID...
Modifying a SQL query within the Select statement
I have a stored proc that is called from my asp.net page. The field "RecordUpdated" returns TRUE or FALSE. I need that column to return YES (if true) or NO (if false) How would I do that? SET @SQL = 'SELECT RecID, Vendor_Number, Vendor_Name, Invoice_Number, Item_Number, RecordAddDate, RecordUpdated FROM Vendor_Invoice_Log' SET @SQL = @...
How to compute the modulus of a float in TSQL?
The modulus function in Microsoft SQL Server only works on certain data types. According to the MSDN Article [1] on the modulus operator, you normally would use modulus like this... dividend % divisor dividend Is the numeric expression to divide. dividend must be a valid expression of any one of the data types in the integer and mon...
How do I pass a column as a parameter in Stored Procedure?
How do I pass and use the column name to retrieve a bigint variable in the actual column? DECLARE @personID BIGINT, DECLARE @queryString varchar(500) Set @queryString = 'Select @personID = ' + @PersonColumnID + ' from dbo.Loss_Witness where WitnessID = @witnessID' exec(@queryString) Error message states "Must declare variable '@perso...