Cannot connect to local server using servername/instancename

I have reached a point where I need to use Excel to pull information from a SQL Server instance that is running on the same machine. As I will not be the only user to use Excel I need to use the MyComputer1234\MSSQLSERVER, 1433 details to connect however it cannot find the server? Other users […]

Gridview is updated with SqlCommand but then edit and delete are no longer working

I have a gridview (bound to a SQL Server database), a textbox and a button. I can delete or edit the values in the gridview. All works fine. No need to put any code myself, it is there by default. However in the textbox I can put a value with a button to narrow down […]

Parse XML data to SQL with multiple child node data

I have a xml file having following data <SearchProgramsResponse xmlns=”http://api.abc.com/2011-03-01/”> <page>1</page> <items>50</items> <total>3129</total> <programItems> <programItem id=”7779″> <name>Coolblue NL</name> <adrank>5.4</adrank> <categories> <category id=”40″>Cell, Phone &amp; Fax</category> <category id=”11″>Internet services</category> </categories> </programItem> <programItem id=”7780″> <name>ABC NL</name> <adrank>2.4</adrank> <categories> <category id=”42″>Cell</category> <category id=”12″>services</category> </categories> </programItem> </programItems> </SearchProgramsResponse> I want to Transfer this XML data to SQL server tables. […]

Insert default value and extract value from other into new table in SQL server?

I have a TimeSheet table and want to insert default values when pageload in asp.net by inserting ID from Employee table and current date, default value 1, but I get an error: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘,’. Code: create PROCEDURE [dbo].[sp_TimeSheet_Init] @EmpID int, @TimeCheckDate datetime, @Day1 int,@Day2 int,@Day3 […]

PIVOT returns only one row

I’ve been creating a pivot that turns the string into a table, and fortunately it did. But it only contains one row (the first record in the table). Here’s the code: SELECT COL1,COL2,COL3…. etc. FROM ( SELECT LEFT(CA.val,CHARINDEX(‘=’,CA.val)-1) ColumnName, SUBSTRING(CA.val,CHARINDEX(‘=’,CA.val)+1,100) Value FROM (MYTABLE) CROSS APPLY (MYFUNCTION)(MYTABLECOLUMNNAME,’|’) CA ) PD PIVOT ( MAX(Value) FOR ColumnName IN […]

DataBinding is very slow

I am using Windows application c#. When I bind the data table to Datagridview it gets slow and I am getting a SQL connection timeout error. At the same time my data table has bulk records. How can I solve this problem? Code: private void Window_Loaded(object sender, RoutedEventArgs e) { con.StatisticsEnabled = true; con.Open(); DataTable […]

T-SQL – How to count the total number of rows containing a substring inside a column for numerous multiple substrings in only one request?

This web site has been so useful to me for years. There was always the question (and the answer) to my SQL questions. This time, I couldn’t find it. So here is my question: For a search engine with numerous criteria, I have several columns containing several data / substrings in each. Say : Ref […]

Using SSIS variable (@) as part of UPDATE in `Execute Sql Server Task'?

We have an SSIS project that reads from a text file and inserts to a sql server table. The Flat File Connection Manager for the Flat File Source uses a variable value as ConnectionString property. So essentially, it’s expression ConnectionString = @[User::FileName]. This is working fine, and it’s reading the file from the variable into […]

How to display the sql data horizontal using Pivot

I have attached the screenshot of the sql query and the results table, Here i would to view the table Horizontal. like |iFruit|iPlant|Pat on the back|The Incredebles| |2 | 1 | 1 | 1 | Please help me to write the pivot function to get the above format.

Entity Framework 6 insertion is too slow

In the first place, I am very aware that there are many other articles out there sharing the exact topic. However, those articles are old. For example, Entity Framework is Too Slow. What are my options? this one is 4 years ago, and I assume the framework has released at least one major version update […]

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