10: Interaction with the SQL Server Environment in VS .NET

Create Data Matrix in VS .NET 10: Interaction with the SQL Server Environment

10: Interaction with the SQL Server Environment
ECC200 Recognizer In VS .NET
Using Barcode Control SDK for VS .NET Control to generate, create, read, scan barcode image in VS .NET applications.
Generating DataMatrix In .NET
Using Barcode printer for VS .NET Control to generate, create Data Matrix ECC200 image in .NET framework applications.
Raiserror ('Unable to obtain spelling of number', 16, 1) exec sp_OAGetErrorInfo @intObject, @chvSource OUTPUT, @chvDesc OUTPUT Set @chvDesc = 'Error (' + Convert(varchar, @intErrorCode) + ', ' + @chvSource + ') : ' + @chvDesc Raiserror (@chvDesc, 16, 1) end if @bitObjectCreated = 1 exec sp_OADestroy @intObject return @intErrorCode
Data Matrix ECC200 Decoder In VS .NET
Using Barcode decoder for Visual Studio .NET Control to read, scan read, scan image in VS .NET applications.
Create Bar Code In VS .NET
Using Barcode creation for VS .NET Control to generate, create bar code image in VS .NET applications.
Once you are sure that communication between Transact-SQL and Visual Basic code is working, you can write code in Visual Basic that converts numbers to text. Since this is not a book about Visual Basic, I will not go into detail on that subject. There is an even better example on how to use these stored procedures in 11.
Read Barcode In .NET Framework
Using Barcode decoder for .NET Control to read, scan read, scan image in .NET framework applications.
ECC200 Generation In C#.NET
Using Barcode printer for VS .NET Control to generate, create Data Matrix image in .NET framework applications.
Data Type Conversion
Data Matrix 2d Barcode Creator In VS .NET
Using Barcode encoder for ASP.NET Control to generate, create Data Matrix ECC200 image in ASP.NET applications.
ECC200 Maker In VB.NET
Using Barcode drawer for .NET Control to generate, create DataMatrix image in .NET framework applications.
Keep in mind that code components and Transact-SQL code use different data types. You have to set compatible data types on both sides to allow the OLE Automation system stored procedures to automatically convert data between them. You can identify most of the compatible data types using common sense (for example, varchar, char, and text types in SQL Server translate to the String data type in Visual Basic, and the int SQL Server data type translates to the Long data type). However, some data types deserve special attention. When values are passed from SQL Server to Visual Basic, binary, varbinary, and image are converted to a one-dimensional Byte array. Any Transact-SQL value set to null is converted to a Variant set to null. Decimal and numeric are converted to String (not currency). When values are passed from Visual Basic to SQL Server, Long, Integer, Byte, Boolean, and Object are converted to the int data type. Both Double and Single data types are converted to float. Strings shorter than 255 characters are converted to varchar, and strings longer then 255 characters are converted to the text data type. One-dimensional Byte() arrays shorter than 255 characters become varbinary values, and those longer than 255 become image values.
Barcode Encoder In .NET Framework
Using Barcode drawer for Visual Studio .NET Control to generate, create bar code image in Visual Studio .NET applications.
Linear 1D Barcode Maker In .NET
Using Barcode creator for .NET Control to generate, create Linear 1D Barcode image in .NET applications.
SQL Server 2000 Stored Procedure & XML Programming
Code39 Generator In .NET Framework
Using Barcode creation for .NET Control to generate, create ANSI/AIM Code 39 image in VS .NET applications.
USPS Intelligent Mail Printer In Visual Studio .NET
Using Barcode printer for .NET framework Control to generate, create 4-State Customer Barcode image in .NET framework applications.
Running Programs
Bar Code Generator In None
Using Barcode encoder for Software Control to generate, create bar code image in Software applications.
EAN-13 Printer In None
Using Barcode printer for Online Control to generate, create EAN 13 image in Online applications.
Before Microsoft included support for OLE Automation and COM in SQL Server, administrators ran command prompt programs and commands using the xp_cmdshell extended stored procedure:
European Article Number 13 Recognizer In .NET Framework
Using Barcode reader for .NET framework Control to read, scan read, scan image in .NET framework applications.
Print Code-39 In Objective-C
Using Barcode generator for iPad Control to generate, create USS Code 39 image in iPad applications.
xp_cmdshell {'command'} [, no_output]
Printing Code 39 In Visual C#.NET
Using Barcode drawer for VS .NET Control to generate, create USS Code 39 image in .NET applications.
1D Drawer In VS .NET
Using Barcode printer for ASP.NET Control to generate, create Linear image in ASP.NET applications.
When xp_cmdshell is executed, a command string is passed to the command shell of the operating system to be executed. Any rows of text that are normally displayed by the command shell are returned by the extended stored procedure as a result set. There is also an option to ignore the output. The status of the execution is returned as an output parameter of the extended stored procedure. Its value is set to 0 if execution was successful and 1 if execution failed. In Windows 95 and Windows 98, the value will always be set to 0. Figure 10-2 shows the use of the command prompt instruction to list files in the Backup folder. This output can be received in a temporary table and further processed in Transact-SQL code.
Make Universal Product Code Version A In Java
Using Barcode creator for Java Control to generate, create GS1 - 12 image in Java applications.
UPCA Drawer In VB.NET
Using Barcode generator for VS .NET Control to generate, create UPC-A Supplement 5 image in .NET framework applications.
Figure 10-2
Using xp_cmdshell to run commands and programs
10: Interaction with the SQL Server Environment
The following batch copies files from the Backup folder to another drive:
exec master..xp_cmdshell 'copy e:\w2kPro~1\Mocros~1\' + 'MSSQL\BACKUP\*.* m:', no_output
Running Windows Script Files
The Windows Script Host enables users to write and execute scripts in VBScript, JavaScript, and other languages compatible with the Windows environment. It was initially developed as an additional component, but it is now integrated into the Windows 98, Me, 2000, and XP platforms. Script files usually have .vbs and .js extensions. They are executed from the Windows environment using wscript.exe or from the command prompt using csript.exe. Execution of script files can also be initiated from Transact-SQL code. The following statement runs a demo script that starts Excel and populates a worksheet with information:
exec xp_cmdshell 'c:\windows\command\cscript.exe ' + 'c:\windows\samples\wsh\Excel.vbs', NO_OUTPUT
Running/Looping Through DTS Packages
Data Transformation Services (DTS) is a SQL Server tool used for the visual design and execution of data transformation routines. These routines are stored on a server as DTS packages. Naturally, their design and management are beyond the scope of this book, but I will show you how to run a DTS package from a stored procedure. SQL Server 2000 includes the dtsrun.exe utility, which allows you to execute DTS packages from the command prompt. We can simply call it using xp_cmdshell:
exec xp_cmdshell 'dtsrun /SA1000 /Udbo /E /NExportData ' + '/ADatabase:8=Asset7 /AServer:8=A1000', NO_OUTPUT
One of the features that you might wish to use, but that is missing from DTS, is looping. The tool simply does not allow you to create tasks to be executed in a loop. The solution is to use a scripting language (such as T-SQL or VBScript) to launch a group of tasks (organized into a single DTS package) in a loop. The following
Copyright © OnBarcode.com . All rights reserved.