Showing posts with label sql. Show all posts
Showing posts with label sql. Show all posts

Tuesday, February 3, 2015

Rezolvare problema lui Danezu



create table Datetimes
(
       Date1 datetime,
       Date2 datetime,
       Date3 datetime,
       Date4 datetime,
       Date5 datetime
)

declare @i int
set @i = 1000

while (@i>0)
begin
       insert into Datetimes values(DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0),DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0),DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0),DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0),DATEADD(day, (ABS(CHECKSUM(NEWID())) % 65530), 0))
       set @i = @i - 1
end


go

select
max(Date) from
(
select max(Date1) a1 ,max(Date2) a2, max(Date3) a3, max(Date4) a4, max(Date5) a5 from Datetimes
)
t
unpivot
(
       Date for Dates in ([a1],[a2], [a3],[a4],[a5])
) as UnPvt

Pivot / Unpivot


create table Cart
(
    Product varchar(32),
    Color varchar(32),
    Nr int
)

insert into Cart values ('car','blue',3)
insert into Cart values ('car','red',2)
insert into Cart values ('car','black',3)
insert into Cart values ('car','white',1)
insert into Cart values ('car','blue',1)
insert into Cart values ('carrot','blue',3)
insert into Cart values ('carrot','red',2)
insert into Cart values ('onion','black',3)
insert into Cart values ('onion','white',1)
insert into Cart values ('car','blue',1)
insert into Cart values ('beer','blue',3)
insert into Cart values ('car','red',2)
insert into Cart values ('car','black',3)
insert into Cart values ('beer','white',1)
insert into Cart values ('car','blue',1)
insert into Cart values ('carrot','blue',3)
insert into Cart values ('beer','red',2)
insert into Cart values ('onion','black',3)
insert into Cart values ('onion','white',1)
insert into Cart values ('car','blue',1)
insert into Cart values ('car','blue',1)
insert into Cart values ('beer','blue',3)
insert into Cart values ('car','red',2)
insert into Cart values ('beer','black',3)
insert into Cart values ('beer','white',1)
insert into Cart values ('car','blue',1)
insert into Cart values ('car','blue',3)
insert into Cart values ('beer','red',2)



select * into CartPivot  from Cart
pivot
(
    sum(Nr)
    for Color in ([blue],[black],[white],[red])
) PivotTable



select * from CartPivot


select Product, Nr, details from CartPivot
Unpivot
(
  Nr for details in  ([blue],[black],[white],[red])
) as UnPvt













Wednesday, May 23, 2012

AES Encription (symetric key) with SQL Server

go

--keys for encryption
Create Master Key Encryption by Password='xxxx#'
Create Certificate App_Certificate With Subject='Test Certificate'
Create Symmetric Key App_SymmetricKey With Algorithm=AES_256 encryption by Certificate App_Certificate


go

create procedure EncryptStr
       @str varchar(256),
       @strOUT varbinary(256) output    
as

OPEN SYMMETRIC KEY App_SymmetricKey DECRYPTION BY CERTIFICATE App_Certificate

SET @strOUT = EncryptByKey(Key_GUID('SymmetricKey'), @str)

CLOSE SYMMETRIC KEY App_SymmetricKey

go

create procedure DecryptStr
       @str varbinary(256),
       @strOUT varchar(256) output
as

OPEN SYMMETRIC KEY App_SymmetricKey DECRYPTION BY CERTIFICATE App_Certificate

SET @strOUT = DecryptByKey(@str)

CLOSE SYMMETRIC KEY App_SymmetricKey

go


-- example for use

declare @x varbinary(256)
declare @s varchar(256)
declare @z varchar(256)


set @s = 'aaabbbccc'
--encrypt the string
exec EncryptStr @s, @x out
select @x

--decrypt the string
exec DecryptStr @x,@z out

select @z

Tuesday, September 27, 2011

Query AD using sql

declare @index int
declare @query nvarchar(2000)
declare @query_intreg nvarchar(2000)
declare @word varchar(1)

