Sage 100

I'm trying to build a report for a customer and it's giving me some fits.  I've tried using a Command table in Crystal, but the amount of data they have is just too much and it errors out.  In looking at other options, I have decided to try the Linked Server in SQL (SQL Express).  I'm trying out this process in demo data but running into a roadblock.  I cannot figure out how to pull the Linked Server into Crystal.  In SQL, I have:

Created a System Database called "tempdb"
Created a view in tempdb that uses the "...From OpenQuery(Sage100, 'Select * from ....') AS derivedtbl_1
Created System DSN that points to the SQL instance (I think i've tried both 32-bit and 64-bit

When I pull up Crystal and try to connect to those DSNs, it shows me the tempdb table, but it doesn't show my derivedtbl_1 table or the View I created. I've scoured google trying to figure it out but I either don't understand the technical things people are saying or I'm not finding what I'm looking for.  

Can someone help explain to me how I can get this to work like I'm in elementary school, please?  Thanks,

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------

Hi Chris,

There's already a System Database called TempDB, so that part kind of scares me. Can you use a different name (maybe name the database SAGE)?

Assuming this is for Sage Standard or Advanced, here's my typical process:
1) Install Workstation Setup on the SQL Server
2) Create a silent ODBC System DSN on the SQL Server (usually x64) that points to the Sage 100 server/install. Test that it works.
3) Create the Linked Server in SQL to point to that ODBC System DSN. The Provider should be Microsoft OLE DB Provider for ODBC Drivers and you can just use the ODBC Name for the Data source
4) Edit Server Objects \ Linked Servers \ Providers \ MSDASQL and Enable Level zero only

Now you can reference the Sage data by using a more natural query by using the Linked Server name and three dots, like "SELECT * FROM SAGE100...GL_Account". So back in your new SAGE database, you would create the view as "CREATE VIEW vGL_Account as SELECT * FROM SAGE100...GL_Account" and then vGL_Account should appear in Crystal and return everything.



------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
------------------------------

Steve,

My apologies, I liked about one thing.  I did not create a DB called tempdb, That is a system DB as you stated.  I created a DB called "Test" and my view is under that.  It appears my problems is that my ODBC connection pointing to SQL cannot find my Test DB, so I used tempdb by mistake.  That's the problem when you start the project one day and then wait a week or more to jump back into the issue. Can't provide the right information. 

I actually have all steps #1-4 completed. My follow up problem is that I can find the Test DB in either a new ODBC that looks at the SQL table or I can't find it in Crystal. It's showing me an Acumatica DB I have installed on this machine, but not the one called Test.  
 
So once I've created the Linked Server and the view, do I need to create a new ODBC that uses the SQL Server Native Client 11.0 and use that ODBC in Crystal?  Or do I go to Create New Connection and choose Microsoft OLE DB Provider for SQL Server?


​

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------

Original Message:
Sent: 09-21-2022 08:50
From: Steve Iwanowski
Subject: Using a SQL Linked Server in Crystal Reports

Hi Chris,

There's already a System Database called TempDB, so that part kind of scares me. Can you use a different name (maybe name the database SAGE)?

Assuming this is for Sage Standard or Advanced, here's my typical process:
1) Install Workstation Setup on the SQL Server
2) Create a silent ODBC System DSN on the SQL Server (usually x64) that points to the Sage 100 server/install. Test that it works.
3) Create the Linked Server in SQL to point to that ODBC System DSN. The Provider should be Microsoft OLE DB Provider for ODBC Drivers and you can just use the ODBC Name for the Data source
4) Edit Server Objects \ Linked Servers \ Providers \ MSDASQL and Enable Level zero only

Now you can reference the Sage data by using a more natural query by using the Linked Server name and three dots, like "SELECT * FROM SAGE100...GL_Account". So back in your new SAGE database, you would create the view as "CREATE VIEW vGL_Account as SELECT * FROM SAGE100...GL_Account" and then vGL_Account should appear in Crystal and return everything.



------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
------------------------------

Make sure you are connecting with the correct SQL instance (if the machine has more than one SQL install).
For SQL Express, the way you connect is usually Servername\SQLExpress

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

Original Message:
Sent: 09-21-2022 10:24
From: Chris Love
Subject: Using a SQL Linked Server in Crystal Reports

