Generate SQL Server Insert Script by Stored Procedure

Generate SQL Server Insert Script by Stored Procedure --EXEC sp_generate_inserts TABELNAME --============================================================= SET NOCOUNT ON PRINT 'Checking for the existence of this procedure' IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL BEGIN PRINT 'Procedure already exists. So, dropping it' DROP PROC sp_generate_inserts END GO CREATE PROC sp_generate_inserts (       @table_name varchar(776),       @target_table varchar(776) = NULL,       @include_column_list bit = 1,       @from varchar(800) = NULL,       @include_timestamp bit = 0,       @debug_mode bit = 0,       @owner varchar(64) = NULL,       @ommit_images bit = 0,       @ommit_identity bit = 0,       @top int = NULL,       @cols_to_include varchar(8000) = NULL,       @cols_to_exclude varchar(8000) = NULL ) AS BEGIN SET NOCOUNT ON IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL)) BEGIN RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not specify  both',16,1) RETURN -1 END IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include)  =0)) BEGIN RAISERROR('Invalid use of @cols_to_include property',16,1) PRINT 'Specify column names surrounded by single quotes and separated by  commas' PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'', ''title''"' RETURN -1 END IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude)  =0)) BEGIN RAISERROR('Invalid use of @cols_to_exclude property',16,1) PRINT 'Specify column names surrounded by single quotes and separated by  commas' PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'' ,''title''"' RETURN -1 END IF (parsename(@table_name,3)) IS NOT NULL BEGIN RAISERROR('Do not specify the database name. Be in the required database  and just specify the table name.',16,1) RETURN -1 END IF @owner IS NULL BEGIN IF (OBJECT_ID(@table_name,'U') IS NULL) BEGIN RAISERROR('User table not found.',16,1) PRINT 'You may see this error, if you are not the owner of this table. In that  case use @owner parameter to specify the owner name.' PRINT 'Make sure you have SELECT permission on that table.' RETURN -1 END END ELSE BEGIN IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = @owner) BEGIN RAISERROR('User table not found.',16,1) PRINT 'You may see this error, if you are not the owner of this table.  In that case use @owner parameter to specify the owner name.' PRINT 'Make sure you have SELECT permission on that table.' RETURN -1 --Failure. Reason: There is no user table with this name END END --Variable declarations DECLARE @Column_ID int, @Column_List varchar(8000), @Column_Name varchar(128), @Start_Insert varchar(786), @Data_Type varchar(128), @Actual_Values varchar(8000), @IDN varchar(128) --Variable Initialization SET @IDN = '' SET @Column_ID = 0 SET @Column_Name = 0 SET @Column_List = '' SET @Actual_Values = '' IF @owner IS NULL BEGIN SET @Start_Insert = 'INSERT INTO ' + '[' +RTRIM(COALESCE(@target_table, @table_name)) + ']' END ELSE BEGIN SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].'  + '[' +RTRIM(COALESCE(@target_table,@table_name)) + ']' END --To get the first column's ID IF @owner IS NULL BEGIN SELECT @Column_ID = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE TABLE_NAME = @table_name END ELSE BEGIN SELECT @Column_ID = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE TABLE_NAME = @table_name AND TABLE_SCHEMA = @owner END --Loop through all the columns of the table, to get the column names  --and their data types WHILE @Column_ID IS NOT NULL BEGIN IF @owner IS NULL BEGIN SELECT @Column_Name = '[' + COLUMN_NAME + ']', @Data_Type = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE ORDINAL_POSITION = @Column_ID AND TABLE_NAME = @table_name END ELSE BEGIN SELECT @Column_Name = '[' + COLUMN_NAME + ']', @Data_Type = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE ORDINAL_POSITION = @Column_ID AND TABLE_NAME = @table_name AND TABLE_SCHEMA = @owner END IF @cols_to_include IS NOT NULL --Selecting only user specified columns BEGIN IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2)  +'''',@cols_to_include) = 0 BEGIN GOTO SKIP_LOOP END END IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns BEGIN IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2)  +'''',@cols_to_exclude) <> 0 BEGIN GOTO SKIP_LOOP END END --Making sure to output SET IDENTITY_INSERT ON/OFF in case the  ---table has an IDENTITY column IF (SELECT COLUMNPROPERTY(OBJECT_ID(@table_name), SUBSTRING(@Column_Name,2,LEN(@Column_Name)- 2),'IsIdentity')) = 1 BEGIN IF @ommit_identity = 0  --Determing whether to include or exclude the IDENTITY column SET @IDN = @Column_Name ELSE GOTO SKIP_LOOP END --Tables with columns of IMAGE data type are not supported  --for obvious reasons IF(@Data_Type in ('image')) BEGIN IF (@ommit_images = 0) BEGIN RAISERROR('Tables with image columns are not supported.',16,1) PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the  rest of the columns.' PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit  column list using @include_column_list=0, the generated INSERTs will fail.' RETURN -1 --Failure. Reason: There is a column with image data type END ELSE BEGIN GOTO SKIP_LOOP END END --making sure, not to lose any data from flot, real, money, smallmomey,  --datetime columns SET @Actual_Values = @Actual_Values + CASE WHEN @Data_Type IN ('char','varchar','nchar','nvarchar') THEN ''''''''' + '+'COALESCE(REPLACE(RTRIM(' + @Column_Name +'), '''''''',''''''''''''),''nvkon©'')' + ' + ''''''''' WHEN @Data_Type IN ('datetime','smalldatetime') THEN ''''''''' + '+'COALESCE(RTRIM(CONVERT(char,' + @Column_Name +',109)), ''nvkon©'')' + ' + ''''''''' WHEN @Data_Type IN ('uniqueidentifier') THEN ''''''''' + '+'COALESCE(REPLACE(CONVERT(char(255),RTRIM(' +  @Column_Name+ ')),'''''''',''''''''''''),''NULL'')' + ' + ''''''''' WHEN @Data_Type IN ('text','ntext') THEN ''''''''' + '+'COALESCE(REPLACE(CONVERT(char,' + @Column_Name +'), '''''''',''''''''''''),''NULL'')' + ' + ''''''''' WHEN @Data_Type IN ('binary','varbinary') THEN 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' +  @Column_Name +'))),''NULL'')' WHEN @Data_Type IN ('timestamp','rowversion') THEN CASE WHEN @include_timestamp = 0 THEN '''DEFAULT''' ELSE 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' +  @Column_Name +'))),''NULL'')' END WHEN @Data_Type IN ('float','real','money','smallmoney') THEN 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name + ',2)' +')),''NULL'')' ELSE 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' +  @Column_Name + ')' +')),''NULL'')' END + '+' + ''',''' + ' + ' --Generating the column list for the INSERT statement SET @Column_List = @Column_List + @Column_Name + ',' SKIP_LOOP: --The label used in GOTO IF @owner IS NULL BEGIN SELECT @Column_ID = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE TABLE_NAME = @table_name AND ORDINAL_POSITION > @Column_ID END ELSE BEGIN SELECT @Column_ID = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE TABLE_NAME = @table_name AND ORDINAL_POSITION > @Column_ID AND TABLE_SCHEMA = @owner END --Loop ends here! END --To get rid of the extra characters that got concatened during the last run -- through the loop SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1) SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6) --Forming the final string that will be executed, to output the  --INSERT statements IF (@include_column_list <> 0) BEGIN SET @Actual_Values = 'SELECT ' + CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END + '''' + RTRIM(@Start_Insert) + ' ''+' + '''(' + RTRIM(@Column_List) + '''+' + ''')''' + ' +''VALUES(''+ ' + 'REPLACE(' + @Actual_Values +  ',''''''nvkon©'''''',''NULL'')' + '+'')'''+ ' ' +COALESCE(@from,' FROM '  + CASE WHEN @owner IS NULL THEN '' ELSE '['+  LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']'  +'(NOLOCK)') END ELSE IF (@include_column_list = 0) BEGIN SET @Actual_Values = 'SELECT ' + CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP '  +LTRIM(STR(@top)) +  ' ' END + '''' + RTRIM(@Start_Insert) + ' '' +''VALUES(''+ ' + 'REPLACE(' + @Actual_Values + ',''''''nvkon©'''''', ''NULL'')' +'+'')''' + ' ' + COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL  THEN '' ELSE '['+ LTRIM(RTRIM(@owner)) + '].' END  + '[' + rtrim(@table_name) + ']' +'(NOLOCK)') END --Determining whether to ouput any debug information IF @debug_mode =1 BEGIN PRINT '/*****START OF DEBUG INFORMATION*****' PRINT 'Beginning of the INSERT statement:' PRINT @Start_Insert PRINT '' PRINT 'The column list:' PRINT @Column_List PRINT '' PRINT 'The SELECT statement executed to generate the INSERTs' PRINT @Actual_Values PRINT '' PRINT '*****END OF DEBUG INFORMATION*****/' PRINT '' END PRINT '' PRINT 'SET NOCOUNT ON' --Determining whether to print IDENTITY_INSERT or not IF (@IDN <> '') BEGIN PRINT 'SET IDENTITY_INSERT ' + '[' +RTRIM(COALESCE (@target_table,@table_name)) + ']' + ' ON' PRINT 'GO' END PRINT 'PRINT ''Inserting values into ' + '[' +RTRIM(COALESCE (@target_table,@table_name)) + ']' + '''' --All the hard work pays off here!!! You'll get your INSERT statements, -- when the next line executes! EXEC (@Actual_Values) PRINT 'PRINT ''Done''' IF (@IDN <> '') BEGIN PRINT 'SET IDENTITY_INSERT ' + '[' +RTRIM(COALESCE (@target_table,@table_name)) + ']' + ' OFF' PRINT 'GO' END PRINT 'SET NOCOUNT OFF' SET NOCOUNT OFF RETURN 0 --Success. We are done! END GO PRINT 'Created the procedure' GO SET NOCOUNT OFF GO PRINT 'Done'