set @index = 65

while @index<91
begin
    set @word = char(@index)
    set @query = '''  select  mail,telephonenumber,cn,physicalDeliveryOfficeName,sAMAccountName,manager,adspath,Company,ipphone
                  from  ''''LDAP://AD.local/DC=AD,DC=local''''
                  WHERE sAMAccountName ='''''+@word+'*'''' AND Company = ''''*''''  ''  '


    set @query_intreg = 'select  mail,telephonenumber,cn,physicalDeliveryOfficeName,Company,0,sAMAccountName,adspath,manager,ipphone from  openquery(ADSI, '+@query+')'
   
    insert into #temp
    EXEC  sp_executesql @query_intreg
   
    set @index = @index +1
end

select * from #temp
drop table #temp

Wednesday, August 24, 2011

Get modified stored procedures and tables in sql server

SELECT name, type_desc,*
FROM sys.objects
WHERE (type = 'P' or type='U' or type='V')
order by modify_date desc

Friday, June 3, 2011

Get modified stored procedures and tables in sql server

SELECT name, type_desc,*
FROM sys.objects
WHERE (type = 'P' or type='U' or type='V')
order by modify_date desc

Thursday, June 2, 2011

Execute as on sql server

CREATE PROCEDURE dbo.Test
     @var int
WITH EXECUTE AS 'dbo'
AS

SELECT  *   FROM TABLE

Thursday, December 30, 2010

Search stored procedures with some words

select object_name(id),* from syscomments where text like '%IBAN%'

cautare fisiere pe hard-disk

create table #Exista (Id int, Filepath varchar(512), Exista bit)
insert into #Exista(ID,FilePath)
select Id, replace(FilePath,'/','') from Document where TipDocumentaFK = 16 and DocTemplateFK <>21
create table #Res (ExistaFis bit, EsteDir bit,ExistaParinte bit)



declare @Id int, @filePath varchar(512), @res bit
declare crs cursor for select Id, FilePath from #Exista
open crs
fetch next from crs into @Id, @filePath
while @@fetch_status = 0
begin
set @res = -1
truncate table #Res
insert into #Res exec master..xp_fileexist @filePath
select @res = ExistaFis from #Res
if @res <> - 1
update #Exista set Exista = @res where Id = @Id

fetch next from crs into @Id, @filePath
end
close crs
deallocate crs

select * from #Exista where Exista = 0

Foreach printre baze de date

create table #tmpUse (
ProcName varchar(128),
DBName varchar(128)
)

EXEC master.sys.sp_MSforeachdb "USE [?];insert #tmpUse select object_name(id),db_name() from syscomments where lower(text) like '%opendatasource%'"

select object_name(id), * from syscomments where lower(text) like '%opendatasource%'
select object_name(id), * from syscomments where lower(text) like '%openrowset%'
select object_name(id), * from syscomments where lower(text) like '%openquery%'

Monday, November 15, 2010

Levestein Metric

CREATE FUNCTION edit_distance(@s1 nvarchar(3999), @s2 nvarchar(3999))
RETURNS int
AS
BEGIN
  DECLARE @s1_len int, @s2_len int, @i int, @j int, @s1_char nchar, @c int, @c_temp int,
    @cv0 varbinary(8000), @cv1 varbinary(8000)
  SELECT @s1_len = LEN(@s1), @s2_len = LEN(@s2), @cv1 = 0x0000, @j = 1, @i = 1, @c = 0
  WHILE @j <= @s2_len
    SELECT @cv1 = @cv1 + CAST(@j AS binary(2)), @j = @j + 1
  WHILE @i <= @s1_len
  BEGIN
    SELECT @s1_char = SUBSTRING(@s1, @i, 1), @c = @i, @cv0 = CAST(@i AS binary(2)), @j = 1
    WHILE @j <= @s2_len
    BEGIN
      SET @c = @c + 1
      SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j-1, 2) AS int) +
        CASE WHEN @s1_char = SUBSTRING(@s2, @j, 1) THEN 0 ELSE 1 END
      IF @c > @c_temp SET @c = @c_temp
      SET @c_temp = CAST(SUBSTRING(@cv1, @j+@j+1, 2) AS int)+1
      IF @c > @c_temp SET @c = @c_temp
      SELECT @cv0 = @cv0 + CAST(@c AS binary(2)), @j = @j + 1
    END
    SELECT @cv1 = @cv0, @i = @i + 1
  END
  RETURN @c
END

Monday, March 15, 2010

Upload csv file in sql server using bcp

If you want to upload text files into sql database you must create an fmt file with file configuration. "fmt" file must have the same configuration with table you upload in.

create fmt file

exec master.dbo.xp_cmdshell 'bcp "DATABASE.dbo.TABLE" format null -f C:\FOLDER\FILE.fmt -c -T -U username -S ip
-e C:\FOLDER\err.txt'

where FILE.fmt reprezents the fmt file that you create
ERR.txt - error log file

The upload code is different int sql 2000 and 2005

--2005

select *
FROM OPENROWSET(BULK 'C:\FOLDER\FILE.csv',
FORMATFILE = 'c:\FOLDER\FILE.fmt') as tmp1


--2000

BULK INSERT ABR_FRAMEWORK.dbo.ABR_CRB_ERR_TRANSMISIE FROM 'c:\FILE\FILE.csv'
WITH (FORMATFILE = 'c:\FOLDER\FILE.fmt')

create sql users from a table

If you want to create Sql Server database users from an table (TABLE_USERS) you can use this code:

DECLARE @name VARCHAR(100)
DECLARE @cnp VARCHAR(100)
DECLARE @userName VARCHAR(100)
DECLARE @sql NVARCHAR(300)
DECLARE @id int
DECLARE @Name varchar(100)

DECLARE db_cursor CURSOR FOR
select   a.DomainName, c.Code,c.Name from TABLE_USERS

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name,@cnp ,@Name
WHILE @@FETCH_STATUS = 0
BEGIN

SET @userName = ltrim(rtrim(@name))
IF not EXISTS (SELECT * FROM sys.server_principals WHERE name = @userName)
begin
     select @sql = 'CREATE LOGIN ['+@userName+'] FROM WINDOWS  
           WITH  DEFAULT_DATABASE = [DATABASE],  DEFAULT_LANGUAGE=[us_english]'

     EXEC sp_executesql @sql
 
    select @sql = 'CREATE USER ['+@userName+'] FOR LOGIN ['+@userName+'] WITH  
          DEFAULT_SCHEMA=[dbo]'
    EXEC sp_executesql @sql
end

select @sqlRollMember = 'execute sp_addrolemember '+@DBRole2+' , '''+@userName + ''''
EXEC sp_executesql @sqlRollMember
END
CLOSE db_cursor
DEALLOCATE db_cursor

Create SSIS Package - SQL Server

To create a new SSIS package, follow these steps:
1. Launch Business Intelligence Development Studio

2. Select File -> New -> Project.
3. Select the Business Intelligence Projects project type.

4. Select the Integration Services Project template.

5. Select a convenient location.

6. Name the new project ISProject1 and click OK.


Working with Connection Managers


SSIS uses connection managers to integrate different data sources into packages.
SSIS includes a wide variety of different connection managers that allow you to move data around from place to place. Table 16-1 lists the available connection managers.

ADO Connection Manager Connecting to ADO objects such as a Recordset
ADO.NET Connection Manager  - Connecting to data sources through an ADO.NET provider.
Analysis Services Connection Manager  - Connecting to an Analysis Services database or cube.
Excel Connection Manager  - Connecting to an Excel worksheet.
File Connection Manager - Connecting to a file or folder.
Flat File Connection Manager  - Connecting to delimited or fixed width flat files.
FTP Connection Manager - Connecting to an FTP data source.
HTTP Connection Manager Connecting to an HTTP data source.

MSMQ Connection Manager Connecting to a Microsoft Message Queue.
Multiple Files Connection Manager Connecting to a set of files, such as all text files on a particular hard drive.
Multiple Flat Files Connection Manager Connecting to a set of flat files.
ODBC Connection Manager Connecting to an ODBC data source.
OLE DB Connection Manager Connecting to an OLE DB data source.
SMO Connection Manager Connecting to a server via SMO.
SMTP Connection Manager Connecting to a Simple Mail Transfer Protocol server.
SQL Server Mobile Connection Manager Connecting to a SQL Server Mobile database.
WMI Connection Manager Connecting to Windows Management

To create a Connection Manager, you right-click anywhere in the Connection Managers area of a package in BIDS and choose the appropriate shortcut from the shortcut menu. Each Connection Manager has its own custom configuration dialog  box with specific options that you need to fill out.

Try It!


To add some connection managers to your package, follow these steps:
1. Right-click in the Connection Managers area of your new package and select New OLE DB Connection.
2. Note that the configuration dialog box will show the data connections that you created in Chapter 15; data connections are shared across Analysis Services and Integration Services projects. Click New to create a new data connection.
3. In the Connection Manager dialog box, select the SQL Native Client provider.
4. Select your test server and provide login information.
5. Select the Chapter16 database.
6. Click OK.
7. In the Configure OLE DB Connection Manager dialog box, click OK.
8. Right-click in the Connection Managers area of your new package and select New Flat File Connection.
9. Enter DepartmentList as the Connection Manager Name.
10. Enter C:\Departments.txt as the File Name (this file will be supplied by your instructor).
11. Check the Column Names in the First Data Row checkbox. Figure 16-4 shows the completed General page of the dialog box.



 
12. Click the Advanced icon to move to the Advanced page of the dialog box.
13. Click the New button.
14. Change the Name of the new column to DepartmentName.
15. Click OK.
16. Right-click the DepartmentList Connection Manager and select Copy.
17. Right-click in the Connection Managers area and select Paste.
18. Click on the new DepartmentList 1 connection to select it.


Use the Properties Window to change properties of the new connection.
Change the Name property to DepartmentListBackup. Change the ConnectionString property to C:\DepartmentsBackup.txt.


The Control Flow tab of the Package Designer is where you tell SSIS what them package will do. You create your control flow by dragging and dropping items from the toolbox to the surface, and then dragging and dropping connections between the objects. The objects you can drop here break up into four different groups:
• Tasks are things that SSIS can do, such as execute SQL statements or transfer objects from one SQL Server to another. Table 16-2 lists the available tasks.
• Maintenance Plan tasks are a special group of tasks that handle jobs such as checking database integrity and rebuilding indexes. Table 16-3 lists the maintenance plan tasks.
The Data Flow Task is a general purpose task for ETL (extract, transform, and load) operations on data. There’s a separate design tab for building the details of a Data Flow Task.
• Containers are objects that can hold a group of tasks. Table 16-4 lists the available containers.


To add control flows to the package you’ve been building, follow these steps:


1. If the Toolbox isn’t visible already, hover your mouse over the Toolbox tab until it slides out from the side of the BIDS window. Use the pushpin button in the Toolbox title bar to keep the Toolbox visible.
2. Make sure the Control Flow tab is selected in the Package Designer.
3. Drag a File System Task from the Toolbox and drop it on the Package Designer.
4. Drag a Data Flow Task from the Toolbox and drop it on the Package Designer, somewhere below the File System task.
5. Click on the File System Task on the Package Designer to select it.
6. Drag the green arrow from the bottom of the File System Task and drop it on top of the Data Flow Task. This tells SSIS the order of tasks when the File System Task succeeds.
7. Double-click the connection between the two tasks to open the Precedence Constraint Editor.
8. Change the Value from Success to Completion, because you want the Data Flow Task to execute whether the File System Task succeeds or not.
9. Click OK.
10. Select the File System task in the designer. Use the Properties Window to set properties of the File System Task. Set the Source property to DepartmentList. Set the Destination property to DepartmentListBackup.Set the OverwriteDestinationFile property to True.



Building Data Flows


The Data Flow tab of the Package Designer is where you specify the details of any Data Flow tasks that you’ve added on the Control Flow tab. Data Flows are made up of various objects that you drag and drop from the Toolbox:
• Data Flow Sources are ways that data gets into the system. Table 16-5 lists the available data flow sources.
• Data Flow Transformations let you alter and manipulate the data in various ways. Table 16-6 lists the available data flow transformations.
• Data Flow Destinations are the places that you can send the transformed data.

Try It!


To customize the data flow task in the package you’re building, follow these steps:

1. Select the Data Flow tab in the Package Designer. The single Data Flow Task in the package will automatically be selected in the combo box.
2. Drag an OLE DB Source from the Toolbox and drop it on the Package Designer.
3. Drag a Character Map Transformation from the Toolbox and drop it on the Package Designer.
4. Drag a Flat File Destination from the Toolbox and drop it on the Package Designer.
5. Click on the OLE DB Source on the Package Designer to select it.
6. Drag the green arrow from the bottom of the OLE DB Source and drop it on top of the Character Map Transformation.
7. Click on the Character Map Transformation on the Package Designer to select it.
8. Drag the green arrow from the bottom of the Character Map Transformation and drop it on top of the Flat File Destination.
9. Double-click the OLE DB Source to open the OLE DB Source Editor.
10. Select the HumanResources.Department view. Figure 16-7 shows the completed OLE DB Source Editor.

11. Click OK.

12. Double-click the Character Map Transformation.
13. Check the Name column.
14. Select In-Place Change in the Destination column.
15. Select the Uppercase operation. Figure 16-8 shows the completed Character Map Transformation Editor.


16. Click OK.

17. Double-click the Flat File Destination.
18. Select the DepartmentList Flat File Connection Manager.
19. Select the Mappings page of the dialog box.
20. Drag the Name column from the Available Input Columns list and drop it on top of the DepartmentName column in the Available Destination Columns list.




The data flows in this package take a table from the Chapter16 database, transform one of the columns in that table to all uppercase characters, and then write that transformed column out to a flat file.

Sunday, March 14, 2010

SQL Connection Strings samples

SQL Server

OleDb Sql Connection String - user and password

Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;


OleDb Sql Connection String - trusted connection (integrated authentification)

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;

Mysql

Standard
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Specifying port
Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Specifying default command timeout
Use this one to specify a default command timeout for the connection. Please note that the property in the connection string does not supercede the individual command timeout property on an individual command
object.Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;default command timeout=20;

Connect c# to sql

C# Class exemple for database work

using System;
using System.Text;
using System.Data.SqlClient;
using System.Data;

class Locations
{
private int _Id;
private String _Location;

public Locations()
{
_Id = 0;
_Location = "";
}

public Locations(int m_Id, String m_Location)
{
_Location = m_Location;
}

public static DataTable GetMultipleLocations()
{
SqlConnection conn = new SqlConnection("");
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "procedura";
DataTable tabel = new DataTable();
SqlDataAdapter dt = new SqlDataAdapter(command);
dt.Fill(tabel);
return tabel;
}

public void GetLocations(String Id)
{
SqlConnection conn = new SqlConnection("");
conn.Open();
SqlCommand command = new SqlCommand();
command.Connection = conn;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "procedura";
SqlParameter p = new SqlParameter("Id", SqlDbType.Int);
command.Parameters[0].Value = Id;
SqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
_Id = dr.GetInt32(0);
_Location = dr.GetString(2);
}
}
public int Id
{
get { return _Id; }
set { _Id = value; }
}
public String Location
{
get { return _Location; }
set { _Location = value; }
}
}