Steve,

My apologies, I liked about one thing.  I did not create a DB called tempdb, That is a system DB as you stated.  I created a DB called "Test" and my view is under that.  It appears my problems is that my ODBC connection pointing to SQL cannot find my Test DB, so I used tempdb by mistake.  That's the problem when you start the project one day and then wait a week or more to jump back into the issue. Can't provide the right information.

I actually have all steps #1-4 completed. My follow up problem is that I can find the Test DB in either a new ODBC that looks at the SQL table or I can't find it in Crystal. It's showing me an Acumatica DB I have installed on this machine, but not the one called Test.

So once I've created the Linked Server and the view, do I need to create a new ODBC that uses the SQL Server Native Client 11.0 and use that ODBC in Crystal?  Or do I go to Create New Connection and choose Microsoft OLE DB Provider for SQL Server?


​

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------


Original Message:
Sent: 09-21-2022 08:50
From: Steve Iwanowski
Subject: Using a SQL Linked Server in Crystal Reports

Hi Chris,

There's already a System Database called TempDB, so that part kind of scares me. Can you use a different name (maybe name the database SAGE)?

Assuming this is for Sage Standard or Advanced, here's my typical process:
1) Install Workstation Setup on the SQL Server
2) Create a silent ODBC System DSN on the SQL Server (usually x64) that points to the Sage 100 server/install. Test that it works.
3) Create the Linked Server in SQL to point to that ODBC System DSN. The Provider should be Microsoft OLE DB Provider for ODBC Drivers and you can just use the ODBC Name for the Data source
4) Edit Server Objects \ Linked Servers \ Providers \ MSDASQL and Enable Level zero only

Now you can reference the Sage data by using a more natural query by using the Linked Server name and three dots, like "SELECT * FROM SAGE100...GL_Account". So back in your new SAGE database, you would create the view as "CREATE VIEW vGL_Account as SELECT * FROM SAGE100...GL_Account" and then vGL_Account should appear in Crystal and return everything.



------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯

First, let's make sure the SQL Server is all set.

I believe SQL Server Express still has Remote Connections disabled by default, which you can correct by running this:

EXEC sp_configure 'remote access', 0;
GO  
RECONFIGURE;  
GO

My second thought is that you need to allow the SQL Server service through the Windows Firewall.

After those steps, on the client side, just to test access, create a new ODBC DSN with the "SQL Server" driver (not Native Client), fill in the Name, Server (SQL Server instance probably SERVERNAME\SQLEXPRESS), then click FINISH and Test Data Source. If the test succeeds, the server is completely set. If the test fails, then you may need to check SQL user permissions or make sure the Domain User is present in SQL and set for Trusted Windows Authentication.

Back in Crystal, I would create the report with OLE DB \ Microsoft OLE DB Provider for SQL Server source, that way you won't need to create ODBC DSNs everywhere.

------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
------------------------------

Original Message:
Sent: 09-21-2022 10:24
From: Chris Love
Subject: Using a SQL Linked Server in Crystal Reports

Steve,

My apologies, I liked about one thing.  I did not create a DB called tempdb, That is a system DB as you stated.  I created a DB called "Test" and my view is under that.  It appears my problems is that my ODBC connection pointing to SQL cannot find my Test DB, so I used tempdb by mistake.  That's the problem when you start the project one day and then wait a week or more to jump back into the issue. Can't provide the right information.

I actually have all steps #1-4 completed. My follow up problem is that I can find the Test DB in either a new ODBC that looks at the SQL table or I can't find it in Crystal. It's showing me an Acumatica DB I have installed on this machine, but not the one called Test.

So once I've created the Linked Server and the view, do I need to create a new ODBC that uses the SQL Server Native Client 11.0 and use that ODBC in Crystal?  Or do I go to Create New Connection and choose Microsoft OLE DB Provider for SQL Server?


​

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------


Original Message:
Sent: 09-21-2022 08:50
From: Steve Iwanowski
Subject: Using a SQL Linked Server in Crystal Reports

Hi Chris,

There's already a System Database called TempDB, so that part kind of scares me. Can you use a different name (maybe name the database SAGE)?

