Syntax:
USE [YourDatabaseName]
GO
ALTER TABLE YourTableName
ALTER COLUMN column_name datatype;
Senario:
I have a database call Afterowl with a table call Birds. In my Birds table, I want to change TypeOfBird data type from NVARCHAR(10) to VARCHAR(100).
Example:
USE Afterowl
GO
ALTER TABLE Birds
ALTER COLUMN TypeOfBird VARCHAR(100)
I use this to bounce against my weekly ETL’s to make sure that every day of the week data is captured. This helps me quickly identify a Calendar date with no ETL date which means no data was pulled.
DECLARE @start_date DATETIME= ‘2017-01-01’;
DECLARE @end_date DATETIME= GETDATE();
WITH CalendarDates
AS (
SELECT CAST(@start_date AS DATE) AS Calendar_Day
UNION ALL
SELECT DATEADD(DAY, 1, Calendar_Day)
FROM CalendarDates
WHERE Calendar_Day < @end_date)
SELECT Calendar_Day,
DATENAME(dw, Calendar_Day) AS Day_of_Week,
DATENAME(week, Calendar_Day) AS Week_Number
FROM CalendarDates OPTION(MAXRECURSION 0);
Check out the FREE Afterowl Invoice Me Windows Application.
Here’s a great trick to find specific patterns for a column. The LIKE CLAUSE can be use as a RegEx
Here’s a link to LIKE from Microsoft.
You can use the wildcard pattern matching characters as literal characters. To use a wildcard character as a literal character, enclose the wildcard character in brackets. The following table shows several examples of using the LIKE keyword and the [ ] wildcard characters.
| Symbol | Meaning |
|---|---|
| LIKE ‘5[%]’ | 5% |
| LIKE ‘[_]n’ | _n |
| LIKE ‘[a-cdf]’ | a, b, c, d, or f |
| LIKE ‘[-acdf]’ | -, a, c, d, or f |
| LIKE ‘[ [ ]’ | [ |
| LIKE ‘]’ | ] |
| LIKE ‘abc[_]d%’ | abc_d and abc_de |
| LIKE ‘abc[def]’ | abcd, abce, and abcf |
In this example, I want to find all user names that starts with the letter “S” and is follow by numbers 0-9.
Here’s a simple query to directly SELECT a Text File.
HDR = Header Row. If YES, that means your first row is a header.
SELECT *
FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’, ‘Text;Database=E:\;HDR=YES’, ‘SELECT * FROM Log.txt’)
If you need the Microsfot ACE OLEDB 12 driver, please refer to Microsoft Jet OLEDB 12
You’re probably getting this error message in BIDS/Data Tools
Error 1 Validation error. Data Flow Task: Package1: The requested OLE DB provider Microsoft.Jet.OLEDB.12.0 is not registered. If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000. An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”. Package1.dtsx 0 0
The issue here is that your computer is 64 bit and both versions (32 bit and 64 bit) of Excel are installed. By installing Microsoft Access Database Engine 2010 Redistributable your issue should go away but this alone did not work for me.
What worked for me is to also install 2007 Office System Driver: Data Connectivity Components which corrected the Microsoft.Jet.OLEDB.12.0 issue.
File Type (extension) Extended Properties
———————————————————————————————
Excel 97-2003 Workbook (.xls) “Excel 8.0”
Excel 2007 Workbook (.xlsx) “Excel 12.0 Xml”
Excel 2007 Macro-enabled workbook (.xlsm) “Excel 12.0 Macro”
Excel 2007 Non-XML binary workbook (.xlsb) “Excel 12.0”
I hope this helps solve your issue.
What’s wrong with using SQL Server Management Studio (SSMS)? Nothing! What’s wrong with you! Well, I’m in a hurry.
As much as I love GNR Patience, I don’t have any… well not so much of it. Thanks Smart Phone era.
I always use SQLCMD when SSIS is not available– not all of my customers install SSIS along with SQL. I once had a project where I had to scan through terabytes of files to find certain image files. Once the files were found, it would immediately write the path to the database. Since this had to be performed and completed over the weekend, I ran hundreds instances of my C# application to seek out the files.
So here’s how it works
In the command prompt (cmd.exe), run sqlcmd /? and you’ll see the following
There’s a lot of options and the ones I frequently use are
-S Server Name
-E Trusted Connection
-Q Query
In the example below, I’m querying a table, however you can always call a stored procedure to INSERT/DELETE/UPDATE/SELECT your data instead of writing out the query– SP is safer
I hope this gives you some insight as to why/when you would use SQLCMD
