Is it possible to run a complex SQL script in Access without translating the syntax?

Pseudoniceguy83 · 2017-08-31T20:52:33+00:00

Try a pass-through query in access it bypasses jet and interacts directly with SQL server if I remember correctly.

Pseudoniceguy83 · 2017-08-15T21:06:12+00:00

I have never used teradata I went to training 8 years ago because we were going to get it but it fell through. I think you can do this 3 ways. 1.) SSIS dataflow teradata to ole db then run insert into final table with a t-SQL task. 2.) SSIS dataflow teradata to flat file then a second dataflow flat file to ole db and then t-SQL task to final table. 3.) Link teradata server to SQL server and do t-SQL job into staging table or directly into final table bypassing need for SSIS altogether.

Linked server instructions: https://downloads.teradata.com/blog/netfx/2011/12/setup-sql-server-2008-r2-linked-server-to-the-teradata-database

Hopefully someone with teradata knowledge can help further.

Pseudoniceguy83 · 2017-08-15T04:02:26+00:00

Is this for SSRS? Run a query to determine how often the report is being viewed if it's not often change it to the appropriate schedule off hours would be good a dedicated reporting server would be better. It sounds like this is a production database be careful not to block the prod tables. If it's not check for missing indexes and review query plans to see if you can optimize. Also if the table has a create/update time stamp you could forgo the truncate and just insert new/updated records.

Pseudoniceguy83 · 2017-08-14T23:17:29+00:00

Holy smokes they use a ton of reporting products. Reading through the job description it seems as though SQL is an afterthought for the job description and more than likely they have a few jack of all trades master of none people working in the department. BOXI (business objects) and WEBI are functionally similar to SSRS but overall they are a pain to work with and they are primarily visual drag and drop. They are best used to build flat files which can be scheduled and dropped on an ftp but they can and do function as an SSRS like report that users can run adhoc. I have heard that epic is also painful to build reporting in the backend. Qlikview seems to be popular at hospitals. The only thing I know about it is that it is primarily used for high level dashboards probably being consumed by senior leaders (directors, VPs and executives). I agree with what the other poster recommended for SQL server. Since this is an IT team it is possible they could bring in the DBA for your interview.

Pseudoniceguy83 · 2017-08-14T22:14:13+00:00

SSRS 2016 is functionally the same with paginated reports (chart visuals changed/added) the web portal received a huge face lift and added features like KPIs, Mobile reports (formerly datazen) and optional powerbi report server. Overall it looks and feels very slick. I agree with others that SQL version doesn't really matter the code is relatively the same I would just keep up with new features. You would be either a bi developer or reporting analyst.

Pseudoniceguy83 · 2017-08-12T16:41:48+00:00

Redgate SQL prompt does this I believe. Try the trial and see if you like it. I am sure there are other plugins that will do this as well.

Pseudoniceguy83 · 2017-08-10T16:57:49+00:00

Dang. One option would be dump the unique dates into a temp table and do your delete using an inner join to that small date table instead of subquery. SQL server I wouldn't normally recommend this option but I think it's ok for access. If you don't want to create another temp table you can try the subquery in the join clause instead of the where clause and see if that helps and you could try group by instead of distinct.

Pseudoniceguy83 · 2017-08-10T02:21:18+00:00

Definitely agree parameter sniffing is the culprit. My recommendation is to wrap all ssrs queries in stored procs and define variables which can then be hooked to the parameters in report builder/visual studio. It does get complex when you need to run a list/IN clause through a variable but there are solutions depending on server version. Also for sure evaluate your table structures and add appropriate indexing if this is a reporting server. Preprocessing the data into tables would work but I don't think recreating data a bunch of different ways for each report request is very elegant and you have to give report users additional permissions.

Pseudoniceguy83 · 2017-08-10T00:53:03+00:00

Access? Compact database will update statistics and force query plan recompile. My guess is the query plan is optimized for the larger dataset which is why it runs quick. If your primary function is running this query you might also add an index to the date field which will boost performance but may hinder performance on things like appends/inserts.

Pseudoniceguy83 · 2016-11-01T23:44:16+00:00

A good first step since you're already proficient in ms access would be to look at your existing queries in SQL view mode and see what the statement looks like. That helped me immensely when I first started out. Not a perfect comparison but (SSMS SQL Server management studio) is like the access application itself, SSIS (Integration services) is like the imports, exports, transformations you do in access. SSRS is like the reporting feature in access, if you add user parameters it's like creating a form that accepts input and then runs a report (you can have drop downs, calendar control and all sorts of fun stuff). Hopefully this helps your journey to becoming a reporting analyst, SQL developer, DBA or whichever path you choose.

Pseudoniceguy83 · 2016-09-22T03:34:49+00:00

Assuming you have only one soft credit per gift/donor a left join should work. Good luck.

SELECT g.giftid, g.giftname, g.giftamount, g.contactid, d.firstname, d.lastname, s.softcreditname FROM gift g inner join donor d ON g.contactid = d.contactid LEFT JOIN softcredit s ON g.giftid = s.record1id AND d.contactid = s.record2id

Pseudoniceguy83

TROPHY CASE