July 31, 2006 at 6:57 am
I am trying to build an app with VB.NET (2005) that checks source out of VSS and uses it as an input file (much like OSQL does) to create/alter stored procedures. I can successfully check out/in the files and I have been able to use a call to OSQL in order to use the files as input.
Is there a way to do this in VB.Net itself without having to call OSQL?
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
August 1, 2006 at 7:09 am
what exactly are you trying to do? read in a template to generate a SP to then load into the SQL Server?
we read files and load them onto our server with vb.net, and also generate code from templates and xml files. might be something i can give you an example on if i knew a little more...
August 1, 2006 at 7:19 am
Our development teams use Source Safe for code control. When a stored procedure(s) is ready for release, they will send us (DBAs) a list of the procedures that are to be installed or altered (the correct syntax is in the sproc). At times, there can be over 100 sprocs being released for one application. I am trying to add some automation to this process since it is very tedious opening each sproc in an editor and then copy & paste into Query Analyzer.
I have gotten as far as being able to copy the list of sprocs into a text box and then check out each, shell out and use osql (input file is the checked sproc) and capture the osql output file to determine if the compile was successful or not.
I'd like to find out if there is any way of doing this as a "single step" without leave VB.Net (2005).
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
August 1, 2006 at 7:39 am
i have vb.net 2003 that reads files in, checks for proper syntax, drops old object, and loads new object.
our app reads a directory tree, displays them in a grid, and then when were ready loads them on to the selected server. The basic code is here:
we read .sql files in line by line into string (Body) and check for what type of object it is (trigger, SP, function, etc...) and set "SqlTypeName" to the object type Type
we use: Public GrantPermissionTo As String = "[DOMAIN USERS], someuser"
for the permissions
this deletes the object....
Public Sub Delete(ByVal conn As SqlConnection)
Try
Dim cmdText As String = "IF OBJECT_ID('" & Me.Name & "') IS NOT NULL DROP " _
& Me.SqlTypeName & " " & Me.Name
Dim cmd As SqlCommand = New SqlCommand(cmdText, conn)
cmd.ExecuteNonQuery()
Me.Loaded = False
Catch ex As Exception
Me.ErrorType = SqlItemError.Delete
Me.ErrorText = ex.Message
End Try
End Sub
and this loads it and sets permissions....
Public Sub Load(ByVal conn As SqlConnection)
Try
Dim cmd As SqlCommand = New SqlCommand(Me.Body, conn)
cmd.ExecuteNonQuery()
If (Me.GrantPermissionTo Nothing) And (Me.GrantPermissionTo "") Then
cmd = New SqlCommand("GRANT EXECUTE ON " & Me.Name & _
" TO " & Me.GrantPermissionTo, conn)
cmd.ExecuteNonQuery()
End If
Me.Loaded = True
Catch ex As Exception
Me.ErrorType = SqlItemError.Load
Me.ErrorText = ex.Message
End Try
End Sub
... let me know if you need more...
August 1, 2006 at 10:35 am
Thanks for example. I am in no way a VB coder let alone a VB.Net coder. I was hoping to not need to read the disk file into a command, that's why I chose to shell out to OSQL.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
August 1, 2006 at 3:02 pm
<?xml version="1.0" ?>
<!--
Filename: Create_DB_Command_File.wsf
Purpose: Builds a packing list of files from a specified
folder or drive.
-->
<package>
<job>
<resource id="inputPath">.</resource>
<resource id="outputPath">\build_latest_objects.cmd</resource>
<script type="text/javascript" language="javascript">
//<![CDATA[
var DAYS_AGO_INTERVAL = 5;
var background = "#ffffee";
var outputFile = getResource("inputPath") + getResource("outputPath");
var startDir = getResource("inputPath");
var title = "Folder View - ";
var count = 1;
// Open output file
fso = new ActiveXObject( "Scripting.FileSystemObject" );
fo = fso.OpenTextFile( outputFile, 2, true );
// Write HTML header and define styles
function WriteHead()
{
fo.Write( "@echo off\r\n"
+"REM: Command File Created by Microsoft Visual Database Tools\r\n"
+"REM: Date Generated: 7/11/2006\r\n"
+"REM: Authentication type: SQL Server\r\n"
+"REM: Usage: CommandFilename [Server] [Database] [Login] [Password]\r\n"
+"\r\n"
+"if '%1' == '' goto usage\r\n"
+"if '%2' == '' goto usage\r\n"
+"if '%3' == '' goto usage\r\n"
+"\r\n"
+"if '%1' == '/?' goto usage\r\n"
+"if '%1' == '-?' goto usage\r\n"
+"if '%1' == '?' goto usage\r\n"
+"if '%1' == '/help' goto usage\r\n" );
}
// Write HTML footer
function WriteFoot()
{
fo.Write( "\r\ngoto finish\r\n"
+"\r\n"
+"REM: How to use screen\r\n"
+":usage\r\n"
+"echo.\r\n"
+"echo Usage: MyScript Server Database User [Password]\r\n"
+"echo Server: the name of the target SQL Server\r\n"
+"echo Database: the name of the target database\r\n"
+"echo User: the login name on the target server\r\n"
+"echo Password: the password for the login on the target server (optional)\r\n"
+"echo.\r\n"
+"echo Example: MyScript.cmd MainServer MainDatabase MyName MyPassword\r\n"
+"echo.\r\n"
+"echo.\r\n"
+"goto done\r\n"
+"\r\n"
+"REM: error handler\r\n"
+":errors\r\n"
+"echo.\r\n"
+"echo WARNING! Error(s) were detected!\r\n"
+"echo --------------------------------\r\n"
+"echo Please evaluate the situation and, if needed,\r\n"
+"echo restart this command file. You may need to\r\n"
+"echo supply command parameters when executing\r\n"
+"echo this command file.\r\n"
+"echo.\r\n"
+"pause\r\n"
+"goto done\r\n"
+"\r\n"
+"REM: finished execution\r\n"
+":finish\r\n"
+"echo.\r\n"
+"echo Script execution is complete!\r\n"
+":done\r\n"
+"@echo on\r\n" );
}
// Write table row with data
function WriteRow( obj )
{
v = new Array( count, obj.file, obj.size, obj.date, obj.lines );
fo.Write( "\r\nrem ==== [DATE:"+ obj.date +"] ====\r\n" );
fo.Write( "\r\nosql -S %1 -d %2 -U %3 -P %4 -b -i \"" );
fo.Write( obj.file );
fo.Write( "\"\r\nif %ERRORLEVEL% NEQ 0 goto errors\r\n" );
count++;
}
// Object to store file properties.
function FileProps()
{
this.file = "";
this.size = "";
this.date = "";
this.lines = 0;
}
// Format the file size in friendlier terms
function FormatSize( size )
{
var gb = 1073741824;
var mb = 1048576;
var kb = 1024;
var rs;
if( size > gb )
rs = Math.round( size / gb ) + " GB";
else if( size > mb )
rs = Math.round( size / mb ) + " MB";
else if( size > kb )
rs = Math.round( size / kb ) + " KB";
else
rs = size + " B";
return( rs );
}
// Function to scan directory
function scandir( dir )
{
// Get Current Folder
var srcFolder = fso.GetFolder( dir );
// Get Files in current directory
var files = new Enumerator( srcFolder.files );
// Loop through files
for(; !files.atEnd(); files.moveNext() )
{
// Create object and store file properties
var fObj = new FileProps();
var sFileName = String(files.item());
fObj.file = sFileName.replace(startDir+"\\", "");
fObj.size = FormatSize( files.item().Size );
fObj.date = formatDate(new Date(files.item().DateLastModified), "MM/dd/yy hh:mm:ss");
fObj.lines = 0;
if (new Date(files.item().DateLastModified) > (dateAdd('d', -DAYS_AGO_INTERVAL, new Date()))) {
// Write data to file
if (/(.prc|.udf|.viw?)$/gi.test(sFileName)) {
WriteRow( fObj );
}
}
}
// Get any sub folders to current directory
var esub = new Enumerator( srcFolder.SubFolders );
// Loop through sub folder list and scan
// through a recursive call to this function
for(; !esub.atEnd(); esub.moveNext() )
{
var f = fso.GetFolder( esub.item() );
scandir( f );
}
}
function addZero(vNumber){
return ((vNumber < 10) ? "0" : "") + vNumber
}
function formatDate(vDate, vFormat){
/*
formatDate(DateObject, FormatString)
dd for Date of the month
MM for month
yy or yyyy for Year
hh for hours
mm for minutes
ss for seconds
e.g. dd/MM/yy hh:mm:ss
*/
var vDay = addZero(vDate.getDate());
var vMonth = vDate.getMonth()+1; // addZero(vDate.getMonth()+1);
var vYearLong = addZero(vDate.getFullYear());
var vYearShort = addZero(vDate.getFullYear().toString().substring(3,4));
var vYear = (vFormat.indexOf("yyyy")>-1?vYearLong:vYearShort)
var vHour = addZero(vDate.getHours());
var vMinute = addZero(vDate.getMinutes());
var vSecond = addZero(vDate.getSeconds());
var vDateString = vFormat.replace(/dd/g, vDay).replace(/MM/g, vMonth).replace(/y{1,4}/g, vYear)
vDateString = vDateString.replace(/hh/g, vHour).replace(/mm/g, vMinute).replace(/ss/g, vSecond)
return vDateString
}
// used by dateAdd, dateDiff, datePart, weekdayName, and monthName
// note: less strict than VBScript's isDate, since JS allows invalid dates to overflow (e.g. Jan 32 transparently becomes Feb 1)
function isDate(p_Expression){
return !isNaN(new Date(p_Expression)); // <<--- this needs checking
}
// REQUIRES: isDate()
function dateAdd(p_Interval, p_Number, p_Date){
if(!isDate(p_Date)){return "invalid date: '" + p_Date + "'";}
if(isNaN(p_Number)){return "invalid number: '" + p_Number + "'";}
p_Number = new Number(p_Number);
var dt = new Date(p_Date);
switch(p_Interval.toLowerCase()){
case "yyyy": {// year
dt.setFullYear(dt.getFullYear() + p_Number);
break;
}
case "q": { // quarter
dt.setMonth(dt.getMonth() + (p_Number*3));
break;
}
case "m": { // month
dt.setMonth(dt.getMonth() + p_Number);
break;
}
case "y": // day of year
case "d": // day
case "w": { // weekday
dt.setDate(dt.getDate() + p_Number);
break;
}
case "ww": { // week of year
dt.setDate(dt.getDate() + (p_Number*7));
break;
}
case "h": { // hour
dt.setHours(dt.getHours() + p_Number);
break;
}
case "n": { // minute
dt.setMinutes(dt.getMinutes() + p_Number);
break;
}
case "s": { // second
dt.setSeconds(dt.getSeconds() + p_Number);
break;
}
case "ms": { // second
dt.setMilliseconds(dt.getMilliseconds() + p_Number);
break;
}
default: {
return "invalid interval: '" + p_Interval + "'";
}
}
return dt;
}
// Main just for organization
function main()
{
WriteHead();
scandir( startDir );
WriteFoot();
// Tell the user we are finished.
WScript.Echo( "Finished" );
}
main();
//]]>
</script>
</job>
</package>
August 1, 2006 at 3:02 pm
Here is what I do using VS 2003...
That's it...
Some things to note:
I hope this helps. Ths code for Create_DB_Command_File.wsf is below.
CBaptiste
August 1, 2006 at 3:20 pm
Since the title is VB.Net you could write an executable to do the same... I suppose.
August 1, 2006 at 3:42 pm
CBaptiste - thanks for your reply - I have most of it done in VB.Net already.
Our developers use a variety of tools. Fortunately, the syntax for creating database objects hasn't changed. All source for dev, test(integration) and production is in Visual Source Safe. Only DBAs can promote objects into production databases (after approval by UA and PM), hence the need for a VSS interface.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply