berbagisolusi
SQL Server
SQL Server Last ID Identity
Assuming a simple table: CREATE TABLE dbo.foo(ID INT IDENTITY(1,1), name SYSNAME); We can capture IDENTITY values in a table variable for further consumption. DECLARE @IDs TABLE(ID INT); — minor change to INSERT statement; add an OUTPUT clause: INSERT dbo.foo(name) OUTPUT inserted.ID INTO @IDs(ID) SELECT N’Fred’ UNION ALL SELECT N’Bob’; SELECT ID FROM @IDs; The nice … Continue reading
Find Slow/Worst Query in Database
1. SQL Server Sql Server haven’t monitoring GUI for finding worst query may be need optimized, but can use this sql script SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END – qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS … Continue reading
Get All Table,Field and properties in SQL Server
1. Getting All Table in database : USE your_database SELECT name FROM sys.tables 2. Getting All Field in database, if you want getting all field with description can use this : SELECT u.name + ‘.’ + t.name AS [table], td.value AS [table_desc], c.name AS [column], cd.value AS [column_desc] FROM sysobjects t INNER JOIN sysusers u … Continue reading
Remote Access Reporting Service (Setting Role Reporting Service SQL Server 2005)
WINDOWS XP & 2003 SERVER a. Run cmd.. type “inetmgr” b. Disable Anonymous Access in Reports&ReportServer Virtual Directory c. Goto IE Browser, and type http://localhost/reports > Properties Tab > Security d. Add new Role Assigment for NT AUTHORITY\IUSR or IUSR_<COMPUTERNAME> e. Enable Anonymous Access in Reports&ReportServer Virtual Directory f. Restart SQLSERVER service. WINDOWS 7 & 2008 SERVER a. … Continue reading
SQL Server 2005 : Drilldown Reporting Service
The solution to these challenges is to understand some subtle techniques when using the Reporting Services report designer. This discussion will demonstration three specific techniques. All of the materials needed for this demonstration are included so you can try this yourself. The techniques are the following: Technique #1 – The Standard “Drilldown” Technique #2 – … Continue reading
ASP.NET : Read & Write Text File
Reading From a Text File The first step in reading from a text file is hooking up a StreamReader to the specified file. This is accomplished by using the File class’s OpenText() method. This can be accomplished in the following single line of code: ‘VB.NET Dim sr as StreamReader = File.OpenText(“C:\test.txt”)// C# StreamReader sr = File.OpenText(“C:\\test.txt”); A couple quick things to … Continue reading
SQL Server 2005 Jobs Error : Cannot insert the value NULL into column ‘owner_sid’, table ‘msdb.dbo.sysjobs’; column does not allow nulls.
When you create a job when your are connected from an other domain SQL Server cannot validate your NT authentication and a error as Cannot insert the value NULL into column ‘owner_sid’, table ‘msdb.dbo.sysjobs’; column does not allow nulls. INSERT fails. is shown. You can correct this by setting the job owner by hand with te … Continue reading
SSIS Data Conversion
Data conversion : SQL Server Data type Numeric —-> [DT_Numeric] SQL Server Data type Varchar —–> DT_STR SQL Server Data type NVarchar —–> Unicode String [DT_WSTR] The following table provides guidance on mapping the data types used by certain databases to Integration Services data types. These mappings are summarized from the mapping files used by … Continue reading
SSIS The exit codes values
Executing an SSIS package from within a stored procedure is not an uncommon request. People sometimes scratch their head when it comes to figuring out if that package ran successfully or not though. There may be a process that runs a stored procedure and kicks off a certain SSIS package depending on the situation. Maybe there are parameters … Continue reading
Error SSIS Variable (Cannot assign value to variable): The result of the expression “_” on property “Executable” cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Stack error assign value to SSIS paramter via ASP.NET or batch file. Yesterday I’m stack in Execute Process Task to execute Batch file, that batch is setting of SSIS execute. The First solution, 1. restart your IIS (if using ASP.NET) always restart your IIS after changed the SSIS component and properties value 2. Change variable properties … Continue reading
Web Statistic
berbagisolusi 