|
|
 |
Update SQL Server database with Updategrams
|
| Total Hit (2632) |
You can use XML to transfer both data and commands. In this column I'll show how you can update a database by means of XML commands sent over the HTTP protocol and, even more interesting, how to perform bulk uploads with these commands.
An updategram is a piece of XML data that contains informati
....Read More |
Rating
 |
|
|
|
OLE Automation in SQL server
|
| Total Hit (3560) |
This is an example of a Transact-SQL statement batch that uses the OLE Automation stored procedures to create and use an SQL-DMO SQLServer object. Portions of the code are used as examples in the stored procedure references.
DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DE
....Read More |
| Rating
|
|
|
|
Checking for Changes within trigger
|
| Total Hit (2026) |
The UPDATE and COLUMNS_UPDATED functions are available within both types of triggers to allow the trigger to determine which columns were modified by the triggering action statement. For example, the following trigger prevents any modifications to the lastname column in the Employees table. Here, th
....Read More |
| Rating
|
|
|
|
|
|
|
|
How to export table or Query output to a file using BCP
|
| Total Hit (2813) |
To export Query result to a file use following command to your command prompt
«code LangId=6»EXEC master..xp_cmdshell
'bcp "select * from pubs..authors" queryout c:\authors.txt -c -Usa -Pmypassword'«/code»
To export table to a file use following command to your command prompt
«code LangId
....Read More |
| Rating
|
|
|
|
Quickly copy records between tables with same structure
|
| Total Hit (2946) |
The SQL language offers a simple and effective way to quickly move all records from a table to another table with same field structure, that is same fields' name, type and position:
«Code LangId=6»
INSERT Customers SELECT * FROM New_Customers
-- you can optionally complete the move by
-- delet
....Read More |
| Rating
|
|
|
|
Check a string for numeric digits only
|
| Total Hit (1804) |
The ISNUMERIC function in SQL60/65/70 checks for decimal & integer values. Hence characters like D, E are valid float representations & similarly ','. This is a simple logic that can check only for numeric digits without using a loop of any kind.
|
| Rating
|
|
|
|
|
|
How to start SQL Server Agent automatically when SQL Server starts.
|
| Total Hit (3520) |
When you install SQL Server by default SQL Server Agent Service is set to Start Manual mode and some times this can stop your scheduled jobs. Its also possible that you stop Agent and forget to turn it on.
This article will show you how to create a SP which can start SQL Server Agent service if i
....Read More |
Rating
 |
|
|
|
How to restore a SQL Server database marked as "suspect"
|
| Total Hit (10662) |
At times a database appears to be marked as "suspect" in the Enterprise Manager. SQL Server marks a database as suspect with it can't access the database. What happens at a low level is that SQL Server sets one of the bits in the status field in the sysdatabases table.
In general, this problem ha
....Read More |
Rating
 |
|
|
|
|
|
|
|
GROUP BY and UNION may return unsorted resultsets
|
| Total Hit (3159) |
In SQL Server 6.5 and previous versions, the GROUP BY clause in a SELECT statement was carried out by temporarily sorting the resultset before grouping similar rows, therefore the end result was always sorted. For this reasons, many programmers omitted the ORDER BY clause, because the result was sor
....Read More |
| Rating
|
|
|
|
|
|
A Server by Any Other Name
|
| Total Hit (750) |
It is imperative to change the server name when infrastructure policy changes, naming convention standard changes or making a standby server the production server (when production server fails). When a server name is changed, SQL server should go through a process to acknowledge and assimilate the c
....Read More |
| Rating
|
|
|
|
|
|
|
|
Sysprocesses table definition
|
| Total Hit (750) |
The sysprocesses table holds information about processes running on Microsoft® SQL Server™. These processes can be client processes or system processes. sysprocesses is stored only in the master database.
|
| Rating
|
|
|
|
SQL Server Stored Procedures Administration
|
| Total Hit (604) |
A stored procedure is a precompiled collection of Transact-SQL statements stored under a name and processed as a unit that you can call from within another Transact-SQL statement or from the client applications.
|
| Rating
|
|
|
|
Scripting Traces for Performance Monitoring on SQL Server
|
| Total Hit (985) |
The SQL Profiler is a great tool for monitoring and analyzing SQL Server Performance. I use it all the time to watch the detailed actions of a stored procedure, trigger or user-defined function (UDF) that I am developing. It can also be used to monitor aggregate performance of an entire SQL Server i
....Read More |
| Rating
|
|
|
|
Querying the SQL Server System Catalog FAQ
|
| Total Hit (1120) |
Data Types
How do I find the data types of the columns of a specified table?
How do I find the LOB data types of a specified table?
How do I find the columns that depend on a specified data type?
How do I find the computed columns that depend on a specified CLR user-defined type or alias d
....Read More |
| Rating
|
|
|
|
|
|
Get Started Using SQL Server 2005 Notification Services
|
| Total Hit (934) |
People receive many notifications daily: weather updates, news updates, updates about stock prices, or a current account balance via a SMS message. But how can you build such notification applications on the Microsoft platform? You have two options: the first and less palatable is to build everythin
....Read More |
| Rating
|
|
|
|
|
|
Troubleshooting SQL Server Jobs
|
| Total Hit (681) |
If you have problems with SQL Server jobs, review this troubleshooting checklist to find potential solutions.
|
| Rating
|
|
|
|
Data Validation for Merge Replication
|
| Total Hit (624) |
Microsoft SQL Server 7.0/2000 allows you to validate data for merge replication. You can specify rowcount only validation or rowcount and checksum validation of the article's data.
When you choose rowcount only validation, then SQL Server calculates a rowcount at the publisher level, then compares
....Read More |
| Rating
|
|
|
|
How can I change the filename for a text file connection?
|
| Total Hit (619) |
It is a common scenario to have a scheduled package that imports a new file each day. Conversely you may wish to produce a uniquely named file each day. You can change the filename (DataSource) property of a connection at run-time from within an ActiveX Script Task.
....Read More |
| Rating
|
|
|
|