Sziasztok,

Mindig is szerettem volna önmegvalósítani. Ennek egy fontos lépéséhez érkeztem el. .

SQL Server Management Studio and TortoiseSVN

Update: SQL Source Control was released a while back! See my article on Simple-Talk for more information.

At work we maintain a few SQL Server Management Studio (SSMS) solutions for our SQL views, stored procedures and functions. We also use TortoiseSVN for source control. Unfortunately, there are no SVN add-ins for SSMS and the ones for Visual Studio don’t work (VisualSVN, AnkhSVN). Its a bit frustrating that SSMS is built on the same technology as Visual Studio, but lacks so many of the features that I’ve grown accustomed to, such as the Add-in Manager.

Red Gate, however, is currently working on a add-in called SQL Source Control with a planned release in 2010. But what to do until then? Well, there is one officially supported point of extensibility in SSMS: External Tools. Here are a few that I’ve been using with TortoiseSVN lately:

Title: SVN Commit
Command: C:\Program Files\TortoiseSVN\bin\TortoiseProc.exe
Arguments: /Command:commit /path:”$(SolutionDir)
Initial directory: $(SolutionDir)

Title: SVN Update
Command: C:\Program Files\TortoiseSVN\bin\TortoiseProc.exe
Arguments: /Command:update /path:”$(SolutionDir)”
Initial directory: $(SolutionDir)

Title: SVN Log (Solution)
Command: C:\Program Files\TortoiseSVN\bin\TortoiseProc.exe
Arguments: /Command:log /path:”$(SolutionDir)”
Initial directory: $(SolutionDir)

Title: SVN Log (Current Item)
Command: C:\Program Files\TortoiseSVN\bin\TortoiseProc.exe
Arguments: /Command:log /path:”$(ItemFileName)$(ItemExt)”
Initial directory: $(ItemDir)

Title: SVN Diff
Command: C:\Program Files\TortoiseSVN\bin\TortoiseProc.exe
Arguments: /Command:diff /path:”$(ItemFileName)$(ItemExt)”
Initial directory: $(ItemDir)

Oracle datas into Excel:

Példa:

