|
A stored proc (or any other sql query) returns zero for success and non-zero for an error
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I'm playing with a hypothetical company with several branch offices and one main office. Said company has limited IT resources (ie, no budget), but would like to keep local office sales data in SQL Server Express, then update nightly to the main office SQL Server Standard edition. Is this even possible? Can the Express edition participate in replication?
And yes, it's for a homework assignment I'm assisting with... 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
we don't do homework assistance assistance. you want rentacoder.
|
|
|
|
|
So do we do looped assistance or is it like nested assistance and is below the assistance threshold.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Luc Pattyn wrote: you want rentacoder
Too expensive! 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
looks like Express does not support replication info[^]
Another aspect to consider is the records identity information, integer identity format no longer works. Look into uniqueidentifier/GUID. Horrible, ugly canankerious bastard things that they are. One gotcha is that a GUID is not a nullable type.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Maybe you could use integer keys locally, and apply an integer-to-GUID conversion table before exporting it all.
|
|
|
|
|
If you only need to update nightly, you should look into a simple application that creates xml files, and sends them by ftp or something like that, and an application on the other side that imports the data from these files.
It's a robust system that we use ourselves, and built without a budget 
My advice is free, and you may get what you paid for.
|
|
|
|
|
That's a good thought, and I may do it just for the learning experience. But I'm just looking for what's possible - only the design is required for homework, not the implementation.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I would maintain a transaction table. And send the unsent transactions periodically.
I would also include a field that indicates where the transaction came from.
|
|
|
|
|
That would make a lot of sense, as one of the requirements I made up is that the stores have to be autonomous during comm failures, then update the home server when a link is available.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Just a tip, you should include a status flag in that table while you are at it. This flag can have separate values for (for example) "Handled", "ImportError", "Corrupted", etc
The most common problems when transferring data are data corruption, transfer failures, and import failures (usually because some data is in the wrong format or missing entirely, say a date may have the year 1900 instead of 2010, which happens astoundingly often).
Based on the flag you can then have the import application take different actions (correct data with default values, alert someone, try to retrieve the data again from the source, etc.)
My advice is free, and you may get what you paid for.
|
|
|
|
|
Hi I have an Sql Table Like this
MatTable
type = 1 Is IN Qty ( Buy )
type = 0 Is Out Qty ( Sell )
mat qty type
-------------------------------------------------- ---------------------- -----
mat1 10 0
mat2 2 0
mat3 10 0
mat1 5 1
mat2 5 1
mat2 7 1
mat4 4 0
mat4 4 0
mat4 8 1
mat3 6 1
mat1 10 0
How I can Get a result Like
mat Current qty
-------------------------------------------------- ----------------------
mat1 15
mat2 -10
mat3 4
mat4 0
Thank you In Advance
I know nothing , I know nothing ...
|
|
|
|
|
Try:
"Select mat, sum(case type
when 0 then qty * -1
else qty) [Current qty] from MatTable
group by mat"
or if you don't like to use a case statement then -
select mat, sum(qty) from (
(Select mat, sum(qty * -1) qty from MatTable group by mat
where type = 0) t1
union
(Select mat, sum(qty) qty from MatTable group by mat
where type = 1) t2
)
group by mat
I didn't check either of these for syntax so let me know if you have problems running these...
|
|
|
|
|
Thank you so much ,
it;s worked like charming ,
However I added a little "END" word to the first statement ,
SELECT MAT, SUM(CASE TYPE WHEN 0 THEN Qty ELSE -Qty END) AS CurrentQty FROM MatTable GROUP BY Mat
thank you , for you code , it's so simple and brilliant
I know nothing , I know nothing ...
|
|
|
|
|
Hi,
I have a bit of a problem with a Forms App using Access for the DB.
I have a Calendar Control that has date ranges bolded from records in a table in the DB.
I have the Calendar Control populated and now the user can select a date and find the event associated to the date. The date may be the "StartDate", "EndDate", or a date within the range of the "StartDate" & "EndDate" in a row.
What I need help with is wrighting the SQL to bring back the record ID where the selected date falls between the StartDate & End Date and return the Record ID value.
SELECT E_ID FROM Events WHERE "SelectedDate" is Between StartDate AND EndDate
OR something like that???
|
|
|
|
|
Try
SELECT E_ID FROM Events
WHERE (SelectedDate >= StartDate) AND (SelectedDate <= EndDate)
Make sure you replace all the date variables with their string representations for the SQL, of course.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Hi Walt,
Thanks for the sample code... It works great. But I have another issue that maybe you can help me with.
The Value I am passing is a string with the DateTimeValue in it. I have put single quotes around the value and it works when I run the SQL manually... but when I run it in the app it rejectes it because the "Time" is still attached.
My issue is how can I convert the string value to a Date ONLY value?
I have tried Convert.ToDateTime(Value)... but that still has a Time in it.
I tried DateTime.ParseExact(Value,... ,...) but I couldn't find a Date only filter.
I am using this in a Forms App in C# on an Access DB, I build my SQL in the App and pass it over.
Got any suggestions on how to convert the string value of "DateTime" to a "Date" only value?
Thanks
|
|
|
|
|
Have your tried DateTime.ToShortDateString?
A good summary of the assorted DateTime stuff is at [^]. I keep that page bookmarked.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Walt,
Thanks for the pointer the page dose have a lot of great formats etc on it. I have added it to my favorites.
I had to change the Single Quotes to # around each of the parameter dates and it worked great.
Thanks for all the help and pointers.
SquireDude
|
|
|
|
|
Walt Fair, Jr. wrote: DateTime.ToShortDateString
Not ideal, as it depends on regional settings (and could contain all kinds of nonsense), whereas the database content does not. An explicit format would be the preferred one here.
Here is an alternative summary[^].
|
|
|
|
|
True! Thanks for pointing that out, Luc.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
No problem.
|
|
|
|
|
Hi,
I need some feedback on some code generation software I'm in the process of re-developing for SQL Server (2005/2008).
Its called SSCodeGen (for 2005/2008) and the new version (8.3) is now up at www.RaisinTech.com/SSCodeGen.exe, It will
- Generate the 4 standard (A/I/U/D) stored procedures for a table (text/image field not currently handled)
- Generate the execute statements for a stored procedure (slightly more complete than that generated from SS Man Studio)
- Generate the sql for S/I/U/D statements for one or 2 tables on the database.
Obviously it is free, and it doesn't require installation, you can just download and run.
There is a version for SQL Server 7/2000 at http://users.quista.net/thealey/.
Any comments appreciated
BillyTheKidney
|
|
|
|
|
This is a good excercise and every developer should build such a tool, I am continually astonished to see some quite skilled dev hand coding crud procedures and methods.
Try extending it to include the c#/VB stubs to call the stored proc. Also think about parameterised queries instead of procs, I am currently changing mine to do this because sql compact does not support procs  .
Then go on to create the model class for the table. Take into account where you have a view of the same data and add the additional fields into the model class.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
