|
|
SQL Stuff |
|
Here are some tips and code snippets for SQL...here.
This page is a "work in progress", so a few things might be
incomplete.
The code snippets are from and for different SQL
clients...b/c not all are the same.
AS400 STRSQL, Microsoft Query Manager, Microsoft Access, and
Foxy SQL...they all use similar syntax but some things arent transferrable. So
something that is in this section might need to be modded.
Some are by no means "working", they are just here to give you
ideas on what and how things work.
Links
SQL Performance - http://www.sql-server-performance.com/articles/audit/hardware_performance_optimization_p1.aspx
Statements / Examples
String Stuff
- Row Count(s)
select ROW_NUMBER()
OVER
(ORDER BY
(SELECT 100))
AS RowNumber
, *
FROM CustomerMaster
select
top 100 ROW_NUMBER()
OVER(ORDER BY (SELECT NULL)) as
rn
select * FROM [master]..spt_values
select 'insert into plantloc (controlnumber,location,plantlocation,
insideoroutsidelocation) values(' +
cast( a.n
+ 1000 as varchar(10))
+ ',''MNRPT'',' +
cast(a.n
as varchar(10)) +
',''fred'')'
from (SELECT TOP
(1000) n =
ROW_NUMBER() OVER
(ORDER BY
number) FROM [master]..spt_values ORDER BY
n ) A
- Look for uppercase only
select
'drop
table '
+
ltrim(rtrim(TABLE_NAME))
as
MeSQLStmt
,*
from
information_schema.TABLES
where
TABLE_TYPE =
'BASE
TABLE'
and
TABLE_NAME like
'%BU%'
COLLATE
SQL_Latin1_General_CP1_CS_AS
order
by
TABLE_NAME
- Select into 1 string
--simple
(but has comma in beginning)
SELECT
','
+
cast(controlnumber
as
varchar(max))
FROM
shipto WHERE
aremail <>
''
FOR
XML
PATH
('')
--simple
and removing comma at beginning
select
STUFF((
SELECT
','
+
cast(controlnumber
as
varchar(max))
FROM
shipto WHERE
aremail <>
''
FOR
XML
PATH
('')
),
1,
1,
'')
- Capitalize the first character
update tblstr set
stcnty = concat(upper(substring(stcnty,1,1)),
lower(substring(stcnty,2,length(stcnty))) )
- Compare a numeric to a character field if ANumber is defined
signed numeric and NumInChar is a number stored in a alphanumeric
field
select ANUMBER, NUMINCHAR,*
from
YOURFILENAME
where ANUMBER = real(NUMINCHAR)
- strip a name apart that is in a field like: "lastname,
firstname"
select substring(YOURFIELD, 1, charindex(', ',
YOURFIELD) - 1) AS [lastName],
substring(YOURFIELD, charindex(', ', YOURFIELD) + 1, len(YOURFIELD)) AS
[firstName]
from YOURDBNAME.dbo.YOURTABLENAME
opposite like: "firstname lastname"
select substring(YOURFIELD, 1,
charindex(' ', YOURFIELD) - 1) AS
[FirstName],
substring(YOURFIELD,
charindex(' ', YOURFIELD) + 1, len(YOURFIELD)) AS [LastName]
from
YOURDBNAME.dbo.YOURTABLENAME
better way...wont
hide errors
PARSENAME(REPLACE(yourfield, ',', '.'), 1) AS
[FirstName],
PARSENAME(REPLACE(yourfield, ',', '.'), 2) AS
[Lastname]
- remove
non-numeric characters from a string (good for phone numbers with junk in
them)
update
YOURFILE
set YOURFIELD = replace(YOURFIELD, substring(YOURFIELD,
patindex('%[^a-zA-Z0-9 ]%', YOURFIELD), 1), '')
***see
function on "Procs and Funcs" page (above)
- lists - IN or NOT IN
select FIELD1,
STATUS, *
from YOURFILE
where STATUS in ('A', 'C', 'D',
'G')
--where STATUS not in ('A', 'C', 'D', 'G')
select FIELD1,
YOURNUMERIC, *
from YOURFILE
where YOURNUMERIC in (123, 456, 444,
987)
--where YOURNUMERIC not in (123, 456, 444, 987)
- string lengths
select
length(rtrim(mfname))+length(rtrim(mlname)) as
fullname,mlname
from YOURFILE
order by
-fullname
OR
select
max(length(rtrim(mfname))+length(rtrim(mlname)))
from YOURFILE
- find quotes in a string, single and
double
-single
SELECT * FROM yourfile
WHERE
yourfieldname like '%''%' or yourfieldname like '%"%'
- string lengths looking for special characters, this example looks
for the percent sign % (which is a wildcard in SQL)
All values must be string
(character). This comparison uses value-2 as a pattern to match value-1. The
optional ESCAPE sub-clause specifies an escape character for the pattern,
allowing the pattern to use '%' and '_' (and the escape character) for
matching. The ESCAPE value must be a single character string. In the pattern,
the ESCAPE character precedes any character to be escaped.
For
example, to match a string ending with '%', use:
x LIKE '%/%' ESCAPE
'/'
A more contrived example that escapes the escape character:
y LIKE '/%//%' ESCAPE '/'
... matches any string beginning with
'%/'. \
To find a percent anywhere in the string, the first
percent says starts with anything, the last percent says ends with anything,
the middle percent is presented at face value b/c the escape tells it
to
y like '%/%%' escape '/'
Numeric
Stuff
- increment a
row number
SELECT a.field1, a.[field2],
row_number() over (order by field1) as controlnumber
into
file1backup
FROM file1 a
select
'insert
into plantloc (controlnumber,location,plantlocation, insideoroutsidelocation)
values('
+
cast(
a.n
+
1000 as
varchar(10))
+
',''MNRPT'','
+
cast(a.n
as
varchar(10))
+
',''fred'')'
from
(SELECT
TOP
(1000)
n =
ROW_NUMBER()
OVER
(ORDER
BY
number)
FROM
[master]..spt_values
ORDER
BY
n )
A
-
increment a field
-note if
you have to renumber the primary key, remove the primary then add records that
need added then renumber then add primary key back
declare @mycounter
int
set @mycounter = 0
update DB.dbo.FILENAME
set @mycounter =
FIELDINYOURFILE = @mycounter + 1
- increment counter within a group and reset the
counter on a "group" break
declare @mycounter int
declare @dptnum int
declare tb_cur cursor
for
select deptnumber as
dptnum
from departments
open
tb_cur
fetch next from tb_cur into
@dptnum
while @@fetch_status = 0
BEGIN
set @mycounter = 0
UPDATE process
set @mycounter =
@mycounter + 1, processid = (deptnumber * 1000) + @mycounter
where
deptnumber = @dptnum
fetch next from tb_cur into
@dptnum
end
close
tb_cur
deallocate tb_cur
- increment a number within a group
print ' ----
incrementing shipto number'
go
declare @mycounter int
set @mycounter
= 0
--select A.customernumber,b.shiptonumber,A.*
update
dbname.dbo.shipto
set @mycounter = shiptonumber =
b.shiptonumber
from dbname.dbo.shipto as [A]
inner
join
(
select controlnumber, row_number() over
(partition by customernumber order by shiptonumber) shiptonumber
from
dbname.dbo.shipto
)
as [B] on A.controlnumber =
B.controlnumber
Copying Tables
- copy file (will create
file)
SELECT *
INTO dbo.YOURFILE_backup
FROM
dbo.YOURFILE
go
select * into yourfile_BU_CCYYMMDD from
yourfile ;
- copy file (file already
exists)
insert into
DATABASE.dbo.OUTPUTTEDFILE
select * from DATABASE.dbo.INPUTTEDFILE
- export to CSV using cmd line
(osql or
) sqlcmd -s SERVER\INSTANCE -U userid -P yourpassword
-s"," -d DBNAME -q "select * from FILENAME" -o c:\test.csv
- create a file for CSV transfer to import
from
CREATE TABLE
[dbo].[YOURFILE]
(
[fieldjunk1] [varchar](50)
NULL, -- this is for extra quote
[field1] [varchar](50) NULL,
[field2] [varchar](50) NULL,
[field3] [varchar](50) NULL,
[field4] [varchar](50) NULL,
[field5] [varchar](50) NULL,
[fieldjunk2] [varchar](50) NULL, -- this is for extra
quote
) ON
[PRIMARY]
--***you can keep on going with numeric fields needed for
however many fields you need, last field needs to be for extra quote
- import CSV file (within an SQL
tool)
BULK INSERT scanin
FROM
'C:\yourfile.txt'
WITH
(
firstrow = 2, --- this is for the header
row
FIELDTERMINATOR =
'","',
ROWTERMINATOR =
'\n'
)
- bulk import from DOS prompt (if you have
a clean CSV)
bcp DATABASE.dbo.YOURFILE in
C:\yourfile.txt -sSERVERNAME -uUSERID -pPASSWORD -c
-t
Counts, Subtotals, Dups
- display multi columns in same
row
http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm
- find
duplicates
select YOURFIELD,
count(YOURFIELD)
as NumOccurrences
FROM YOURDB.dbo.YOURFILE
group
by YOURFIELD
having ( count(YOURFIELD) > 1 )
OR list all the records so you can change them
select
*
from YOURDB.dbo.YOURFILE a
where a.YOURFIELD in (
select YOURFIELD
from YOURDB.dbo.YOURFILE
group
by YOURFIELD
having ( COUNT(YOURFIELD) > 1 )
)
order by
a.YOURFIELD
- find dupps with a mult-field
key
select A.FIELD1,A.FIELD2,A.*
from
FILENAME as [A]
inner join
(
select
FIELD1,FIELD2
from FILENAME
group by
FIELD1,FIELD2
having count(*) >1
)
as [B] on
A.FIELD1 = B.FIELD1 and A.FIELD2 = B.FIELD2
- count distinct
SELECT
COUNT(distinct FIELDNAME)
from FILENAME
- Count number of entrys
select field1,count(*) from YOURFILE
where field2 = 'SOMETHING'
group by field1
order by field1
- Total with subtotal
select t1.id, count(*) as totalrecords,
(select distinct count(*) as subcount from YOURFILE t2
where FIELDCHECKING <> '0001-01-01' and t1.id = t2.id
group by id) fieldnotempty
from YOURFILE t1
group by t1.id
order by t1.id
- Totals, selective totals in different columns from same field base
on another criteria
select FIELD1,
sum(case when FIELD2 = ' ' then FIELD3 else 0 end) as TOTAL1,
sum(case when FIELD2 = 'Y' then FIELD3 else 0 end) as TOTAL2,
sum(FIELD4) as TOTAL4
FROM YOURFILE T01
WHERE FIELD5 = 'Y'
AND FIELD6 <= '2007-11-16'
GROUP BY FIELD1
ORDER BY FIELD1
dynamic pivot (for sql2005)
create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as
--USAGE:
--EXEC dynamic_pivot
--'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
--INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ',
--'Year(OrderDate)',
--'Count(OrderDate)'
declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')
create table #pivot_columns (pivot_column varchar(100))
Select @sql='select distinct pivot_col from ('+@select+') as t'
insert into #pivot_columns
exec(@sql)
select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns
select @sql=
'
select * into stan from
(
'+@select+'
) as t
pivot
(
'+@Summaries+' for pivot_col in ('+@pivot+')
) as p
'
exec(@sql)
TSQL Pivot without aggregate function
use the max function
Table Connections and
updates
- compare same table/columns from 2 different databases (check to
see if a column didnt convert)
SELECT c.table_name, c.column_name,
c.DATA_TYPE, d.data_type
FROM database1.INFORMATION_SCHEMA.Columns
c
left join database2.INFORMATION_SCHEMA.Columns d on c.table_name =
d.table_name and c.column_name = d.column_name
where c.TABLE_NAME =
'yourfilename'
and c.DATA_TYPE <> d.data_type
- retreive all records even though they don't exist in secondary
file
select *,count(*) from FILE1 a, FILE2 b
left join FILE2 b on a.FIELD1 = b.FIELD2
- unmatched records
select count(*) from FILE1 a
where a.FIELD1 not in (select FIELD1 from FILE2)
- Update a file based on another file
update tblstr a
set a.stcnty = (select b.county from zipcodes b
where a.stzip = b.zip)
- Flag a record if it doesnt meet criteria - Also pulling from
multiple files
select t1.event , char(t1.evdacd) as ADCode,
t1.inumbr,
t2.idescr,
t1.evdapr,
t2.icucst,t3.dsc151 as
tagprice,
case when t1.evdapr <=
t2.icucst
then 'bad' else ' ' end as
lessthancost,
case when t1.evdapr >=
real(t3.dsc151)
then 'bad' else ' ' end as
greaterthanretail
from YOURFILE1
t1
left join YOURFILE2 t2 on t1.inumbr = t2.inumbr
left
join YOURFILE3 t3 on t1.inumbr = t3.inumbr
where t1.event =
323
order by
adcode
- Reverse sort (like a dollar amount from hi to lo)
in
the sort part of your select statement put a minus sign in front of the
variable
select *
from YOURFILE
order by FIELD1, -FIELD2
- Group By on an alias field (basically the outer select is doing
grouping, inside parans is doing the detail of the alias)
select MYGROUPFIELD, count(*) as MYGROUPCOUNT
from
(select
case
when (a.FIELDx > 0 and a.FIELDx <
51) then '000-050xx'
when (a.FIELDx > 50
and a.FIELDx < 101) then '051-100xx'
when (a.FIELDx
> 100 and a.FIELDx < 151) then '101-150xx'
when
(a.FIELDx > 150 and a.FIELDx < 201) then
'151-200xx'
when (a.FIELDx > 200 and a.FIELDx <
251) then '201-250xx'
when (a.FIELDx > 250 and
a.FIELDx < 301) then '251-300xx'
when (a.FIELDx >
300 and a.FIELDx < 351) then '301-350xx'
when
(a.FIELDx > 350 and a.FIELDx < 401) then
'351-400xx'
when (a.FIELDx > 400) then '400
up'
else 'err' end as MYGROUPFIELD
from DB.dbo.FILE1
a
left join DB.dbo.FILE2 b on a.FIELD2 = b.FIELD2
left join DB.dbo.FILE3
c on a.FIELD2 = c.FIELD2
where a.FIELDx > 0 ) SOMETHING --this
'SOMETHING' needs to be here
group by MYGROUPFIELD
order by
MYGROUPFIELD
SQL Coding Stuff
RAISERROR ('>>> execute :
%s', 10, 1,
@sqlstmt) WITH NOWAIT ;
execute( @sqlstmt )
;
FETCH NEXT FROM TableCursor INTO
@TableName
end
SQL loop from inserted list
example
declare @DBname varchar(50),
@sqlstmt nvarchar(max) ;
declare
@listOfDatabases table (DBName nvarchar(50));
insert
@listOfDatabases(DBName) values('mainDATABASENAME') ;
insert
@listOfDatabases(DBName) values('alternateDATABASENAME')
;
insert
@listOfDatabases(DBName) values('ReportServer') ;
--DO NOT remove
SSRS - loop will check for existence
-------------------------------------------------------------------------------------------------------------
---- Database loop
DECLARE TableCursor
CURSOR FOR SELECT DBName FROM @listOfDatabases
;
OPEN TableCursor
;
FETCH NEXT FROM TableCursor INTO @DBName
WHILE @@FETCH_STATUS = 0
begin
if DB_ID(@DBname) IS
NOT NULL
begin
RAISERROR ('>>> %s DB
BACKUP starting ' , 10,
1,@DBname) WITH NOWAIT ;
set @sqlstmt = 'BACKUP DATABASE
[' + @DBname + '] TO DISK =
N''' + @BAKName + ''' WITH NOFORMAT, INIT,
SKIP, NOREWIND, NOUNLOAD, STATS = 10 ';
RAISERROR ('>>> BACKUP
starting : %s' , 10,
1,@sqlstmt) WITH NOWAIT ;
execute( @sqlstmt )
;
end
FETCH NEXT FROM TableCursor INTO
@DBName
end
---- Database loop END
-------------------------------------------------------------------------------------------------------------
Whole Database function and File
information
Utilities
- dead locks -
select cmd,* from sys.sysprocesses
where blocked > 0
--kill <spid>
FILE
- find length of
record
use YOURDB
go
select sum(length)
from
syscolumns
where id =
object_id('YOURFILE')
SELECT ob.name[tablename], sum(col.length)
[Max column Length]
from sysobjects ob, syscolumns col
where ob.id =
col.id and ob.xtype = 'U'
group by ob.name
----having sum(col.length)
> 8060
- display all fields in a table
w/ attributes
exec sp_MShelpcolumns
'FILENAME',null,N'col_name'
- clear all nulls in a table
--
-- READ READ READ
- change your table names below...
--
use
DATABASE
go
declare @sql
nvarchar(1000)
declare @colname nvarchar(30)
declare @updatetable
nvarchar(20)
set @updatetable =
'YOURTABLE'
print '.....update ' +
@updatetable
DECLARE db_cursor CURSOR for
SELECT column_name FROM INFORMATION_SCHEMA.Columns c where table_name =
@updatetable and DATA_TYPE in ( 'char','varchar','nvarchar')
open
db_cursor
FETCH NEXT FROM db_cursor INTO @colname
WHILE @@FETCH_STATUS =
0
BEGIN
print '....doing-' +
@colname
set @sql = 'update ' +@updatetable + ' set
['+ @colname +'] = '''' where ['+ @colname +'] is
null'
EXEC(@sql)
FETCH NEXT FROM db_cursor INTO
@colname
END
go
CLOSE
db_cursor
DEALLOCATE db_cursor
DECLARE
db_cursor CURSOR for SELECT column_name FROM INFORMATION_SCHEMA.Columns c
where table_name = @updatetable and DATA_TYPE in (
'int','float')
open db_cursor
FETCH NEXT
FROM db_cursor INTO @colname
WHILE @@FETCH_STATUS =
0
BEGIN
print '....doing-' +
@colname
set @sql = 'update ' +@updatetable + ' set
['+ @colname +'] = 0 where ['+ @colname +'] is
null'
EXEC(@sql)
FETCH NEXT FROM db_cursor INTO
@colname
END
go
CLOSE db_cursor
DEALLOCATE
db_cursor
go
print ' '
print ' '
print '.....we all done'
print
' '
print ' '
Database
- drop/delete
all tables in database
EXEC sp_MSforeachtable 'DROP TABLE ?'
- clear all
records in all tables
EXEC
sp_MSforeachtable 'Truncate
TABLE ?'
- display locks
on database
exec sp_who
- show users in
DB for a certain program name
select program_name
from
master.dbo.sysprocesses
where program_name = 'YOUR PROGRAM
NAME'
- list all
constraint keys (to help find duplicate names)
SELECT
OBJECT_NAME(OBJECT_ID) AS
NameofConstraint,
SCHEMA_NAME(schema_id) AS
SchemaName,
OBJECT_NAME(parent_object_id) AS
TableName,
type_desc AS ConstraintType
FROM
sys.objects
WHERE type_desc LIKE
'%CONSTRAINT'
order by
nameofconstraint
-
clear all tables
use
DATABASE
go
Declare @t varchar (1024)
Declare tbl_cur cursor
for
select TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = 'BASE TABLE'
OPEN tbl_cur
FETCH NEXT from
tbl_cur INTO @t
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('TRUNCATE
TABLE '+ @t)
FETCH NEXT from tbl_cur INTO @t
END
CLOSE
tbl_cur
DEALLOCATE tbl_Cur
-
record count for all tables in DB
select
object_name(id)as tablename, rows
from sysindexes
where indid in (1,
0)
order by tablename
- display all tables in a DB with field info
SELECT table_name,column_name,data_type,character_maximum_length as
maxlen,numeric_precision,numeric_scale
FROM
INFORMATION_SCHEMA.Columns c
order by table_name,
column_name
- list all indexes (to help find wrongly prefixed ones (mainly for
clarion dictionary defs of keys))
use YOURDBNAME
go
select s.name,
t.name, i.name,
c.name
from sys.tables t
inner join sys.schemas s on t.schema_id =
s.schema_id
inner join sys.indexes i on i.object_id =
t.object_id
inner join sys.index_columns ic on
ic.object_id = t.object_id
inner join sys.columns c on c.object_id =
t.object_id and
ic .column_id = c.column_id
where i.index_id > 0
and i.type in
(1, 2) -- clustered &
nonclustered only
and i.is_primary_key = 0
-- do not include
PK indexes
and i.is_unique_constraint
= 0 -- do not include UQ
and i.is_disabled =
0
and i.is_hypothetical =
0
and ic.key_ordinal > 0
order by i.name,
t.name
- reindex all files in a DB
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
- Rebuild 1 files indexes
--rebuild
indexes:
DBCC DBREINDEX (YOURFILE,' ',0)
--log file
cleanup
dbcc shrinkfile(YOURFILE_log)
- Rebuild all indexes in a DataBase
USE DatabaseName --Enter the
name of the database you want to reindex
DECLARE @TableName
varchar(255)
DECLARE TableCursor CURSOR
FOR
SELECT table_name FROM
information_schema.tables
WHERE table_type =
'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO
@TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC
DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM
TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
- display record size for all files in DB
with
ocols AS (
SELECT o.object_id, c.column_id, o.name AS oname, c.name
AS cname,
t.name AS tname, t.max_length,
c.is_nullable,
CASE
WHEN t.name IN ('char', 'binary')
AND c.max_length >= 0
THEN
c.max_length
WHEN t.name IN ('varchar', 'varbinary',
'nvarchar')
AND c.max_length >= 0 THEN c.max_length +
2
WHEN t.name IN ('nchar') THEN c.max_length /
2
WHEN t.name IN ('bit') THEN 1
WHEN t.name IN
('decimal', 'numeric', 'float', 'real')
THEN
c.max_length
WHEN t.name IN ('sql_variant', 'xml') THEN
0
WHEN c.max_length = -1 THEN 0
ELSE
t.max_length
END AS eff_length
FROM sys.objects
o
INNER JOIN sys.columns c ON o.object_id =
c.object_id
INNER JOIN sys.types t ON c.user_type_id =
t.user_type_id
WHERE o.schema_id = 1
AND o.type =
'U'
),
ocolsum (object_id, column_count, is_nullable, eff_length)
AS
(
SELECT object_id, count(column_id), is_nullable,
sum(eff_length)
FROM ocols
GROUP BY object_id,
is_nullable
),
Bytes AS (
SELECT o.object_id,
o.name,
ISNULL(ocolsum.column_count,0)
+
ISNULL(ocolsumnull.column_count,0) AS
NumCols,
ISNULL(ocolsum.eff_length,0) AS
FixedDataSize,
ISNULL(ocolsumnull.column_count,0) AS
NumVariableCols,
ISNULL(ocolsumnull.eff_length,0) AS
MaxVarSize,
2 + ((ISNULL(ocolsum.column_count,0)
+
ISNULL(ocolsumnull.column_count,0) + 7) / 8
)
AS NullBitmap,
2 +
(ISNULL(ocolsumnull.column_count,-1) * 2)
+
ISNULL(ocolsumnull.eff_length,0) AS
VarDataSize
FROM sys.objects o
LEFT OUTER JOIN
ocolsum
ON o.object_id = ocolsum.object_id
AND
ocolsum.is_nullable = 0
LEFT OUTER JOIN ocolsum
ocolsumnull
ON o.object_id =
ocolsumnull.object_id
AND ocolsumnull.is_nullable =
1
WHERE o.type = 'U'
)
SELECT name, NumCols, FixedDataSize,
NumVariableCols, MaxVarSize,
NullBitmap,
VarDataSize,
FixedDataSize + VarDataSize + NullBitmap + 4 AS
RowSize
FROM Bytes
--ORDER BY name
order by rowsize
desc
- display table sizes
SET NOCOUNT
ON
DBCC UPDATEUSAGE(0)
SELECT sysobjects.[name] AS
[TableName],
SUM(sysindexes.reserved) * 8 AS
[Size(KB)],
SUM(sysindexes.dpages) * 8 AS
[Data(KB)],
(SUM(sysindexes.used) -
SUM(sysindexes.dpages)) * 8 AS [Indexes(KB)],
(SUM(sysindexes.reserved) - SUM(sysindexes.dpages)) * 8 AS
[Unused(KB)]
FROM dbo.sysindexes AS sysindexes
JOIN dbo.sysobjects AS sysobjects ON sysobjects.id = sysindexes.id
WHERE sysobjects.[type] = 'U'
GROUP BY sysobjects.[name]
ORDER BY [Size(KB)] DESC
- table size within a database
create
table #blah (name nvarchar(20), rows char(11), reserved varchar(18), data
varchar(18), index_size varchar(18), unused varchar(18))
exec
sp_msforeachtable 'insert into #blah exec sp_spaceused ''?'''
go
select
cast(substring(data,1,charindex(' KB',data)) as integer) as datasize, * from
#blah order by datasize desc
drop table
#blah
go
MySQL Stuff
copy table for MySql
create table YOURFILEBU like
YOURFILE;
insert YOUFILEBU select * from YOURFILE;
Date stuff
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx
http://www.sql-server-helper.com/tips/date-formats.aspx
format date/time - http://www.albsecurity.com/page/T-SQL-How-to-format-datetime-date-.aspx
list of all kinds of date things - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762
list
of stuff - http://www.calendarzone.com/Software/
- Simple Date stuff that you cant
remember
date range how to format
select
yourdatetimefield,*
from YOURDB.dbo.YOURFILE
where yourdatetimefield
>= '2005-07-01' and yourdatetimefield <= '2005-07-31'
just format
select
CONVERT(VARCHAR(10), current_datetime, 111) ,*
from
YOURDB.dbo.YOURFILE
where controlnumber = 0
order by
current_datetime desc
select ones on certain days
select *
from YOURDB.dbo.YOURFILE
where CONVERT(VARCHAR(10),
current_datetime, 111) in('2014/01/23','2013/05/10')
order by
current_datetime desc
- cast a datetime field to INT....avoiding the rounding up of the
datetime field
if you were to take a datetime field and do a
straight cast, it will round up anything after 12noon
so to "zero" out the
time part of the field and just convert the actual date part to an
INT
cast(dateadd(dd,0,
datediff(dd,0,YOURDATEFIELD))as int)
- concate a date together that is in 4 separate
fields
convert(datetime, cast( cast((vaddcc *
1000000 + vaddyy * 10000 + vaddmm * 100 + vadddd)as int(8)) as char(8) ) )
- Dates stored as: 1,0 for Century and 6,0 for YMD
- turn to just MM/DD/YY
substr(digits(datefld),3,2)
|| '/' ||
substr(digits(datefld),5,2)
|| '/' ||
substr(digits(datefld),1,2)
- turn to MM/DD/CCYY
cast(case
left(digits(centuryfld),1)
when '0' then '19' else '20' end
||
substr(digits(datefld),1,2)
|| '-'
||
substr(digits(datefld),3,2)
|| '-'
||
substr(digits(datefld),5,2)
as dateOutput)
- pull year off a 6 digit date
substr(digits(yourdate),1,2)
- date field comes in as zero(not a real SQL date field)(this
example is Clarion long)
case when YOURDATEFIELD > 0
then
convert(datetime,([YOURDATEFIELD]-36163))
else YOURDATEFIELD
end as OUT_YOURDATEFIELD
- SQL clear the time part of a SQL defined
DateTime
dateAdd(dd,datediff(dd,0,YOURDATETIMEFIELD),0)
- Current Date minus a year
select
getdate()
as
todaysdate
,
getdate()
-
(365
*
2)
as
daysmultiplied
,
DateAdd(yy,
-2,
GetDate())
as
dateaddfunction
,
'2013-01-23'
,
cast('2013-01-23'
as
date)
-
JDEdwards date CYYDDD (kinda julian
format)
DATEADD (DAY,
cast(@CYYDDD as
bigint) %
1000, DATEADD(YEAR,
@CYYDDD / 1000, -1)) as
dateINETexample --
https://www.c-sharpcorner.com/blogs/mssql-to-jdedwards-date-convertion
other
Examples
declare @CYYDDD int;
set @CYYDDD=100203
set @CYYDDD=100186
--set @CYYDDD=000001 --this dont work
SELECT
@CYYDDD as
myvar
, dbo.JDEDateTOSQLDate(@CYYDDD)
as datePROC
, convert(char(10),dateadd(day,convert(int,right(@CYYDDD,3)),'01/01/'+left(right(@CYYDDD,5),2))-1,101)
as date101
, convert(char(10),dateadd(day,convert(int,right(@CYYDDD,3)),'01/01/'+left(right(@CYYDDD,5),2))-1,111)
as date111
, convert(char(10),dateadd(day,convert(int,right(@CYYDDD,3)),'01/01/'+left(right(@CYYDDD,5),2))-1,112)
as date112
, CASE @CYYDDD
WHEN 0 THEN ''
ELSE
CONVERT(VARCHAR(10),
cast(left(CAST(@CYYDDD as decimal)+1900000,
4) as char(4))
+ dateadd(day,0,
cast(right(CAST(@CYYDDD as
decimal)+1900000,
3) as int)-1),
111)
END as
dateCASE
, DATEDIFF(d,
'1800-12-28', convert(char(10),dateadd(day,convert(int,right(@CYYDDD,3)),'01/01/'+left(right(@CYYDDD,5),2))-1,111)
) as
dateClarion
, datediff(day,'18001228','20000721') as
dateClarionDate2000721
, DATEADD(YEAR, @CYYDDD /
1000, 0) as
dateYEAR --notice
the -1 is zero now
, year( DATEADD(YEAR, @CYYDDD /
1000, 0) )
as dateCCYY --notice the -1 is zero
now
, cast(@CYYDDD as
bigint) %
1000 as dateDayOfYear --modulus only works with
bigint
, DATEADD(DAY, cast(@CYYDDD as
bigint) %
1000, DATEADD(YEAR,
@CYYDDD / 1000, -1)) as
dateINETexample --
https://www.c-sharpcorner.com/blogs/mssql-to-jdedwards-date-convertion
--dont work overflow, case when @CYYDDD > 0
then DATEADD (day , cast(@CYYDDD as int) % 1000 - 1, DATEADD(year,cast(@CYYDDD
as real)/1000-1900, 0 ) )
end
-
Playing with Dates
select
getdate() as sqlgetdate
, cast(getdate() as date) as sqlgetdatecastwithoutTime --cast as "date"
meaning date only part (only SQL2012 up)
, cast( 0.0 as datetime) as MSSQLstartdate -- MSSql start/zero date
1900-01-01
, convert(decimal(12,0),getdate()) as RAWnumber --RAW date number, this is really how sql stores the date
as a number
, convert(decimal(20,9),getdate()) as RAWnumberwithTime --RAW datetime
number
, '---Clarion Stuff --->>>'
, convert(datetime, 0 - datediff(day,'1800/12/28','1900/01/01') ) as convertclarioninteger -- convert the integer to a datetime to show the "zero"
date
, convert(datetime, 1 - datediff(day,'1800/12/28','1900/01/01') ) as convertclarionintegerNumber1 -- convert the integer to a datetime to
show the "first" day
, convert(datetime, -8942 - datediff(day,'1800/12/28','1900/01/01') ) as convertclarionintegerNumber2 -- convert the integer to a datetime
, datediff(day,-36163,'2022/06/09') as convertclariondate1
-- convert a
date to an integer for a clarion long date field
, datediff(dd,-36163,getdate()) as ClarionIntegerTodaysDate --convert todays date
to a clarion integer, clarion start date is December 28, 1800
declare @mydate datetime='2022/06/09' ;
select
@mydate
, month(@mydate) as justmonth
, day(@mydate) as justday
, cast( month(@mydate) as varchar(2)) + '/' + cast( day(@mydate) as varchar(2)) as casttogether
, right ('0' + rtrim(month(@mydate)),2 ) as justmonth2digit
, right ('0' + rtrim(day(@mydate)),2 ) as justday2digit
, right ('0' + rtrim(month(@mydate)),2 ) + '/' + right ('0' + rtrim(day(@mydate)),2 ) as just2digitscasttogether
, convert(varchar(max),@mydate,101) as f101 -- MM/DD/CCYY
, convert(varchar(max),@mydate,102) as f102 -- CCYY.MM.DD
, convert(varchar(max),@mydate,103) as f103 -- DD/MM/CCYY
, convert(varchar(max),@mydate,104) as f104 -- DD.MM.CCYY
, convert(varchar(max),@mydate,105) as f105 -- DD-MM-CCYY
, convert(varchar(max),@mydate,106) as f106 -- DD Mon CCYY
, convert(varchar(max),@mydate,107) as f107 -- Mon DD, CCYY
, convert(varchar(max),@mydate,108) as f108 -- HH:MM:SS
, convert(varchar(max),@mydate,109) as f109 -- Mon DD CCYY
HH:MM:SS:NNNAM
, convert(varchar(max),@mydate,110) as f110 -- MM-DD-CCYY
, convert(varchar(max),@mydate,111) as f111 -- CCYY/MM/DD
, convert(varchar(max),@mydate,112) as f112 -- CCYYMMDD
, convert(varchar(max),@mydate,113) as f113 -- DD Mon CCYY HH:MM:SS:NNN
, '
-- these are 2 digit years -- '
, convert(varchar(max),@mydate,1) as f1 --
, convert(varchar(max),@mydate,2) as f2 --
, convert(varchar(max),@mydate,3) as f3 --
, convert(varchar(max),@mydate,4) as f4 --
, convert(varchar(max),@mydate,5) as f5 --
, convert(varchar(max),@mydate,6) as f6 --
, convert(varchar(max),@mydate,7) as f7 --
, convert(varchar(max),@mydate,8) as f8 --
, convert(varchar(max),@mydate,9) as f9 --
, convert(varchar(max),@mydate,10) as f10 --
, convert(varchar(max),@mydate,11) as f11 --
, convert(varchar(max),@mydate,12) as f12 --
, convert(varchar(max),@mydate,13) as f13 --
-
SQL Attach DB (mdf file) without Log file
(*.ldf)
SQL2005
***if the mdf is in place and where you want it...and the ldf is
nowwhere to be found...then
delete the ldf
start at step 14 below.
1. stop SQL...it may be best to use Services
2. make a copy
of the MDF file your trying to use
3. make a copy of the MDF file
4. did
you really make a copy of the MDF file...if not copy it
5. start SQL
6.
open MSMSEE (SQL Studio)
7. delete the database that has issues (ensure that
you backed up/copied, b/c if not it will delete the mdf)
8. create the
database (ensure it creates with the same named mdf)
9. stop SQL
10.
delete the newly created mdf and ldf files
11. copy the copy that you made
and ensure the name matches in step 8
12. delete the ldf file
13. start
SQL
14. run each individual item below...the rebuild log will do the
magic
--ALTER DATABASE YourDatabaseName SET EMERGENCY
--ALTER
DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
--ALTER
DATABASE YourDatabaseName REBUILD LOG ON
(NAME=YourDatabaseName_Log,FILENAME='e:\sqldata\YourDatabaseName_log.ldf')
--DBCC
CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE
YourDatabaseName SET MULTI_USER
should be good
SQL 2000 and below (this wont work with 2005 and SP4)
if something has happened to the log file such as lost the drive the log file
was on.
1-1. Copy your original MDF file somewhere. (copy the whole
directory that contains all MDFs)
1-2. Delete the database (the one marked
suspect) from SQL Manager
1-3. Create a new database with the same name and
same MDF and LDF files
2. Stop sql server and rename the existing MDF to a
new one and copy the original MDF to this location and delete the LDF
files.
3. Start SQL Server
4. Now your database will be marked suspect
5. Update the sysdatabases to update to Emergency mode. This will not use
LOG files in start up
Sp_configure "allow updates", 1
go
Reconfigure with
override
GO
Update master.dbo.sysdatabases set status = 32768 where name =
'BADDATABASENAME'
go
Sp_configure "allow updates", 0
go
Reconfigure
with override
GO
6. Restart sql server. now the database will be in emergency
mode
7. Now execute the undocumented DBCC to create a log file
dbcc rebuild_log(DATABASENAME,'c:\DATABASENAME_Log.ldf') --
Undocumented step to create a new log file.
(replace the DATABASENAME and log file name based on ur
requirement)
8. Execute sp_resetstatus <DATABASENAME>
9. Restart SQL
server and see the database is online.
10. Now the DB will be marked as "DBO
Use Only"
11. do right-mouse on DB, goto "Properties, Options(tab)" uncheck
"Restrict Access"
***these steps were taken from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85187 with a few steps added
SQL Backup - External using the OSQL
command sqlbackup.sql
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
BACKUP DATABASE databasename TO DISK='C:\sqlbackups\daily\today.bak' WITH SKIP,INIT sqlbackup.sql
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
sqlbackup.bat
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
rem make sure you test Test TEST any changes you make rem @echo off rem
rem @echo off
rem ----------------------------------------------------------------------------
rem sql server parms
rem *** NOTE - make sure you you change the script inside SCRIPTFILE for database name and location of bak file
rem *** NOTE the "80" here is the version of sql that is install
rem 7 SQL Server 7.0
rem 8 SQL Server 2000
rem 9 SQL Server 2005
rem 10 SQL Server 2008
set CustomerName=Global
set SERVERNAME=server
set SCRIPTFILE=c:\zbackups\sqlbackup.sql
set LOGFILE=c:\zbackups\sqlBackup.log
rem set OSQLLOC="C:\Program Files\Microsoft SQL Server\90\Tools\Binn\osql.exe"
set OSQLLOC="C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\osql.exe"
rem ----------------------------------------------------------------------------
rem zip file parms
set DOW=%date:~0,3%
set PKZIPLOC=c:\zbackups\pkzip25\
set ZIPLOC=C:\sqlbackups\daily\
set FILENAME=%CustomerName%-SQLDaily
set BAKLOC=C:\sqlbackups\daily\
rem ----------------------------------------------------------------------------
rem external location
set EXTLOC=g:\sqlbackups
rem ----------------------------------------------------------------------------
rem create the dirs if they dont exist
if not exist %ziploc% md %ziploc%
if not exist %extloc% md %extloc%
rem ----------------------------------------------------------------------------
rem run the osql command
%OSQLLOC% -S %SERVERNAME% -E -i %SCRIPTFILE% -o %LOGFILE%
rem ----------------------------------------------------------------------------
rem now zip it
%PKZIPLOC%pkzip25 -add -temp=c:\ %ZIPLOC%%FILENAME%-%dow%.zip %BAKLOC%*.bak
rem ----------------------------------------------------------------------------
rem copy to an external location
rem xcopy /y /c %ZIPLOC%*.zip %EXTLOC%*.zip
robocopy %ZIPLOC% %EXTLOC% *.zip /MIR /r:0
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
download robocopy if needed - Win2k/Win2k3 (Win2k3 resource kit
will work on Win2k)
http://www.google.com/search?hl=en&q=robocopy+windows+2000&sourceid=navclient-ff&rlz=1B3GGGL_enUS272US272&ie=UTF-8
Versioning (Microsoft SQL)
Service Pack Listing - http://support.microsoft.com/kb/321185
http://en.wikipedia.org/wiki/Microsoft_SQL_Server
commands :
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY
('productlevel'), SERVERPROPERTY ('edition')
SELECT @@VERSION (version 7
below)
| Sqlservr.exe |
Release |
Will display as |
|
Version |
Year |
Release Name |
Codename |
| 6.50.201 |
SQL Server 6.5 RTM |
|
|
1.0 (OS/2) |
1989 |
SQL Server 1.0 |
- |
| 6.50.213 |
SQL Server 6.5 Service Pack 1 (SP1) |
|
|
4.21 (WinNT) |
1993 |
SQL Server 4.21 |
- |
| 6.50.240 |
SQL Server 6.5 Service Pack 2 (SP2) |
|
|
6 |
1995 |
SQL Server 6.0 |
SQL95 |
| 6.50.258 |
SQL Server 6.5 Service Pack 3 (SP3) |
|
|
6.5 |
1996 |
SQL Server 6.5 |
Hydra |
| 6.50.281 |
SQL Server 6.5 Service Pack 4 (SP4) |
|
|
7 |
1998 |
SQL Server 7.0 |
Sphinx |
| 6.50.415 |
SQL Server 6.5 Service Pack 5 (SP5) |
|
|
- |
1999 |
SQL Server 7.0 OLAP Tools |
Plato |
| 6.50.416 |
SQL Server 6.5 Service Pack 5a (SP5a) |
|
|
8 |
2000 |
SQL Server 2000 |
Shiloh |
| 6.50.479 |
SQL Server 6.5 Service Pack 5a (SP5a) Update |
|
|
8 |
2003 |
SQL Server 2000 64-bit Edition |
Liberty |
| 7.00.1063 |
SQL Server 7.0 Service Pack 4 (SP4) |
|
|
9 |
2005 |
SQL Server 2005 |
Yukon |
| 7.00.623 |
SQL Server 7.0 RTM |
|
|
10 |
2008 |
SQL Server 2008 |
Katmai |
| 7.00.699 |
SQL Server 7.0 Service Pack 1 (SP1) |
|
|
10.25 |
2010 |
SQL Azure DB |
CloudDatabase |
| 7.00.842 |
SQL Server 7.0 Service Pack 2 (SP2) |
|
|
10.5 |
2010 |
SQL Server 2008 R2 |
Kilimanjaro(aka KJ) |
| 7.00.961 |
SQL Server 7.0 Service Pack 3 (SP3) |
|
|
11.0 |
2012 |
SQL Server 2012 |
Denali |
| 8.00.194 |
SQL Server 2000 RTM |
2000.80.194.0 |
|
12.0 |
2014 |
SQL Server 2014 |
Hekaton |
| 8.00.384 |
SQL Server 2000 SP1 |
2000.80.384.0 |
|
|
|
|
|
| 8.00.534 |
SQL Server 2000 SP2 |
2000.80.534.0 |
|
|
|
|
|
| 8.00.760 |
SQL Server 2000 SP3 |
2000.80.760.0 |
|
|
|
|
|
| 8.00.760 |
SQL Server 2000 SP3a |
2000.80.760.0 |
|
|
|
|
|
| 8.00.2039 |
SQL Server 2000 SP4 |
2000.80.2039 |
|
|
|
|
|
| 9.00.1399 |
SQL Server 2005 RTM |
2005.90.1399 |
|
|
|
|
|
| 9.00.2047 |
SQL Server 2005 Service Pack 1 |
2005.90.2047 |
|
|
|
|
|
| 9.00.3042 |
SQL Server 2005 Service Pack 2 |
2005.90.3042 |
|
|
|
|
|
| 9.00.4039 |
SQL Server 2005 Service Pack 3 |
2005.90.4039 |
|
|
|
|
|
| 9.00.5000 |
SQL Server 2005 Service Pack 4 |
2005.90.5000 |
|
|
|
|
|
| 10.00.1600.022 |
SQL Server 2008 |
2007.0100.1600.022 |
|
|
|
|
|
| 10.00.2531.00 |
SQL Server 2008 Service Pack 1 |
2007.100.2531.0 |
|
|
|
|
|
| 11.0.2100.60 |
SQL Server 2012 RTM |
|
|
|
|
|
|
| 11.0.3000.0 |
SQL Server 2012 Service Pack 1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
SQL 2014
Latest SP - https://support.microsoft.com/en-us/kb/2958069/ (none
04/2015)
SQL 2012
Latest SP - https://support.microsoft.com/en-us/kb/2755533/ (SP2
10/2014)
SSMSE - http://www.microsoft.com/en-us/download/details.aspx?id=22985
SQL 2008 Express
Latest SP - https://support.microsoft.com/en-us/kb/968382/ (SP4
09/2014)
Main page - http://www.microsoft.com/express/sql/download/
straight install with tools - http://go.microsoft.com/?linkid=9394725
with Advanced Services - http://www.microsoft.com/downloads/details.aspx?FamilyId=B5D1B8C3-FDA5-4508-B0D0-1311D670E336&displaylang=en
SP1 - http://www.microsoft.com/downloads/details.aspx?familyid=01AF61E6-2F63-4291-BCAD-FD500F6027FF&displaylang=en
to apply SP1 /action=patch
For SQL Server 2008 Express Core, you have a choice of 3
packages:
- SQLEXPR32_x86_ENU.exe - This contains 32bit binaries only. If
you are only installing on a 32bit operating system, use this package.
- SQLEXPR_x86_ENU.exe - This contains the same binaries as the
32bit only package plus some x64 binaries so
that you can install SQL Express in a WoW environment. Use this package if you
want to install SQL Server 2008 Express 32bit on a 64bit operating
system.
- SQLEXPR_x64_ENU.exe - x64
package for native x64 SQL Server Express on a
64bit operating system.
SSMSE - Microsoft® SQL Server® 2008 Management Studio Express -
http://www.microsoft.com/downloads/details.aspx?FamilyID=08e52ac2-1d62-45f6-9a4a-4b76a8564a2b&displayLang=en
64bit - listed with the 32bit...look for x64....32bit is
x86
Windows Installer 4.5 required - http://www.microsoft.com/en-us/download/details.aspx?id=8483
Windows
PowerShell v1.0 required - Server2003 - http://www.microsoft.com/en-us/download/details.aspx?id=20020
SQL 2005 Express
Latest SP - https://support.microsoft.com/en-us/kb/913089/ (SP4
12/2010)
Main page - http://www.microsoft.com/express/sql/previous/default.aspx
need dot NET v2 - http://download.microsoft.com/download/5/6/7/567758a3-759e-473e-bf8f-52154438565a/dotnetfx.exe
straight install - http://www.microsoft.com/downloads/details.aspx?familyid=220549B5-0B07-4448-8848-DCC397514B41&displaylang=en
SP3 - http://www.microsoft.com/downloads/details.aspx?FamilyID=3181842a-4090-4431-acdd-9a1c832e65a6&DisplayLang=en
64bit - http://go.microsoft.com/fwlink/?linkid=83387
with Advanced Services
SQL 2005 Express with Advanced Services - http://www.microsoft.com/downloads/details.aspx?familyid=4C6BA9FD-319A-4887-BC75-3B02B5E48A40&displaylang=en
SP3 for SQL2005Express with Advanced Services - http://www.microsoft.com/downloads/details.aspx?familyid=B448B0D0-EE79-48F6-B50A-7C4F028C2E3D&displaylang=en
SQL Server Management Studio Express
SSMSE 2005 - http://go.microsoft.com/fwlink/?linkid=65110
SSMSE needs XML core - http://www.microsoft.com/downloads/details.aspx?familyid=993c0bcf-3bcf-4009-be21-27e85e1857b1&displaylang=en
64bit - http://go.microsoft.com/fwlink/?linkid=65264
MSDE (Microsoft SQL
Server 2000 Desktop Engine OR SQL 2000 Express)
main page - http://www.microsoft.com/downloads/details.aspx?FamilyID=413744D1-A0BC-479F-BAFA-E4B278EB9147&displaylang=en
SP4 - http://www.microsoft.com/downloads/details.aspx?familyid=8E2DFC8D-C20E-4446-99A9-B7F0213F8BC5&displaylang=en
SSMSE - you used to have to download the full blown trial
version of SQL2000 and do a selective install and just install the Management,
but now you can just use SSMSE2005 - http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en
MSDE - Microsoft SQL Desktop Engine
- Versions
- MSDE 7.6 - d/l and install latest SP for SQL (all SQL SP's
work on MSDE)
- MSDE2000a - release A - d/l and install latest SP for
SQL (all SQL SP's work on MSDE)
- SP4 tip - clear the SA password and include
"BLANKSAPWD=1" on setup line
- setup /upgradesp sqlrun DISABLENETWORKPROTOCOLS=0
BLANKSAPWD=1 INSTANCENAME=NS_DB /L*v C:\MSDELog.log
- enable network protocols with - srvnetcn.exe located in
"c:\Program Files\Microsoft SQL Server\80\Tools\Binn\"
- SQL Server 2005 Express Edition (renamed from DE) - ditto
on SP's
- How to tell what version and service pack you have
installed - http://support.microsoft.com/kb/321185
- these versions are actual restricted versions of the full
blown SQL servers. Their restrictions are database instances and database size
(which will just stop working without telling you).
- Need Enterprise Manager??? - you can d/l the full blown SQL
(SQLEval) and just do a custom install to install the "Enterprise
Manager"...when installing you will get to a screen that says "Client Tools,
Client & Server, Connect.." just to the Client Tools. The will give you
"MS SQL Enterprise Manager"...this will manage all versions of SQL
- once you d/l the file, expand it to a temporary directory and
read the "ReadMexxxxx.htm" in that directory on how to install
- To install a default instance configured to use Windows
Authentication Mode, execute:
- setup SAPWD="AStrongSAPwd"
- Where AStrongSAPwd is a strong password to be
assigned to the sa login.
- VERY HELPFUL - SQL Manager
- there are SQL tools out there that you can buy to maintain
your SQL, but the free ones won't allow you to do things that you really
need to do ie: like security stuff. You CAN download the
full blown SQL versions (which allows you to "trial" it for 90days or so),
when you install, unselect everything but the "SQL Manager" and this will
install the manager which is very handy.
- enable "Named Pipes" on the server (under Network Config on
server props) to be able to log onto server from another machine with SQL
Manager
SQL 2008 install in Windows XP
before even starting,
install these...
.NET 3.5 sp1 - dotnetfx35.exe - http://download.microsoft.com/download/2/0/e/20e90413-712f-438c-988e-fdaa79a8ac3d/dotnetfx35.exe
Windows installer v4.5 - WindowsXP-KB942288-v3-x86.exe -
http://www.microsoft.com/downloads/details.aspx?FamilyId=5A58B56F-60B6-4412-95B9-54D056D6F9F4&displaylang=en
Windows Powershell - WindowsXP-KB926139-v2-x86-ENU.exe - http://www.microsoft.com/downloads/details.aspx?FamilyID=6ccb7e0d-8f1d-4b97-a397-47bcc8ba3806&displaylang=en&Hash=cFbpfenT1tHDa0YSA%2bqf%2bliXrRlUE95%2bKpX%2fUID1zn8Lex6N4m5AHbRRE8wGey3icZZPJM86QtQGTby6hF8XdA%3d%3d
You are here: Home-Computer Tips & Help-Programming-SQL
Previous Topic: Clarion Next Topic: SQL Dates Subtopics: Procs and Funcs
|
