Tuesday, February 3, 2015
Rezolvare problema lui Danezu
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
--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 @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
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
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
@var int
WITH EXECUTE AS 'dbo'
AS
SELECT * FROM TABLE
Thursday, December 30, 2010
Search stored procedures with some words
cautare fisiere pe hard-disk
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
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
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
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'
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
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
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.
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.
• 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.
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.
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.
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.
Sunday, March 14, 2010
SQL Connection Strings samples
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;
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
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; }
}
}