declare
l_cursor number := dbms_sql.open_cursor;
output utl_file.file_type;
begin
output := utl_file.fopen( 'c:\temp\', 'emp2.slk', 'w',32000 );

dbms_sql.parse( l_cursor,
'select empno id, ename employee,
sal Salary, comm commission ' ||
'from scott.emp ' ||
'where job = ''MANAGER'' ' ||
'and sal > 2000',
dbms_sql.native );

owa_sylk.show(
p_file => output ,
p_cursor => l_cursor,
p_sum_column =>
owa_sylk.owaSylkArray( 'N', 'N', 'Y', 'Y' ),
p_show_grid => 'NO' );
dbms_sql.close_cursor( l_cursor );
utl_file.fclose( output );
end;

*/

create or replace
package owa_sylk as
--
type owaSylkArray is table of varchar2(2000);
--
procedure show(
p_file in utl_file.file_type,
p_query in varchar2,
p_parm_names in owaSylkArray default owaSylkArray(),
p_parm_values in owaSylkArray default owaSylkArray(),
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES' );
--
procedure show(
p_file in utl_file.file_type,
p_cursor in integer,
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES' );
--
end owa_sylk;
/
show error

create or replace
package body owa_sylk as
--
g_cvalue varchar2(32767);
g_desc_t dbms_sql.desc_tab;

type vc_arr is table of varchar2(2000) index by binary_integer;
g_lengths vc_arr;
g_sums vc_arr;
--
--

g_file utl_file.file_type;


procedure p( p_str in varchar2 )
is
begin
utl_file.put_line( g_file, p_str );
end;

function build_cursor(
q in varchar2,
n in owaSylkArray,
v in owaSylkArray ) return integer is
c integer := dbms_sql.open_cursor;
i number := 1;
begin
dbms_sql.parse (c, q, dbms_sql.native);
loop
dbms_sql.bind_variable( c, n(i), v(i) );
i := i + 1;
end loop;
return c;
end build_cursor;
--
--
function str_html ( line in varchar2 ) return varchar2 is
x varchar2(32767) := null;
in_html boolean := FALSE;
s varchar2(1);
begin
if line is null then
return line;
end if;
for i in 1 .. length( line ) loop
s := substr( line, i, 1 );
if in_html then
if s = '>' then
in_html := FALSE;
end if;
else
if s = '<' then
in_html := TRUE;
end if;
end if;
if not in_html and s != '>' then
x := x || s;
end if;
end loop;
return x;
end str_html;
--
function ite( b boolean,
t varchar2,
f varchar2 ) return varchar2 is
begin
if b then
return t;
else
return f;
end if;
end ite;
--
procedure print_comment( p_comment varchar2 ) is
begin
return;
p( ';' || chr(10) || '; ' || p_comment || chr(10) || ';' );
end print_comment;
--
procedure print_heading( font in varchar2,
grid in varchar2,
col_heading in varchar2,
titles in owaSylkArray )
is
l_title varchar2(2000);
begin
p( 'ID;ORACLE' );
print_comment( 'Fonts' );
p( 'P;F' || font || ';M200' );
p( 'P;F' || font || ';M200;SB' );
p( 'P;F' || font || ';M200;SUB' );
--
print_comment( 'Global Formatting' );
p( 'F;C1;FG0R;SM1' ||
ite( upper(grid)='YES', '', ';G' ) ||
ite( upper(col_heading)='YES', '', ';H' ) );
for i in 1 .. g_desc_t.count loop
p( 'F;C' || to_char(i+1) || ';FG0R;SM0' );
end loop;
--
print_comment( 'Title Row' );
p( 'F;R1;FG0C;SM2' );
for i in 1 .. g_desc_t.count loop
g_lengths(i) := g_desc_t(i).col_name_len;
g_sums(i) := 0;
begin
l_title := titles(i);
exception
when others then
l_title := g_desc_t(i).col_name;
end;
if i = 1 then
p( 'C;Y1;X2;K"' || l_title || '"' );
else
p( 'C;X' || to_char(i+1) || ';K"' || l_title || '"' );
end if;
end loop;
end print_heading;
--
function print_rows(
c in integer,
max_rows in number,
sum_columns in owaSylkArray,
show_null_as in varchar2,
strip_html in varchar2 ) return number is
row_cnt number := 0;
line varchar2(32767) := null;
n number;
begin
loop
exit when ( row_cnt >= max_rows or
dbms_sql.fetch_rows( c ) <= 0 );
row_cnt := row_cnt + 1;
print_comment( 'Row ' || row_cnt );
--
p( 'C;Y' || to_char(row_cnt+2) );

for i in 1 .. g_desc_t.count loop
dbms_sql.column_value( c, i, g_cvalue );
g_cvalue := translate( g_cvalue,
chr(10)||chr(9)||';', ' ' );
g_cvalue := ite( upper( strip_html ) = 'YES',
str_html( g_cvalue ),
g_cvalue );
g_lengths(i) := greatest( nvl(length(g_cvalue),
nvl(length(show_null_as),0)),
g_lengths(i) );
line := 'C;X' || to_char(i+1);
line := line || ';K';
begin
n := to_number( g_cvalue );
if upper( sum_columns(i)) = 'Y' then
g_sums(i) := g_sums(i) + nvl(n,0);
end if;
exception
when others then
n := null;
end;
line := line ||
ite( n is null,
ite( g_cvalue is null,
'"'||show_null_as||
'"', '"'||g_cvalue||'"' ),
n );
p( line );
end loop;
--
end loop;
return row_cnt;
end print_rows;
--
procedure print_sums(
sum_columns in owaSylkArray,
row_cnt in number ) is
begin
if sum_columns.count = 0 then
return;
end if;
--
print_comment( 'Totals Row' );
p( 'C;Y' || to_char(row_cnt + 4) );
p( 'C;X1;K"Totals:"' );
--
for i in 1 .. g_desc_t.count loop
begin
if upper(sum_columns(i)) = 'Y' then
p( 'C;X' || to_char(i+1) || ';ESUM(R3C:R' ||
to_char(row_cnt+2) || 'C)' );
end if;
end;
end loop;
end print_sums;
--
procedure print_widths( widths owaSylkArray ) is
begin
print_comment( 'Format Column Widths' );
p( 'F;W1 1 7' );
for i in 1 .. g_desc_t.count loop
begin
p( 'F;W' || to_char(i+1) || ' ' ||
to_char(i+1) || ' ' ||
to_char(to_number(widths(i))) );
exception
when others then
p( 'F;W' || to_char(i+1) || ' ' ||
to_char(i+1) || ' ' ||
greatest( g_lengths(i), length( g_sums(i) )));
end;
end loop;
p( 'E' );
end print_widths;
--
procedure show(
p_file in utl_file.file_type,
p_cursor in integer,
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES' ) is
--
l_row_cnt number;
l_col_cnt number;
l_status number;
begin
g_file := p_file;
dbms_sql.describe_columns( p_cursor, l_col_cnt, g_desc_t );
--
for i in 1 .. g_desc_t.count loop
dbms_sql.define_column( p_cursor, i, g_cvalue, 32765);
end loop;
--
print_heading( p_font_name,
p_show_grid,
p_show_col_headers,
p_titles );
l_status := dbms_sql.execute( p_cursor );
l_row_cnt := print_rows(
p_cursor,
p_max_rows,
p_sum_column,
p_show_null_as,
p_strip_html );
print_sums( p_sum_column, l_row_cnt );
print_widths( p_widths );
end show;
--
procedure show(
p_file in utl_file.file_type,
p_query in varchar2,
p_parm_names in owaSylkArray default owaSylkArray(),
p_parm_values in owaSylkArray default owaSylkArray(),
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES' ) is
begin
show( p_file => p_file,
p_cursor => build_cursor( p_query,
p_parm_names,
p_parm_values ),
p_sum_column => p_sum_column,
p_max_rows => p_max_rows,
p_show_null_as => p_show_null_as,
p_show_grid => p_show_grid,
p_show_col_headers => p_show_col_headers,
p_font_name => p_font_name,
p_widths => p_widths,
p_titles => p_titles,
p_strip_html => p_strip_html );
end show;
--
end owa_sylk;
/
show error

http://asktom.oracle.com

[ Szerkesztve ]

Kerekítés 5 Ft-ra

2 megoldás létezik:
1. gondolkodunk és kilogikázzuk a megoldást:
=KEREKÍTÉS(A2/5;0)*5
2. beépített függvényt használunk.
=PADLÓ(A2;5)

http://www.hatekonysag.hu/blog/kerekites-5-ft-ra.htm vs. MS saját oldala

[ Szerkesztve ]

nullif(override,'')

select nullif(' ','')

----
NULL

(1 row(s) affected)

select coalesce(nullif(override,''), description) as Display

ISNULL (Transact-SQL)

NULLIF (Transact-SQL)

SELECT ProductID, NULLIF(TotalPreviousYear, TotalCurrentYear) FROM v_ProductSellByYear
provide NULL if values are equal,
it’s usefull for extra info columns when coupled with ISNULL,
simpler SQL then CASE WHEN

tblCommodityOrderItem oi with(nolock)
LEFT JOIN tblCommodity c with(nolock)
ON SUBSTRING(oi.sCode, 1, ISNULL(NULLIF(CHARINDEX(‘-’, oi.sCode) -1, -1), LEN(oi.sCode))) = c.sCode

[ Szerkesztve ]

select * from NAPLO where LEIRAS like "%10-es%" and IDO between "2012-11-01" and "2012-11-25"

select * from NAPLO where LEIRAS like "%20-as%" and IDO between "2012-11-01" and "2012-11-25"

SELECT t.leiras
,COUNT(*) || ' db'

FROM naplo AS t

WHERE
t.ido BETWEEN '2012-11-01' AND '2012-11-25'
AND t.leiras LIKE '%0-_s%'

GROUP BY t.leiras

SELECT t.leiras
,COUNT(*) || ' db'

FROM naplo AS t

WHERE
t.ido BETWEEN '2012-11-01' AND '2012-11-25'
AND (t.leiras LIKE '%10-es%'
OR t.leiras LIKE '%20-as%'
OR t.leiras LIKE '%30-as%'
OR t.leiras LIKE '%40-es%'
OR t.leiras LIKE '%50-es%'
OR t.leiras LIKE '%60-as%'
OR t.leiras LIKE '%70-es%'
OR t.leiras LIKE '%80-as%'
OR t.leiras LIKE '%90-es%'
OR t.leiras LIKE '%100-as%')

GROUP BY t.leiras

select ACCOUNT, LINE, IDO, LEIRAS
from (
(select ACCOUNT, LINE, IDO, LEIRAS
from NAPLO
where a.LEIRAS like '%Helyszínen%') as a
union
(select ACCOUNT, LINE, IDO, LEIRAS
from NAPLO
where b.LEIRAS like '%Leellenõrizve%') as b
)
where if(a.ACCOUNT = b.ACCOUNT and a.LINE = b.LINE) then timestampdiff(minute, a.IDO, b.IDO) > 15
group by ACCOUNT

(select ......, LEIRAS
from NAPLO
where a.LEIRAS like '%Helyszínen%') as a
union
(select ...., LEIRAS
from NAPLO
where b.LEIRAS like '%Leellenõrizve%') as b

select varos,round(avg(fizetes),0)

Create an Excel file named testing having the headers same as that of table columns and use these queries

1 Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table

select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES', 'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use

EXEC sp_makewebtask
@outputfile = 'd:\testing.xls',
@query = 'Select * from Database_name..SQLServerTable',
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'
(Now you can find the file with data in tabular format)

5 To export data to new EXCEL file with heading(column names), create the following procedure

create procedure proc_generate_excel_with_columns
(
@db_name varchar(100),
@table_name varchar(100),
@file_name varchar(100)
)
as

--Generate column names as a recordset
declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
select
@columns=coalesce(@columns+',','')+column_name+' as '+column_name
from
information_schema.columns
where
table_name=@table_name
select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data
select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file
set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
exec(@sql)

--Generate data in the dummy file
set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
exec(@sql)

--Copy dummy file to passed EXCEL file
set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
exec(@sql)

--Delete dummy file
set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path:

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'
Its a whomping 29 pages but that is because others show various other ways as well as people asking questions just like this one on how to do it.

Follow that thread entirely and look at the various questions people have asked and how they are solved. I picked up quite a bit of knowledge just skimming it and have used portions of it to get expected results.

To update single cells

A member also there Peter Larson posts the following: I think one thing is missing here. It is great to be able to Export and Import to Excel files, but how about updating single cells? Or a range of cells?

This is the principle of how you do manage that

update OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls;hdr=no',
'SELECT * FROM [Sheet1$b7:b7]') set f1 = -99
You can also add formulas to Excel using this:

update OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\test.xls;hdr=no',
'SELECT * FROM [Sheet1$b7:b7]') set f1 = '=a7+c7'

Ad Hoc query error resolved with:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

[ Szerkesztve ]

– Create XLS script DAL – 04/24/2003
–
– Designed for Agent scheduling, turn on “Append output for step history”
–
– Search for %%% to find adjustable constants and other options
–
– Uses OLE for ADO and OLE DB to create the XLS file if it does not exist
– Linked server requires the XLS to exist before creation
– Uses OLE ADO to Create the XLS Worksheet for use as a table by T-SQL
– Uses Linked Server to allow T-SQL access to XLS table
– Uses T-SQL to populate te XLS worksheet, very fast
–
PRINT ‘Begin CreateXLS script at ‘+RTRIM(CONVERT(varchar(24),GETDATE(),121))+’ ‘
PRINT ”
GO

SET NOCOUNT ON
DECLARE @Conn int — ADO Connection object to create XLS
, @hr int — OLE return value
, @src varchar(255) — OLE Error Source
, @desc varchar(255) — OLE Error Description
, @Path varchar(255) — Drive or UNC path for XLS
, @Connect varchar(255) — OLE DB Connection string for Jet 4 Excel ISAM
, @WKS_Created bit — Whether the XLS Worksheet exists
, @WKS_Name varchar(128) — Name of the XLS Worksheet (table)
, @ServerName nvarchar(128) — Linked Server name for XLS
, @DDL varchar(8000) — Jet4 DDL for the XLS WKS table creation
, @SQL varchar(8000) — INSERT INTO XLS T-SQL
, @Recs int — Number of records added to XLS
, @Log bit — Whether to log process detail

– Init variables
SELECT @Recs = 0
– %%% 1 = Verbose output detail, helps find problems, 0 = minimal output detail
, @Log = 1
– %%% assign the UNC or path and name for the XLS file, requires Read/Write access
– must be accessable from server via SQL Server service account
– & SQL Server Agent service account, if scheduled
SET @Path = ‘C:\TEMP\Test_’+CONVERT(varchar(10),GETDATE(),112)+’.xls’
– assign the ADO connection string for the XLS creation
SET @Connect = ‘Provider=Microsoft.Jet.OLEDB.4.0;Data Source=’+@Path+’;Extended Properties=Excel 8.0′
– %%% assign the Linked Server name for the XLS population
SET @ServerName = ‘EXCEL_TEST’
– %%% Rename Table as required, this will also be the XLS Worksheet name
SET @WKS_Name = ‘People’
– %%% Table creation DDL, uses Jet4 syntax,
– Text data type = varchar(255) when accessed from T-SQL
SET @DDL = ‘CREATE TABLE ‘+@WKS_Name+’ (SSN Text, Name Text, Phone Text)’
– %%% T-SQL for table population, note the 4 part naming required by Jet4 OLE DB
– INSERT INTO SELECT, INSERT INTO VALUES, and EXEC sp types are supported
– Linked Server does not support SELECT INTO types
SET @SQL = ‘INSERT INTO ‘+@ServerName+’…’+@WKS_Name+’ (SSN, Name, Phone) ‘
SET @SQL = @SQL+’SELECT au_id AS SSN’
SET @SQL = @SQL+’, LTRIM(RTRIM(ISNULL(au_fname,””)+” ”+ISNULL(au_lname,””))) AS Name’
SET @SQL = @SQL+’, phone AS Phone ‘
SET @SQL = @SQL+’FROM pubs.dbo.authors’

IF @Log = 1 PRINT ‘Created OLE ADODB.Connection object’
– Create the Conn object
EXEC @hr = sp_OACreate ‘ADODB.Connection’, @Conn OUT
IF @hr <> 0 — have to use <> as OLE / ADO can return negative error numbers
BEGIN
– Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

IF @Log = 1 PRINT char(9)+’Assigned ConnectionString property’
– Set a the Conn object’s ConnectionString property
– Work-around for error using a variable parameter on the Open method
EXEC @hr = sp_OASetProperty @Conn, ‘ConnectionString’, @Connect
IF @hr <> 0
BEGIN
– Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

IF @Log = 1 PRINT char(9)+’Open Connection to XLS, for file Create or Append’
– Call the Open method to create the XLS if it does not exist, can’t use parameters
EXEC @hr = sp_OAMethod @Conn, ‘Open’
IF @hr <> 0
BEGIN
– Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

– %%% This section could be repeated for multiple Worksheets (Tables)
IF @Log = 1 PRINT char(9)+’Execute DDL to create ”’+@WKS_Name+”’ worksheet’
– Call the Execute method to Create the work sheet with the @WKS_Name caption,
– which is also used as a Table reference in T-SQL
– Neat way to define column data types in Excel worksheet
– Sometimes converting to text is the only work-around for Excel’s General
– Cell formatting, even though the Cell contains Text, Excel tries to format
– it in a “Smart” way, I have even had to use the single quote appended as the
– 1st character in T-SQL to force Excel to leave it alone
EXEC @hr = sp_OAMethod @Conn, ‘Execute’, NULL, @DDL, NULL, 129 — adCmdText + adExecuteNoRecords
– 0x80040E14 for table exists in ADO
IF @hr = 0x80040E14
– kludge, skip 0×80042732 for ADO Optional parameters (NULL) in SQL7
OR @hr = 0×80042732
BEGIN
– Trap these OLE Errors
IF @hr = 0x80040E14
BEGIN
PRINT char(9)+””+@WKS_Name+”’ Worksheet exists for append’
SET @WKS_Created = 0
END
SET @hr = 0 — ignore these errors
END
IF @hr <> 0
BEGIN
– Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

IF @Log = 1 PRINT ‘Destroyed OLE ADODB.Connection object’
– Destroy the Conn object, +++ important to not leak memory +++
EXEC @hr = sp_OADestroy @Conn
IF @hr <> 0
BEGIN
– Return OLE error
EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT
SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

– Linked Server allows T-SQL to access the XLS worksheet (Table)
– This must be performed after the ADO stuff as the XLS must exist
– and contain the schema for the table, or worksheet
IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
IF @Log = 1 PRINT ‘Created Linked Server ”’+@ServerName+”’ and Login’
EXEC sp_addlinkedserver @server = @ServerName
, @srvproduct = ‘Microsoft Excel Workbook’
, @provider = ‘Microsoft.Jet.OLEDB.4.0′
, @datasrc = @Path
, @provstr = ‘Excel 8.0′
– no login name or password are required to connect to the Jet4 ISAM linked server
EXEC sp_addlinkedsrvlogin @ServerName, ‘false’
END

– Have to EXEC the SQL, otherwise the SQL is evaluated
– for the linked server before it exists
EXEC (@SQL)
PRINT char(9)+’Populated ”’+@WKS_Name+”’ table with ‘+CONVERT(varchar,@@ROWCOUNT)+’ Rows’

– %%% Optional you may leave the Linked Server for other XLS operations
– Remember that the Linked Server will not create the XLS, so remove it
– When you are done with it, especially if you delete or move the file
IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
IF @Log = 1 PRINT ‘Deleted Linked Server ”’+@ServerName+”’ and Login’
EXEC sp_dropserver @ServerName, ‘droplogins’
END
GO

SET NOCOUNT OFF
PRINT ”
PRINT ‘Finished CreateXLS script at ‘+RTRIM(CONVERT(varchar(24),GETDATE(),121))+’ ‘
GO

DATEDIFF(dd,kezdő_dátum,vég_dátum)

A következő komolyabb munka a csv, xls fájl generálás sql scriptből.

Dátum konverziók:

SELECT CONVERT(nvarchar(30), GETDATE(), 100)

Dec 6 2013 10:03AM

SELECT CONVERT(nvarchar(30), GETDATE(), 101)

12/06/2013

SELECT CONVERT(nvarchar(30), GETDATE(), 0)

Dec 6 2013 10:03AM

SELECT CONVERT(nvarchar(30), GETDATE(), 120)

2013-12-06 10:03:20

SELECT REPLACE(LEFT(Convert(nvarchar(30),GETDATE(),120),16),'-','.')

2013.12.06 10:03

SELECT CONVERT(nvarchar(30), GETDATE(), 121)

2013-12-06 10:03:20.687