Assuming this is for Sage Standard or Advanced, here's my typical process:
1) Install Workstation Setup on the SQL Server
2) Create a silent ODBC System DSN on the SQL Server (usually x64) that points to the Sage 100 server/install. Test that it works.
3) Create the Linked Server in SQL to point to that ODBC System DSN. The Provider should be Microsoft OLE DB Provider for ODBC Drivers and you can just use the ODBC Name for the Data source
4) Edit Server Objects \ Linked Servers \ Providers \ MSDASQL and Enable Level zero only

Now you can reference the Sage data by using a more natural query by using the Linked Server name and three dots, like "SELECT * FROM SAGE100...GL_Account". So back in your new SAGE database, you would create the view as "CREATE VIEW vGL_Account as SELECT * FROM SAGE100...GL_Account" and then vGL_Account should appear in Crystal and return everything.



------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯

Steve/Kevin,

Thank you. I was missing one very important part of connecting to SQL Express as Kevin stated, which is I needed to put \SQLExpress on the end of the computer name. That was the gotcha I was missing. I now see my Test DB and was able to run a view off of it in Crystal. I think the system has usually put that in there for me in the past, or it may have always been SQL Standard so I haven't had to deal with it, so that's just something I forgot. I think that resolves it for now, but will reach out again if I run into more issues. Thank you!

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------

Original Message:
Sent: 09-21-2022 10:45
From: Steve Iwanowski
Subject: Using a SQL Linked Server in Crystal Reports

First, let's make sure the SQL Server is all set.

I believe SQL Server Express still has Remote Connections disabled by default, which you can correct by running this:

EXEC sp_configure 'remote access', 0;GO  RECONFIGURE;  GO

My second thought is that you need to allow the SQL Server service through the Windows Firewall.

After those steps, on the client side, just to test access, create a new ODBC DSN with the "SQL Server" driver (not Native Client), fill in the Name, Server (SQL Server instance probably SERVERNAME\SQLEXPRESS), then click FINISH and Test Data Source. If the test succeeds, the server is completely set. If the test fails, then you may need to check SQL user permissions or make sure the Domain User is present in SQL and set for Trusted Windows Authentication.

Back in Crystal, I would create the report with OLE DB \ Microsoft OLE DB Provider for SQL Server source, that way you won't need to create ODBC DSNs everywhere.

------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
------------------------------


Original Message:
Sent: 09-21-2022 10:24
From: Chris Love
Subject: Using a SQL Linked Server in Crystal Reports

Steve,

My apologies, I liked about one thing.  I did not create a DB called tempdb, That is a system DB as you stated.  I created a DB called "Test" and my view is under that.  It appears my problems is that my ODBC connection pointing to SQL cannot find my Test DB, so I used tempdb by mistake.  That's the problem when you start the project one day and then wait a week or more to jump back into the issue. Can't provide the right information.

I actually have all steps #1-4 completed. My follow up problem is that I can find the Test DB in either a new ODBC that looks at the SQL table or I can't find it in Crystal. It's showing me an Acumatica DB I have installed on this machine, but not the one called Test.

So once I've created the Linked Server and the view, do I need to create a new ODBC that uses the SQL Server Native Client 11.0 and use that ODBC in Crystal?  Or do I go to Create New Connection and choose Microsoft OLE DB Provider for SQL Server?


​

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)


Original Message:
Sent: 09-21-2022 08:50
From: Steve Iwanowski
Subject: Using a SQL Linked Server in Crystal Reports

Hi Chris,

There's already a System Database called TempDB, so that part kind of scares me. Can you use a different name (maybe name the database SAGE)?

Assuming this is for Sage Standard or Advanced, here's my typical process:
1) Install Workstation Setup on the SQL Server
2) Create a silent ODBC System DSN on the SQL Server (usually x64) that points to the Sage 100 server/install. Test that it works.
3) Create the Linked Server in SQL to point to that ODBC System DSN. The Provider should be Microsoft OLE DB Provider for ODBC Drivers and you can just use the ODBC Name for the Data source
4) Edit Server Objects \ Linked Servers \ Providers \ MSDASQL and Enable Level zero only

Now you can reference the Sage data by using a more natural query by using the Linked Server name and three dots, like "SELECT * FROM SAGE100...GL_Account". So back in your new SAGE database, you would create the view as "CREATE VIEW vGL_Account as SELECT * FROM SAGE100...GL_Account" and then vGL_Account should appear in Crystal and return everything.



