tsql

sql to find non-printable characters in a string

Hi, one of my SQL queries is returning non-printable characters in the data because of which I get an error in the report. Please let me know how can I check if a string has a non-printable characters in T-SQL, so that I can find those rows and fix the data? Thanks in advance. ...

select null as testdate into #temp_table

Hi All, Is there a way to insert/update a datetime value (getdate()) into a temp table created like the following: select id, null as testdate into #temp_table and then later statement: update #temp_table set testdate=getdate() i get error: "cannot convert datetime into int..." Thanks, rod. ...

TSQL Statement to move Name Suffix (Jr, Sr, IV, etc) into another field

I would like to have a TSQL Statement to move Name Suffix (Jr, Sr., IV, etc) into another field. The suffixes I see are JR SR I II III IV V Here is a sample of the data LastName BRUNNING, II BURCH II BUSS, JR. CANI III CHRISTIAN,SR COLVIN Jr COWHERD,JR. I would like the suffix moved out of the LastName field into another field call...

How can I INSERT data into two tables simultaneously in SQL Server?

Let's say my table structure looks something like this: CREATE TABLE [dbo].[table1] ( [id] [int] IDENTITY(1,1) NOT NULL, [data] [varchar](255) NOT NULL, CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC) ) CREATE TABLE [dbo].[table2] ( [id] [int] IDENTITY(1,1) NOT NULL, [table1_id] [int] NOT NULL, [data] [v...

sql help please - get latest activity for customer

Hi All, Given 2 tables CustomerActivity CustomerId, ActivityId, CreatedOnDate 1, 1, 8/1/2010 1, 2, 8/15/2010 2, 1, 7/24/2010 2, 2, 8/15/2010 TempUpdateTable CustomerId, RecentActivityDate 1, NULL 2, NULL How do I fill in the NULLs in TempUpdateTable using the CustomerActivity table? My first attempt didn't pan out: UPDATE [TempUpd...

Executing TRACEON in a Stored Procedure as a public user

Running SQL Server 2000. I have a linked server over ODBC to Providex data files. Never mind mind if you are not familiar with them, the point is that for SQL Server to be able to select from the linked server, an arcane and undocumented TRACE needs to be turned on. The exact command is: DBCC TRACEON(8765) The problem is that i need to ...

can vs2008, T-SQL, and DebugView be integrated for debugging?

scenario: asp.net WebForms + c# code behind + SQL server 2005 (via stored procedures invoked through a c# data access layer). debug.writeline can create output that can be trapped by Mark Russinovich's DebugView utility: http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx DebugView greatly speeds up the debugging process bec...

how to use having clause in sql query in my result set that I am getting

SELECT P.PK_PatientId ,PV.PK_PatientVisitId --, PV.LastUpdated , ISNULL(P.FName,'')+ ', '+ ISNULL(P.LName,'') AS NAME , MAX(TVP.PK_VisitProcedureId) AS PK_VisitProcedureId , MAX(PV.LastUpdated)AS DateSort FROM dbo.M_Patient AS P ...

Strange Problem With Left Join vs Subquery in Select

I have a T-SQL query that is causing performance issues. Its a chunky one but the part that seems to be causing an issue is a simple LEFT JOIN. This can be resolved by removing the left join and using a subquery in the select, however this seems unsatisfactory to me as I can't see why one works quickly and not the other. Theres not a ...

Duplicating records efficiently in tsql

Hey guys, I've a scenario where I have a parent table which has '1 to many' relationships with two or three tables. These child tables again have '1 to many' relationships with more tables and so on. This goes up to 5 to 6 levels of hierarchy. Now, based on single primary key value of the parent table, I want to duplicate all informati...

Multi-part Identifier could not be bound - Update Query SQL Server 2005

Hi, SQL Server novice here. UPDATE dbo.ObjectivesApproved SET dbo.ObjectivesApproved.VAP = 'Y' WHERE ((dbo.Approved.Cri_Group In ('X01' ,'X02' ,'X03' ,'X04' ,'X05' ,'X07' ,'X08' ,'X09' ,'X10' ,'X11' ,'X12' ,'X13' ,'X14'))) gives the following error The multi-part identifier "dbo.Approved.Cri_Group" could not be bound. What's caus...

How do I get records before and after given one?

Thanks everyone for the feedback. This is a favorite. Hi All, Log Table Id, Message, Type 1, John Doe 2, Jane Smith 3, Error 4, Jane Smith Is there a way to get the error record and the surrounding records? Find all Errors and the record before and after them. Thanks, rod. ...

Percentage row makes up of total where each row determine by group by clause

I'm sure this is a stupid n00b question, but I have the following results table (which is created through a long-winded query I only half understand) and what I'd like is to be able to add in two columns that work out the row percentage relative to the total: Sample results table: CREATE TABLE #temp (category varchar(30) ,count_people ...

SQL Server efficiently filter rows where times are not near another table's times

I have two tables and I'm looking for the rows in one table where a time column is not near any of the values in another table's time column. (Near is defined as within a minute). Here's a code sample: create table temp1 ( id int identity primary key, value datetime not null ) GO create index ix_temp1 on temp1(value, id); GO ...

help with reducing the number of connections to the database

I have a list of strings that are just invoice numbers. I am enumerating through this list to get the details of each invoice from the database. This list can easily be 700 to 1000 in size. the way I am doing it now results in 700-1000 connections to the database. this is taking too long to complete is there a better way to do this tha...

SQL: Building where clause

Is there a way to get build a WHERE clause on the fly in a sql statement? This code is within a Stored Procedure. I have x amount of parameters and each parameter's default value is NULL SELECT * FROM MyTable m WHERE IF(NOT(@Param1 IS NULL)) m.Col1 = @Param1 END IF AND IF(NOT(@Param2 IS NULL)) m.Col2 = @Param2 END...

User Defined Function Best Practice

Hi All, I am contemplating using some user defined function calls within some of my queries instead of using a bunch of inline case statements. The inline statements will probably perform better, but the functions make it so much easier to view and possibly maintain. I just wanted to get an idea of what the typical best practice is fo...

How to design Query for creating dynamic columns from rows

I have data Table1 ID Name ----------- 1 n1 2 n2 3 n4 Table2 FID YearS Val ---------------------- 1 2008 Up 1 2009 Down 1 2010 Up 2 2000 Up 2 2001 Down 2 2002 Up 2 2003 Up 3 2009 Down 3 2010 Up I want to return data in following format:...

SQLCE: Find next available date

I have a SQLCE table with a date field. Any given date could have one or more records. My client would like the input form to default to the next date (starting from and including the current date) that doesn't yet have a record. I'm having trouble wrapping my head around a query to accomplish this. Googling I've found a couple snippets,...

Is it a good idea to use varchar(max) as a stored procedure argument?

I have a stored procedure that takes a string of a pipe delimited array of IDs and parses them out. I want to have this happen in a transaction, so don't want to pass them in one at a time. If I use varchar(max) as to not limit the size of the argument passed in, will this cause problems? I don't see the limit being hit, but I also don'...