Finally Crossed Over ...
May 13, 2008
Good morning (or afternoon, depending on your local).
I've finally crossed over from Access to SQL Server. An interesting step fraught with surprises, syntax quirks and occasionally some exclamations of joy (just kidding).
Anyway, here's my first question. I'm working on moving a currently functioning VB app from an Access backend to a SQL Server back end. In the process, I have been converting my app driven SQL calls to Server based stored procedures. Aside from a few issues (like SQL not liking concatenating string with the &), everything has worked out until this gem:
SET NOCOUNT ON
TRANSFORM Max(tE.EntryKey) AS Expr1
SELECT tE.ProjectKey
FROM TS_tblWeeks tW
INNER JOIN (TS_tblProjects tP
INNER JOIN (TS_qryGroup tG
INNER JOIN TS_tblEntry tE
ON tG.GroupKey = tE.GroupKey)
ON tP.ProjectKey = tE.ProjectKey)
ON tW.WeekKey = tE.WeekKey
WHERE (tE.WeekKey) = 1 And (tE.PersonKey) = 1
GROUP BY tW.WeekKey, tE.ProjectKey, tP.ProjectSort
ORDER BY tW.WeekKey, tP.ProjectSort, tG.GroupKey
PIVOT tG.GroupKey;
SQL server reports:Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'tE'.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'PIVOT'.
The highlighted lines are the TRANSFORM and PIVOT lines.
The query works fine in Access, and works fine through ADO.NET as a supplied query. My intention is to make the two integers in the WHERE clause to be parameters supplied to the procedure.
It's something simple, isn't it?
Thanks!
View 14 Replies
Dec 10, 2007
Hello anyone!!! Can anyone helpme with this pls?
I have this data model: there is 4 tables A, B, C and D. The constraints says that a identity row in the table A is the forain key in the table B, and the same row is a forain key in the table C. The table D have two forain keys with the table C and B.
C A
++ <-- ++
++ ++
| |
V V
++ <-- ++
++ ++
D B
I need make in a part of a report this: for each row of the table D the report have to print the row of the table C that has found by the constraint and then in the next row the report have to print the row of the table B found by the other constraint. It could looks like this:
C.field1
B.field1
The Problem that I have is that If in the table D are more rows, ie. 3, the report have to crossed sort the next rows such this
C.field1
B.field1
C.field2
B.field2
C.field3
B.field3
In the report with a matrix I have done more or less this, but not exactly that I need to implement. cuz in this way the report returns to me this:
C.field1
C.field2
C.field3
B.field1
B.field2
B.field3
anoter solution that I tried to implement is do this, in a single field write this "Fields!C1.Value & vbCrLf & Fields!B1.Value" but the problem with this is that the fields of the table B are not the same or the same number that in the table C. So this isn't the solution too.
So wonderfull developers, anyone have any idea or know how to resolve this? THX!!!
View 1 Replies
View Related
Feb 10, 2014
I have a table set of records. Its contains some customerID,SportsGoods,Price in different datetime. I want to add customer spent. If crossed 1000 means i have to show purchase time when it is crossed 1000. I need query without while and looping.
Example:
Customer NameGoodsPriceDatePurchased
ABat2501/31/2014
ABall221/31/2014
BCarrom Board4752/2/2014
CTennis Ball502/1/2014
AFootball1502/2/2014
DBat2501/31/2014
BBall221/31/2014
AHockey Bat1252/4/2014
CChess552/4/2014
AVolley Ball552/4/2014
View 9 Replies
View Related
Jul 25, 2006
Within my script component I was having some difficulty getting nested Exceptions to bubble out of the script component logic. The symptom appears when you get an ex.message of "Object reference not set to an instance of an object." As soon as I removed the Finally section my message (or the actual Exception object) was properly passed to the caller.
I've only done minimal work with Try/Catch/Finally structures. Is this normal?
Thanks,
Jeff Tolman
E&M Electric
View 6 Replies
View Related
Nov 7, 2006
So where do I go from here/
SSMS doesn't seem to have anything like QA?
I spent the better part of today installing everything...read somewhere about using VS to write code for sprocs.
What does everyone else do?
View 6 Replies
View Related
Mar 30, 2008
Apress pro t-sql 2005. so far it seems pretty good in clearing up a lot of things Ive been doing but wasnt really sure I understood the same way I understand for example, C#. It was the little things about the language that Ive never been realy clear on such as basic control flow statements. Anyone else read this book yet ?
View 2 Replies
View Related
Sep 28, 2007
Hi all,
We have 2003 EE 64 bit & SQL 2005 EE 64 bit as well(service packs for windows & SQL pack 2 (9.030.42), now I have discovered this things with conversion of DTS to SSIS... We have set Oracle linked servers - they are working, at least we can see stuff, haven't tried to write on Oracle yet, sqlplus is also able to connect to oracle servers.. Our Oracle guy have installed 10.2.0.3 patch 10, but still no luck with SSIS...I'm not going to rename folders on production, like someone suggested it here...I checked path also, there was seems to be required folders included..I even tried to create SSIS on my PC and deployed it on 64 bit, using files method, without saving on SQL server - doesn't work, when it hit part with Oracle connection..And in connection string it looks like it use OLEDB..
I'm not excited about using OPENQuery etc, instead of SSIS.. It's just driving me nuts with this things...How Microsoft testing this stuff if bunch of people facing this issues and there's still no officiall fix or something like that..
View 9 Replies
View Related
Oct 4, 2006
Please help. I'm completely new to SSIS, and I was hoping someone could point me in the right direction(s).
I am developing a winforms client app, and I need to be able to provide the user with the ability to import data in CSV format into our application's database.
I'd like to use SSIS if possible, as long as what I am trying to do isn't near impossible.
I'm thinking of a UI where the user can specify a flat file (CSV) to upload and be processed by an SSIS package on a remote database server.
This package will be responsible for validating the CSV file and inserting data into the database as appropriate.
Is there a way I can:
launch an SSIS package remotely, from a winforms app on the client machine that is not running SQL server (preferably asynchronously) -- and pass it some parameters, including the import file itself
(optionally) provide progress feedback to the client pc to let them know it is being processed
display a nice SSRS report on the client upon completion that tells them exactly what the success/failure of the import was (how many rows processed, which ones failed and why, etc)
Any helpful examples, links, etc would be most appreciated.
Thanks in advance!
View 1 Replies
View Related