Category: SQL Server

Posted on

Inserting multiple records at once into a database table

For demonstration purposes, we are going to assume that we have a table named “employees” with two fields, name and department.

Oracle

insert all
  into employees (name, department) values('Aaron','Accounting')
  into employees (name, department) values('Ibis','IT')
  into employees (name, department) values('Mary','Marketing')
  into employees (name, department) values('Paul','Useless')
select '' from dual;

MySQL, PostgreSQL, and SQL Server

insert into employees (name, department) values
  ('Aaron','Accounting'),
  ('Ibis','IT'),
  ('Mary','Marketing'),
  ('Paul','Useless');
Posted on

Crystal Reports Server XI backup and recovery step-by-step

I am running Crystal Reports Server XI with a SQL Server 2000 database. These two components are running on two different servers, but it should not change the procedures below much.

Procedures for Scheduled Daily Backups

On the database server, I set up a batch file that utilizes SQL Server 2000’s osql command to perform backup of the Central Management Server (CMS) database; I scheduled it to run nightly. The content of the batch file is as follows. Note that you should substitute in your server name, user name, password, and file paths. Also, note the copy command makes a copy of the backup file to another server, which is highly recommended; how good is a backup if the system failure is caused by a hard disk burning out and the backup resides on the same hard disk, right?

osql -S [serverHostName] -U [databaseUsername] -P [myPassword] -Q "begin declare @CMSBackupFile varchar(255) set @CMSBackupFile='C:cms.bak' backup database [cmsDatabaseName] to disk = @CMSBackupFile end"
xcopy.exe /c /d /e /h /r /y C:cms.bak \anotherServerbackupscms

On the Crystal Reports Server XI server, there is a folder that stores the actual reports; Crystal Reports Server XI refers to this folder as the Input File Repository. By default, it is located at C:Program FilesBusiness ObjectsBusinessObjects Enterprise 11FileStoreInput, which is what the example below will make use. The example is another batch file, scheduled to run nightly, that makes a backup copy of the reports to another server.

xcopy.exe /c /d /e /h /r /y "C:Program FilesBusiness ObjectsBusinessObjects Enterprise 11FileStoreInput*.*" \anotherServerbackupsfileStore

Restore Procedures

My plan to deal with the unthinkable is listed below.

For the SQL Server, if needed, install SQL Server and then copy the backup file (“cms.bak” in this example) to the new database server. Next, issue the following two osql commands in the command prompt to create/recreate the CMS database.

osql -S [newServerHostName] -U [databaseUsername] -P [myPassword] -Q "drop database [cmsDatabaseName]"
osql -S [newServerHostName] -U [databaseUsername] -P [myPassword] -Q "restore database [cmsDatabaseName] from disk='c:cms.bak'"

Next, we will likely find an orphaned user in the restored database; this can be seen in SQL Server Enterprise Manager’s “Users” section for the CMS database; you will see an user without “Login Name”. To fix this, run the following osql command in the command prompt.

osql -S [newServerHostName] -U [databaseUsername] -P [myPassword] -d [cmsDatabaseName] -Q "exec sp_change_users_login 'Auto_Fix', '[crystalReportsDatabaseUser]', NULL, '[passwordHere]'"

For the Crystal Reports Server XI reports, if needed, they can be copied directly from your backup to the new Input File Repository location.

When you have completed the restore, you may wish to restart the CMS and Input File Repository services in the Central Configuration Manager.

Posted on

Fixing orphaned users after attaching or restoring a SQL Server database

I ran into this issue when I took a backup set from a production database and restored it into a machine in my development environment. I noticed that the recently restored database has an user named “bob”, but when looking at bob’s entry in the newly restored database, the Login Name field is blank. To fix this, I made use of SQL Server’s built-in stored procedure sp_change_users_login. The syntax is below:

Syntax:

sp_change_users_login 'action'
	[, 'user']
	[, 'login']
	[, 'password']

Valid values for 'action':

Report
Update_One
Auto_Fix

The “Report” action displays a report of all possible orphans in the current database and requires no further parameters. Sample usage:

exec sp_change_users_login 'Report'

