berbagisolusi
Database
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
Postgres Database
Backup SQL Format using PgAdmin: 1. Right click in database 2. Set Format to “Plain” Restore SQL Format (Plain) in Windows 1. cmd 2. type : psql -h host -U user -p port dbname < file (User use U capslock) example : C:\Program Files\PostgreSQL\9.3\bin>psql -h localhost -U postgres -p 5443 fantasi < c:/fantasy_server.backup Restore SQL Format … 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
PHP Report : iReport (JasperReport) with PHP JavaBridge
NOW! Professional Reporting can embeding in PHP source, that report will be helping Reporting data from Mysql and handle paper size dan page number. iReport will helping design and setting print data. A. AppServer AppServ 2.5.10 Apache 2.2.8 PHP 5.2.6 MySQL 5.0.51b phpMyAdmin-2.10.3 Download Sourceforge.net : http://prdownloads.sourceforge.net/appserv/appserv-win32-2.5.10.exe?download MD5SUM : 279c0c39866fbecb8a3904969fd5d0f4 B. iReport Download from http://sourceforge.net/projects/ireport/files/iReport%20(classic)/ (test using iReport 0.5.0 … Continue reading
Excel : Macro VBA Tutorial
Run a Macro Every Time a Cell Value Changes in Excel Several readers have asked questions which require Excel to run a section of macro every time a value changes in the Excel spreadsheet. First, the improved method available only in XL97: Excel 97 has some new event handlers that allow a macro to be … 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
Web Statistic
berbagisolusi 