computer courses

Best Crystal Reports-Microsoft SQL Server

Crystal Reports-Microsoft SQL Server

Microsoft SQL Server is the leader for center as well as low-cost dimension SQL options. Several software application suppliers use Crystal Reports as the major coverage device: Microsoft, Best Software, Oracle to call a couple of.

Occasionally programmers attempt to make use of Crystal straight to draw information from several data source systems – we comprehend the demand to offer you added pointers to raise Crystal record versatility.

 

Usual instance when Crystal record ought to draw information from several data source systems. Thinking that you are producing Crystal Reports on Windows system – we advise you to make use of Microsoft SQL Server as a data source gain access to device.

1. Usage Crystal when you have your question tuned– no matter just how excellent Crystal Reports is – it is reporting device – not inquiring atmosphere. Tune your inquiry initially and also make certain that you are drawing the appropriate information.

2. Usage SQL Server Linked Servers. Usage expert device, such as Microsoft SQL Server, where you can produce connected web servers to Oracle, Ctree, Btrieve, MS Access, Unidata, DB2 as well as various other systems.

3. Usage Distributed Queries– when it comes to SQL Server – study the opportunities with OPENQUERY, OPENROWSET conditions to link to non-SQL Server connected web servers.

4. Develop Stored Procedures – when you require specifications to be moved to the SQL declaration – you require kept treatment. If temperature tables are called for to do intermediate work in your SQL inquiry, you require saved proc.

5. Record Flexibility– when you have actually record based upon the SQL sight or kept proc – if you require to change the information question – you do not need to alter the record – you transform your SQL sight/ saved treatment.

6. Produce SQL Query to penetrate the information– once more – we constantly advise adjusting your inquiry and also see that you are obtaining ample outcomes– regardless– Crystal Report is simply a wonderful device to reveal the outcomes of your inquiry.

Pleased personalizing, developing as well as quizing! if you desire us to do the task – offer us a telephone call 1-866-528-0577! [email protected]

Microsoft SQL Server is the leader for center as well as affordable dimension SQL options. Thinking that you are producing Crystal Reports on Windows system – we advise you to utilize Microsoft SQL Server as a data source accessibility device.

Usage SQL Server Linked Servers. Usage specialist device, such as Microsoft SQL Server, where you can develop connected web servers to Oracle, Ctree, Btrieve, MS Access, Unidata, DB2 as well as various other systems.

You require saved proc if temperature tables are needed to do intermediate work in your SQL question.


Microsoft Great Plains Dynamics on Pervasive SQL.2000 data repair – overview for Database Administrator

Up to the version 7.5 Microsoft Great Plains, and if you are looking to version 7.0, 6.0, 5.5, 5.0 or 4.0 – then it was Great Plains Dynamics were available on Pervasive SQL.2000 or Btrieve.

Crystal Reports-Microsoft SQL Server
Crystal Reports-Microsoft SQL Server

I am confident that majority of database administrators know how to do data repair via Transact SQL query if you are on Microsoft SQL Server

2000/7.0, but I would like to help these ones, who have to support Microsoft Great Plains on Pervasive SQL.2000 or Ctree. Some introduction into Great Plains Software products, now Microsoft Business Solutions.

Microsoft Great Plains becomes more and more popular, partly because of Microsoft muscles behind it. Now it is targeted to the whole spectrum of horizontal and vertical market clientele.

Small companies use Small Business Manager (which is based on the same technology – Great Plains Dexterity dictionary DYNAMICS.

DIC and runtime DYNAMICS.EXE), Great Plains Standard on MSDE is for small to midsize clients, and then Great Plains serves the rest of the market up to big corporations.

If you are database administrator who is supporting the version on Pervasive SQL.2000/Ctree – read this and you will have the clues on where to look further.

1Microsoft Great Plains Tables Structure – Launch Great Plains and go to Tools->Resource Description->Tables. Find the table in the proper series.

If you are looking for the customers – it should be RM00101 – customer master file, account master file – GL00101, Sales Header Work file – SOP10200 – and you got the idea.

2. Use Microsoft Access. Lunch Microsoft Access and make a link to Microsoft Great Plains tables. This will allow you to do data modification/repair.

Some people are trying to use Pervasive control center – we do not recommend this – MS Access does job better.

