Home
All posts
Rabu, 04 Mei 2016
MSFlexgrid Data Retrieval With ADO
Open the database and execute the query. Loop through the recordset's
Fields array to get the field names and use them as column headers.
For each record in the Recordset, loop through the fields saving their values in the FlexGrid.
For each record in the Recordset, loop through the fields saving their values in the FlexGrid.
Selasa, 03 Mei 2016
how to reset the record no in the crystal report when the group header change
I have a complex report here and i need to reset my record number after the group header change
like..
Formula Code :
WhilePrintingRecords;
NumberVar counter;
if PreviousIsNull ({ado.iBarcode}) then
counter := 1
else if previous({ado.iBarcode}) <> {ado.iBarcode} then
counter := 1
else
counter := counter + 1
like..
WhilePrintingRecords;
NumberVar counter;
if PreviousIsNull ({ado.iBarcode}) then
counter := 1
else if previous({ado.iBarcode}) <> {ado.iBarcode} then
counter := 1
else
counter := counter + 1
Select from Stored Procedure in SQL Function using OpenQuery
SQL functions are limited executable units when compared with SQL stored procedures. Select from stored procedure in SQL Server T-SQL programming is possible using OpenQuery. Using similar SQL programming tricks, SQL developers can execute SQL stored procedure in user defined sql function. Directly from SQL OpenQuery query, data rows can be filtered with a SELECT statement as illustrated in this SQL tutorial.
How to Use a Linked Server in a SQL Server Database Project
Problem
I use the SQL Server Database Project for developing, maintaining, and publishing/deploying my databases. I added some views to my database project that query data via a linked server and now I am not able to successfully build my project. The first error I get is SQL71561 - the view has an unresolved reference to the object referenced by the linked server. I did some research and found out that you can use a linked server if you add a DACPAC file to the project. However, when I tried to create the DACPAC I get error SQL71564 - the element cannot be deployed as the script body is encrypted. Can you help me to get this working?Solution
In order to query using a linked server in a database project, you have to add a database reference to your project which requires that you create a DACPAC file. Unfortunately as you have noted, you cannot create a DACPAC file on a database that has objects that have been created "WITH ENCRYPTION". I have run in to this problem myself and I will offer my solution in this tip.I will cover the following tasks in this tip:
- Create a view that can access a linked server
- Create a DACPAC file for a database referenced by a linked server
- Add a database reference to the database project for the linked server
- Publish the database project
- Demo the sample view that uses a linked server
- Add a view that uses the linked server without adding a database reference to the project
I am using the SQL Server Data Tools (SSDT) for Visual Studio 2012 for this tip. You can download SSDT here.
Create a SQL Server View
Add the following view to the database project:CREATE VIEW dbo.Product AS SELECT [ProductID], [Name] FROM [BARLEYWIN8\SQL2012].[AdventureWorks2012].[Production].[Product]
SQL71561: View: [dbo].[Customer] has an unresolved reference to object [BARLEYWIN8\SQL2012].[AdventureWorks2012].[Production].[Product].
[BARLEYWIN8\SQL2012] is a linked server defined on my machine. However, the database project cannot resolve this reference yet.
Create a DACPAC File
A DACPAC file (also known as a Data-tier application) is defined here as "a logical database management entity that defines all of the SQL Server objects - like tables, views, and instance objects, including logins – associated with a user’s database. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC".Follow these steps to create a DACPAC file for a database (e.g. AdventureWorks2012):
- Open SQL Server Management Studio (SSMS)
- Connect to the SQL Server instance for your database
- Right click the database in the Object Explorer
- Select Tasks, Extract Data-tier Application from the menu
- Fill in the dialog as shown below
Add Database Reference
In general when your database project references objects in another database, you must add a database reference to your project.Follow these steps to add a database reference for a linked server:
- Right click on References in the Solution Explorer
- Select Add Database Reference from the menu
- Fill in the dialog as shown below
- Click the Data-tier Application (.dacpac) radio button
- Fill in the full path to the DACPAC file (we created this in the previous step)
- Select Different database, different server for Database location; this is typically the case for a linked server
- Specify the name of the database you will access via the linked server
- Specify the server name; this is the server name of the linked server; include \instance if a named instance
- Database variable is the name of the SQLCMD variable that will be added to the project; use this to set different values for the database name based on the publishing profile
- Server variable is the name of the SQLCMD variable added to the project; use this to set different values for the server name based on the publishing profile
CREATE VIEW [dbo].[Product] AS SELECT [ProductID], [Name] FROM [$(AW_LINKED_SERVER)].[$(AW_DATABASE)].[Production].[Product]
Publish the Database Project
The database project has a Publish function that will create or update a target database based on the project contents. Right click the project in the Solution Explorer and select Publish from the menu. Fill in the dialog as shown below:- The target database connection is a SQL Server 2014 instance on my laptop
- The AW_LINKED_SERVER SQLCMD variable value of BARLEYWIN8\SQL2012 is a linked server on my laptop
- Click the Save Profile As button to save the publishing profile in the project; you can double click it to publish again with the saved values
- Click Publish to create or update the database on the target
Use a Linked Server Without a Database Reference
Now that we have seen how to use a linked server with a database reference, let's see how to use a linked server without a database reference. This is a work around for the situation where you are unable to create a DACPAC file; e.g. some database objects were created "WITH ENCRYPTION".The solution that I am using in this case is as follows:
- Create a dummy table-valued function (TVF) that defines the result set from the query that uses a linked server; e.g. create the TVF, but do not return any rows
- ALTER the TVF in the post-deployment script; put in the select statement that references the linked server
- Create a new view to select from the TVF
CREATE FUNCTION [dbo].[Product_TVF] ( ) RETURNS @returntable TABLE ( ProductID int, Name nvarchar(50) ) AS BEGIN RETURN END
CREATE VIEW [dbo].[Product2] AS SELECT * FROM dbo.Product_TVF()
ALTER FUNCTION [dbo].[Product_TVF] ( ) RETURNS @returntable TABLE ( ProductID int, Name nvarchar(50) ) AS BEGIN INSERT @returntable SELECT ProductID, Name FROM [BARLEYWIN8\SQL2012].[AdventureWorks2012].[Production].[Product] RETURN END
Langganan:
Postingan (Atom)