------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯

The first install of SQL on a machine is usually just the machine name (default instance).  Using SQL Express, or more than one instance of regular SQL, and it becomes servername\instancename

Edit:
If you look at the Windows Services, you can see the names there.  This example has three different instances of SQL installed.
The first one is the default instance.  If you see "MSSQLSERVER" that is the one you connect with using "servername".
Then there is servername\SAGE2022 and servername\SQLExpress... which are both completely separate SQL environments.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

Original Message:
Sent: 09-21-2022 10:50
From: Chris Love
Subject: Using a SQL Linked Server in Crystal Reports

Steve/Kevin,

Thank you. I was missing one very important part of connecting to SQL Express as Kevin stated, which is I needed to put \SQLExpress on the end of the computer name. That was the gotcha I was missing. I now see my Test DB and was able to run a view off of it in Crystal. I think the system has usually put that in there for me in the past, or it may have always been SQL Standard so I haven't had to deal with it, so that's just something I forgot. I think that resolves it for now, but will reach out again if I run into more issues. Thank you!

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)
------------------------------


Original Message:
Sent: 09-21-2022 10:45
From: Steve Iwanowski
Subject: Using a SQL Linked Server in Crystal Reports

First, let's make sure the SQL Server is all set.

I believe SQL Server Express still has Remote Connections disabled by default, which you can correct by running this:

EXEC sp_configure 'remote access', 0;GO  RECONFIGURE;  GO

My second thought is that you need to allow the SQL Server service through the Windows Firewall.

After those steps, on the client side, just to test access, create a new ODBC DSN with the "SQL Server" driver (not Native Client), fill in the Name, Server (SQL Server instance probably SERVERNAME\SQLEXPRESS), then click FINISH and Test Data Source. If the test succeeds, the server is completely set. If the test fails, then you may need to check SQL user permissions or make sure the Domain User is present in SQL and set for Trusted Windows Authentication.

Back in Crystal, I would create the report with OLE DB \ Microsoft OLE DB Provider for SQL Server source, that way you won't need to create ODBC DSNs everywhere.

------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯


Original Message:
Sent: 09-21-2022 10:24
From: Chris Love
Subject: Using a SQL Linked Server in Crystal Reports

Steve,

My apologies, I liked about one thing.  I did not create a DB called tempdb, That is a system DB as you stated.  I created a DB called "Test" and my view is under that.  It appears my problems is that my ODBC connection pointing to SQL cannot find my Test DB, so I used tempdb by mistake.  That's the problem when you start the project one day and then wait a week or more to jump back into the issue. Can't provide the right information.

I actually have all steps #1-4 completed. My follow up problem is that I can find the Test DB in either a new ODBC that looks at the SQL table or I can't find it in Crystal. It's showing me an Acumatica DB I have installed on this machine, but not the one called Test.

So once I've created the Linked Server and the view, do I need to create a new ODBC that uses the SQL Server Native Client 11.0 and use that ODBC in Crystal?  Or do I go to Create New Connection and choose Microsoft OLE DB Provider for SQL Server?


​

------------------------------
Chris Love
Accounting Systems, Inc. (ASI)


Original Message:
Sent: 09-21-2022 08:50
From: Steve Iwanowski
Subject: Using a SQL Linked Server in Crystal Reports

Hi Chris,

There's already a System Database called TempDB, so that part kind of scares me. Can you use a different name (maybe name the database SAGE)?

Assuming this is for Sage Standard or Advanced, here's my typical process:
1) Install Workstation Setup on the SQL Server
2) Create a silent ODBC System DSN on the SQL Server (usually x64) that points to the Sage 100 server/install. Test that it works.
3) Create the Linked Server in SQL to point to that ODBC System DSN. The Provider should be Microsoft OLE DB Provider for ODBC Drivers and you can just use the ODBC Name for the Data source
4) Edit Server Objects \ Linked Servers \ Providers \ MSDASQL and Enable Level zero only

Now you can reference the Sage data by using a more natural query by using the Linked Server name and three dots, like "SELECT * FROM SAGE100...GL_Account". So back in your new SAGE database, you would create the view as "CREATE VIEW vGL_Account as SELECT * FROM SAGE100...GL_Account" and then vGL_Account should appear in Crystal and return everything.



------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