SQL: BCP

Sample

In one batch file named "test.bat", perform the BCP to extract the records from the database through a stored procedure:

bcp "EXEC get_she_codes %1" queryout "%2.csv" -c -t, -S localhost -T

In another batch file, call test.bat to create the csv file: From here, you can call test.bat multiple times using different parameters:
call test.bat 1039646 JennyCompany-%date:~-4,4%%date:~-10,2%%date:~-7,2%
Output file:
JennyCompany-20153007.csv

Windows: Random Notes

Common DOS commands:

msinfo32 => shows you the system information

MVC: Routing Basics

The convention is to map URLs to a particular action (a method) on a particular controller. The action then executes (usually, but not always) returns an instance of ActionResult. The ActionResult handles Framework logic such as rendering to HTML or JSON, and writing to the HTTP response that will be returned to the user's browser.

Basic version: http://mydomain/controllername/methodname

Web: Random Notes

URL Paths

Two types of Paths:

• Absolute - starts with a leading slash
• Relative - does not start with slash. Location depends on where you are using them

default.asp is: /pages/default.asp - if called from /pages/linktool.asp

Two special directory names:
. means current directory
.. means parent directory

../default.asp is /pages/default.asp if called from /pages/tools/linktools.asp
../../default.asp is  /pages/default.asp if called from /pages/admin/tools/linktools.asp

~ tilde means root of the application
------------------------------------------------------------------

Nullable value types

private int? _copid;
protected double? d = 5;

The variable can be assigned "null". Normally primitives like "int" and "double" can't be null.

------------------------------------------------------------------

Null coalescing operator

x = y ?? z

x is assigned y's value. If y is null, then it's assigned z's value.

------------------------------------------------------------------
TryParse Method

Syntax: public static bool TryParse(string s, out int result)


if ( int.TryParse( itemId, out parsedItemId ) && parsedItemId > 0 )

Meaning: Parse itemId, assign it to parsedItemID as int. If parsedItemID is int and greater than 0 then...

------------------------------------------------------------------

(if) ? then : else

var objectInstance = condition ? foo : bar;

userType = user.Type == 0 ? "Admin"
         : user.Type == 1 ? "User"
         : user.Type == 2 ? "Employee"
         : "The default you didn't specify";


------------------------------------------------------------------

Web.Config - Handling errors

Default value = RemoteOnly - which shows a pretty error page to remote users.

<system.web>
<customErrors mode="On">
</system.web>

Error message:
Error. An error occurred while processing your request.

Error view - just a razor view where you can modify it to include other information.
Views > Shared > Error.cshtml

TSQL: For XML Path

To retrieve results of a SQL query as XML, specify FOR XML clause in the query.
FOR XML has PATH as one of its modes. It provides a simpler way to mix elements and attributes.

In PATH mode, column names or column aliases are treated as XPATH expressions, which indicate how the values are being mapped to XML.

To better illustrate the use of FOR XML PATH, I have this table #tmp:

create table #tmp
 (num int)

 insert into #tmp
 values(1),(2),(3)

Script	Output
SELECT num from #tmp FOR XML PATH	<row><num>1</num></row><row><num>2</num></row><row><num>3</num></row>
SELECT cast(num as varchar) from #tmp FOR XML PATH	<row>1</row><row>2</row><row>3</row>
SELECT num from #tmp FOR XML PATH ('')	<num>1</num><num>2</num><num>3</num>
SELECT cast(num as varchar) from #tmp FOR XML PATH ('')	123
SELECT cast(num as varchar) from #tmp FOR XML PATH (''), type	123
SELECT cast(num as varchar) from #tmp FOR XML PATH (''), type	,1,2,3
select (SELECT ',' + cast(num as varchar) from #tmp FOR XML PATH (''), type).value('.', 'varchar(max)')	,1,2,3
select stuff((SELECT ',' + cast(num as varchar) from #tmp FOR XML PATH (''), type).value('.', 'varchar(max)'), 1, 1, '')	1,2,3

I just used my samples on one of my query requirements. More examples in Books Online.

SQL: Random Notes

How to set the database from Single_User to Multi_User

ALTER DATABASE <databasename>
SET MULTI_USER;

If you're working on SQL Server Management, and one query window is connected to the database you'd like to alter, switch the database to master and run the alter query again.

Or to Single_User:
ALTER DATABASE <databasename> SET SINGLE_USER WITH ROLLBACK IMMEDIATE


To set the recovery model to simple:
ALTER DATABASE <database name> SET RECOVERY SIMPLE WITH NO_WAIT


Get some local system information:

SELECT NAME as "Server" FROM SYS.SERVERS; -- list of servers including linked servers
SELECT HOST_NAME() as "Host";
SELECT @@version as "Version";


To get some backup information - when the database was last backed up

 select database_name, type, backup_size, backup_start_date, backup_finish_date, DATEDIFF(dd,backup_start_date,getDate()) as "days"
from msdb.dbo.backupset s
where s.backup_set_id in
(
select max(s1.backup_set_id)
from msdb.dbo.backupset s1
where s1.database_name = s.database_name and s1.type = s.type
) and s.database_name in
(
select name
from msdb.sys.databases
)
order by database_name, type;

Log space
 DBCC sqlperf(logspace);


Database size

 select s.instance_name 'database', s.cntr_value DataSize, (select s1.cntr_value from sys.sysperfinfo s1 where s1.instance_name=s.instance_name and counter_name in ('Log File(s) Size (KB)')) LogFileSize,
(select s1.cntr_value from sys.sysperfinfo s1 where s1.instance_name=s.instance_name and counter_name in ('Log File(s) Used Size (KB)')) LogFileUsedSize
from sys.sysperfinfo s
where s.counter_name in ('Data File(s) Size (KB)')
and s.instance_name not in ('_Total', 'mssqlsystemresource')
order by s.instance_name;

SQL: Set up Database Mail

Configure Database Mail in your SQL Server

• Open SQL Server Management Studio > Management > Database Mail. Right-click and select Configure Database Mail

 

 

• Enable the feature if it's not available. Create new database mail account, and select Basic Authentication. Supply your network username and password.

• Set up Profile Security

• Test sending mail

Notes:

• Ensure that your Database Mail XPs is configured in your server as an option.
• Ensure Service Broker is enabled in your database.
• If you're getting this error, contact relevant IT teams within the organisation to set up an exchange relay for the server.

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-07-27T14:16:13). Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: 4.3.2 Service not available). )

