USE master GO ALTER PROCEDURE [dbo].[sp_searchit] ( @val varchar(255), @escape char(1) = NULL, @table sysname = NULL ) AS BEGIN SET NOCOUNT ON DECLARE @cols TABLE ( id int identity(1,1), colname sysname, tabname sysname ) DECLARE @results TABLE ( id int identity(1,1), colval varchar(max), colname sysname, tabname sysname ) DECLARE @id int , @colname sysname , @tabname sysname , @sql nvarchar(4000) INSERT INTO @cols ( colname, tabname ) SELECT A.name "colname" , B.name "tabname" FROM syscolumns A (NOLOCK) INNER JOIN sysobjects B (NOLOCK) ON A.id = B.id WHERE A.type IN ( SELECT B.type FROM systypes B (NOLOCK) WHERE B.name IN ( 'char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext', 'sysname' ) ) AND B.type = 'U' AND ( @table IS NULL OR B.name = @table ) SELECT @id = max(id) FROM @cols WHILE @id > 0 BEGIN SELECT @colname = A.colname , @tabname = A.tabname FROM @cols A WHERE A.id = @id SET @sql = 'SELECT TOP 1 [' + @colname + '] "result", ''' + @colname + ''' "column", ''' + @tabname + ''' "table" FROM [' + @tabname + '] WHERE [' + @colname + '] LIKE ''%' + @val + '%''' IF @escape IS NOT NULL BEGIN SET @sql = @sql + ' ESCAPE ''' + @escape + '''' END INSERT INTO @results ( colval, colname, tabname ) EXEC sp_executesql @sql SET @id = @id - 1 END SELECT * FROM @results END --sp_searchit GO
Year: 2012
‘The SECRET’ to fast SSIS packages!
Nope, you don’t need no stinkin’ law of attraction, yoga, contemplative breath prayers, 12 steps to paganism, or 3 points and a poem to follow the road to enlightened performance. Just follow this simple formula:
Step 1: Rewrite them as stored procedures…
c# display SqlCommand statement
public static string cmdToString(SqlCommand cmd)
{
var c = ” “;
var s = cmd.CommandText;
for (int i = 0; i < cmd.Parameters.Count; i++) { s += c + cmd.Parameters[i].ParameterName; s += " = " + cmd.Parameters[i].Value; c = ", "; } return (s); }
Views in Drag
Ran into a real winner today. For a completely unexpected reason, several views began timing out and not returning results.
The only thing that worked was recreating the views.
I suspect a possible cause is that the views reference synonyms that in turn reference objects across a linked server. The linked server options had been modified right before the views stopped working.
Dedupe based on compound key and timestamp
DECLARE @sourcetab TABLE ( id1 int, id2 int, update_on smalldatetime, val varchar(50) )DECLARE @targettab TABLE ( id1 int, id2 int, update_on smalldatetime, val varchar(50) )
insert into @sourcetab (id1, id2, update_on, val)
SELECT 10, 1, ‘20120110’, ‘testrec1’
UNION SELECT 10, 1, ‘20120201’, ‘testrec2’
UNION SELECT 5, 2, ‘20120201’, ‘testrec3’
UNION SELECT 5, 1, ‘20120201’, ‘testrec4’
UNION SELECT 5, 1, ‘20120205’, ‘testrec5’
UNION SELECT 12, 18, ‘20120201’, ‘testrec6’
UNION SELECT 12, 18, ‘20120205’, ‘testre7’
UNION SELECT 12, 5, ‘20120201’, ‘testrec8’
UNION SELECT 17, 3, ‘20120201’, ‘testrec9’
UNION SELECT 18, 4, ‘20120201’, ‘testrec10’insert into @targettab (id1, id2, update_on, val)
SELECT 10, 1, ‘20120101’, ‘testrec01’
UNION SELECT 12, 5, ‘20120101’, ‘testrec02’
UNION SELECT 20, 19, ‘20120101’, ‘testrec03’–not deduped
SELECT A.id1, A.id2, A.update_on, A.val
FROM @sourcetab A
ORDER BY A.id1, A.id2, A.update_on–deduped with most current wins
SELECT A.id1, A.id2, A.update_on, A.val
FROM @sourcetab A
WHERE A.update_on = ( SELECT max(A1.update_on)
FROM @sourcetab A1
WHERE A1.id1 = A.id1
AND A1.id2 = A.id2 )–update with most recent
UPDATE A
SET A.val = B.val
, A.update_on = B.update_on
FROM @targettab A
INNER JOIN @sourcetab B
ON A.id1 = B.id1
AND A.id2 = B.id2
WHERE B.update_on > A.update_on
AND B.update_on = ( SELECT max(B1.update_on)
FROM @sourcetab B1
WHERE B1.id1 = B.id1
AND B1.id2 = B.id2 )–insert new recs
INSERT INTO @targettab ( id1, id2, update_on, val )
SELECT A.id1, A.id2, A.update_on, A.val
FROM @sourcetab A
WHERE A.update_on = ( SELECT max(A1.update_on)
FROM @sourcetab A1
WHERE A1.id1 = A.id1
AND A1.id2 = A.id2 )
AND NOT EXISTS ( SELECT TOP 1 1
FROM @targettab B
WHERE B.id1 = A.id1
AND B.id2 = A.id2 )–final result
SELECT A.*
FROM @targettab A
ORDER BY A.id1, A.id2, A.update_on
Passing parameterized string to OpenQuery
OpenQuery can only accept a static string as an argument for the sql statement to execute over the remote link.
The only way around this is to execute the OpenQuery as a dynamic sql string and concatenate the parameters as part of the “static” string.
The results can then be piped into a temp table or table variable. The trick is that “SELECT INTO” will not work, so the table has to be defined beforehand to match the result set of the dynamic sql and use “INSERT INTO”.
The most obnoxious bit is the crazy nested tick-quotes…
DECLARE @mycodes AS TABLE (
[id] [int] NOT NULL,
[code] [varchar](25) NULL,
[description] [varchar](80) NULL
)declare @sql nvarchar(4000)
, @myparm varchar(255)SET @myparm = ‘someval’
SET @sql = ‘select id, code, description from openquery([MYDBLINK],”EXEC mydb.dbo.mystoredproc @myparm = ”” + @localval + ””” )’
print @sql
insert into @codes
EXEC sp_executesql @sqlselect * from @codes
MonoTouch Bare Bones
iOS MonoTouch Developement
Will Need:
1. Mac running OSX 10
2. Install latest version of Apple’s Developer Tools / Xcode
https://developer.apple.com/technologies/tools/
3. Once installed, in Xcode go to “XCode”–>”Preferences”–>”Downloads” and install “Command Line Tools” (will need this if you want to “make” any bindings to additional Objective-C components).
4. Download and install MonoTouch. This will include: 1) Mono, an open source .NET implementation; 2) MonoDevelop, an open source IDE similar to Visual Studio; and, 3) MonoTouch, the proprietary set of libraries that allow you to compile C# code into iOS apps.
5. Your now ready to do the first tutorials found here:
http://docs.xamarin.com/ios
To this point you can write code and run it in the simulator.
To actually deploy it to a device you will need a licensed copy of Monotouch and one of the following:
Apple Developer Program ($99 a year) – provision ad-hoc deployment on up to 100 different devices for testing and distribute/sell apps via app store.
https://developer.apple.com/programs/ios/
Apple Enterprise Program ($299 a year) – deploy .ipa bundled apps to corporate owned or corporate employee owned devices.
Error creating database diagrams
Error: Cannot insert the value NULL into column ‘diagram_id’, table ‘mydb.dbo.sysdiagrams’; column does not allow nulls. INSERT failes. The statement has been terminated. The ‘sp_creatediagram’ procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead (.Net SqlClient Data Provider)
Fix: Add the following trigger
CREATE TRIGGER tr_sysdiagrams_i ON dbo.sysdiagrams INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;DECLARE @id int
SELECT @id = max(A.diagram_id) + 1
FROM dbo.sysdiagrams AINSERT INTO dbo.sysdiagrams
( name
, principal_id
, diagram_id
, [version]
, [definition] )
SELECT name
, principal_id
, @id
, [version]
, [definition]
FROM inserted
END –tr_sysdiagrams_i
GO
Explanation:
I seem to remeber getting this same error when I was using the SQL Server 2008 Management Studio (SSMS) on a SQL Server 2005 instance. I got this again using the SSMS 2012 on a 2008R2 instance. Either the diagram_id is supposed to be an identity, or the id should be generated by SSMS. In either case, just making the column allow NULLs leads to other problems. So either have to recreate the table with the column as an identity, or add this trigger.
How to find objects modified since a certain date
SELECT *
FROM sys.all_objects
WHERE modify_date > ‘20120628 00:00:00’