adrian.facio (1/17/2012)

---

Hi,

I sometimes like to construct the code to execute the procedures with a script like the following, it has been very useful to me lot of times, but it...

DECLARE @TblName varchar(50)

DECLARE @ColValue int

set @ColValue=30

set @tblName='MyTable'

execute sp_executesql N'Select * from '+ QUOTENAME(@tblName) + ' where somecol = @ColVal' , '@ColVal int', @ColValue;

.Netter (1/17/2012)

---

But im getting the following error

"Msg 457, Level 16, State 1, Line 1

Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is...

DDL and sample data would be very useful, click this link to see how best to lay it out. Help us to help you![/url] 😀

Off the bat, your UNION...

Just lucky 😀

As I said, if you're using 2008 then you can get similar...

Gail wrote a blog on the issue you're describing[/url].

Dynamic is the best way to go (or RECOMPILE) - and more vigorous testing! 😛

SQL Kiwi (1/17/2012)

---

See:

http://www.sqlservercentral.com/articles/APPLY/69953/

http://www.sqlservercentral.com/articles/APPLY/69954/

http://msdn.microsoft.com/en-us/library/ms190329.aspx

I tend to prefer CONVERT because it supports explicit styles. ...

Yes. You'd need to go through and change the job name, the table names, schema names and database names. Also, it's...

mic.con87 (1/16/2012)

---

I think I have found the problem. There are 64 dates that have a problem. These can be defaulted to null. The leading zero disappears when you create the...

The most likely issue is that the data has been entered in multiple formats, e.g. DDMMYYYY and MMDDYYYY

This makes the whole task harder because you can't know which way you...

Bit of a stab in the dark: -

USE [msdb]

IF EXISTS (

SELECT job_id

FROM msdb.dbo.sysjobs_view

WHERE name = N'YourJobName'

)

BEGIN

EXEC msdb.dbo.sp_delete_job @job_name = N'YourJobName',

@delete_unused_schedule = 1

END

DECLARE @jobId BINARY (16)

EXEC msdb.dbo.sp_add_job @job_name = N'YourJobName',

@enabled =...

BEGIN TRAN

CREATE TABLE #Dates (CurrentDateFormat int, IncidentDate Date )

INSERT INTO #Dates VALUES (0,'1900-01-01') --All 0's to default to '1900-01-01'

INSERT INTO #Dates VALUES (14041979,'1979-04-14')

INSERT INTO #Dates VALUES (04021983,'1983-02-04')

INSERT INTO #Dates VALUES...

Apologies, I didn't read the whole thread when I replied.

BEGIN TRANSACTION

CREATE TABLE mytable (id INT identity(1, 1),PersonID INT,Unit VARCHAR(10))

INSERT INTO mytable VALUES (1,'Che')

INSERT INTO mytable VALUES (1,'Mat')

INSERT INTO mytable VALUES...

SQL Kiwi (1/16/2012)

---

Jeff Moden (1/16/2012)

---

Generating the data with a script that uses NEWID() avoids another problem... backwards compatibility for restores. Here's what happens when someone using SQL Server 2005...

Your issue is probably due to your index.

Try this replication script: -

BEGIN TRAN

SET NOCOUNT ON

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment

END

--1,000,000 Random rows of data

;WITH CTE AS (

SELECT DATEADD(HOUR,ROW_NUMBER() OVER...