SQL Server: Multiple table joins with one or more WHERE clauses

I am running SQL Server and am trying to join multiple tables from CRM application but I can’t get the result I need. I found this article SQL Server: Multiple table joins with a WHERE clause containing info close to what I am looking for, but I couldn’t get it to work either. I have […]

How to escape double quotes in bcp sql query in stored procedure sql server

The following is my stored procedure code: Alter PROCEDURE [dbo].[ConvertToFile] @TempFileName varchar(8000) AS BEGIN SET NOCOUNT OFF; DECLARE @bcpFileCmd varchar(8000) SET @bcpFileCmd= ‘bcp “SELECT id,full_Name,message from NotesTable ” queryout ‘+@TempFileName+’ -t, -c -T’ EXEC master..xp_cmdshell @bcpFileCmd END BCP dumps all the data from NotesTable in file. But I need to enclose the message results with […]

SSIS icons in Visual Studio 2013 lost color

On installing Visual Studio 2013 and the required SSIS components, I found that all the icons for the SQL Server toolbox were showing up in Black and White. I found the solution for VS 2012 where I can bring some of the color back here: Is it possible to change icons in Visual Studio 2012? […]

Error is“ string or binary data truncated”

If txt file is uploaded then is successfullly. But large txt file is uploaded then error occurs. Error is” string or binary data truncated” protected void Button1_Click(object sender, EventArgs e) { FileUpload1.SaveAs(Server.MapPath(“~\\upload\\”) + FileUpload1.FileName); f = Path.GetFileNameWithoutExtension(FileUpload1.FileName); st = Path.GetFileName(FileUpload1.FileName); ml = Server.MapPath(“~\\upload\\”); nww = FileUpload1.FileName; ty = Path.GetExtension(FileUpload1.FileName); kk = ml + f + […]

Insert Into Table1 Select x, y, z From Table2 Where x = 1

In mssql is it possible to run this query: Insert Into Table1 Select x, y, z From Table2 Where x = 1 when Table1 has an id column that is identity & auto-incrementing I am getting the following error: Msg 8101, Level 16, State 1, Line 1 An explicit value for the identity column in […]

java program only gives first column of database table “Users” MSSQL

I am having an issue where i connect to the database,but cannot retrieve more than the first column even though my query statement is “Select * from Users” below is my code: import java.sql.*; public class Connecttodb { public void dbConnect(String db_connect_string,String db_UserID,String db_UserPass) { try{ Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”); Connection conn = DriverManager.getConnection(db_connect_string,db_UserID,db_UserPass); System.out.println(“connected”); Statement statement= conn.createStatement(); […]

SQL Server index on multiple “LIKE @filter + '%'” filters

When creating LIKE filters (I’m doing StartsWith logic, not Contains) do I need to do anything special with the indexes, or just create standard nonclustered index? Query: SELECT * FROM Contact WHERE AccountUID = @AccountUID AND (FirstName LIKE @Filter + ‘%’ OR LastName LIKE @Filter + ‘%’ OR Company LIKE @Filter + ‘%’) Proposed Index: […]

How do you efficiently filter out older versions in a table representing multiple versions of the same logical object?

In SQL Server, I have a table with rows that represents “Resources.” When a resource changes, it gets another row in the same table, with the same “ID” field. (“ID” is unfortunately named, and doesn’t actually represent a unique key.) So the same logical Resource might have multiple columns, representing its change over time. Each […]

consolidate duplicate records using stored proc

I have a database of persons that has some duplicates I need to clean up. I have a stored proc that will merge 2 people together, but I don’t know how to execute this from a loop somehow Although there are a bunch of fields and tables involved, for simplicity’s sake let’s say there’s just […]

Does SELECT account for time zone modification in WHERE statement?

event_time is stored in UTC in the database. In trying to account for those “near the end of the month” hours so as to make sure all the results are in the local time, not the UTC, here is a simplified version of my code: SELECT MONTH(event_time) AS ‘month’ FROM <table1> INNER JOIN <table2> ON […]

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