• If you'd like to configure Gmail account, use the following:

Server Name: smtp.gmail.com
Port number: 587
Requires a secure connection (SSL). Choose Basic Authentication, and enter your Gmail ID and password.

• Ensure 'Database Mail XPs' is configured.

sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO
sp_configure;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE;
GO

• To confirm that the Database Mail is started

EXEC msdb.dbo.sysmail_help_status_sp;

To start it:
EXEC msdb.dbo.sysmail_start_sp;

• To view the error messages returned by Database Mail:

SELECT * FROM msdb.dbo.sysmail_event_log;

SQL: Linked Server

Sample script I used to create a linked server.

More explanation later...

EXEC master.dbo.sp_addlinkedserver @server = N'<linkedservername>', @provider=N'SQLNCLI', @datasrc=N'<sqlservername\ instance,port>', @srvproduct='<anything could be db name but it doesn't matter>';

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'<linkedservername>',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL;




EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'collation compatible', @optvalue=N'false';

EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'data access', @optvalue=N'true';

EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'dist', @optvalue=N'false';

EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'pub', @optvalue=N'false';

EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'rpc', @optvalue=N'false';

EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'rpc out', @optvalue=N'false';

EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'sub', @optvalue=N'false';

EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'connect timeout', @optvalue=N'0';

EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'collation name', @optvalue=null;

EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'lazy schema validation', @optvalue=N'false';

EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'query timeout', @optvalue=N'0';

EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'use remote collation', @optvalue=N'true';

EXEC master.dbo.sp_serveroption @server=N'<linkedservername>', @optname=N'remote proc transaction promotion', @optvalue=N'true';

------------------------------------

Script used to set up security options for one linked server:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'<linkedservername>',@useself=N'False',@locallogin=NULL,@rmtuser=N'lawlex_user',@rmtpassword='<somepassword>'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'<linkedservername>',@useself=N'True',@locallogin=N'lawlex_user',@rmtuser=NULL,@rmtpassword=NULL
GO

Web: How to resolve Warning : The element 'entityFramework' has invalid child element 'providers'. List of possible elements expected: 'contexts'

Error:

The element 'entityFramework' has invalid child element 'providers'. List of possible elements expected: 'contexts'

<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>


Solution:
Install Entity Framework 6 Designer for VS2012 to update the schema that validates the config files.

http://www.microsoft.com/en-us/download/details.aspx?id=40762

TSQL: Referencing Sequence Objects

Introduced in SQL Server 2012


create sequence AdSequence
as int
start with 1
increment by 1
no cycle
no cache;

select nextvalue fro AdSequence


select nextvalue fro AdSequence
go 50

TOBETESTED...

TSQL: Updating Large-Value Data Types

Syntax:

UPDATE tablename
set column_name.WRITE(expression, @Offset, @Length)
[where <condition> ]

where:
expression - value to be written
@Offset - start point, zero-based, bigint
        NULL offset means perform an append to existing column value
@Length - length from offset to be replaced by the expression, bigint
        NULL length removes all data from the offset to the end of the column value

Sample:--------------------------------------------------------------
create table temp4
(textid int identity(1,1),
sampletext varchar(max))

insert into temp4(sampletext)
values('This is the day that the Lord has made. We will rejoice and be glad in it.')
---------------------------
update temp4
set sampletext.write(' Yes', null, null)
where textid = 1
Output:
This is the day that the Lord has made. We will rejoice and be glad in it. Yes

---------------------------
update temp4
set sampletext.write('Love... love... love...', 0, 0)
where textid = 1

Output:
Love... love... love...This is the day that the Lord has made. We will rejoice and be glad in it. Yes

---------------------------
update temp4
set sampletext.write('TRUE -', 23, 0)
where textid = 1

Output:
Love... love... love...TRUE -This is the day that the Lord has made. We will rejoice and be glad in it. Yes

---------------------------
update temp4
 set sampletext.write('Clearly', 69, null)
 where textid = 1

Output:
Love... love... love...TRUE -This is the day that the Lord has made. Clearly