Microsoft SQL Server Programming Articles
T-SQL, Reporting Services, Analysis Services (OLAP), SQL Server 2005 (Yukon) and SQL Server 2008 (Katmai) Articles and Tutorials
|
SQL Server 2005 Service Pack 2 is now available
Microsoft has announced SQL Server 2005 Service Pack 2 (SP2) on 16th of February 2007
For more information on SP2 read article at SQL Server 2005 SP2 titled article.
|
SQL Server 2005 Service Pack 1 is now available
Microsoft has announced SQL Server 2005 Service Pack 1 (SP1) on 18th of April
For more information on SP1 read article at SQL Server 2005 SP1 titled article.
|
| T-SQL |
Resize or Modify a MS SQL Server Table Column with Default Constraint using T-SQL Commands
If you have to resize a SQL database table column on which a default constraint is declared, how can you alter table column data type and data size or data length?
|
| T-SQL |
At least one of the arguments to COALESCE must be a typed NULL
COALESCE T-SQL function returns the first non-null expression among its variable number of arguments.
And what happens if all arguments to the Coalesce are null values.
|
| T-SQL |
Create a Numbers Table in MS SQL Server 2005 or SQL2008 Databases
With the implementation of recursive sql using CTE (Common Table Expression) in SQL server 2005, SQL developers can now create numbers table easily in MS SQL Server 2005 as well as SQL Server 2008 databases.
|
| SQL Server 2008 |
Reason 1815 : File activation failure during SQL Server 2008 Create Database For Attach command without .ldf log file
"FOR ATTACH" or "FOR ATTACH_REBUILD_LOG" the following Create Database command used in order to attach an .mdf file may fail with the following error : File activation failure during SQL Server 2008 Create Database For Attach command without .ldf log file.
|
| SSMS |
SQL Server Management Studio - Enhancements in Object Explorer Details Window with MS SQL 2008
Microsoft is improving SQL Server with every new version and with every service pack.
Now with the MS SQL Server 2008 there exists many enhancements for sql server administrators as well as sql developers.
|
| T-SQL |
T-SQL Coding, Naming and Formatting Standards for MS SQL Server Administrators and Developers
T-SQL Coding, Naming and Formatting Standards for MS SQL Server Administrators and Developers
|
| SSIS |
Connect to SSIS Service on machine "{ServerName}" failed: The RPC server is unavailable. Windows Firewall configuration.
Connect to SSIS Service on machine "{ServerName}" failed: The RPC server is unavailable.
How to Configure Windows Firewall for SSIS (SQL Server Integration Services)
|
| SQL Server 2008 |
MS SQL Server 2008 Attach Database .MDF File
This step-by-step tutorial summarizes the steps a SQL Server administrator
or a SQL developer should do in order to attach database file to a MS SQL Server database instance.
|
| SQL Server 2008 |
Sparse Columns in MS SQL Server 2008 databases
Sparse columns are introduced to SQL developers as an enhancement in data storage and optimize the size of total amount of data size required for storing null values in Microsoft SQL Server 2008 databases.
|
| SQL Server 2008 |
Table Valued Parameters New In SQL Server 2008 T-SQL Improvements
One of the T-SQL enhancements or improvements introduced with Microsoft SQL Server 2008 is Table Valued Parameters (TVPs).
Table Values Parameters allow MS SQL Server Developers to pass a set of rows as a parameter to a stored procedure. This is great since if you are following community forums about Microsoft SQL Server and T-SQL, one of the most asked question is about how to pass a set of rows as parameter to a stored procedure.
|
| T-SQL |
How to delete a file from Microsoft SQL Server using T-SQL scripts, xp_cmdShell and OLE Automation Procedures
It is sometimes required to reach the outside of SQL Server, I mean the folder structures of the operating systems and create a file, write a file, delete a file or read from file. Since the MS SQL Server is not build for operating system tasks, it is not easy to manage such operatings on the file system by just using t-sql statements. Fortunately we have some tools for this tasks like Ole Automation Procedures and like xp_cmdshell extended stored procedure.
|
| SQL Server 2008 |
How to enable 'Web Assistant Procedures' using sp_configure in order to access to procedure 'sys.xp_makewebtask'
Msg 15281, Level 16, State 1, Procedure xp_makewebtask, Line 1 SQL Server blocked access to procedure 'sys.xp_makewebtask' of component 'Web Assistant Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Web Assistant Procedures' by using sp_configure. For more information about enabling 'Web Assistant Procedures', see "Surface Area Configuration" in SQL Server Books Online
|
| SQL Server 2008 |
A connection was successfully established with the server, but then an error occurred during the pre-login handshake.
Cannot connect to KatmaiCTP6.
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
|
| SQL Server 2008 |
Microsoft SQL Server 2008 February CTP (CTP6) - SSMS fails to complete sql query execution or query parsing
Microsoft SQL Server 2008 February CTP (CTP6) SQL Server Management Studio (SSMS) fails to complete sql queries execution or even query parsing and execution hangs if Microsoft SQL Server Compact 3.5 SP1 Beta Management Tools ENU is installed on that server.
|
| SQL Server |
Microsoft SQL Server 2005 Database Encryption Step-by-Step
This is a how-to guide which will aims to help ms sql server developers and ms sql server administrators to implement Microsoft SQL Server 2005 Encryption metodologies.
This tutorial is a step-by-step guide for encryption and decryption in MS SQL Server 2005 and later (MS SQL2008 aka Katmai)
|
| SQL Server |
SQL Server 2005 Analysis Services - The trust relationship between the primary domain and the trusted domain failed.
While processing a cube using the BIDS (Business Intelligence Development Studio), I got the following error message:
"The following system error occured: The trust relationship between the primary domain and the trusted domain failed."
|
| SQL Server |
SQL Server 2005 - Invalid Query: CUBE and ROLLUP cannot compute distinct aggregates.
In Microsoft SQL Server 2005, when GROUP BY is executed with ROLLUP or CUBE options you can not compute or calculate distinct aggregates like COUNT(DISTINCT column_name), etc.
Unlike MS SQL Server 2005, SQL Server 2008 (Katmai) can manage computing distinct aggregates successfully.
|
| SQL Server |
Running Active Directory Services Queries Using MS SQL Server OPENQUERY Command
Using Microsoft SQL Server, you can connect to Microsoft Active Directory Services applications in your network and query users or objects in the Active Directory Services
|
| T-SQL |
Ordering Students According to Exam Scores Grouped by Their Classes Using DENSE_RANK() T-SQL Functions
ROW_NUMBER() is a powerful function and if it is used with PARTITION BY it can solve numbering items within groups divided by column values for example.
Here is a sample using multiple DENSE_RANK() t-sql window functions within the same sql select query.
|
|
|
| SQL Server |
One way to read Key-Value pairs from Visual Studio 2005 XML Resource Files into Table Columns
If you work on the resource files alot in order to update them you might experienced that this task is somehow difficult and a bit boring. Microsoft SQL Server 2005 and its XML features and XML processing may help us in this situation.
|
| SQL Server |
Use FullTextServiceProperty to determine if full-text service is installed.
Recently I need to determine whether full-text search functionality is installed and enabled
on a Microsoft SQL Server 2005 instance. I later discovered that the T-SQL function FullTextServiceProperty can be used to determined if Full-Text search is installed or not on a SQL Server instance.
|
| Reporting Services |
How to Enable Remote Errors for SQL Server Reporting Services
Enabling remote errors for SQL Server Reporting Services means setting EnableRemoteErrors Reporting Services system property to True. You can update the ConfigurationInfo table which is in the ReportServer database for configuration record named "EnableRemoteErrors". Or you can enable remote errors by using and running a script which updates the running configuration values of the Reporting Services.
|
| Reporting Services |
Could not load file or assembly 'Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies.
Microsoft SQL Server 2000/2005 Reporting Services is a great tool that can be used and customized in web applications. I have previously used ReportViewer control for displaying reports. Recently I have used the ASP.NET web control ReportViewer in an other web application to display reports in a customized report viewer aspx web page. When I deployed the web application on an other IIS web server for testing purposes, I got an error message which is indicating that the Microsoft.ReportViewer.WebForms assembly could not be loaded.
|
| SQL Server |
How to enable the use of 'Ad Hoc Distributed Queries' by using sp_configure
If you are planning to use OpenRowset queries in order to connet to remote database servers or if you have already implemented OpenRowset queries as a solution to remote connections as an alternative tp linked servers in Microsoft SQL Server 2005, you should first configure the database instance to enable Ad Hoc Distributed Queries in the installed SQL Server database instance where the Ad Hoc query will run.
|
| SQL Server |
Create Short-Cuts in SQL Server Management Studio Query Window
Using short-cuts in SQL Server Management Studio query windows may ease your job while editing and writing sql codes and statements.
One of the short-cuts that is defined by default and very well-known is Alt+F1 key combination. Alt-F1 is the short-cut of "sp_help" system stored procedure.
|
| SQL Server 2008 |
Setup has detected one or more instances of SQL Server 2000 on the computer where you are installing SQL Server 2008.
Setup has detected one or more instances of SQL Server 2000 on the computer where you are installing SQL Server 2008. SQL Server 2008 installation is not supported side-by-side with SQL Server 2000. You must either install SQL Server 2008 on a different computer or uninstall SQL Server 2000 before installing SQL Server 2008.
|
|
|
| SQL Server 2008 |
Change Data Capture feature in SQL Server 2008
Change data capture feature is a new enhancement in sql server management with SQL Server 2008, Katmai. Change Data Management is included in the new features of Katmai with the June CTP release of SQL Server 2008. Change data capture enables SQL Server administrators and developers to capture insert, update and delete events in a sql server table as well as the details of the event which caused data change on the relevant database table.
|
| SQL Server 2008 |
New MERGE t-sql command enhancement in SQL Server 2008
One of the t-sql improvements in SQL Server 2008 aka Katmai is the MERGE t-sql command. By using the Merge command you can compare two tables and update matched ones, or insert unmatched rows from one into other, or delete unmatched ones from the primary table. In short, it is a new t-sql statement which you can insert, update and delete in one sentence.
|
| SQL Server 2008 |
New Insert Into Values t-sql enhancement in the SQL Server 2008 aka Katmai
Multiple Inserts using the Insert Into Values syntax is a new enhancement in t-sql in SQL Server 2008. It is simple but effective to use the new Insert Into syntax if you use insert multiple records into a table in one process using t-sql statements.
|
| T-SQL |
Get column values as comma seperated list using XML PATH() instead of UDF's using SQL COALESCE
You can find different methods in this article to get a list of values as string. You can use SQL COALESCE in UDF's and XML PATH() method at best.
|
| Reporting Services |
Altering Reporting Services Report Parameters as Hidden Automatically for a Report Folder
You can automatically set the reporting services report parameters as hidden parameters especially useful during migrating SQL Server 2000 Reporting Services projects to SSRS 2005
|
| SQL Server 2005 |
How to Create a Database Snapshot
Microsoft SQL Server administrators has a new feature with the SQL Server 2005 Edition which is database snapshots. Database snapshots are new with SQL Server 2005 and only available with Microsoft SQL Server 2005 Enterprise Edition. A database snapshot can be described as a photo of a database. Snapshots are read-only so we can easily say that their main usage areas cover mostly the reporting applications.
|
| T-SQL |
SELECT TOP n or Random n Rows From a Table For Each Category or Group
With the T-SQL enhancements introduced with SQL Server 2005, it is now easier to fetch top n even random records by running select statements using CROSS APPLY or ROW_NUMBER() OVER(PARTITION BY ...) syntaxes.
|
| T-SQL |
How to delete duplicate records or rows among identical rows in a table where no primary key exists
It is a very common situation that you may find yourself against a problem dealing with deletion of dublicate records in a database table. This is a real problem if the records are identical even for values in each column. So you can not distinguish one row from the other. This problem may occur on any table if the table does not have a unique index or an identity column.
|
| T-SQL |
How to Calculate Age of a Person using SQL Codes
You can easily calculate the age of a person by using only a line of sql codes.
|
| Reporting Services |
Reporting Services Client-Side Printing and Silent Deployment of RSClientPrint.cab ActiveX file
RSClientPrint.cab is a Microsoft ActiveX control that provides client side printing for Microsoft SQL Server Reporting Services reports. If the end users are not granted the admin privileges they can not download and install the RSClientPrint.cab ActiveX print client file. So silent deployment of the RSClientPrint.cab is the duty of IT departments to in order to support their clients to work with SQL Server Reporting Services (SSRS) without a problem.
|
| SQL Server 2005 |
How to kill all processes that have open connection in a SQL Server Database
You may frequently need in especially development and test environments instead of the production environments to kill all the open connections to a specific database in order to process maintainance task over the SQL Server database.
|
| Reporting Services |
Reporting Services report deployment problem : "Check your connection information and that the report server is a compatible version"
If you get the error "The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version" while you are deploying reports from your Reporting Services (BI) project ReportingServices.Designer then check the project's TargetServerURL property.
|
| SQL Server 2005 |
How to Configure SQL Server 2005 Database Mail Feature
Here you can find the steps how to configure SQL Server 2005 Database Mail feature and how you can use it
|
|
|
| SSIS |
How to copy a table from a database to an other database using SQL Server Integration Services (SSIS)
Using SQL Server Integration Services (SSIS) "Transfer SQL Server Objects Task" Control Flow item can be used to transfer a table as well as any database object in SQL Server between SQL Server instances.
|
| Reporting Services |
How to back up the reporting services encryption key to a text file using rskeymgmt utility
Reporting Services uses encryption keys to secure credentials, connection information, and accounts that are used in server operations. Encryption keys are created during setup. If you ever need to repair a Reporting Services installation due to changes in computer name, instance name, or user account values, you can apply the key to make the report server database operational. Reporting Services provides the rskeymgmt utility that you can use to extract a copy of the encryption key from the report server database. The utility writes the key to a file that you specify, and then scrambles the key using a password that you provide. For lated use you should not forget the password and keep the destination file where the key is exported in a safe place.
|
| SQL Server |
How to check the compatibility level of a database in SQL Server 2005
The compatibility level of SQL Server database defines the behaviors of that SQL database compared with compatible with earlier versions of SQL Server and defines the compability between the two SQL Server database versions. sp_dbcmptlevel is a stored procedure that can be used to check or set the compability level of a database in Microsoft SQL Server 2005.
|
| SQL Server |
How to Change The Logical File Names of SQL Server Databases
Since you may want to move this database on a production server, or change the database name because of any reason, you may also want to change the names of the database files. You may want the database names consistent with the database names or ant other rules you obey for standardization.
|
| SQL Server 2005 |
SQL Server 2005 Full-Text Search Indexing
Microsoft SQL Server 2005 Full-Text Catalogs, Full-Text Indexes and Full-Text Searches. And sample full-text search queries with CONTAINS, FREETEXT, CONTAINSTABLE and RANK
|
| Reporting Services 2005 |
How to Install IIS 7 (Internet Information Services) for SQL Server 2005 Reporting Services
If you have installed Microsoft SQL Server 2005 on Windows Vista, you may have noticed that the SQL Server is not aware of the IIS 7 running on the Vista. Because of this, the SQL Server 2005 setup wizard does not allow the installation of SQL Reporting Services 2005. But if you read the article and configure the IIS 7 with the features shown you can successfully install the Reporting Services 2005 on Windows Vista RC 1.
|
| SQL 2005 |
Cannot Connect to MS SQL Server 2005 Instance with Windows Authentication on Windows Vista RC1
You may have experience failures in connectting to a MS SQL Server 2005 Instance with Windows Authentication on Windows Vista RC1 operating systems because of the User Account Control that limits the default user account and requires confirmation for administrative privileges from the user.
|
| Reporting Services |
SQL Server Reporting Services Error : Server is not configured for RPC
Reporting Services Error
An error has occured during report processing. (rsProcessingAborted)
Query execution failed for data set '...'. (rsErrorExecutingCommand)
Server '...' is not configured for RPC.
|
| Reporting Services |
The value of parameter 'Parameters' is not valid. Check the documentation for information about valid values.
I am using Microsoft SQL Server 2000 Reporting Services not only for reporting purposes by creating reports but also for creating subscriptions of specific reports and sending these reports via email to subscribers when the scheduled time has arrived. |
| T-SQL |
How to use SQL variables in an Update Statements Where Variable is also Updated for each row during the Update Process
You can update a sql variable that you have declared earlier whose value changes with each row during the process of a t-sql update code statement. And use that sql variable in the same sql update code. |
| SQL 2005 |
The query has exceeded the maximum number of result sets
The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid. |
| SQL 2005 |
SQL Server Migration Assistant for Access
SQL Server Migration Assistant for Access (SSMA) for Access converts Access database objects to SQL Server database objects in its workspace then loads those converted objects into SQL Server 2005 and then SSMA can also migrate data from MS Access to MS SQL Server 2005. Supported versions of MS Access as source database by the SSMA for Access are version of Microsoft Access 97 and later versions including Access 2003 databases. And as target database SSMA supports Microsoft SQL Server 2005 aka Yukon. |
| T-SQL |
How to create SQL Server cursor and sample sql cursor code
Here is a sample sql cursor code created for looping through a list of records as a result of a select query, which enables the sql developer to execute a stored procedure for each row in the cursor which use the values fetched by the cursor as the input arguments. The sample cursor is developed on a SQL Server and is a sample for sql server cursor. The sql codes may use t-sql codes so the sample may have differences than a typical pl sql cursor or an oracle cursor. |
| T-SQL |
How to read a text file using xp_cmdshell
You sometimes need to read the contents of a text file using SQL commands. You may want to read the results into a SQL result set or you may want to set the contents of the text file to a variable. xp_cmdshell is an extended stored procedure that enables SQL developers and SQL administrators to run dos commands on the underlying operating system. |
| T-SQL |
sys.fn_MSdayasnumber
The new SQL Server 2005 function sys.fn_MSdayasnumber returns days as integer from beginning the year 2000 to the date which you pass to the function as an argument. |
| T-SQL |
SysProcesses And Blocking Processes
You can use sysprocesses to identify which processes are blocking other processes running on the SQL Server. |
| Tools |
SQL Prompt Version 2.0
SQL Prompt is a free tool from Red-Gate software which supplies intellisense support for Query Analyzer, Enterprise Manager, SQL Server Management Studio, Visual Studio .NET 2003, Visual Studio .NET 2005, UltraEdit and EditPlus 2. |
| T-SQL |
fn_helpcollations() for the list of collations
You can use the fn_helpcollations() function to return the list of all collations that are supported by Microsoft SQL Server 2005 and use ::fn_helpcollations() for Microsoft SQL Server 2000 |
| T-SQL |
How to parse a domain name from a URL using SQL statements
You may need to parse a domain name from a string expression which identifies a URL of a web address. You can use the SQL user defined function Parse_For_Domain_Name for fetching the domain name from a string which is the url address. |
| T-SQL |
How to find the first day of a month and the last day of a month?
You can use these two user defined functions to use in SQL scripts to find the first day of a month and the last day of a month. |
| T-SQL |
RowNumber or AutoNumber Columns in SQL Select Statements
With the enhancements in T-SQL in the new version of Microsoft SQL Server, we have now the RowNumber functionality in sql Select statements. Or we can say that we have the ability of using AutoNumber columns in sql select statements. The new function which enables us to use autonumbering or row numbering in sql is ROW_NUMBER(). |
| SQL 2005 |
MS SQL Server 2005 - Msg 6505, Could not find Type in assembly
If you can register a CLR (Common Language Runtime) assembly successfully but can not create an external stored procedure referencing the registered CLR assembly and getting the following SQL Server 2005 error message:
Msg 6505, Level 16, State 1, Procedure myProcedure, Line 2
Could not find Type 'myCLRMethods' in assembly 'myCLRAssembly'.
You can solve your problem by using the class name of your assembly in the format [NamespaceName.ClassName]
|
| SQL 2005 |
An error occurred while attempting to register the endpoint 'sql_endpoint'
An error occurred while attempting to register the endpoint 'sql_endpoint'. One or more of the ports specified in the CREATE ENDPOINT statement may be bound to another process. Attempt the statement again with a different port or use netstat to find the application currently using the port and resolve the conflict. |
| T-SQL |
How to enable xp_cmdshell extended stored procedure for Microsoft SQL Server 2005
xp_cmdshell extended stored procedure executes a given operating system command shell. The xp_cmdshell is capable of extending a database users ability over operating system it is a very powerful tool. To make the SQL Server more secure, xp_cmdshell is disabled by default on SQL Server 2005. |
| T-SQL |
What is ParseName And How to split IP Addresses Using ParseName T-SQL Function
SQL Server uses four part object naming syntax for representing its database objects. Each part is delimeted with a period (.) IP Addresses also have a delimeted with period structure very similar to database objects in SQL Server. |
| Reporting Services |
How to Build Your First Report In MS SQL Server 2005 Reporting Services
You can build a sample report using MS SQL Server 2005 Reporting Services and deploy it on a report server by following the steps in this article. |
| SQL 2005 |
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
While registering a Microsoft SQL Server 2005 instance running on a remote computer, you may get the following an error message.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. |
|
|
| DTS Designer Components |
How to design and run SQL 2000 Server Data Transformation Services (DTS) packages from SQL Server 2005
You can design, edit and run DTS (Data Transformation Services) packages that you have created on a SQL Server 2000 from a MS SQL 2005 Server by the help of Microsoft SQL Server 2000 DTS Designer Components. |
| Makalenin Devamı... |
| Reporting Services |
A sever error occured on the current command. The results, if any, should be discarded.
During the execution of your MS Reporting Services 2000 reports, did you ever had the error message
An error occured during report processing. Cannot read the next data row for the data set Dataset1. A sever error occured on the current command. The results, if any, should be discarded. |
| Reporting Services |
How to Add Auto Number Records, Rows or Lines In SQL Server Reporting Services by Using RowNumber Function
As a developer while you are preparing a code for a report, adding auto numbers to your reports records or rows, lines etc. is a common process. There is mainly two approaches for maintaining your reports row numbers. One method for supplying a row number to a record is adding row numbers during the selection of these records by adding them within the sql statements. The second method which I will try to explain here in this article is customizing the report tool, in our case MS SQL Server 2000 Reporting Services, in order to add a column which is automatically containing row numbers for each record.
In Reporting Services during coding expressions for textboxes which contain information, you can use aggregate functions such as RowNumber, RunningValue, Avg, Sum, etc. |
| Analysis Services |
How to Create an OLAP Cube from an SQL View?
There are a few ways of creating an OLAP Cube for MS Analysis Services. You can create an OLAP Cube by following the steps defined in the wizard. You should first select your fact table and then add dimension tables in this wizards.
The wizard may be a little bit confusing for some cases. So why bother it? Just define a fact table then select your dimensions and measures within this fact table. |
| Reporting Services |
How can I learn the version of a running Microsoft SQL Server Reporting Services?
In order to learn or check the version of a Microsoft SQL Server Reporting Services application, you can browse the http://servername/reportserver on the report server.
If the Reporting Services application is running on your localhost then browse the http://localhost/reportserver address using an internet browser. |
| T-SQL |
xp_regread
xp_regread extended stored procedure (saklı yordamını) istenilen bir registry key'in değerini okumak için kullanılabilir.
xp_regread is used to read the value of a registry key. |
| T-SQL |
sp_MSdrop_object
sp_MSdrop_object tipi ne olursa olsun objeyi drop etmek için kullanılabilir.
sp_MSdrop_object can be used to drop an object whatever its type is. |
| T-SQL |
sp_MSget_qualified_name
sp_MSget_qualified_name stored procedure (saklı yordamını) istenilen bir objenin qualified ismini elde etmek için kullanılabilir.
You can use sp_MSget_qualified_name stored procedure to get the qualified name of a database object. |
| T-SQL |
MS SQL Server 2000 Undocumented Stored Procedures
Bu makalede elverdiğince bilinen dökümante edilmemiş saklı yordamların (undocumented stored procedures) bir listesine yer vermeye ve bu stored procedure'ların kullanımları ile ilgili ipuçları ve örnekler sağlamaya çalışacağım. |
| MS SQL Server |
Address Windowing Extensions (AWE)
Microsoft SQL Server 2000 Enterprise Edition Microsoft Windows 2000 Address Windowing Extensions (AWE) kullanarak yüksek miktarda fiziksel memory kullanabilir. Bu yöntemle SQL Server 2000 Enterprise Edition Windows 2000 Advanced Server üzerinde 8 GB ve Windows 2000 Data Center üzerinde 64 GB memory kullanabilir. |
| Reporting Services |
Reporting Sevices Raporlarında RunningValue Fonksiyonunun kullanımı
Reporting Services ile hazırladığınız raporlarda bazı kolonların değerlerinin daha üstte yeralan satırdaki değerler ile toplanarak görüntülenmesini istiyor olabilirsiniz. Buna örnek olarak bir kolonda satış miktarları gösterirken hemen yanındaki kolonda bu miktarları toplayarak göstermek isteyebilirsiniz. SQL Server Reporting Services bize bu özelliği RunningValue fonksiyonu ile sağlıyor. |
| T-SQL |
DBCC CHECKIDENT (TableName, RESEED, IdentityValue) ile bir tablodaki Identity değerini değiştirmek
Bir SQL Server 2000 veritabanında DBCC CHECKIDENT (TableName, RESEED, IdentityValue) komutu ile tablo ismini parametre olarak geçerek o tablodaki Identity değerini değiştirebilirsiniz.
|
| Analysis Services |
Microsoft DSO (Decision Support Objects) Kullanarak Microsoft Analysis Services (OLAP) Server'larına Bağlanmak
Microsoft Decision Support Objects (DSO) kütüphanesini kullanarak Microsoft SQL Server 2000 Analysis Services Manager kullanıcı arayüzü ile gerçekleştirebileceğiniz pek çok işlemi programatik olarak da gerçekleştirebilirsiniz. Bu yazıda Decision Support Objects (DSO) kullanarak bir Analysis Services sunucusuna (server) bağlanmaya ve bu sunucuda bulunan veritabanı (database) ve küpleri bir Windows Forms uygulaması içinde listelemeye çalışacağız. |
| Reporting Services |
VS.NET 2003 Kullanarak İlk MS SQL Server 2000 Reporting Services Raporunuzu Oluştumak
MS SQL Server 2000 Reporting Services hızlı bir şekilde raporlama ihtiyaçlarınızı çözmek için güçlü bir araç. Kullanım kolaylığı yanında öğrenme kolaylığı da hızlı bir şekilde üretken bir çalışma temposu yakalamanıza olanak sağlıyor. Bu makalede basit bir rapor hazırlayarak Reporting Services ile rapor geliştirme konusuna bir giriş yapmaya çalıştım. |
| Reporting Services |
Shared Data Source Oluşturmak
Shared Data Sources aynı veritabanını kullanan rapor uygulamalarında olduğu gibi aynı veri kaynağını kulllanan raporların oluşturulmasını kolaylaştırırlar. Aynı verirabanını kullanan raporları barındıran bir Reporting Services projesinde (Business Intelligence projeleri) bir Shared Data Source oluşturduğunuzda bu veri kaynağını diğer tüm raporlarınız için de bir kaynak olarak gösterebilirsiniz. |
| T-SQL |
Bir Tabloda Yer Alan Tüm Kolonlardaki Değerleri REPLACE Etmek
Bir tablodaki bir kolonda yer alan değerin içindeki bir karakteri başka bir karakter ile değiştirmek için REPLACE T-SQL komutunu kullanabiliriz. Bu tablodaki tüm kolonlarda bu işlemi gerçekleştirmek için daha genel bir çözüme giderek bir stored procedure hazırlayabiliriz. |
| T-SQL |
COALESCE Kullanılarak UserDefinedFunction Yardımı İle Bir Kitabın Yazarlarının Okunması
Kitaplar hakkında bilgi veren bir uygulamanız var ve bir kitabın yazarlarını da bu uygulamada görüntülemek istiyorsanız bir kitabın birden fazla yazarının olması durumunda bu bilgiyi kitap ile ilgili tabloda bir kolon içinde tutmanız olanaksız hale gelecektir. Bu durum için kullanılabilecek çözümlerden birisi cross tablolar kullanarak yazar id bilgisi ile kitap id bilgisini bu tabloda birleştirmektir. Böylece bu tabloda birden fazla yazar id bilgisini bir kitap için farklı kayıtlar olarak girebilirsiniz.
|
| Yukon |
Yukon ile gelen T-SQL Yenilikleri : DDL Triggers (Data Definition Language Triggers)
MS SQL Server 2005'e dek trigger'lar DML (Data Manipulation Language) komutları üzerinde tanımlanabiliyorlardı. SQL Server 2005 Beta 2 (Yukon) ile DDL (Data Definition Language) komutları üzerinde de trigger'lar tanımlama olanağına sahip olduk. |
| Yukon |
Yukon ile Gelen Yeni Veri Tipleri : Large Value Data Types
MS SQL Server 2005 BETA 2 (Yukon) ile gelmiş olan yeniliklerden birisi de Large Value Data Types yani Büyük Değerli Veri Tipleri olarak adlandırabileceğimiz varchar(max), nvarchar(max), varbinary(max) veri tipleridir. |
| Reporting Services |
Microsoft SQL Server 2000 Reporting Services Kurulumu
Microsoft SQL Server 2000 Reporting Services raporlama ihtiyaçları için ciddi bir çözüm arayanların üzerine ciddiyetle eğilmeleri gereken bir raporlama aracı ve sunucusu. Reporting Services, kurulumu, rapor tasarımı ve geliştirmesi kolay bir ürün. Bu yazıda kurulum işlemlerini kısaca tanıtmaya çalışacağım.
|
| T-SQL |
NEWID() Kullanarak Kayıtları Random olarak sıralama
NEWID() fonksiyonu kullanarak çok basit bir şekilde kayıtlar arasından rastgele seçim yapabilirsiniz. İçinden seçim yapmak istediğiniz kayıt kümesini "Select" ile elde ettiğiniz sorgunun "Order by" kısmına "NEWID()" eklerseniz elde etmiş olduğunuz kayıt setinin rasgele üretilmiş olan NEWID() 'ye göre sıralandığını görebilirsiniz. |
| Yukon |
Yukon ile gelen T-SQL Yenilikleri : T-SQL Window Fonksiyonları (Window Functions)
SQL Server 2005 (Yukon) ile gelen T-SQL yeniliklerinden bir kısmını da Window fonksiyonları (Window Table Functions) oluşturmaktadır.
|
| Yukon |
Yukon ile gelen T-SQL Yenilikleri : CTE (Common Table Expressions)
Yukon'la beraber T-SQL 'e gelen yeniliklerden birisi de CTE (Common Table Expressions). CTE, SQL 2005 BOL'da geçici isimlendirilmiş sonuç kümeleri (temporary named result set) olarak tanımlanmış. |
| MS SQL Server |
Linked Servers
Linked Server'lar kullanarak birbirlerinden farklı sunucularda bulunan iki veritabanı arasında işlemler gerçekleştirebilirsiniz.
Böyle bir bağlantıya neden ihtiyaç duyabiliriz?
Örneğin bir sunucudaki tablonun diğerine de içindeki verilerle aktarılması gerekti.Veya en azından birbirinin aynı olmasını beklediğiniz iki tablo arasında farklı olan kayıt var mı yok mu tespit etmek istediniz.
Bu durumda herhangi bir sunucuyu diğeri üzerinde Linked Server olarak tanımlayabilirsiniz. |
| MS SQL Server |
MS SQL Server Maintenance Plan Oluşturmak
MS SQL Server üzerinde veritabanlarınız var ve bunları düzenli olarak yedeklemek istiyorsunuz. Ve diyelim ki geriye dönük olarak iki aylık bir süreç içinde aldığınız tüm yedekleri tutmak istiyorsunuz. İki aydan daha eski olanları da otomatik olarak sisteminizden silmek diskinizde bir yer problemi oluşmasını engelleyecek.
Bunun için en uygun yöntem MS SQL Server üzerinde Enterprise Manager ile bir Maintenance Plan oluşturmanız olacaktır. |
| MS SQL Server |
MS SQL Server Version (Sürüm) Bilgisi
Sisteminizde kurulu olan Sql Server 'larınızın sürümleri zaman geçtikçe üzerlerine kurulan SP (Service Pack) 'ler ve Hotfix 'ler nedeniyle değişecektir. Sisteminizde kurulu veritabanı sunucularının sayısının çokluğu, Service Pack ve Hotfix'lerin sıklığı, bazı yamaların genelden ziyade özel problemlere yönelik dağıtılması yanında bu yamaların tek bir elden ve sistematik olarak tüm sunuculara kurulamaması gibi nedenlerden dolayı versiyon (sürüm) takibi de başlı başına bir problem olabilir. Geliştirme, test ve canlı sistemleriniz siz her ne kadar birbirlerine özdeş olmalarına çalışsanız da zaman içinde birbirlerinden farklılaşabilirler. Özellikle böyle sistemlerde yazılımların sürümlerini takip edebilmek önemlidir. |
| MDAC |
MDAC (Microsoft Data Access Components) Sürümünün belirlenmesi
Bilgisayarınızda kurulu olan MDAC (Microsoft Data Access Components) Sürümünü tespit etmek için en güvenilir yöntem Registry kayıtlarına bakmaktır. |
| SQL Server |
DBCC SHRINKDATABASE ve DBCC SHRINKFILE ile veritabanı dosyalarının boyutlarının küçültülmesi
DBCC SHRINKDATABASE ve DBCC SHRINKFILE komutları ile veritabanınızı oluşturan veri veya log dosyalarınızın boyutunu küçültebilirsiniz. |
| Reporting Services |
E-mail Subscription'ları için Konfigürasyon Ayarları
Reporting Services ile geliştirdiğiniz raporlarınızda e-mail subscription'ları oluşturabilir ve bunların belli bir takvime göre çalıştırılmasını sağlayabilirsiniz. Dilerseniz bu oluşturduğunuz subscription'lar ile raporlarınızın otomatik olarak ilgili yerlere e-mail yolu ile iletilmesini de sağlayabilirsiniz. E-mail ile ilgili olarak yapacaklarınızdan önce Report Server üzerinde bazı konfigürasyon ayarlarını gerçekleştirmek zorundasınız. |
| T-SQL |
PATINDEX
Patindex fonksiyonu ile tablonuzun bir kolonunda aradığınız belirli bir "pattern"ın konumunu belirleyebilirsiniz. Böylece "pattern"ı içinde aradığınız kolon üzerinde bulduğunuz index'e bağlı olarak string işlemler yapabilirsiniz. |
| Reporting Services |
Reporting Services ReportViewer Component
ReportViewer control can be further developed to include some more properties such as ClearSession to have "rs:ClearSession=true" functionality.
The default place where you can find the project folder of the ReportViewer.dll is "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer"
I used the vb version of the ReportViewer so I updated the "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\Samples\Applications\ReportViewer\vb\ReportViewer.sln" solution. I only added "ClearSession" public property to the ReportViewer class in ReportViewer.vb file.
You can download the file at http://www.kodyaz.com/downloads/reportviewer.zip and check for the updates for ClearSession property. You can add more properties to have further functionality for your ReportViewer component used applications. The only thing to be done will be compiling the updated ReportViewer solution and replacing the reportviewer.dll with the newer one. The new version for the ReportViewer component will supply an entry point in the properties window for setting the ClearSession property in order to have a report Url having rs:clearsession=true query string parameter.
A sample project using the updated ReportViewer is also available for download at http://www.kodyaz.com/downloads/ky.zip . You should update ServerUrl and ReportPath properties. |
| Yukon |
Yukon ile gelen T-SQL Yenilikleri : TOP
Yukon'a dek yazdığımız T-SQL statement'larında kullandığımız TOP anahtarından sonra sabit bir kayıt sayısı kullanmak zorunda oluşumuz kod geliştiricileri kısıtlamaktaydı
Yukon ile gelen yeniliklerden birisi de artık TOP cümleciği sonrasında belirtilen kayıt sayısının dinamik olarak bir parametreye bağlı kullanılabilmesi. |
| Reporting Services |
İlk Bakışta "MS SQL Server 2000 Reporting Services"
"Reporting Services" basit anlamda bir raporlama aracı. Fakat pek çok özelliği ile bu tanımlamanın ötesinde bir tanımlamayı hakediyor. |