sql server job puzzle

 job called c# exe

the job failed but not in the logs

the answer - the bug was in the finally block - no error handling

get current connection string- cute

 select

    'data source=' + @@servername +

    ';initial catalog=' + db_name() +

    case type_desc

        when 'WINDOWS_LOGIN' 

            then ';trusted_connection=true'

        else

            ';user id=' + suser_name()

    end

from sys.server_principals

where name = suser_name()

delete duplicate rows

WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY [TagID],[TemplateID] ORDER BY  [TagID],[TemplateID]) AS RN
FROM [TPDocCore].[TagTemplatexRef]
)

DELETE FROM CTE WHERE RN<>1

cursors with variables

need to have values set to the variables before declaring the cursor
i had a declaration section with out settings and this failed

schema issues

someone hardcoded in an old program the wrong schema in an update statement

I went ahead and creates a matching schema and put the tables in a s views (select * )

using like in dynamic sql parameters

add the '%' top the parameter value

set @p_SUBJECT = '%' +  @p_SUBJECT + '%'

sp to help create table for user defined table

ALTER  PROCEDURE [ADMIN].[GET_UDF_TABLE_INFO]

(

       @p_NAME VARCHAR(100)

)

AS

BEGIN

       BEGIN TRY

       select tt.name AS TableName, c.name AS ColumnName,st.name AS DataType

       from sys.table_types tt

              INNER JOIN sys.columns c on c.object_id = tt.type_table_object_id

              INNER JOIN sys.systypes AS ST  ON ST.xtype = c.system_type_id

       where  tt.name  = @p_NAME

  order by c.column_id

      

       END TRY

       BEGIN CATCH

              EXECUTE ADMIN.LOG_DB_ERROR;

              return 0

       END CATCH

END

 order by c.column_id is real important because ado.net is ordinal

"in" in access

in ssql server this

select * from

(select 1 as p union select 2 as p union select 3 as p ) as main

where p in (1,1)

will give you  1 row

in access - you will get 2 rows

raiseerror

with out a try catch : the code will continue

Column name or number of supplied values does not match table definition in false if statement

if @@SERVERNAME  = 'vxcvxc'

begin

insert into Tblah values('tyut')

end

will be raised

dealing with apostraphe's in sql variables

set  @p_blah = replace(@p_ blah,char(39),char(39)+char(39))

CONTAINSTABLE and '&'

set  @p_blah = replace(@p_blah,'&','/&')

latest changes sp's

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
order by modify_date desc

order by on binary cast as bigint

order by cast(sequence_no as bigint)

will not order by correctly

Trying to pass a table-valued parameter with 11 column(s) where the corresponding user-defined table type requires 12 column(s).

select tt.name,count( c.name) from sys.table_types tt
inner join sys.columns c on c.object_id = tt.type_table_object_id
group by tt.name

View dependencies

in ssms is not always correct because it depends on sysdepends



this is more accurate

select t.name as TableWithForeignKey, fk.constraint_column_id as FK_PartNo , c.name as ForeignKeyColumn
from sys.foreign_key_columns as fk
inner join sys.tables as t on fk.parent_object_id = t.object_id
inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where fk.referenced_object_id = (select object_id from sys.tables where name = 'BLAH')
order by TableWithForeignKey, FK_PartNo

top variable needs parentheses

declare @int int = 10

select top (@int) * from

bozo

more things done to improve sql

if force order hint is used - put left joins after inner joins
create indicies on temp tables after inserts

recent things done to an important and complex sql query to improve performance

1. changed cross- apply to cte's
2. added indices that had all the output columns needed instead of using clustered index which forced more reads
3. removed max varchar columns from initial query to avoid key lookups and retrieved them at end of sql

object_id with table created by mistake with schema name

OBJECT_ID(N'blah.[blah.boo]')


brackets around the whole name worked