Author: sqldba

Posted on by — Leave a comment

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 @sql


select * from @codes

Posted on by — Leave a comment

MonoTouch Bare Bones

using System;
using MonoTouch.Foundation;
using MonoTouch.UIKit;
namespace myNameSpace
{
   public class Application
   {
      static void Main(string[] args)
      {
         UIApplication.Main(args, null, “AppController”);
      }
   }
   [Register (“AppController”)]
   public class AppController : UIApplicationDelegate 
   {
      UIWindow window;
      public override bool FinishedLaunching(UIApplication app, NSDictionary options)
      {
         // create the main view controller
         var vc = new MainViewController();
         // create main window and add main view controller as subclass
         window = new UIWindow(UIScreen.MainScreen.Bounds);
         window.AddSubview(vc.View);
         window.MakeKeyAndVisible();
         return( true );
      }
      public override void OnActivated(UIApplication application) 
      {
         //required override on iOS 3.0
      }
   }
   [Register]
   public class MainViewController : UIViewController 
   {
      public override void ViewDidLoad()
      {
         base.ViewDidLoad();
         //custom code can start here…
         Console.WriteLine(“App Loaded”);
      }
   }
}//myNameSpace
Posted on by — Leave a comment

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.

https://developer.apple.com/programs/ios/enterprise/

Posted on by — Leave a comment

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 A


     INSERT 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.

Posted on by — Leave a comment

Remote table-valued function calls are not allowed

Error: Remote table-valued function calls are not allowed

Fix: use “WITH (NOLOCK)” instead of just “(NOLOCK)

Explanation:  When issuing a select that has uses four part naming to address the table and the table has a (nolock) hint, the t-sql will fail to execute with the error “Remote table-valued function calls are not allowed.”

If you execute the query with 3 or 2 part naming it runs without error.

To get the query to work using 4 part naming you have to put the “with” keyword before the (nolock).

http://connect.microsoft.com/SQLServer/feedback/details/126162/remote-table-valued-function-calls-are-not-allowed

Posted on by — Leave a comment

sp_CodeGenerator