3. Locked batch repair – unlocking the batch, which was interrupted in posting. The tables: SYoo500 – batch header – you need to change Batch Status or Marked for Posting field

4. Activity table – If you have user locked in Great Plains – just ask all the other users exit and delete ACTIVITY.BTR (Pervasive) or ACTIVITY.DAT (Ctree)

5. Crystal Report link to Pervasive/Ctree – you have to install Pervasive SQL ODBC driver from Great Plains Dynamics CD. This will allow you to deploy ODBC connection to Great Plains database

6. Sometimes you have to rescue to Great Plains Dexterity – when you need ongoing data import and you do not own Great Plains Integration manager or when Integration Manager doesn’t have the object to deploy.

This is proprietary Great Plains Software tool and development environment and you need some time invested, before you can do programming.

7. Complex Data Migration – use MS SQL Linked Server – you can create linked server to Pervasive/ctree and then you can create the cursor,

coming through all the tables in your migration set – we usually recommend reading tables schema in MS SQL Server (it is the same to Pervasive) and pull the data from Pervasive database using EXEC statement in Transact SQL.

Happy data repairing! if you want us to do the job – give us a call 1-866-528-0577! [email protected]


Microsoft Great Plains migration from one SQL Server to another – overview for consultant

Microsoft Great Plains has tens thousands of installation across the USA as well as in Australia, New Zealand, Spanish speaking South America, UK, Poland, Middle East and South Africa.

Crystal Reports-Microsoft SQL Server
Crystal Reports-Microsoft SQL Server

It is on the market since 1994 when Great Plains Software released its Graphical Mac and Windows based Accounting system – Great Plains Dynamics.

Now, in post-recession time we see thousands of clients world-wide are without support or as Microsoft Business Solutions names them – orphan clients.

Our goal here is to educate IT department or general IT consultants to self-support Microsoft Great Plains.

Today’s topic is how to move Microsoft Great Plains (old names – Dynamics and eEnterprise) from one Microsoft SQL Server to another.

1Microsoft Great Plains security model – currently Great Plains has user IDs stored in Great Plains tables as well as on the level of Microsoft SQL Server 2000 (or 7.0). Look at the Logins for the SQL Server, hosting Great Plains – you will figure out that all the GP users are Logins.

And they are stored in Master database. Now if you look at Company database (TWO database, for example) – you will find users on the database level

– these users have the same names as logins on the system level, plus you will find DYNGRP – this is the role on the company database level.

2. Security Migration – now you see that if you just install SQL Server on the second computer and restore DYNAMICS and company databases from their backup – SQL Server system logins and their security will be lacking.

If you feel comfortable being MS SQL DBA – you are good to use MS SQL Server Data Transformation Services to import security information from the old server.

If not – then you need to use confidential (you should have Microsoft Business Solutions annual support plan) scripts to migrate Great Plains security information or call Microsoft Great Plains consultant.

3. Recommended Steps – We recommend you to install MS SQL Server, then install exactly the same Great Plains environment with the same companies IDs and GL account segment structure/sorting (empty companies).

At this point you are ready to restore DYNAMICS and all companies from their backups – do it. And finally – you need to transfer security – if you want DTS way – go ahead – should work.

If you are not comfortable with DTS and have MBS support, login to Microsoft Business Solutions customer support section and find techknowledge #878449:

“How to transfer Great Plains and Microsoft SQL Server 7.0 or SQL Server 2000 to a new server” with capture_logins, drop_users_dynamics and other scripts and follow the instructions

Happy migrating! if you want us to do the job – give us a call 1-630-961-5918 or 1-866-528-0577! [email protected]


Microsoft Great Plains SQL Reporting and data fixing

This is beginner level SQL scripting article for DB Administrator, Programmer, IT Specialist
Our and Microsoft Business Solutions goal here is to educate database administrator,

Crystal Reports-Microsoft SQL Server
Crystal Reports-Microsoft SQL Server

programmer, software developer to enable them support Microsoft Great Plains for their companies.

In our opinion self support is the goal of Microsoft to facilitate implementation of its products: Great Plains, Navision, Solomon, Microsoft CRM.

You can do it for your company, being aware on simple data repair techniques and appealing to Microsoft Business Solutions Techknowledge database. This will allow you to avoid expensive consultants visits onsite.

You only need the help from professional when you plan on complex customization, interface or integration, then you can appeal to somebody who specializes in these tasks and can do inexpensive nation-wide remote support for you.