The “Update_One” action links a specified orphaned user in the current database to an existing SQL Server login. With this action, the “user” and “login” parameters must be provided, and the “password” parameter must be NULL or not provided. Sample usage:

exec sp_change_users_login 'Update_One', 'bob', 'bob'

Finally, the method I chose to perform to fix my issue was the “Auto-Fix” action, which saves me the step of having to manually create the “bob” SQL Server login first because it will automatically create the login if it does not already exist. The “Auto_Fix” action requires the “user” and “password” parameters, and the “login” parameter must be NULL. Sample usage:

exec sp_change_users_login 'Auto_Fix', 'bob', NULL, 'password123'
Posted on

Building an AIM buddy list from your database employee table with VBScript

The code below can be used against Oracle, SQL Server, or MySQL database table to automatically create an AIM buddy list. The SQL statement should select out a “group_name” field (ideas: a department name such as “Accounting” or “Purchasing”, an office location such as “Taipei” or “New York”, etc.) and an “aim_name” field. The “group_name” will be used as AIM groups, while “aim_name” are the users’ actual registered AIM names. The output file is a flat text file in a format that can be imported into AIM.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' AIM Buddy List Builder                                        '
'                                                               '
' Description: Builds an AIM buddy list from your database      '
'      employee table.                                          '
' Author: C. Peter Chen                                         '
' Version Tracker:                                              '
'       1.0   20081021   Base version                           '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "oracle"                ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "hostName"              ' Hostname of the database server
dbName = "dbName"                ' Name of the database/SID
dbUser = "user"                  ' Name of the user
dbPass = "password"              ' Password of the above-named user
outputFile = "c:buddylist.blt"  ' Path and file name of the output CSV file

' SQL statement below; be sure to select out "group_name" and "aim_name" in your SQL statement.
sqlStr = "select department_name as group_name, aim_name from employees_table where aim_name is not null order by department_name, aim_name"
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''

dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
if dbType = "oracle" then
	conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
elseif dbType = "sqlserver" then
	conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";")
elseif dbType = "mysql" then
	conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")
end if

dim a, showList, prevGroup

set a = fso.createtextfile(outputFile)
a.writeline ("Config {")
a.writeline (" version 1")
a.writeline ("}")
a.writeline ("User {")
a.writeline (" screenName dummyAimName")
a.writeline ("}")
a.writeline ("Buddy {")
a.writeline (" list {")

set showList = conn.execute(sqlstr)