Multi column Order By in LINQ

Multi column Order By in LINQ

There is multiple ways to do multi columns sorting/order by with LINQ query

var employee = _db.Employee.Orderby(e => e.Ename).ThenBy(s => s.Salary);

OR

var employee = from emp in Employee

               orderby emp.Ename, emp.Salary ascending

               select emp;
OR

var employee = from emp in Employee

               orderby emp.Ename ascending, emp.Salary descending

               select emp;

Multi select combo box with Checkbox in Silverlight 4.0

Multi select combo box with Checkbox in Silverlight 4.0

I found multi select combo box on msdn and I converted into Silverlight 4.0, just copy "Main.Xmal"

and "Main.xmal.cs" into your new Silverlight 4 solution. Its works well for me with "Silverlight 4.0" and ".Net Framework 4.0"

Its look like below, you can change look and feel as you want

you can download code from here

http://code.msdn.microsoft.com/multiselectdropdown/Release/ProjectReleases.aspx?ReleaseId=4455

There is another code multi select combo box source code is , its works well with Silverlight 3.0

http://www.codeproject.com/KB/silverlight/MultiSelComboBox.aspx

“IN” clause in LINQ

"IN" Clause in LINQ

Some time we have a requirement to select records with more than one criteria as same as "IN" clause with SQL like:

