I have my error routines and raiserrors working fine. I can run my stored procs through query analyzer and I get the error messages I expect. WHen the stored proces are run through Access, VB, C++, what is required to return the
error messages I see in QA. Is it specific to the app front end. All my front ends will be using ODBC as their
connection
Hi,
Does sql server return an error code when a stored procedure fails, or an operation inside a stored procedure fails. How does one trap it?
We have an application that executes a number of stored procedures. I would like to verify that the procedures executed successfully, and the operations contained within the stored procedures (i.e. insert into a table) did not result in an error. If there was an error with the stored procedure of the operations inside, I would like to trap it, and return it to the calling program (non sql server).
I have seen the @@error global variable, but it only gives the return code of the last operation, which means I would have to check after every select, insert, delete, etc. I am hoping to avoid this.
Any thoughts?
Thanks in advance
I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.
The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?
Below is a post by blindman from my other "mentoring" thread (http://www.dbforums.com/showthread.php?t=1608569). I wanted to respond to it but not in that thread:
Yeah, but if he's going to go unbound he might as well develop in .Net rather than Access. You lose half the convenience of MSAccess as a front end if you use unbound forms and controls.
Besides, he's trying to learn SQL Server, not front-end development. Learning both at the same time may be a bit much. I'd suggest he stick with his idea of converting the Access database to SQL Server back-end.
But, you should definitely convert the Access front-end to a Microsoft Access Data Project, rather than a classic mdb file.
Going unbound would increase your application performance, but probably not noticably unless you have scores of uses, and since you are currently using straight MS Access I'd guess you have no more than five or six simultaneous users, right?
blindman
To start I have decided that ADO is the way to go even though I don't necessarily need the performance boost for my current situation. Keep in mind I am planning for future and more demanding opportunities, hopefully :) And I'd like to learn the "right" way to do things.
I actually use ADO quite a bit in my current DB app and bound controls as well so it really wasn't a new concept. However using strictly ADO produces alot more work and code. And like blindman says it pretty much takes all the ease of Access away and VB.NET for that matter too. I mean all the wizards are useless. So I have two questions:
1. When going to all ADO you are pretty much recreating what Access already does for you right? Coding in ADO is more work right? I mean you have to setup connections and fill fields and listboxes manually in code right? I don't mind the extra work I just want to make sure if I'm going to be doing all this extra coding that it is the right thing to be doing.
2. MDB vs. ADP - From what little I've read Access Data Projects are made for use with SQL Server. It is even more useful if you don't/can't actually work in SQL Server. But again if I'm using ADO it strips all the usefulness of the ADP's doesn't? I don't need ADP's to create views or sprocs. I can do all of that through the management studio. So does it really matter if I use an ADP or MDB as my front end if that is all it is going to be is a front end?
Lastly I wanted to use VB 2005 as the front end but we utilize a ton of reports. As far as I know Crystal Reports or something like it has to be used with VB 2005. Or does VB 2005 have some built in reporting capabilities? I thought I saw some report designer for VB6. Anyway I decided to stick with Access because I am familiar with its reporting capabilities.
I should note that I have VB 2005 Express and SQL Server Express. Right now my company is not putting any money into a higher version.
Let me know what you all think.
Thanks,
GG
Hi
Can anybody tell us what frount end sould i learn for
better result from sql server.
Can also tell us what is price of sql server developer &
enterprise edition
Thanks
ASM
I am in the planning stages of a front-end to access sql server databases. The project involves analysis of stored data as well as storing results of the analysis. The classes for the analytical part is fairly complex so I would like to speed up the data manipulation part of the project. I am already familiar programming with ADO but I was hoping for a more complete solution. Is there a sdk or example code (third-party OK) that could facilitate this?
The client is not especially fond of .NET and would prefer to use VB6. However, with the right solution, C# or VB.NET would be acceptable languages.
I can't get my asp page creatd in Front Page 2000 to dump info into SQL database...am I missing something?
View 1 Replies View RelatedDoes anyone know how to link (not import) an sql server table to an access
database using script? I tried a few methods but it doesn't seem to be working. Please help.
Thanks
Ziggy
how can i do an access frontend to sql server backend
View 4 Replies View Relatedin create a new software application using C# as front end and database with sql. how to connect SQL server with my front end application.dot net training in chennai <a href="http://www.fita.in/dot-net-training-in-chennai/">Dot Net Training in Chennai</a>
View 2 Replies View RelatedAfternoon all,Apologies for cross-posting but as my query covers both Access and SQLServer I thought I'd send it both!I have inherited a project to migrate a fairly complex series ofAccess databases into a single proper SQL database with a web frontend.Its quite a nasty job as people are working on a variety of data setsat several Universities around the world and the data has got verymessy; hence the requirement to put it all on one live web enableddatabase server and provide a web-based front end (particularly assome users insist on using Macs so can't run Access as a front endanyway).If anyone could give me hints on how to perform such a migration or ifanyone knows of any good books or other documents on this I'd begrateful for assistance.Many thanksRich MayMuseum of London
View 9 Replies View Related
Dear Friends,
We have MS Access database with Forms and Reports, which was started 10 years ago by users and now the data is growing very rapidly.
Did anyone tried by having MS Access as front end and SQL Server 2000/2005 as backend with minimum modifications to the forms and reports in MS Access?
Please let me know, your ideas and if there are any links in the web or in Microsoft please provide here.
Thanks in advance,
I have a back-end front-end application in MS Access. Instead of using MS Access queries it gets data via recordsets generated from SQL scripts in VBA routines.
I'm planning to upgrade it to use SQL Server as the back-end. However I need to retain the alternative option of using a file based back-end. (It currently has the capacity to be switched between alternative Access .mdb back-end files.) Is there any information available on how to do this and on how to get Access to synchronize between SQL Server and SQL Server CE back-ends? (Eventually the application will be migrated to VB.NET, but that is a long way down the track.)
Hi,
I need some help in setting up a front end IIS box and back end SQL 2005 box. I will be using merge replication.
Front end server will be a 32 bit.
How exactly do you do this? Anyone have any links to articles that walks through the setup?
Thanks,
p
I am currently designing an adp front end that links to a Sql Server database.
In terms on front end upgrade prespective i am thinking about a logon routine procedure ( which should be a vba code code that runs at startup) to check wheter the adp front end which is being used is the latest release hence compatible with the database current version.
Create and manage version number at back end level is quiet easy however to set a version number or whatever else information in the adp is somewhat a big hassle as database properties are not available from visual basic when using adp.
Was also thinking about using my xml file which holds my configuration information such as Data source - catalog but that's mean deployment of new front end must be carried out with local xml file change.
Also any manual update of information about version in xml file could allow old front end to run with database.
Please any tips and advises on how to handle this issue highly appreciated.
Regards to everybody
Sir, Is there any way to insert picture to image datatype in sql server 2000 without using front end. If so please let me know. Thanks in Advance,Arun.
View 3 Replies View RelatedI just changed my Access 2002 database to a SQL Server ADP project. Ihad a form where the user entered a value into a text box and when acommand button on the form was clicked a Report was opened. The reportsrecord source is a query. The query uses the value from the form textbox to restrict the query.Table name = EggsTableone of the columns in the table is named: EggColorForm name = EggColorFormForm text box name = ColorTextBoxThis sql worked for the query before I converted to SQL:SELECT EggsTable.EggColorFROM EggsTableWHERE (((EggsTable.EggColor)=[Forms]![EggColorForm]![ColorTextBox]));This no longer works. Can I change the syntax somehow to get this towork? I tried dropping the brackets around the word "Forms", I trieddropping all the square brackets, etc., nothing worked.I also tried just opening the report with the report's Server Filterproperty set to:EggColor=N'Forms.EggColorForm.ColorTextBox'I tried using the Report's open event to pass the form value directlyto the report. I tried setting a variable from the text box value onthe form. So far, nothing works. Any ideas?
View 7 Replies View RelatedI am in the process of migrating 40 access databases to SQL Server and still use an access front end.All of these databases are Identical in structure and purpose. The goal is to centralize all of the databases backends into one Sql server database, but the data needs to
stay seperated by each location(user). I was able to add a location feild to all of the tables to keep the data seperate. Then I imported three of the locations data for testing. I would like to know how could I filter the data on the access frontend, so that it would ask the user for their location and only allow the user to access, update, and delete that Locations data ?
4 Layered Web Application for Inserting data into a database using sql server as the back end and a web form as the front end using C# .
Can someone provide with code as I am new to this architecture and framework.
Better send email.
Thanks In Advance,
A New Bie
We have a Customized share point application with Very minimal data usage and we have used only 5 to 6 lists and libraries only in the share point.
Configuration is
Clients -- fire wall --- Load Balancer ---- WF1 and WF2 --- SQL DB
ROUTING IS VIA FIRE WALL.
SUDDENLY THE SITE GOT DEAD SLOW AND UNABLE TO TRACE THE PROBLEM AS EVERY THING LOOKS FINE.
Checked with the firewall Team and they stated its fine from their end & even we have verified the counters, CPU, Memory & Page life expectancy, buffer counters all looks good and even we do not have huge data in the database. We have only 50 concurrent users are working...
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this:
SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89
The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.
Why?
I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.
If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?
Thanks,
Lee
I've created a small company database where the tables reside in a SQLServer database. I'm using Access 2000 forms for a front end.I've got a System DSN set-up to SQL Server and am using links withinAccess 2000 to get to the SQL Server tables.My forms worked fine until I made a few minor changes to the databaseschema on SQL Server (e.g. added a foreign key, or added a column).After that, all the links break - I click on a table link and get anerror msg like "invalid object name."Deleting the links after a schema change and re-adding the links seemedto fix the problem. The forms I'd already created seemed to work fineafter re-creating the links.But then I got more advanced with my forms. I have it set up so thatfor certain entry fields, the combobox gets populated with values froma table (the description appears in the drop-down and the correspondingprimary key value gets populated in the table). I created a number offorms using this technique, entered data, and everything worked fine.Made a small schema change and it broke everything -- not the actualtable links, but the functionality for the drop-downs. My values nolonger appeared, and this was true for forms that accessed tables whoseschemas did not change.This is driving me nuts. Is there any way to keep my forms frombreaking each time I make a small schema change?Thanks.- Dana
View 5 Replies View RelatedStarwin writes "when i execute DBCC CHECKDB, DBCC CHECKCATALOG
I reveived the following error.
how to solve it?
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -2093955965, index ID 711, page ID (3:2530). The PageId in the page header = (34443:343146507).
. . . .
. . . .
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1635188736)' (object ID -1635188736).
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1600811521)' (object ID -1600811521).
. . . .
. . . .
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -8748568, index ID 50307, page ID (3:2497). The PageId in the page header = (26707:762626875).
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -7615284, index ID 35836, page ID (3:2534). The PageId in the page heade"
I hvae a stored procedure that has this at the end of it:
BEGIN
EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
Hi,I have a employee attendance table, and Im trying to return the dates on which an employee is absent. There are no table entries for an employee who is absent on a particular day. So can anyone please help on how i can return the dates on which an employee is absent. Any help will be greatly appreciated, Thanks,Najla.
View 5 Replies View RelatedA problem: I created some user defined roles in SQL SERVER 2000 and assigned users to different roles. Can you help me with a way (stored procedure etc.) to return the correct roles of my logged in users to my ASP.NET application.
View 1 Replies View RelatedI have a table tbl_rules. This table will define rules for each role. I have not yet defined the fields for the rules. But the table definition is as below:
Create table tbl_Rules
(
ID int identity(1,1) not null,
Role_ID int not null,
primary key (ID),
constraint fk_RoleName foreign key (Role_ID)
references tbl_Role(ID)
)
The table tbl_role has two columns as below:
ID RoleName
1 Manager
2 Analyst
3 Admin
So far so good. I created the tbl_rules.
But what i want to do is when I do select * from tbl_Rules, i want to show as below:
ID Role_ID
1 Manager
2 Admin
Instead it shows:
ID Role_ID
1 1
2 3
Is there a way to do this? The goal is to return the select * from tbl_Rules results to a gridview to enable adds and changes. I could do this by doing queries for each column, but I was hoping to make it easier. Not sure if this is even possible.
Our software vendors upgraded their software and our environment - and applications do not interface as they ought. The vendors do not appear to have any further ideas (to those listed below) and at this stage neither do I. It is now a week since the *upgrade* went live. Looking for new suggestions....
Full story (epic) below. Object names have been changed to protect the *innocent*.
Our organisation had 2 applications:-
appBank
appHouse
both running on the respective platforms:
Server name svrCAT:
Windows Server 2000 sp 4 / SQL Server 2000
Server name svrDOG:
Windows Server 2000 sp 4 / SQL Server 2000
Transactions were "posted" from svrDOG to svrCAT via a linked SQL Server, using login dbL1nkLogin.
dbL1nkLogin was made a user of (with relevant table permissions) of the respective databases:
dbForBank
dbForHouse
*** At this point all was working ***
Upgrading appBank to appBigBank required SQL Server 2005 (and more diskspace).
A new server was purchased:
Windows Server 2003 R2 sp 2 / SQL Server 2005 (Server name svrHORSE)
A linked server was created from the SQL instance on svrHORSE to svrDOG using login name dbL1nkLogin.
dbL1nkLogin was made a user (with relevant table permissions) on database:
dbForBigBank
Attempts to "post" transactions were "posted" from svrDOG to svrHORSE via the linked SQL Server fail with
the appBank application generating the following errors:
On first attempt:
************* FILE ERROR *************
Error Code...... 3902-
Error Message...The COMMIT TRANSACTION request has no co
.rresponding BEGIN TRANSACTION.
Table Name......** NOT APPLICABLE **
Description.....** NOT APPLICABLE **
Function....... COMMIT
sys991: Program already in use
COBOL error at 000444 in sys500
Called from 00076A in sys990ssv
Called from 000457 in sys991
Called from 01177D in gls489
Called from 000DFA in gls396
Called from 003961 in gls394
Called from 000258 in glp900
Called from 003DC1 in syp250
On subsequent attempts:
************* FILE ERROR *************
Error Code...... 1206-
Error Message...The Microsoft Distributed Transaction Co
.ordinator (MS DTC) has cancell
Table Name......tblHouseFinancials
Description.....DYNAMIC SQL CALL INTERLUDE
Function....... UPDATE
*FAILED* Processing Job 'WIN/'
* Parameters and controls within the appBank application were verified.
* Password on dbL1nkLogin was reset in the SQL instances on svrHORSE and svrDOG
* Compared the link server settings on svrHORSE to svrCAT:
Security tab: Be made using security context - Remote Logging: dbL1nkLogin / password
Server Options: Collation Compatible checked (on svrHORSE only)
Data Access checked
RPC checked
RPC Out checked
* Made dbL1nkLogin dbo on databases dbForBigBank and dbForHouse.
* Changed default database from master to dbForBigBank on svrHORSE
* Rebooted svrHORSE and svrDOG (multiple times)
* Verified that svrHORSE could ping svrDOG, and vice versa
* Using DTCping.exe, Verified that the SQL instance on svrHORSE could ping svrDOG, and vice versa
* On svrHORSE, Administrative Tools -> Component Services -> Computers has the following settings for "My Computer" Network DTC Access:
Network DTC Access - checked
Allow Remote Clients - checked
Allow Remote Administration - checked
Allow Inbound - checked
Allow Outbound - checked
No Authentication required - checked
Enable XA Transactions - checked
DTC Logon Account - NT AuthorityNetworkService
These settings were already set. My understanding of KB329332 suggest they are correct. As I could not establish the configuration date of these settings rebooted the server to ensure they'd taken effect.
* Loaded SQL Server 2000 sp4 on svrDOG (Rebooted afterwards in case services not stopped and started correctly)
srvDOG now has MDAC 2.8 sp1 (2.81.1117.6) while srvHORSE has a later version (2.82.3959)- I assume sp2. While they are not the same version, MDAC 2.8 sp2 KB 231943 advises that 2.8 sp2 is not available via the web - so I think it's the best I can do).
* Stopped and manually started MSDTC and SQL Server (via Administrative Tools - Services) in case MSDTC was not being started first.
* KB873160 suggests opening port 135 and adding msdtc.exe as an exception in the Windows Firewall. At present there is no firewall on svrHORSE - which I would assume negates this necessity (at present). In Administration Tools - Services there was a service for the firewall running. Stopping this service has no effect on this issue.
* ( The svrHORSE machine was not created from an ghosted image ).
** None of these things have resolved the issue. **
.... to be continued...
KD
Sorry for the lengthy question, but here goes:
I currently have an access database with a FE and BE that resides on each users laptop. Weekly they connect to the network and synch the BE the master on a network drive. There are numerous propblems with this as it has grown quite extensive.
We would like to have this be a server based database with the remote users either having access through the web or something else?
A few of the capabilities of the current database:
1. generate and print reports 2. update project forms. 3. enter time logs 4. enter notes
My question is:
What are my options for a remote FE? I am well versed in VB6 and Access. Would be willing to learn other languages (VB.net, my SQL, etc.) Would I be able to have the same functionality or would I have to have separate software for certain functions?
I have a stored procedure that can take up to 2 hours to run, has anyone any ideas on what front end would be best used to call it
View 3 Replies View RelatedHi,
If I don't have any visual studio design tools etc.
Is there any way I can build a simple form that will allow users to view, insert, update and delete single rows from a SQL 2005 database?
Or is there any free downloads that might help. the table is really simple and has only 4 fields
TIA
Hello,
I am new to SQL Server. I currently have an Access 2007 database and next year my client would like to migrate to SQL Server. Which Front End is the best...Access, VB.net or some other platform altogether?
Thanks,
M. Wilson