Skip to main content

Posts

Showing posts from 2014

How to pass a data table into a stored procedure as a Table Value Parameter (TVP) in SQL server 2008 and above.

In some instances there might be a need to manipulate an entire data set rather than passing individual values as variables into a database. To do this I explain how to start by creating the proc with the parameters then obtaining the inserted identity values from the insert statement to update a related table. Note the special table inserted which is an SQL table which stores the values of an insert just before this hits your table. Refer MSDN on inserted and deleted Tables 1.       To start with define the table value parameter under your database ->programmability->Types-> User-Defined Table Types 2.       Right click on the User Defined Table Types Create a data table definition of the set needed to be passed. Clicking on CTRL-Shift-M would bring up the Dialog to create them visually. The script should look like the following -- ================================ -- Create User-defined Table Type -- ================================ USE AdventureWorks G

Growing Log file issues - How to shrink a SQL server Log and data file.

Sometimes it is necessary to do this via a script as the UI does not work for reasons such as the DB is being in used. This method sets the recover mode and then shrinks the log and data file. Before doing this backup your databases using Management studio Open up a query window associated with the database with the large transaction log. (Right-click on the DB and choose new query.) Get the logical name of the transaction log file. (Right-click on the DB, select Properties, then in the Files screen, grab the Logical Name… probably ends in something like _Log.) Execute the following, substituting with the appropriate logical name of the database log file, no quotes needed: Use [Mydatabase] ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE DBCC SHRINKFILE(<log_file_name_Log>) ALTER DATABASE [mydatabase] SET RECOVERY FULL   The following screen shows the log file before the shrink. Log file after shrink Points of interest Transaction logs