|
|
|
|
|
|
Script to create dummy tables/procs and views for testing purpose
|
Total Hit (5027) |
This script can generate N number of Tables/Views and Stpored Procs in few seconds with some dummy data. I use this for stress testing and create dummy tables with data.
Change @howmanytables to create N number of tables and then set
- @howmanycolumns to create N number of dummy columns per tab
....Read More |
Rating
|
|
|
|
|
How to do error handling in transaction.
|
Total Hit (2223) |
The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do is halt the sequential processing of the code and either branch to another code segment in the proc
....Read More |
Rating
|
|
|
Export SQL data to XML File (3 different ways)
|
Total Hit (13387) |
Many times we need to export sql server data to some form of XML. As we know that SQL Server can output data in XML format when you use "FOR XML" clause. You can use ADO and MSXML along with "FOR XML" sql query to generate XML files from SQL Server data. The following script is pure VB Script so if
....Read More |
Rating
|
|
|
How to run *.bat file using xp_CmdShell
|
Total Hit (6636) |
xp_CmdShell is a powerful SP if used with care. You can execute executable files (e.g. *.com, *.exe and *.bat) using xp_CmdShell however there is a little trick when executing executable file.
When you use the the following command you will get an error
<font color=red>'C:\myscript.bat' is no
....Read More |
Rating
|
|
|
|
|
|
|
|
DTS How to...Skip Rows during Import
|
Total Hit (849) |
Firstly you can skip header rows by using the First Row property of the DataPump Task, see Advanced properties sheet. The Text File Connection also has a Skip Rows property, see file Properties of the Connection.
|
Rating
|
|
|
How to move, copy, and back up full-text catalog folders and files
|
Total Hit (1506) |
Full-text catalogs and indexes are not stored in a SQL Server database. Full-text catalogs and indexes are stored in separate files that the Microsoft Search service manages. The full-text catalog files are not recovered during a Microsoft SQL Server recovery. Additionally, you cannot use the Transa
....Read More |
Rating
|
|
|
|
Find an Access Database (from anywhere on the file system)
|
Total Hit (639) |
It may be that we have built a DTS package to synchronise data held in an Access database and our SQL Server. The Access databases all have a consistent name but due to the fact that they are used by travelling salesmen on their laptops we cannot guarantee where the database will be.
For this we
....Read More |
Rating
|
|
|
Managing Users Permissions on SQL Server
|
Total Hit (1298) |
Permissions are the rights to access the database objects. Permissions can be granted to a user or role to allow that user or role to perform operations such as selection, insertion or modification of data rows.
|
Rating
|
|
|
How do I enumerate tables in an Access Database
|
Total Hit (1164) |
If you need to enumerate the tables in an Access database, the easiest method is through the ActiveX Data Objects Extensions for Data Definition Language and Security. The ADOX library, as it is better known, can be used just like other COM objects from within ActiveX script, such as a task or workf
....Read More |
Rating
|
|
|
|
MDX Member Functions: The "Family" Functions
|
Total Hit (857) |
This is the fifth article of the series, MDX Essentials. The primary focus of this series is an introduction to the MDX language. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding fea
....Read More |
Rating
|
|
|
How to export all tables in a database
|
Total Hit (764) |
This is a very short article as it is really a demonstration of concepts explained elsewhere, although quite a useful implementation in it's own right. The export itself is done with the Bulk Export Task, and this is wrapped in a recordset driven loop as covered in the article How to loop through a
....Read More |
Rating
|
|
|
New Version Showcases Native XML Type and Advanced Data Handling
|
Total Hit (1170) |
The next version of Microsoft SQL Server, code-named "Yukon," represents quite a few steps forward in the evolution of XML integration. Yukon supports native storage of XML data using the XML data type, which makes it possible to run native queries on XML data using the emerging industry standard XQ
....Read More |
Rating
|
|
|
Arrays and Lists in SQL Server
|
Total Hit (771) |
In the public forums for SQL Server, you often see people asking How do I use arrays in SQL Server? Or Why does SELECT * FROM tbl WHERE col IN (@list) not work? This text describes several methods to do this, both good and bad ones. I also present data from performance tests of the various methods.
....Read More |
Rating
|
|
|
|
SQL Server 2000 DTS Part 4 - DTS Designer Tasks
|
Total Hit (1930) |
In the previous article of our series dedicated to SQL Server 2000 Data Transformation Services, we covered the first of the essential elements of a package - connection. Now it is time to look into another critical component - task - that is responsible for the processing of data retrieved through
....Read More |
Rating
|
|
|
Generate Scripts for SQL Server Objects
|
Total Hit (2545) |
We all like the Generate Script feature of Enterprise Manager and like to use it programmatically. One of the most important uses would be backing up scripts periodically using SQL Server Scheduler.
|
Rating
|
|