"Where EmpId IN (1,2,3)"

We can do this with LINQ to Object also, we have to use "Contains" function like below

public static Employee[] GetEmployee(string[] EmpID)

{

        return (from p in GetEmp()

        where EmpID.Contains(p.EmployeeID)

        select p).ToArray<Employee>();

}

Reference: http://johnpapa.net/all/need-an-quot-where-field-in-x-y-z-quot-clause-with-linq/

Asking to Install Silverlight 4 when it’s already installed with Visual Studio 2010

Asking to Install Silverlight 4 when it's already installed with Visual Studio 2010 Check one thing in your system 1) Make sure Microsoft Silverlight is enabled on internet explorer.  Go to IE à Tools à Internet optionsà on the programs tab click on "manage add ons" button and check for Microsoft Silverlight 4 is Enable. Still if you are not able to solve this please uninstall all Silverlight components and install in proper way, follow the installation instruction in http://www.microsoft.com/getsilverlight/Get-Started/Install/Default.aspx 2) If you don't have time to do this so, still you can test your application by using Silverlight enable browser. Go to the Project à right click à go to Property page Go to the Silverlight page and click on the "Enable running application out of the Browser Now run your application using Visual Studio 2010

3) Make sure you are running silverlight App on 32 bit Browser, Because  Silverlight App does not works on 64 Bit IE Browsers.
  
I Solve this issue in following way, It is not the solution but its works for me


When I create application first time and run, it giving me pop up for Silverlight installation..

Now go to project properties --> Silverlight Tab and just Check and Uncheck again (so no change) on the 

"Enable running application out of browser" check box and close this window with Save or without saving now run the application (press F5), it works fine. 

Make sure your startup project set as SilverlightApplication NOT SilverlightApplication.Web


I tested this with

• SilverLight 4 
• IE Browser 8/9 32bit (this issue  not comes in other browser like Chrome, Mozilla etc.)
• Visual Studio 2010
• Window 7 as OS

Finally Finally I got the solution for this .....................

1. Make sure you are not running IE 64 bit, because silverlight 4.0 and less  does not support IE 64 bit, Silverlight 5.0 and above support this
2. Now if you are using IE 32 bit then Add your application URL as a Trusted site, try following option

Go to IE --> Internet Options -->  Security --> Trusted sites --> Sites --> Add

• Add you site URL
• Uncheck "Require server verification (https:) for all sites in this zone"

That's it, you are good to go.

Attempting to access a service in a cross-domain way without a proper cross-domain policy in place......

Error: Attempting to access a service in a cross-domain way without a proper cross-domain policy in place, or a policy that is unsuitable for SOAP services. You may need to contact the owner of the service to publish a cross-domain policy file and to ensure it allows SOAP-related HTTP headers to be sent. This error may also be caused by using internal types in the web service proxy without using the InternalsVisibleToAttribute attribute. I resolved this issue by placing "ClientAccessPolicy.xml" into Web service location "C:/inetpup/MyService" folder. Go ahead and create one "ClientAccessPolicy.xml" file with following content and save into your service location folder in "C:/inetpup/ <?xml version="1.0" encoding="utf-8"?> <access-policy>        <cross-domain-access>               <policy>                      <allow-from http-request-headers="SOAPAction">                            <domain uri="*"/>                      </allow-from>                      <grant-to>                            <resource path="/" include-subpaths="true" />                      </grant-to>               </policy>        </cross-domain-access> </access-policy> In some cases you may have place one more file "crossdomain.xml" <?xml version="1.0"?> <!DOCTYPE cross-domain-policy SYSTEM "http://www.macromedia.com/xml/dtds/cross-domain-policy.dtd"> <cross-domain-policy> <allow-http-request-headers-from domain="*" headers="*"/> </cross-domain-policy>

CS0135: 'Model' conflicts with the declaration 'System.Web.Mvc.ViewPage.Model'

CS0135: 'Model' conflicts with the declarationSystem.Web.Mvc.ViewPage<TModel>.Model After spending couple of hours I found the solution, before my try I tried all the option suggested on internet like: add the namespace with the class which you are using Inherits="System.Web.Mvc.ViewPage<Attachment>"  and add a <%@ Import namespace="MyProject.Models" %> Or Inherits="System.Web.Mvc.ViewPage< MyProject.Models.Attachment>"  But for me problem was different, the problem was Cause I used validation control (ValidationMessageFor) to and passed wrong parameter with "=>" expression like: Line -1: <%= Html.ValidationMessageFor(Model => Model.Employee)%></div> Line -2: <% Html.RenderPartial("Attachment", Model ); %>  Because of Line -1: mistake Line -2: was giving error "CS0135: 'Model' conflicts with the declaration 'System.Web.Mvc.ViewPage<TModel>.Model" Solution I correct above line like below <%= Html.ValidationMessageFor(Employee=> Model.Employee)%></div> <% Html.RenderPartial("Attachment", Model ); %> It was very stupid mistake but its took longer time to analyzed, Now my page is working fine.

ASP.NET MVC 2 common problems while develope a View

