Proper use of SqlDataReader

I’ve seen 2 examples of SqlDataReader The first one has a using which manages the disposal of the connection and the used memory, and the second has less code. Which one is to prefer? using (SqlConnection myConnection = new SqlConnection(ConnectionString)) { myConnection.Open(); using (SqlCommand cmd = new SqlCommand(“SELECT Name FROM User;”, myConnection)) { using (SqlDataReader […]

Aggregate and alias on UNPIVOT

How can I rename (alias) the default States (eg: QLD to Quensland) as an output from the below query? Also how can I aggregate (SUM) the states’ forecast to a higher level (eg: National)? SELECT PRODUCT_BK, Month, State, Forecast FROM (SELECT * FROM Forecast_Table) t UNPIVOT (Forecast FOR State IN (SA_NT, QLD, VIC_TAS, WA, NSW_ACT)) […]

How can I avoid encoding when using “FOR XML PATH”?

Using FOR XML PATH encodes my HTML making my hyperlinks useless. I see that others are using the value method to prevent encoding. However, this strips out the table structure I need. I could do a search replace, effectively decoding the html. That does not seem like the right approach. The Company column in this […]

SQL: Select records that have XML tag

I would like to ask how to select only those records from a table that has a column with XML, that in their XML contain a specific tag. Table name is: Searchindex Column with XML: Record XML element: The tag is included only on a few records, not all. XML sample structure: <Record> <ppn>asasaa</ppn> <NAM>asdad</NAM> […]

TFS / SSDT Deployment in Multi Environment Scenario

This is the scenario I am currently experiencing. In Development environment, developers usually make changes in the DEV SQL Server, then they will do a schema compare in Visual Studio 2013 / TFS, update the TFS then check the changes in. Now, say in DEV, there are many stored procedures in a database that refer […]

Need to change the file name (excel) using xp_cmdshell

In a file path (c:\Test\), I have an EXCEL file named as “SundayReport(WTD and MTD).xls“ I need to rename the file to “SundayReport_WTD_MTD).xls” using xp_cmdshell. Kindly suggest me how to change the name.

splitting one column into several

First time poster here, so please forgive mishaps. I have a query in sql that for the sake of simplicity returns duration, business duration and impacted service. The problem lies in the fact that if its impacted service, sometimes it has more than one value in the row. For example it can be ‘crm, payrol […]

Detect SQLServer features from ADO.net

I have an app that creates SQL tables and enables DATA_COMPRESSION=Page. However, that feature is only available when the Enterprise version of SQLServer is being used. There are times when I would like to use this same code on, for example, a SQLLocalDB SKU. For these cases I would like to skip the DATA_COMPRESSION setting […]

SQL import from openrowset mixed type correctly

I have a column in Excel I’m trying to import, and it includes codes like the following: 01166 1166 2354654765432 xx132 I use IMEX=1 and everything imports as TEXT but when I assign the select to temporary table with nvarchar type, the codes with long numbers become incorrect: 1001051 becomes 1.00105e+006 I tried to cast […]

SQL Server CASE WHEN in WHERE clause

I have a query with some joins. One of the joins depends of another value. This is my query: SELECT * FROM CLIIRE A LEFT JOIN CLIIOR B ON A.PTA = B.PTA AND A.ORD = B.ORD AND A.ITE = B.ITE LEFT JOIN CLIORD C ON C.PTA = B.PTA AND C.ORD = B.ORD LEFT JOIN CLIPAC […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.