prevGroup = "placeholder"
do while not showList.eof
	if (showList("group_name") <> prevGroup) then
		if (prevGroup <> "placeholder") then
			a.writeline ("  }")
		end if
		a.writeline ("  """ + showList("group_name") + """ {")
	end if
	a.writeline ("   " + showList("aim_name"))
	prevGroup = showList("group_name")
	showList.movenext
loop
showList.close
set showList = nothing

a.writeline ("  }")

a.writeline (" }")
a.writeline ("}")

' Close
set a = nothing
set fso = nothing
conn.close
set conn = nothing

'You're all done!!  Enjoy the file created.
msgbox("AIM Buddy List Created!")

Interested in obtaining a generic AIM buddy list import file format? Please see below for an illustration with fictional data.

Config {
 version 1
}
User {
 screenName dummyAimName
}
Buddy {
 list {
  "Accounting" {
   MrCFO_fictionalUser
   BobAtAccounting_fictionalUser
   JaneDoe_fictionalUser
  }
  "Purchasing" {
   LewisTheBuyer_fictionalUser
  }
  "Useless Employees" {
   PaulJohnson_fictionalUser
  }
 }
}
Posted on

Writing SQL output to CSV with VBScript

Enter your specific information in the “Configuration” section near the top of the script. For the “dbType” variable, the only accepted values are “oracle”, “sqlserver”, or “mysql”. Once this is done, just run the script and you should have your quote-delimited comma-separated CSV file!

The email-related variables are optional. To enable the emailing functionality (send the generated CSV file to the address as an attachment), enter the recipient email address in the box. If you do not wish to email, just leave that variable as empty string (“”), and the other email related variable such as smtp and smtpPort will be ignored.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Data Exporter                                                 '
'                                                               '
' Description: Allows the output of data to CSV file from a SQL '
'       statement to either Oracle, SQL Server, or MySQL        '
' Author: C. Peter Chen, http://dev-notes.com                   '
' Version Tracker:                                              '
'       1.0   20080414 Original version                         '
'	1.1   20080807 Added email functionality                '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
option explicit
dim dbType, dbHost, dbName, dbUser, dbPass, outputFile, email, subj, body, smtp, smtpPort, sqlstr

'''''''''''''''''
' Configuration '
'''''''''''''''''
dbType = "oracle"                 ' Valid values: "oracle", "sqlserver", "mysql"
dbHost = "dbhost"                 ' Hostname of the database server
dbName = "dbname"                 ' Name of the database/SID
dbUser = "username"               ' Name of the user
dbPass = "password"               ' Password of the above-named user
outputFile = "c:\output.csv"      ' Path and file name of the output CSV file
email = "email@me.here"           ' Enter email here should you wish to email the CSV file (as attachment); if no email, leave it as empty string ""
subj = "Email Subject"            ' The subject of your email; required only if you send the CSV over email
body = "Put a message here!"      ' The body of your email; required only if you send the CSV over email
smtp = "mail.server.com"          ' Name of your SMTP server; required only if you send the CSV over email
smtpPort = 25                     ' SMTP port used by your server, usually 25; required only if you send the CSV over email
sqlStr = "select user from dual"  ' SQL statement you wish to execute
'''''''''''''''''''''
' End Configuration '
'''''''''''''''''''''



dim fso, conn

'Create filesystem object 
set fso = CreateObject("Scripting.FileSystemObject")

'Database connection info
set Conn = CreateObject("ADODB.connection")
Conn.ConnectionTimeout = 30
Conn.CommandTimeout = 30
if dbType = "oracle" then
	conn.open("Provider=MSDAORA.1;User ID=" & dbUser & ";Password=" & dbPass & ";Data Source=" & dbName & ";Persist Security Info=False")
elseif dbType = "sqlserver" then
	conn.open("Driver={SQL Server};Server=" & dbHost & ";Database=" & dbName & ";Uid=" & dbUser & ";Pwd=" & dbPass & ";")
elseif dbType = "mysql" then
	conn.open("DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & dbHost & ";PORT=3306;DATABASE=" & dbName & "; UID=" & dbUser & "; PASSWORD=" & dbPass & "; OPTION=3")
end if

' Subprocedure to generate data.  Two parameters:
'   1. fPath=where to create the file
'   2. sqlstr=the database query
sub MakeDataFile(fPath, sqlstr)
	dim a, showList, intcount
	set a = fso.createtextfile(fPath)
	
	set showList = conn.execute(sqlstr)
	for intcount = 0 to showList.fields.count -1
		if intcount <> showList.fields.count-1 then
			a.write """" & showList.fields(intcount).name & ""","
		else
			a.write """" & showList.fields(intcount).name & """"
		end if
	next
	a.writeline ""
	
	do while not showList.eof
		for intcount = 0 to showList.fields.count - 1
			if intcount <> showList.fields.count - 1 then
				a.write """" & showList.fields(intcount).value & ""","
			else
				a.write """" & showList.fields(intcount).value & """"
			end if
		next
		a.writeline ""
		showList.movenext
	loop
	showList.close
	set showList = nothing

	set a = nothing
end sub

' Call the subprocedure
call MakeDataFile(outputFile,sqlstr)

' Close
set fso = nothing
conn.close
set conn = nothing

if email <> "" then
	dim objMessage
	Set objMessage = CreateObject("CDO.Message")
	objMessage.Subject = "Test Email from vbs"
	objMessage.From = email
	objMessage.To = email
	objMessage.TextBody = "Please see attached file."
	objMessage.AddAttachment outputFile
	
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = smtp
	objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = smtpPort
	
objMessage.Configuration.Fields.Update
	
	objMessage.Send
end if

'You're all done!!  Enjoy the file created.
msgbox("Data Writer Done!")
Proudly powered by WordPress