ASP.NET MVC 2 common problems and solutions While working with MVC2 with ASP.Net, I faced couples of problem while developing the View form, some of those are below 1)      Multiple submit button on the same page 2)      How to make Default Button 3)      How to add Image on the Submit Button 4)      Validation Control on ASP.Net MVC 2 5)      How to add Image with the validation message After spending couple of hours I got good links where you get all these solution How to put multiple submit button on the same page When you have one web page having couples of action then you need multiple buttons on the same page and each button has different ResultAction, Find solution here http://stackoverflow.com/questions/442704/how-do-you-handle-multiple-submit-buttons-in-asp-net-mvc-framework How to make Default Button in ASP.Net MVC View When you have multiple button and out of that you want to make one button as default, Find solution here http://codehobo.spaces.live.com/blog/cns%21ECA371B988C4C469%21147.entry How Image button works like page Submit button in ASP.Net MVC View When you have a requirement to put Image on the page submit button and have multiple submit button on same page. The main problem is Image button will not work like Submit button then how to put image on the submit button. I tied following solution and it works for me , Find solution here http://efreedom.com/Question/1-3857249/ASPNET-MVC-Problem-Using-Image-Submit-Tag-AjaxBeginForm   Validation Control on ASP.Net MVC 2 When you have to implements validation control in your ASP.Net MVC 2 web form then I found very good article about this, How to use validation control in ASP.Net MVC 2, Find Solution here http://weblogs.asp.net/scottgu/archive/2010/01/15/asp-net-mvc-2-model-validation.aspx Add image with validation message You have validation message and with the validation message your requirement to add Image (like: alert, explanation image etc.), then you can find the solution from here http://efreedom.com/Question/1-1265857/Extend-HtmlValidationMessage-Can-Include-Image-Error

WCF Errors with IIS7 with .Net Framework 4.0

WCF Errors with IIS7 with .Net Framework 4.0  When I start hosting WCF service on the IIS7 then I got following errors and I spend enough time to resolve this, following error could be caused by different reason but I described the reason and solution which I faced. I was using Visual Studio 2010 with .Net framework 4.0 and IIS7.   Error-1: HTTP Error 404.17 - Not Found The requested content appears to be script and will not be served by the static file handler. Cause WCF service developed on different .NET framework (V4.0) and Application Pool set in different version (V2.0). Solution Go to the IIS 7 and select Application Pools à right click on your application and change the .NET Framework version to V4.0. (Change the v2.0 to v4.0 in Application Pool) Error-2: HTTP Error 500.19 - Internal Server Error The requested page cannot be accessed because the related configuration data for the page is invalid. Cause This error occurs when you application deployed on the different version of .NET and Web.config has different version mentioned. Like: Application Pool running on .Net 4.0 and Web.Config includes .Net 3.5 Solution Go to the application project à right click and go to "Property Pages"à Build tab and Set the application Target Framework to .Net Framework 4.0. Another quick fix, change application pool set to 2.0 as default. Error-3: No protocol binding matches the given address 'http://localhost/IISHostedService/MyService.svc'. Protocol bindings are configured at the Site level in IIS or WAS configuration.  <endpoint address="http://localhost/IISHostedService/MyService.svc" binding="wsHttpBinding" contract="IMyService"> Solution Remove this address URL from Web.config, because this is already configured in IIS. Go to the web.config and change in the Services section like below <endpoint address="" binding="wsHttpBinding" contract="IMyService"> Error-4: Metadata publishing for this service is currently disabled. There are couples of help available on internet and on the site page where this error displays. But for that was not the problem Cause Normally this error occurs when "httpGetEnabled" is false in Web.config  check your web.config <serviceMetadata httpGetEnabled="true"> But for me the problem was, I created one folder explicitly in “C:/inetpub” called ‘MyFolder’ and then from the visual studio I created WCF service on that folder choosing Web location: File System and File created on “C:/inetpub/MyFolder”. Now when I deploy my WCF service and try to access then its throws following error “ Metadata publishing for this service is currently disabled.” Solution DONOT create any folder explicitly on “C:/inetpub” and while creating new WCF service choose following option Web location: HTTP and File created on “http://localhost/MyFolder”.