Let’s look at simple data repair techniques.

1. How to delete the user:
If user, shuts down computer, before signing off Great Plains – she/he will get the message that she/he is already signed.

Then you need to delete the user from the activity table. Run the query below against DYNAMICS database – it will unlock the user:
delete ACTIVITY where USERID=’MARIE’

2. How to unlock the batch:
From our experience – this is second FAQ we are hearing from Great Plains administrator. Run this script against company database to unlock hanging batch:

update SY00500 set BCHSTTUS=0 where BACHNUMB = ‘BATCH0023’
Sometimes you also have to run this script against DYNAMICS database (if first script doesn’t do the job)
delete SY00800 where BACHNUMB = ‘BATCH0023’

3. Some Reporting Queries – Before we go there,

we would like you to be able to find the table with the data you are looking for. Launch Great Plains and go to Tools->Resource Description->Tables. Find the table in the proper series. If you are looking for the customers – it should be RM00101 – customer master file.
Now lets query Customer Master table:

select CUSTNMBR, CUSTNAME from RM00101 where STATE=’CA’
This query will return Customer Number, Customer Name for all the customers in California
Next query will give you top 10 Inventory Items with the best Sales Results

select a.ITEMNMBR, a.ITEMDESC, b.SALESNUMBER from IV00101 a join (select top 10 ITEMNMBR, count(ITEMNMBR) as SALESNUMBER from SOP30300 where SOPTYPE=3 group by ITEMNMBR order by count(ITEMNMBR) desc) b on a.ITEMNMBR=b.ITEMNMBR

Happy querying and supporting! if you want us to do the help you – give us a call 1-866-528-0577! [email protected]


SQL Server System Stored Procedures

System Stored Procedures System stored procedures are packaged with SQL Server. Many procedures are used to administer SQL Server, but some are utilities that can be profitablly used by developers.

 

They are global, and can be called from any database application without their fully qualified name. (They are all owned by dbo.) . They are all stored in the Master database, and have the prefix sp_.

This is a reason why it is considered unwise to name local stored procedures with the sp_ prefix. They can be read by viewing their properties in the Query Analyzer.

The system-stored procedures are grouped into these categories.

Category
Description

Active Directory Procedures
Used to register instances of SQL Server and SQL Server databases in Microsoft Windows® 2000 Active Directory™.

Catalog Procedures
Implements ODBC data dictionary functions and isolates ODBC applications from changes to underlying system tables.

Cursor Procedures
Implements cursor variable functionality.

Database Maintenance Plan Procedures
Used to set up core maintenance tasks necessary to ensure database performance.

Distributed Queries Procedures
Used to implement and manage Distributed Queries.

Full-Text Search Procedures
Used to implement and query full-text indexes.

Log Shipping Procedures
Used to configure and manage log shipping.

OLE Automation Procedures
Allows standard OLE automation objects to be used within a standard Transact-SQL batch.

Replication Procedures
Used to manage replication.

Security Procedures
Used to manage security.

SQL Mail Procedures
Used to perform e-mail operations from within SQL Server.

SQL Profiler Procedures
Used by SQL Profiler to monitor performance and activity.

SQL Server Agent Procedures
Used by SQL Server Agent to manage scheduled and event-driven activities.

System Procedures
Used for general maintenance of SQL Server.

Web Assistant Procedures
Used by the Web Assistant.

XML Procedures
Used for Extensible Markup Language (XML) text management.

General Extended Procedures
Provides an interface from SQL Server to external programs for various maintenance activities.

You can create your own system-stored procedures by following the guidelines outlined above. But, even though it is possible to modify an existing system sproc, it is better to name it with another name than to destroy the system version.

Dynamic Queries are created and executed within a stored procedure, and are built typically by passing in text parameters and creating a text SQL string.

Then the string is executed using the form EXEC (@createdstring). Dynamic query plans are not stored in cache, and local variables created in the dynamic query are not available after the query is executed.

To save the query plan, instead of the keyword EXEC, use sp_executesql. Also, with sp_executesql local variables can be used both as INPUT and as OUTPUT parameters.

Autoexec procedures, called autostart procedures can be made by invoking the system stored procedure
sp_procoption procedurename, startup, true

Extended Store Procedures
Extended stored procedures are auxiliary stored procedures created in a DLL using C++. They are prefixed with xp_, and stored in the Master database.

