Here are some websites I've used throughout the years to find different data.
Look up addresses, zip codes, etc...
http://www.melissadata.com/lookups/index.htm
Great place to find and compare cities
http://www.bestplaces.net/
Thursday, June 22, 2017
Tuesday, June 20, 2017
Best BBQ on the Map
I created this map of the Best rated BBQ in Texas. Enjoy the eating...
First Source: http://www.texasmonthly.com/food/the-list-the-top-50-barbecue-joints-in-texas/
First Source: http://www.texasmonthly.com/food/the-list-the-top-50-barbecue-joints-in-texas/
Monday, March 6, 2017
SQL to Create CSV file and upload it using SSH
On a project I had in the past I had to create a comma separated file from a query and upload the results to a secure server using SSH.
Here is the commands I created to do it all in one script.
exec master.dbo.xp_cmdshell 'sqlcmd -S . -d Admin -E -s, -W -h -1 -Q "set nocount on SELECT * FROM YOURTABLE" > D:\YOURFILE.csv'
go
declare @thisdate smalldatetime
declare @thisyear nvarchar(4)
declare @thismonth nvarchar(2)
declare @thisday nvarchar(2)
declare @thislist varchar(8)
declare @query varchar(1000)
-- This block of code create the date stamp on the file name with a particular format
set @thisdate = getdate()
set @thisyear = year(@thisdate)
set @thismonth = month(@thisdate)
set @thisday = day(@thisdate)
if len(@thismonth) = 1 set @thismonth = '0' + @thismonth
if len(@thisday) = 1 set @thisday = '0' + @thisday
print @thismonth
print @thisday
set @thislist = @thismonth + @thisday + @thisyear
print @thislist
-- Rename the file if you need to make it unique
set @query = 'master.dbo.xp_cmdshell "rename D:\YOURFILE.csv YOURFILE_'+ @thislist +'.csv"'
print @query
exec(@query)
go
-- Calling WINSCP using a saved profile in the script
master.dbo.xp_cmdshell 'D:\YOURWINSCPPATH\WinSCP.exe -script=D:\SSHScript.txt'
go
-- Move your file to an archive is highly recommended so you can easily upload manually.
master.dbo.xp_cmdshell 'move D:\*.csv D:\Archive\'
Here is the commands I created to do it all in one script.
exec master.dbo.xp_cmdshell 'sqlcmd -S . -d Admin -E -s, -W -h -1 -Q "set nocount on SELECT * FROM YOURTABLE" > D:\YOURFILE.csv'
go
declare @thisdate smalldatetime
declare @thisyear nvarchar(4)
declare @thismonth nvarchar(2)
declare @thisday nvarchar(2)
declare @thislist varchar(8)
declare @query varchar(1000)
-- This block of code create the date stamp on the file name with a particular format
set @thisdate = getdate()
set @thisyear = year(@thisdate)
set @thismonth = month(@thisdate)
set @thisday = day(@thisdate)
if len(@thismonth) = 1 set @thismonth = '0' + @thismonth
if len(@thisday) = 1 set @thisday = '0' + @thisday
print @thismonth
print @thisday
set @thislist = @thismonth + @thisday + @thisyear
print @thislist
-- Rename the file if you need to make it unique
set @query = 'master.dbo.xp_cmdshell "rename D:\YOURFILE.csv YOURFILE_'+ @thislist +'.csv"'
print @query
exec(@query)
go
-- Calling WINSCP using a saved profile in the script
master.dbo.xp_cmdshell 'D:\YOURWINSCPPATH\WinSCP.exe -script=D:\SSHScript.txt'
go
-- Move your file to an archive is highly recommended so you can easily upload manually.
master.dbo.xp_cmdshell 'move D:\*.csv D:\Archive\'
SQL Rights Setup Read Only, Write, and/or Read/Write
Here is a query I use to lock down users hitting all my databases with Read Only rights:
DROP LOGIN [YOUR_USER]
GO
-- ADD USERS
CREATE LOGIN [YOUR_USER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go
-- add the user to each database
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
CREATE USER [YOUR_USER] FOR LOGIN [YOUR_USER]
PRINT ''User Created in '' + DB_NAME()
END
ELSE
PRINT ''User Already Existed in '' + DB_NAME() '
go
-- add the user to each database db_datareader role
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
exec sp_addrolemember ''db_datareader'',''YOUR_USER''
PRINT ''User added to role in '' + DB_NAME()
END
ELSE
PRINT ''User does not exist in '' + DB_NAME() '
go
-- grant view any definition to user
USE master
go
GRANT VIEW ANY DATABASE TO [YOUR_USER]
PRINT 'Server wide permission granted'
Go
If you want to add writer use this query
-- Add Write ability to user for each database
-- add the user to each database db_datawriter role
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
exec sp_addrolemember ''db_datawriter'',''YOUR_USER''
PRINT ''User added to role in '' + DB_NAME()
END
ELSE
PRINT ''User does not exist in '' + DB_NAME() '
go
DROP LOGIN [YOUR_USER]
GO
-- ADD USERS
CREATE LOGIN [YOUR_USER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go
-- add the user to each database
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
CREATE USER [YOUR_USER] FOR LOGIN [YOUR_USER]
PRINT ''User Created in '' + DB_NAME()
END
ELSE
PRINT ''User Already Existed in '' + DB_NAME() '
go
-- add the user to each database db_datareader role
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
exec sp_addrolemember ''db_datareader'',''YOUR_USER''
PRINT ''User added to role in '' + DB_NAME()
END
ELSE
PRINT ''User does not exist in '' + DB_NAME() '
go
-- grant view any definition to user
USE master
go
GRANT VIEW ANY DATABASE TO [YOUR_USER]
PRINT 'Server wide permission granted'
Go
If you want to add writer use this query
-- Add Write ability to user for each database
-- add the user to each database db_datawriter role
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
exec sp_addrolemember ''db_datawriter'',''YOUR_USER''
PRINT ''User added to role in '' + DB_NAME()
END
ELSE
PRINT ''User does not exist in '' + DB_NAME() '
go
Sometimes users require more access than just read or write. Like to update stored procedures, etc..
-- Add the user to each database as db_owner role
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N''YOUR_USER'')
BEGIN
exec sp_addrolemember ''db_owner'',''YOUR_USER''
PRINT ''User added to role in '' + DB_NAME()
END
ELSE
PRINT ''User does not exist in '' + DB_NAME() '
go
Here are some links on the rights of each sql server roles:
https://technet.microsoft.com/en-us/library/ms189612(v=sql.105).aspx
http://www.jasonstrate.com/2013/07/security-questions-difference-between-db_datawriter-and-db_ddladmin/
https://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/
https://technet.microsoft.com/en-us/library/ms189612(v=sql.105).aspx
http://www.jasonstrate.com/2013/07/security-questions-difference-between-db_datawriter-and-db_ddladmin/
https://www.mssqltips.com/sqlservertip/1900/understanding-sql-server-fixed-database-roles/
Using SQL Query to access Active Directory
One time I was asked to use SQL Server to access Active Directory and came across these queries that helped me.
--Setup Active Directory Linked server
--EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
--Simple Select
--select * from openquery (ADSI,'SELECT NAME FROM ''LDAP://yourdomain.com''')
--Complex Select
SELECT
samAccountName As WinNT_ID
,sn As Last_Name
,givenName As First_Name
,displayName AS Display_Name
,whencreated
,mail As Email_Address
,ISNULL(title, 'N/A') As Job_Title
,ISNULL(telephonenumber, 'N/A') As Phone_Number
,ISNULL(mobile, 'N/A') As Mobile_Number
,ISNULL(facsimiletelephonenumber, 'N/A') As Fax_Number
,ISNULL(physicalDeliveryOfficeName, 'N/A') As Office
--,ISNULL(department, 'N/A') AS Department
,streetAddress As Stree_Address
,l As City
,st As State
,postalCode As Zip_Code
--,cast(telexnumber as varchar(max))
FROM OPENQUERY(ADSI, '
SELECT
samAccountName, sn, givenName, title, displayName
,mail, telephonenumber, mobile, facsimiletelephonenumber
,physicalDeliveryOfficeName,l,streetAddress,st,postalCode,whencreated,telexnumber
FROM ''LDAP://yourdomain.com'
WHERE objectClass=''user'' AND objectClass<>''computer''
') AS tblADSI
WHERE sn IS NOT NULL
AND mail IS NOT NULL
--AND telexnumber IS NOT NULL
--and len(telexnumber) < 20
ORDER BY sn
--Setup Active Directory Linked server
--EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
--Simple Select
--select * from openquery (ADSI,'SELECT NAME FROM ''LDAP://yourdomain.com''')
--Complex Select
SELECT
samAccountName As WinNT_ID
,sn As Last_Name
,givenName As First_Name
,displayName AS Display_Name
,whencreated
,mail As Email_Address
,ISNULL(title, 'N/A') As Job_Title
,ISNULL(telephonenumber, 'N/A') As Phone_Number
,ISNULL(mobile, 'N/A') As Mobile_Number
,ISNULL(facsimiletelephonenumber, 'N/A') As Fax_Number
,ISNULL(physicalDeliveryOfficeName, 'N/A') As Office
--,ISNULL(department, 'N/A') AS Department
,streetAddress As Stree_Address
,l As City
,st As State
,postalCode As Zip_Code
--,cast(telexnumber as varchar(max))
FROM OPENQUERY(ADSI, '
SELECT
samAccountName, sn, givenName, title, displayName
,mail, telephonenumber, mobile, facsimiletelephonenumber
,physicalDeliveryOfficeName,l,streetAddress,st,postalCode,whencreated,telexnumber
FROM ''LDAP://yourdomain.com'
WHERE objectClass=''user'' AND objectClass<>''computer''
') AS tblADSI
WHERE sn IS NOT NULL
AND mail IS NOT NULL
--AND telexnumber IS NOT NULL
--and len(telexnumber) < 20
ORDER BY sn
Pulling 95th Percentile from a SQL query
A few times I've been asked to give 95th percentile of query results to help sharpen results on reporting where there a some outliers.
-- Select the 95th Percentile of a given query
declare @ct as int
,@ctout as int
,@cut as int
,@cut1 as int
,@cut2 as int
,@q as nvarchar(255)
,@q2 as nvarchar(255)
,@ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@ctout int OUTPUT';
set @q = N'select top 257 * from msdb.dbo.backupfile T ' -- Source Query
set @q2 = N'select @ctout=count(*) from (' + @q + ') x2'
exec sp_executesql @q2,@ParmDefinition, @ctout=@ct OUTPUT;
set @cut = ((@ct-@ct*0.95) / 2 )
set @cut1 = @ct-@cut
set @cut2 = @cut1-@cut
print @ct
print @cut
print @cut1
print @cut2
exec('select top ' + @cut1 + ' * from (' + @q + ') x3 Order by backup_set_id desc')
exec('select top ' + @cut2 + ' * from (select top ' + @cut1 + ' * from (' + @q + ') x3 Order by backup_set_id desc)x4 Order by backup_set_id')
select top 257 * from msdb.dbo.backupfile T Order by backup_set_id desc
-- Select the 95th Percentile of a given query
declare @ct as int
,@ctout as int
,@cut as int
,@cut1 as int
,@cut2 as int
,@q as nvarchar(255)
,@q2 as nvarchar(255)
,@ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@ctout int OUTPUT';
set @q = N'select top 257 * from msdb.dbo.backupfile T ' -- Source Query
set @q2 = N'select @ctout=count(*) from (' + @q + ') x2'
exec sp_executesql @q2,@ParmDefinition, @ctout=@ct OUTPUT;
set @cut = ((@ct-@ct*0.95) / 2 )
set @cut1 = @ct-@cut
set @cut2 = @cut1-@cut
print @ct
print @cut
print @cut1
print @cut2
exec('select top ' + @cut1 + ' * from (' + @q + ') x3 Order by backup_set_id desc')
exec('select top ' + @cut2 + ' * from (select top ' + @cut1 + ' * from (' + @q + ') x3 Order by backup_set_id desc)x4 Order by backup_set_id')
select top 257 * from msdb.dbo.backupfile T Order by backup_set_id desc
My Top SQL Server Resources
Here is a few websites I follow for MS SQL Server Stuff:
Great for everything under the SQL Sun
Great script for checking that everything is setup
correctly.
Great for backups and indexing cleanups
Great for SQL Server Versions, CU, and SP updates
SQL Conferences
Subscribe to:
Posts (Atom)