Database

<br />
ALTER VIEW [dbo].[VW_Toi_Deeds_Vuyiswa]<br />
<br />
AS<br />
<br />
SELECT ID, PROPERTY_KEY, LIS_KEY, CASE <br />
WHEN Isnumeric(substring(FUNCTION_KEY,6,4)) = 1 --If Its Numeric on last digit of the <br />
<br />
--GEOSS part(4 Digits) <br />
<br />
AND SUBSTRING(FUNCTION_KEY, 6, 1) = 0 --Making sure that there is a Digit After the Funckey,and its "0"<br />
<br />
THEN SUBSTRING(FUNCTION_KEY, 1, 5) + SUBSTRING(FUNCTION_KEY, 7, 3) <br />
<br />
ELSE FUNCTION_KEY <br />
<br />
END AS FUNCTION_KEY<br />
<br />
, TOWN_NAME, PARCEL_NUMBER, PORTION_NUMBER, FARM_NAME, REGISTRATION_DIVISION, FARM_NUMBER, FARM_PORTION,<br />
 SECTIONAL_SCHEME_NAME, SECTIONAL_SCHEME_NUMBER, SECTIONAL_SCHEME_UNIT_NUMBER, BUYER_NAME, BUYER_ID, <br />
REGISTRATION_DATE, PURCHASE_DATE, PURCHASE_PRICE, <br />
REASON_NO_PRICE, NEW_TITLE_DEED_NUMBER, OLD_TITLE_DEED_NUMBER,<br />
STATUS, SHARES,DATE_CAPTURED,HISTORY_ID<br />
FROM DEEDS.DEEDS_ADMIN.TSHWANE_OWNER_INFORMATION<br />
<br />

<br />
SUBSTRING(FUNCTION_KEY, 1, 5) + SUBSTRING(FUNCTION_KEY, 7, 3) <br />

<br />
 SUBSTRING(FUNCTION_KEY, 1, 5) + SUBSTRING(FUNCTION_KEY, 6, 4)<br />

DECLARE @PaperCost money<br />
declare @PaperCategories nvarchar(30)<br />
set @PaperCategories = '3,7'<br />
<br />
DECLARE @sqlcmd varchar(255)<br />
<br />
select @sqlcmd = ' SELECT sum(amounts) FROM tbl_CostInfo<br />
	where CatID in (' + RTRIM(@PaperCategories) + ') '<br />
<br />
SELECT @PaperCost = exec( @sqlcmd ) 

use MyDatabase

declare @dtStart datetime;
declare @dtEnd datetime;
declare @publisher varchar(50);
declare @outpath varchar(255);
declare @sql varchar(512);
declare @params varchar(512);

set @publisher = 'SomePublisher';
set @dtStart = '2008-01-01 00:00:00.000';
set @dtEnd   = '2008-01-01 11:59:59.999';

set @sql     = 'select * from MyDatabase.dbo.downloads where endtimestamp between ''' +
               Convert(varchar(50), @dtStart, 121) + ''' and ''' + 
               Convert(varchar(50), @dtEnd, 121) + ''' and publishername=''' + @publisher + 
               ''' order by endtimestamp, serialnumber';
set @outpath = '\\SERVERNAME\program files\appfolder\' + @publisher + '_January01.xml';

set @params  = 'bcp "' + @sql + '" queryout "' + @outpath + '" -T -c -r -t -x -S SERVERNAME\SQLSERVER'

execute master..xp_cmdshell @params;

<br />
SQLState = HY000, NativeError = 0<br />
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file<br />

use MyDatabase

declare @dtStart datetime;
declare @dtEnd datetime;
declare @publisher varchar(50);
declare @outpath varchar(255);
declare @sql varchar(512);
declare @params varchar(512);

set @publisher = 'SomePublisher';
set @dtStart = '2008-01-01 00:00:00.000';
set @dtEnd   = '2008-01-01 11:59:59.999';

set @sql     = 'select * from MyDatabase.dbo.downloads where endtimestamp between ''' +
               Convert(varchar(50), @dtStart, 121) + ''' and ''' + 
               Convert(varchar(50), @dtEnd, 121) + ''' and publishername=''' + @publisher + 
               ''' order by endtimestamp, serialnumber';
set @outpath = '\\program files\appfolder\' + @publisher + '_January01.xml';

set @params  = 'bcp "' + @sql + '" queryout "' + @outpath + '" -T -c -r -t -x'

execute master..xp_cmdshell @params;

<br />
SQLState = 08001, NativeError = 2 <br />
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2]. <br />
<br />
SQLState = HYT00, NativeError = 0<br />
Error = [Microsoft][SQL Native Client]Login timeout expired <br />
<br />
SQLState = 08001, NativeError = 2<br />
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the  default settings SQL Server does not allow remote connections.<br />
NULL

<br />
'...<br />
<br />
Dim cmdSql As SqlCommand = New SqlCommand<br />
cmdSql.Connection = connSql<br />
cmdSql.CommandText = "UPDATE pfSchool SET notes = @notes WHERE ID = @id"<br />
<br />
cmdSql.Parameters.Add(New SqlParameter("@notes", SqlDbType.NText))<br />
cmdSql.Parameters("@notes").SourceColumn = "notes"<br />
<br />
cmdSql.Parameters.Add(New SqlParameter("@id", SqlDbType.Int))<br />
cmdSql.Parameters("@id").SourceColumn = "ID"<br />
<br />
' modify the data for testing<br />
dtData.Rows(0)("notes") = "test2"<br />
<br />
Dim daSql As SqlDataAdapter = New SqlDataAdapter<br />
daSql.UpdateCommand = cmdSql<br />
daSql.UpdateCommand.Connection.Open()<br />
<br />
daSql.Update(dtData)<br />
<br />
'...<br />