When called from an application database, they bust be fully referenced, using the Master..xp_procname form. Before you can invoke it, you must register the DLL with SQL Server using the syntax:

sp_addextendedproc [@functname=] ‘procedure’, [@dllname=] ‘dll’

Resources:
Information Getting Started with Sybase

This resource discusses Sybase database management system.

Information: Accessing Stored Procedures

This resource gives insightful information on accessing stored procedures.

Metro NY / NJ SQL Server Consultants
We specialize is custom database software. Call us for a free consultation (973) 635 0080 or email us at paladn.com


SQL Server Security

There are several levels of security to SQL Server. At the a base level, in order to be granted get access to a SQL Server, a user is required to have a login account.

There are two modes of security to SQL Server, distinguished by which program provides the authentication. NT Authentication provides that Windows NT (and/or its successors) verify verifies the identity of a user logging in. In this mode, the SQL Server will ‘trust’ that Windows NT has verified that the user is who he says he is. The second method, SQL Server authentication, it is SQL: Server that authenticates the identity of the user. SQL Server authentication is typically used for internet Internet connections, since not all internet Internet users have Windows NT, and not all NT domains are trusted.

In addition to identifying users, it is sometimes convenient to define roles at the server level. Since within an organization, there are groups of users with similar access needs and levels, it may be convenient to define roles with specific levels of permissions. Once you have defined a role, you can assign users to that role, and those users inherit all of the privileges assigned to the role.

There are two types of permissions assigned to individual users and roles. Statement permissions confer the right to execute certain types of T-SQL commands. Object permissions confer the right to access database objects directly.

Defining Logins Users, and Roles
Logins can be added either through the Database Properties Window (under Logins), or by invoking system stored procedures with appropriate parameters. The command

sp_addlogin loginname, password, databasename

will add a SQL Server authenticated login to the list;

sp_grantlogin ‘domainname/username’

will add an NT authenticated login to the list.

After the login has been allowed, you need to allow the user access to the database. This is done either through the Database Properties Window (under Users for that particular database) or with the command

sp_grantdbaccess loginname.

You can see the list of users using the command sp_helpuser or sp_helpuser username

To cancel a security account for a given database, use sp_revokedbaccess username

To remove an NT Login use sp_revokelogin ‘username’ (note no domain), and to srop stop a SQL Server authenticated login, use sp_droplogin username .

To add a role to a database, use sp_addrole ‘clerical’, and to add meAugust 16, 2005er ‘clerical’, ‘username’. To delete a rolemember use sp_droprolemember username.

At the SQL Server level the rolenames are fixed, and cannot be changed, but users can be added using sp_addsrvrolemember username, dbcreator . To drop a rolmember, use sp_dropsrvmember username .

Granting permissions
The GRANT command is used to assign permissions to a security account. Statement permissions in this way: GRANT right TO username

where right can be any of the following:

CREATE DATABASE
CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE VIEW
DUMP DATABASE
DUMP TRANSACTION

For object permissions, GRANT is also the command to use, but the grammar differs slightly:

GRANT action ON table/view/procedure name TO username

where action can be one of the following:

INSERT, DELETE, EXECUTE, SELECT or UPDATE

One can also apply these this permission to groups or roles as well as usernames. And it is also possible that a role can have a permission, but a member of that role be denied permission for a task. See the next discussion.

In all of the above commands, you can also explicitly deny permission using DENY in place of GRANT. Note however that DENY is not the negation of GRANT .

DENY explicitly excludes the user from that capability. The REVOKE command is used to remove both the permission and refusal.

Resources

• Article: How To Identify Your SQL Server Service Pack Version and Edition

This is an insightful article that discusses about SQL server pack version and edition.

Tutorial: Using Access or SQL Server to Write your ASP Code

This tutorial discusses how SQL server can be used to write the ASP code.

Metro NY / NJ SQL Server Consultants
We specialize is custom database software. Call us for a free consultation (973) 635 0080 or email us at paladn.com

 


crystal reports microsoft sql server,crystal reports sql query example,convert crystal reports to sql reporting services,crystal reports visual studio 2022 example,crystal reports vs sql,crystal reports vs crystal server,

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button
small_c_popup.png

Subscribe now to receive all that is new before others

Subscription successful... Thank you

Subscription successful... Thank you