USE [datastage]
GO
/****** Object:  StoredProcedure [dbo].[sp_CodeGenerator]    Script Date: 09/30/2013 13:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
—      Author: John Olsen
— Create date: 20120323
— Update date: 20120425
— Review date: 
— Description: Generate column related t-sql
—              and c# code for specified table
—              because i have had carpul
—              tunnel surgery once already
— =============================================
ALTER PROCEDURE [dbo].[sp_CodeGenerator] ( @tabname varchar(128) )
AS
BEGIN
————————————-
–DECLARE @tabname varchar(128)
–SET @tabname = ‘truck_transfers’
————————————-
SET NOCOUNT ON;
DECLARE @types TABLE ( xtype tinyint
                     , sql_type varchar(128)
                     , csharp_type varchar(30)
                     , db_type varchar(128)
                     , use_len tinyint
                     , use_prec tinyint
                     , use_scale tinyint
                     , type_class tinyint )
       INSERT INTO @types (xtype, sql_type, csharp_type, db_type, use_len, use_prec, use_scale, type_class)
            SELECT 127, ‘bigint’, ‘int’, ‘BigInt’,0,0,0,1
      UNION SELECT 173, ‘binary’, ‘byte[]’, ‘Binary’,0,0,0,1
      UNION SELECT 104, ‘bit’, ‘int’, ‘Bit’,0,0,0,1
      UNION SELECT 175, ‘char’, ‘string’, ‘Char’,1,1,0,2
      UNION SELECT 40, ‘date’, ‘string’, ‘VarChar’,1,1,0,3
      UNION SELECT 61, ‘datetime’, ‘string’, ‘DateTime’,0,0,0,3
      UNION SELECT 42, ‘datetime2’, ‘string’, ‘DateTime’,0,0,0,3
      UNION SELECT 43, ‘datetimeoffset’, ‘string’, ‘VarChar’,1,1,0,3
      UNION SELECT 106, ‘decimal’, ‘float’, ‘Decimal’,0,1,1,1
      UNION SELECT 62, ‘float’, ‘float’, ‘Float’,0,1,1,1
      UNION SELECT 240, ‘geography’, ‘SqlGeography’, ‘Geography’,0,0,0,4
      UNION SELECT 240, ‘geometry’, ‘SqlGeometry’, ‘Geometry’,0,0,0,4
      UNION SELECT 240, ‘hierarchyid’, ‘string’, ‘NVarChar’,1,1,0,4
      UNION SELECT 34, ‘image’, ‘byte[]’, ‘Image’,0,0,0,4
      UNION SELECT 56, ‘int’, ‘int’, ‘Int’,0,0,0,1
      UNION SELECT 60, ‘money’, ‘float’, ‘Money’,0,0,0,1
      UNION SELECT 239, ‘nchar’, ‘string’, ‘NChar’,1,1,0,2
      UNION SELECT 99, ‘ntext’, ‘string’, ‘NText’,0,0,0,2
      UNION SELECT 108, ‘numeric’, ‘float’, ‘Decimal’,0,1,1,1
      UNION SELECT 231, ‘nvarchar’, ‘string’, ‘NVarChar’,1,1,0,2
      UNION SELECT 59, ‘real’, ‘float’, ‘Real’,0,1,1,1
      UNION SELECT 58, ‘smalldatetime’, ‘string’, ‘SmallDateTime’,0,0,0,3
      UNION SELECT 52, ‘smallint’, ‘int’, ‘SmallInt’,0,0,0,1
      UNION SELECT 122, ‘smallmoney’, ‘float’, ‘SmallMoney’,0,0,0,1
      UNION SELECT 98, ‘sql_variant’, ‘object’, ‘Variant’,0,0,0,4
      UNION SELECT 231, ‘sysname’, ‘string’, ‘VarChar’,1,1,0,2
      UNION SELECT 35, ‘text’, ‘string’, ‘Text’,0,0,0,2
      UNION SELECT 41, ‘time’, ‘string’, ‘VarChar’,0,0,0,3
      UNION SELECT 189, ‘timestamp’, ‘string’, ‘Timestamp’,0,0,0,3
      UNION SELECT 48, ‘tinyint’, ‘int’, ‘TinyInt’,0,0,0,1
      UNION SELECT 36, ‘uniqueidentifier’, ‘SqlGuid’, ‘UniqueIdentifier’,0,0,0,4
      UNION SELECT 165, ‘varbinary’, ‘byte[]’, ‘VarBinary’,1,1,0,4
      UNION SELECT 167, ‘varchar’, ‘string’, ‘VarChar’,1,1,0,2
      UNION SELECT 241, ‘xml’, ‘string’, ‘Text’,0,0,0,2
–SELECT * FROM @types
DECLARE @cols TABLE ( id int identity(1,1)
                    , colname varchar(128)
                    , parmname varchar(128)
                    , sql_type varchar(128)
                    , db_type varchar(128)
                    , csharp_type varchar(30)
                    )
DECLARE @i int
      , @crlf char(2)
      , @classname varchar(128)
      , @colname varchar(128)
      , @parmname varchar(128)
      , @sql_type varchar(128)
      , @db_type varchar(128)
      , @csharp_type varchar(30)
      , @s_dec varchar(8000)
      , @s_assn varchar(8000)
      , @s_assnB varchar(8000)
      , @s_collist varchar(8000)
      , @s_parmlist varchar(8000)
      , @c_dec varchar(8000)
      , @c_update varchar(8000)
      , @c_pop varchar(8000)
      , @c_parm varchar(8000)
      
SET @crlf = char(13) + char(10)
     INSERT INTO @cols ( colname, parmname, sql_type, db_type, csharp_type )
          SELECT B.name “colname”
               , ‘@’ + B.name “parmname”
               , (case when C.use_scale = 1 then C.sql_type + ‘(‘ + cast(B.prec as varchar(30)) + ‘,’ + cast(B.scale as varchar(30)) + ‘)’
                       when C.use_prec = 1 then C.sql_type + ‘(‘ + (case when B.prec = -1 then ‘max’ else cast(B.prec as varchar(30)) end) + ‘)’
                       else C.sql_type end) “sql_type”
               , (case when C.use_len = 1 and B.prec > 0 then ‘SqlDbType.’ + C.db_type + ‘, ‘ + cast(B.prec as varchar(30))
                       else ‘SqlDbType.’ + C.db_type end) “db_type”
               , C.csharp_type
            FROM sysobjects A (NOLOCK)
      INNER JOIN syscolumns B (NOLOCK)
              ON A.id = B.id
      INNER JOIN @types C
              ON B.xtype = C.xtype
           WHERE A.type = ‘U’
             AND A.name = @tabname
        ORDER BY A.name ASC
               , B.colorder DESC
          SELECT @i = max(id) 
            FROM @cols
   SET @classname = dbo.fnTitleCase(@tabname)
   SET @s_parmlist = ”
   SET @s_collist = ”
   SET @s_dec = ”
   SET @s_assn = ”
   SET @s_assnB = ”
   SET @c_dec = ‘   public class ‘ + @classname + ‘DAO : DAO’ + @crlf + ‘   {‘
   SET @c_pop = ‘      public void populate(DataRow row)’ + @crlf + ‘      {‘
   SET @c_update = ‘      public void update()’ + @crlf + ‘      {‘ + @crlf + ‘         SqlCommand oCmd = new SqlCommand();’ + @crlf + ‘         oCmd.CommandType = CommandType.StoredProcedure;’ + @crlf + ‘         oCmd.CommandText = “pUpdate”;’
   SET @c_parm = ”
   WHILE @i > 0
    BEGIN
       SELECT @colname = A.colname
            , @parmname = A.parmname
            , @sql_type = A.sql_type
            , @db_type = A.db_type
            , @csharp_type = A.csharp_type
         FROM @cols A
        WHERE A.id = @i
        SET @s_collist += @crlf + ‘   , ‘ + @colname
        SET @s_parmlist += @crlf + ‘   , ‘ + @parmname
        SET @s_dec += @crlf + ‘   , ‘ + @parmname + ‘ ‘ + @sql_type
        SET @s_assn += @crlf + ‘   , A.’ + @colname + ‘ = ‘ + @parmname
        SET @s_assnB += @crlf + ‘   , A.’ + @colname + ‘ = B.’ + @colname
        SET @c_dec += @crlf + ‘      public ‘ + @csharp_type + ‘ ‘ + @colname + (case when @csharp_type = ‘string’ then ‘ = “”;’ else ‘ = 0;’ end)
        SET @c_pop += @crlf + ‘         this.’ + @colname + ‘ = ‘ 
                   + (case when @csharp_type in (‘float’,’int’) then @csharp_type + ‘.Parse(row[“‘ + @colname + ‘”].ToString());’
                           when @csharp_type in (‘string’) then ‘row[“‘ + @colname + ‘”].ToString();’
                           else ‘(‘+@csharp_type+’) row[“‘ + @colname + ‘”];’ end )
        SET @c_update += @crlf + ‘         oCmd.Parameters.Add(“‘ + @parmname + ‘”, ‘ + @db_type + ‘).Value = this.’ + @colname + ‘;’
       SET @i -= 1
    END
   PRINT @s_collist + @crlf
   PRINT @s_parmlist + @crlf
   PRINT @s_dec + @crlf
   PRINT @s_assn + @crlf + @crlf
   PRINT @s_assnB + @crlf + @crlf
   PRINT @c_dec + @crlf + @crlf
   PRINT @c_pop + @crlf +  ‘      }//populate()’ + @crlf + @crlf
   PRINT @c_update + @crlf +  ‘      }//update()’ + @crlf + @crlf
   PRINT @c_parm + @crlf
   PRINT ‘   } //’ + @classname
END –sp_CodeGenerator()

Posted on by — Leave a comment

Cannot insert the value null into column ‘diagram_id’, table dbo.sysdiagrams

Error Condition: Cannot insert the value null into column ‘diagram_id’, table dbo.sysdiagrams

Problem: This sometimes occurs when a SQL Server database is ported from one version to another (such as 2000 to 2005 or 2005 to 2008) or possibly also when using the SSMS for a newer version of SQL Server against and older database.

Fix: Drop and recreated dbo.sysdiagrams making the column diagram_id an identity column. 

use [mydatabase]
GO
DROP TABLE [dbo].[sysdiagrams]
GO

CREATE TABLE [dbo].[sysdiagrams]

(   [name] [nvarchar](128) NOT NULL,
    [principal_id] [int] NOT NULL,
    [diagram_id] [int] identity(1,1) NOT NULL,
    [version] [int] NULL,
    [definition] [varbinary](max) NULL
)
GO

NOTE: If you want to preserve the diagrams, then rename the table and create a
new one, then SELECT – INTO to copy the data into the new table.