Skip to content

The Dummy Programmer

Stories of daily programming

  • Home
  • The Dummy Programmer Robot
    • Overview
    • Version history & downloads
    • Tutorials
      • TDP Robot – The basics
      • A simple SQL Server backup solution
      • A simple SQL Server backup solution – A next step
  • My other dummy projects
  • Games
    • Space Y: An Asteroids clone
  • Services
    • What’s my user agent
    • What’s my IP address
  • Donate
  • About
  • Toggle search form

Category: Sql Server

Improving SQL Server cursor performance

Hi everyone! Today I want to talk about a performance problem I had a few days ago using a SQL Server cursor. Imagine using a cursor in the following classic way: DECLARE @ID INT DECLARE @ITEM_CODE VARCHAR(50) DECLARE MY_CURSOR CURSOR FOR SELECT ID, ITEM_CODE FROM MY_TABLE T LEFT JOIN A_TABLE T1 ON T.ID = T1.ID LEFT…

Read More “Improving SQL Server cursor performance” »

Sql Server

Delete all rows from all tables in a Sql Server database

Delete all rows from all tables in a Sql Server database may not be an easy task. This because in a relational database you have foreign keys between tables, and to empty all tables you have to write delete statements in the correct order to avoid foreign key violations. I know that there are scripts…

Read More “Delete all rows from all tables in a Sql Server database” »

Sql Server

Search a string in stored procedures, functions and views in a SQL Server database

If you want to search a string in stored procedures, functions and views, maybe you can use some special tools. But, for instance, suppose that you can only use SQL Server Management Studio. Is there a way to do this? Yes…. You could use the following query: SELECT DISTINCT OBJECT_NAME(id) AS [OBJECT_NAME] FROM sys.syscomments WHERE…

Read More “Search a string in stored procedures, functions and views in a SQL Server database” »

Sql Server

Load a huge amount of data in a Sql Server table

Last week I had to load a huge amount of data, about 500,000 rows, in a Sql Server table to make some data processing. Using the “Sql Server Import and Export Data” tool this was an easy job. After processing data in my own Sql Server, I had to reload the data to another Sql…

Read More “Load a huge amount of data in a Sql Server table” »

BCP

The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

I was trying to load the content of an xlsx file into a Sql Server database using the “Sql Server import and export wizard” provided by Sql Server Management Studio, and I got the following error “The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine”. The error persisted even after the installation of the…

Read More “The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine” »

Excel

SQL Server LEN function return 0 passing a string with one space

Try to answer this riddle: What is the result of this SQL Server query: PRINT LEN(‘ ‘) I would answer 1, since I passed a string with one space…. but it’s the wrong answer. In this case the LEN function returns 0! Why this behavior? If you check the Sql Server documentation at https://docs.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql?view=sql-server-2017 this…

Read More “SQL Server LEN function return 0 passing a string with one space” »

Sql Server

Sql Server error using a database with a CLR assembly

Few days ago, I got a Sql Server error using a database with a CLR assembly installed. Running my application I got the following error: “An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted…

Read More “Sql Server error using a database with a CLR assembly” »

CLR

Reset Sql Server cache

Sometimes is useful to check how a certain query or a stored procedure behaves without cached data, so in this post I will show you how to reset SQL Server cache. To do the job, simply run these statements from SQL Management Studio: CHECKPOINT GO DBCC DROPCLEANBUFFERS GO DBCC FREEPROCCACHE GO

Sql Server

Posts navigation

Previous 1 2

Copyright © 2022 The Dummy Programmer | Privacy Policy |

Powered by PressBook Masonry Dark