Support of t-sql scripts execution

Brett Phipps

10 years ago
updated 8 years ago
Completed

I'm a consultant working for a firm who has a reporting contract with a state agency. They state agency has recently started providing Flyspeed to a few business users so that they can do some ad hoc querying. However, there was no training and these business users are not familiar with sql really at all.

I have several stored procedures that I've written in t-sql that would be helpful if the users were able to execute them. The product description on your products page states:

Full support of SQL syntax for Microsoft SQL Server, Oracle, MySQL, Microsoft Jet 4 (MS Access, MS Excel), PostgreSQL, InterBase, Firebird, IBM DB2, Informix, Sybase, SQLite databases.

However, if I copy a t-sql query/script with variables and temp tables, etc, the editor does not process these at all. I don't want to install the product on my machine. Can you point to the main product documentation and provide a pointer to where I need to look to see how to convert T-SQL scripts so they will execute in flyspeed?

Replies 3

Oldest first
Newest first Oldest first

Anonymous

10 years ago

Hello,

I agree that this statement might be confusing. Sorry for this confusion. We need to rethink the feature list to avoid it.

This statement was taken from the feature list of visual query builder (Active Query Builder), which works with SELECT SQL queries only. So this statement relates to the visual query builder only.

To execute a T-SQL script in FlySpeed SQL Query, hold the Ctrl key and then press F5 (F9) key or click on the Execute (Execute in new window) button on the toolbar.

Reply Link

So I just attempted this with a simple script that I run to look up customer ids. (See example below) After inserting the script the toolbar was still grayed for execute in a new window. Even after pressing the ctrl key it remained greyed out. I tried pressing both F5 and F9. Neither executed the query.

If Flyspeed supports t-sql scripts I need more information than this. I taught myself SQL using DB2 on the mainframe in 1999. In 2006 I went to a shop that was a SQL server shop. I taught myself SSMS and learned the little differences with T-sql on my own there. With Flyspeed, I struggle querying the database at all. I do not find it intuitive at all.

I'm working on a contract project that is almost over. One of the things we did was create some views of a highly normalized database to make ad hoc reporting a little easier for the business users who had no visibility into their data.

The business I am supporting on this short term project has had a few copies of flyspeed purchased and given to a few users to for them to use. But they know NOTHING about SQL or querying. If I struggle with your tool and can't get it do what I want how do you expect the users to get it?

They need to be able to execute scripts that developers provide to them so they can learn from that. But the FTE developers that will be left behind don't have time or the inclination to learn flyspeed, so they want to just give them the scripts they've got. But they don't work. Which means your product will never get additional adoption because the first business users will get frustrated and never pick it up.

USE SFS_TEST
declare @customerid varchar(15)
,@agreementNbr varchar(20)
,@Year int

/*
select *
from Customers cu
where CustomerName like '%Bruns%'
--*/

set @agreementNbr = '001-090'
set @Year = 2014
select *
from Customers cu
where cu.AgreementNbr = @agreementNbr

set @customerid =
(select CustomerId
from Customers cu
where cu.AgreementNbr = @agreementNbr
and cu.StatusCode = 'ACTIVE')

--set @customerid = 388

/*
Known customer ids
ID CustomerName AgreementNbr
4157 ADAIR CO. R-I 001-090
4169 VAN-FAR R-I 004-109
4184 RICH HILL R-IV 007-124
388 COLUMBIA 93 010-093
4229 SOUTH CALLAWAY CO. R-II 014-130
4300 COLE CO. R-V 026-005
4384 REPUBLIC R-III 039-134
5159 ST JOHN REGIS SCHOOL 048-457
4590 ST HENRYS SCHOOL 067-400
4646 COOTER R-IV 078-004
4651 ALTENBURG 48 079-078
4670 BOYS & GIRLS TOWN OF MISSOURI 081-401
4719 WENTZVILLE R-IV 092-089
4787 EDGEWOOD CHILDREN'S CENTER 096-520
4837 HURLEY R-I 104-041
4849 HOLLISTER R-V 106-005
5156 ST JOAN OF ARC SCHOOL 115-416
4908 N W MO STATE UNIVERSITY 118-118

*/
select 'Customers'
,*
from Customers cu
where cu.CustomerID = @customerid
and StatusCode = 'ACTIVE'

select top 1000 'Applications'
,*
from Applications a
where a.CustomerID = @customerid
order by StartDate
,VersionNumber

select top 1000 'ApplicationNSLPDetails'
,ad.*
from Applications a
cross apply
(Select *
from ED_SFS_Reporting.dbo.FN_SelectFinalLeaApplications
(@year) as finalApp
where a.ApplicationID = finalApp.ApplicationID) as final
inner join ApplicationNSLPDetails ad
on a.ApplicationID = ad.ApplicationID
where a.CustomerID = @customerid
order by a.StartDate
,a.VersionNumber

select 'SiteApplications' as SiteApplications
,s.SiteNbr
,s.SiteName
,sa.*
from SiteApplications sa
inner join Sites s
on sa.SiteID = s.SiteID
where sa.CustomerID = @customerid
order by SiteNbr
,SiteID
,StartDate
,VersionNumber

9 years ago

Let me know if you need a tester for this.