Moving From Access To A Web Based Approach
Hi all.
I am not a programer but managed to piece together an Access database that is the backbone of my company.
My IT group advised me to move to an SQL server back end and move to a web based front end.
My question. I am very comfortible with Access and modify the databases regularly. What front end is most like Access for modifying forms, reports, macros, etc.
Thanks!
View Complete Forum Thread with Replies
Related Forum Messages:
Can A Set Based Approach Help Me Here...
im practicing set based approaches... and what im trying to do is grab each value from a table , scramble it and put it back in the table... i dont want the solution to this as id rather figure it out myself for practice... the thing im stuck at is i can do this with a cursor but i want to avoid cursors in future, how would i use a set based approach to get each value of a table and work with it?
View Replies !
Cursor, Set Based Approach
I am replacing cursor logic in a SP to a setbased approach to scale better. My setbased approach seems to be better but it runs very fractionaly faster (execution time) than the cursor approach for a single run in test environment. I think resource cost wise, my set based approach should be better. Number of rows iterated thru this cursor is small (0-150). This particular SP is called over 2000 times in production everyday. Is it worth the trouble changing this if we get only marginally benefits, will my set-based approach work better on a server that has lot of activity (lot of connections etc). Our db server runs at about 75-85% cpu usage daily and this particular SP accounts to 13% CPU usage for 2000+ executions. If the data set involved in cursors is small, is it worth the trouble changing them to set based approaches? Am I doing right to change this SP to setbased approach.
View Replies !
What's The Best Approach To Time-based Checking From App. To SQL Server?
Hi I have an VB.NET application connected to a SQL Server Express. I want to let the application to run in either "Normal mode" or "Holiday mode" according to current weekday is normal day or state public holiday. My approach is to find out all the public holidays in a year and enter them into a Holiday Table. Then some code in my application constantly check the current weekday against the one in the holiday table, if matches, the application goes into holiday mode. This approach is not perfect as "State public holidays" are confirmed by the state government in the current year and the coming year. So state public holidays are unconfirmed for the third year afterward. The system is required by client to support public holiday in the next 10 years. I wonder what is the best approach to this problem? Thanks
View Replies !
Best Approach For Pushing Records To MS Access
All, I am new to DTS/SSIS and have a couple of questions about using it to solve a problem. We have an application running on SQL Server 2005 where status records are written to a status table. I need to be able to send those records over to a status table in a legacy application running on Access. Originally, I thought about writing a custom c# stored proc and accessing Access from it and then someone pointed me to DTS/SSIS. Is there a way to exectute the package based on a trigger event that a row was inserted or updated? If not and I take a scheduled approach (every 3 minutes, etc.) do I have to maintain a column for the records that get processed so they are not picked up again. In general is using SSIS the approach to take? The overall business requirements are straight forward, but I am not sure if SSIS is overkill for this or not. Thanks, Steve
View Replies !
Query Data Based On Moving Datetime.
I'm trying to query data from a database for a report that looks for the last 2 weeks starting at 10pm, taking a value once every 24 hours. Using AND DateTime >= DateAdd(wk,-2,GETDATE()) AND DateTime <= GetDate()") I easily get the last two weeks but the query obviously only grabs the data at the time the query runs. I need to be able to run it any time of the day but only grab the data at 10pm. I'm very new at this so please excuse my ignorance but I could really use some help with this. Thanks very much.
View Replies !
Sql Server Express Problem When Moving From VWD File Based To IIS
Hi All, We have a file based asp.net app built using Visual Web Developer and Sql Server Express 2005. We have finished development and testing and are now moving to the deployment stage. As a first step, we would like to be able to view it on a test machine using IIS (instead of VWDs built in web server). We have created a virtual directory in IIS and can view our app correctly at http:localhost/ForIIS_test. However, when we get to a page that tries to access our Sql Sever Express database, we get the following error: An attempt to attach an auto-named database for file C:Documents and SettingsClaudeMy DocumentsVisual Studio 2005WebSitesForIIS_testApp_Data estDatastore.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Does anyone know how to overcome this problem? Any help appreciated. Claude.
View Replies !
Moving Average Using Select Statement Or Cursor Based?
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE) 1 01/01/2007 PURCHASE 10 1000 2 01/01/2007 PURCHAES 5 1100 3 01/01/2007 SALES -5 *TobeCalculated 4 02/01/2007 Purchase 20 9000 5 02/01/2007 SALES -10 *TobeCalculated 5 02/01/2007 purchase 50 8000 6 03/01/2007 Sales -10 *TobeCalculate 7 01/01/2007 Purchase 20 12000 I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here) In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly. When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly. The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated. Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time. Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View Replies !
HELP MS Access Moving To SQL And Don't Know How
I have been using MS Access for a long time. The last couple of months my sites have really picked up in business (good rankings) and we keep crashing the server (No sites on the server that use MS Access will run) We get the errorSystem.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider()at System.Data.OleDb.OleDbConnection.Open()at DreamweaverCtrls.DataSet.DoInit()Also I am finding out from some of my customers that their sites they can't use at times because of the error but the server hasn't crashed yet because others are up. Well I believe we are exceeding MS Access very badly because it crashes the server daily now and sometimes more then once a day. So I am going to start moving my MS Access database to SQL but have NO CLUE how where to start etc or use SQL correctly. If people can point me in the right direction that would be very helpful. Everything I find online is people needing help but no solutions that work for me. My new server is a windows server running ASP.Net 2.0 and to edit my SQL I have "ASP.Net Enterprise Manager" and Plesk for the server.1. A good book to follow and easy to follow for SQL (connections / how tos) or website2. Good examples or book on "ASP.Net Enterprise Manager"3. How to convert access databases into SQL easy (some have over 50,000 listings)4. how to convert Tab files into SQL easy.Thank you so much for any help. Also I am using Dreamweaver for most of the edditing and connections. I know its not your favorite but I don't have the time to write everything by hand and it works good for what I do on the websites.Thanks again,Rusty
View Replies !
Moving From MS SQL To Access
I have A MS SQL 9.0 Database. The database contains Tables, Views and Stored Procedures. I have done some reading and it appears that I cannot import views or Stored Procedures into Access. I am only looking for a way to import the table database. Please let me know if I can help.
View Replies !
Moving From MS Access To SQL
Our Branch uses an Access Database, but we're thinking of rolling it out to our entire organization. I've just spent the day watching about 7 tutorials on SQL 2005, but they only use Management Studio Express in the tutorials, and before I go any further, I'd like to make sure I'm going down the right road. Am I better using the Upsizing Wizard in Access to convert our DB to SQL? Once I've done that, what's the best way to get the rest of the DB on the Web so that our entire organization nationwide can use it? Do I need to learn Visual Studio or something so that I can recreate all our Forms & Reports? Do I even need to do that? Is there a conversion process? Sorry for being so vague, but I would just like to know what is the best way forward. What do most people do when they want to move an Access DB to SQL and make it available outside of their LAN? Thanks in advance for any advice anyoe can provide.
View Replies !
HELP MS Access Moving To SQL And Don't Know How
I have been using MS Access for a long time. The last couple of months my sites have really picked up in business (good rankings) and we keep crashing the server (No sites on the server that use MS Access will run) We get the error System.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider() at System.Data.OleDb.OleDbConnection.Open() at DreamweaverCtrls.DataSet.DoInit() Also I am finding out from some of my customers that their sites they can't use at times because of the error but the server hasn't crashed yet because others are up. Well I believe we are exceeding MS Access very badly because it crashes the server daily now and sometimes more then once a day. So I am going to start moving my MS Access database to SQL but have NO CLUE how where to start etc or use SQL correctly. If people can point me in the right direction that would be very helpful. Everything I find online is people needing help but no solutions that work for me. My new server is a windows server running ASP.Net 2.0 and to edit my SQL I have "ASP.Net Enterprise Manager" and Plesk for the server. 1. A good book to follow and easy to follow for SQL (connections / how tos) or website 2. Good examples or book on "ASP.Net Enterprise Manager" 3. How to convert access databases into SQL easy (some have over 50,000 listings) 4. how to convert Tab files into SQL easy. Thank you so much for any help. Also I am using Dreamweaver for most of the edditing and connections. I know its not your favorite but I don't have the time to write everything by hand and it works good for what I do on the websites. I am afraid if I hurry to much I will build it wrong and really pay for it.. I am paying enough from using MS Access.. LOL Thanks again, Rusty
View Replies !
Moving Access 2002 Db To MS SQL
Hey guys, I was just trying to get some information on how to move an Access 2002 db to MS SQL. Also, what all do I need to be able to access the db from the web. For instance, query the db for info from a website. I don't have that much experience in SQL but I quiet a few programming languages so i'm sure i'll learn it quick, I usually do. Right now I have a server set up with MS SQL server 2005 with all the other things required for testing over in my own little world. DNS, Active Dir., etc.... I'm trying to do this because a friend of a friend has a business(small businessish) who wants to be able to view reports from queries over the internet. And i'm sure I can do it but i told them to let me give it a test run to make sure I can before we commit. Thanks in advance.
View Replies !
Need Help Moving Access Database Into MSDE
I'm tried to move an Access 2002 *.mdb into MSDE using the Access Upsizing Wizard, and that is not working as I expected and I'm not sure how to work with the Access project the Wizard returns.I've Google'd this to death and can't find reference materials that help me understand the correct procedure to replace Access databases with MSDE databases.Any help or advice or links that might help me understand this would be hugely appreciated.Thanks,Tinker
View Replies !
Moving From Access To Sql, Frontend Question.
My company is currently using access to manage equipment in 4-5 different locations. I want to move this to a sql database and have a front-end to do the same thing access is doing now. Should I use access as a front-end or should I develop a custom front end using vb? Just a fyi 5-7 tables 7-9 queries 10 or so reports and the front end is currently a switchboard that links to many other forms. I'm looking for advice. TIA
View Replies !
Moving From Access To SQL Server 2000
Hi there everyone, I have written a database system which tracks the performance of working in a shipping company in access. Im now rewriting the system in sqlserver and the only real problem I have found so far is that its difficult to estimate what kind of a server *cpu* & *ram* would be appropraite. The system currently performs transaction on my desktop machine in a second a quickest and 2 at slowest. There are going to be about 500 users in 3 time zones so there will only really be about 300 max hitting the system in an hour. I was looking at a Dell Poweredge server with twin P3's and half a Gb or ram would this be a good place to start from? Any Advice would be great!!! Steve
View Replies !
Moving From Access To SQL Server 2005
Hi All, I'm new to SQL server. Basically, I am trying to make my Access database accessible on line to my users, so I am transfering it into SQL Server and then writing a web site to use the connection to the database. My questions are as follows: 1) How can I easily move the structure of the tables (relationships too, but content is not important) from Access into SQL? 2) I have the following query in Access which I don't believe will work in SQL because of the Sum funtion... How can I change this to work in SQL?: SELECT DISTINCTROW Player.TeamID, Player.PlayerID, Player.SFD, Sum(Payments.Amount) AS Payment, [SFD]-[Payment] AS Due FROM Player INNER JOIN Payments ON Player.PlayerID = Payments.PlayerID GROUP BY Player.TeamID, Player.PlayerID, Player.SFD; All help will be greatfully received... thanks "In the face of adversity, I stand on the shoulders of giants..."
View Replies !
Help Understand Logic Of Moving From Access To SQL
Hi all, Please, I need some help understanding what I need to do. I'm working with text files and they're too much for Access to handle. The logical conclusion is to use something more robust like SQL. I'm having trouble understanding how it would all fit together, and I'm looking for guidance. First of all, what do you think is a logical approach to this problem: I get 6 .txt files delivered to our webserver via ftp. Every night there's an update, so they overwrite. I need to be able to display that data on the web page and I thought I could use Access to do it. Well, it won't work, so then there's SQL. In my reading today, I find out that SQL isn't an environment like Access, it's a language. I'm assuming that means I can't just import all of this data into an SQL db, get it on the webserver, and then start running queries against it like I can in Access, right? If one of you more experienced users has any ideas, please share. How can I use SQL to search the data in these .txt files? Included with each text file is a .dic file containing field names, data types, and length. What is the best approach. Step by step would be wonderful as I am very new to this. I have only ever worked with SQL queries and Access db. Thank you for any help you can offer. Carrie
View Replies !
Security With Web-based Access
First of all, I have never done any web-based stuff, so if thefollowing sounds ignorant, it's because I am!So far all our SQL Servers are accessed only over our network and weuse Windows authentication. Now the guy I'm working with on thedesign of our next stuff wants the two new databases (a transactionalone and my data warehouse) to be additionally accessed by web-basedapplications via our company intranet (NOT THE INTERNET). How do weauthenticate under these conditions? The webserver machine will betalking to the SQL Server ones, i.e. the databases will each be on thetheir own separate boxes. Can the webserver be a "user"? If so,and we want the actual users to have different privileges, then theweb-based apps have to manage that? Or is there a way for theweb-based apps to grab the Windows user and pass it to SQL Server?
View Replies !
Time Based Access Rights...
Hi All, Is there a way in SQL Server 7.0 or 2000, where I can grant/deny/revoke access rights on a database objects like Table, Stored Procedures for a particular time of the day. Example: I want to prevent user A from acessing Table x and Stored Procedure Y from 9Am to 12 noon everyday. After 12 Noon till 8.59 AM he can have access to Table x and Stored Procedure Y. Is there a way to do this at SQL Server level. Thanks Sri
View Replies !
Auto-increment Fields When Moving A Base From Access To SQL Server
Hello all,I'm a total newbie with SQL Server 2000 and I have a little problem whenmoving a database form Access 2000 to SQL Server 2000.In the Access database, each table has an auto-increment field.After importing the tables in SQL Server, all the auto-increment fieldsare turned into "int" type fields.Does anybody have an explanation for that mystery?Thanks in advance,Yan
View Replies !
Moving : Access---SQL Server 2005(Enterprise Edition) VB6—VB.Net
I am a new in .Net Environment. I am moving from VB to VB.Net and Access DB to SQL Server 2005. Please reply me the following questions bellow. Access Works SQL SERVER (SS)? When I create .MSI file it include ADO library in that executable file and my client install software and don€™t need any kind of file to install and wherever my program install it can be accessed by using following connection string. Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "dbPAYROLL_DB.mdb;Persist Security Info=False" Con.Open What file needed on client€™s PC to access SS on server. What about connection string change dynamically in client sides I create relation on Access Relationship Diagram. Where to create these diagrams either on VB.Net Server Explorer or on Management Studio? And how? Please answers me this basic questions further I have more question in mind but please first answers me these questions€¦
View Replies !
How To Access Ms-sqlserver From A Linux Based Computer
I need to access a MS-SQLSERVER with a Linux (SuSE 9.0) based computer.I need to do some selects.What do I have to do what kind of client Software and how do I have toinstall on the linux Maschine to comunicate with this Server?Probably some kind of odbc?thanx for any hints.Maximilian Gablinger
View Replies !
SQL Server Does Not Exist Or Access Denied. Error While Moving App From Winxp To Win2003
hi, I have an asp.net application which queries an sql server in some other domain and populates a grid with the results. i am using sql server authentication and my connection string is as follows:- Dim connectionString As String = "server=emr01; user id='private'; password='private'; database='NGEMRDev'" i have also used the following tag in web.config:- <identity impersonate="true" /> This works fine on my development pc i.e windows xp with sp2 . but when i tried running the same application in windows 2003 the query gives the following exception:- Server Error in '/TempDel' Application. SQL Server does not exist or access denied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.Source Error: Line 77: dataAdapter.SelectCommand = dbCommand Line 78: Dim dataSet As System.Data.DataSet = New System.Data.DataSet Line 79: dataAdapter.Fill(dataSet) Line 80: Line 81: Return dataSetSource File: c:inetpubwwwroot empdelemrtempdel.aspx Line: 79 Stack Trace: [SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44 System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38 ASP.EmrTempDel_aspx.MyQueryMethod(String username) in c:inetpubwwwroot empdelemrtempdel.aspx:79 ASP.EmrTempDel_aspx.Button2_Click(Object sender, EventArgs e) in c:inetpubwwwroot empdelemrtempdel.aspx:89 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +58 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292 Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032 Please help me as to how do i successfully implement my application in the windows 2003 server. i have given the aspnet user on the win2003 admin rights.
View Replies !
Updating Tables In SQL Server 7 Based On Access 97 Tables
Let me start by saying I'm very new to SQL Server... I've upsized my Access Database to SQL Server successfully, but need to keep updating my SQL Server Database with data in my Access 97 database. For example, a table in my Access Database is updated on a regular basis and at certain times, we want to upload that information to our SQL Server database. How can I easily overwrite data in a SQL Server table with data imported from Access 97? I keep getting error messages about the primary/foreign keys... Any help would be greatly appreciated. Glenn
View Replies !
Best Approach With DTS
Let me see if I can explain this. I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB. Step 1. The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working. Step 2 The next tables data needs to be limited from the data retrieved in step 1 (I’d like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side. Step 3 The returned rows here, need to be limited to key values returned from step 2 Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step. What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step. I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc. What is the best approach?
View Replies !
Best Approach
I have what I feel like is a simple package I am working to create. I am teaching myself SSIS as I go along. Source server SQL 2000 database allows NULL values in columns. Destination Server also SQL 2000 but the database required a value in each column. So I do a basic source select what I want. I next need to read the values and determine if null then insert a space, do some column matching and insert them into the destination sever. I believe I should use a Derived Column and an expression ISNULL to accomplish what I want. Maybe there is a better way. Suggestion and comment appreciated. Ryan
View Replies !
Help With Using The Right Approach
Greetings my friends I am attempting to solve the following problem using SSIS, actually I am attempting to convert a SQL Server 2000 DTS package in to a SSIS package. The package does the following : 1) Retrieve the maximum Price_ID (PK) from a PRICE dimension table. 2) Populate a staging table with data coming from a source system where the PRICE_ID > (Price_ID from above) 3) Update the actual DIM table with the new data help in the staging table. For this task I want to learn the use of the Lookup component which I think is appropriate. My questions are as follows : If I create a global variable to hold the maximum PRICE_ID (see point 1). How do I get to use the variable in the my Data Flow Data source?! I am totally confused... I don't even know where to start with this. Your help would be appreciated. Thanks SQL friends.
View Replies !
Best Approach
I've been banging my head for a while now, and it is sore! :-P I'm a best practice/Microsoft approach type of person and want to make sure I do things correctly. I have a database, kind of like a forum. Obviously executing multiple queries in one "batch" (stored proc) would have an impact on the performance. Now, I would like to give a more detailed/specific error back to the caller (either by aid of error code or whatever) with such situations like... "EditReply" Edit reply takes the threadID, replyID and userID. Before actually commiting the changes, it needs to check: 1) does the user exist in the database? (during the editing of the reply, perhaps the user may have been deleted before running the stored proc, who knows) 2) does the thread exist? 3) does the reply exist? if the conditions are met, only then will it go ahead and update the database. Now that is 3 queries, and 4 statements overall to make a change to a field/table. Obviously if one of the commands returns false, in other words if say "does the thread exist" returns 0 (thread doesnt exist) it will return back to the caller an errorcode, which they will handle in their application. Thats all fine but the question is Am I doing this correctly? (no) - how can I improve this? What do I need to think about? Of course I would like to give a more detailed error back to the caller (aid of errorcode designed in the application overall) instead of just "no, databases not updated". In this situation, am I wrongly assuming that the database designers use this type of approach? Please help, I value your feedback and suggestions. I want to improve and think of the right lines of doing these things.
View Replies !
Appropriate MDX Approach ???
Hello all. I use MDX on a cube which provides data about animal population. The cube contains the keyfigure "ANIMALS" that takes the number of animals. The cube has a dimension "VERSION" which is used to identify the keyfigure as a target or an actual value (possible values: "actual" and "target") The cube has another dimension "ZONE" for the population zones. Possible values for zones: "A", "B", "C" and "D". Now I want to create an MDX statement, that gives me a result row like this: Actual number of animals (as sum of all 4 zones) in column no. 1, Target number of animals (as sum of all 4 zones) in column no. 2, Achieved percentage (as actual number / target number * 100) in column no. 3. Until here my statement works and it looks like this: WITH MEMBER [VERSION].[achieved] AS '[VERSION].[actual] / [VERSION].[target] * 100' SELECT {[VERSION].[actual], [VERSION].[target], [VERSION].[achieved]} on COLUMNS FROM [$MYCUBE] WHERE ([Measures].[ANIMALS]) It surely is possible that the achieved value for all zones together is equal to or greater than 100%, while single zones might have an achieved values less than 100%. In order to account on this, i would like column no. 4 to display one of these words: "ok" if none of the single zones has an achieved value smaller than 100%, "warning" if any of the single zones has an achieved value between 96 and 99%, "alert" if any of the single zones has an achieved value smaller than 95%. That means, i want e.g. the word "yellow" if the lowest achieved value of the 4 zones is between 96 and 99. I want to have "red" if the lowest value is smaller than 95. I am quite new to MDX and I have struggled quite a long time with this. I would be grateful for a hint on how i have to modify / enhance my MDX statement. Regards. Peter.
View Replies !
Best Way To Approach This
I've been working on this project, and had it working in MySQL, but it was badly done and couldn't last more than a few hours without growing so large that everything slowed way down. I don't expect anyone to tell me exactly what to do, just please provide an outline of what the best way to approach this in SQL Server 2005 is. To simplify it, I have one table "Items" and another table "ItemPrices". Items has an id and a name. Each row in ItemPrices has an id for the item, a price and two datestamps (added, last updated). On average, there's about 15,000 active items, 50% of them have new prices every couple minutes, so I'm looking at what seems like a ton of data being constantly imported. There's probably a good way to do this but I only know the bad way :) So.. what I want to be able to do is have maybe a stored procedure (?) that takes the item name and price as parameters. (In MySQL I was using "INSERT... ON DUPLICATE KEY UPDATE") A. If it's a new item name, it will add a row to the Items table and a row to ItemPrices B. If it's an existing item with the same price as the current price (the most recent price for that item in ItemPrices) it will update the "last updated" date field C. If it's a new price it will insert a row into ItemPrices for that item Also, I want historical pricing data, but if I ever release this, 95% of the users will just be looking at current prices. I need the current prices to be very fast to query, in my MySQL version I was using something like this: "SELECT... join on lastupdated=(SELECT Max(lastupdated) FROM ItemPrices ...", after I had 300k price updates querying a list of items took like 15 seconds.. there's got to be a better way? What should I do to make this faster? Does this make any sense? Hopefully someone can lead me in the right direction. Thank you very much!
View Replies !
SQL OPENXML: Best Approach
Hello everyone. I am new to.Net and here is what I have to do. I needto update a SQL table with data coming from a XML file. I have seen some Microsoft documentation on this (the nice SQL statement that updates and inserts in the same stored procedure) but I don'tknow what is the best approach for passing my XML file to the stored procedure. The XML contains about 12 000 records, kind of phonebook info (name, email, phone). What would be the best approach to do this? What objects should I use? Thanks a million, Ben
View Replies !
SqlBulkInsert ?? Or Better Approach?
Hello, I'm doing my best here, but need some help. I have a client that has a company list that they want searched by key word. This is exported from another program (in excel) that they want used and searched on their website. Bad news, is each Keyword is listed with the company separately. So if a company has 5 different key words, they will be listed in the excel file 5 times. The info I have is Name, Address, City, State, Phone, Keywords: So example of excel is:Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Green Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Furry Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Large Company A, 123 Main St, Mycity, Mystate, 123-123-1234, Circular Company B, 746 Sparrow Ave, Diffcity, Diffstate, 987-987-9876, Blue Company B, 746 Sparrow Ave, Diffcity, Diffstate, 987-987-9876, Furry Company B, 746 Sparrow Ave, Diffcity, Diffstate, 987-987-9876, Small I am able to import this large (4.2 MB) file into a table called fctable What i'm trying to do is write SQL scripts or queries that can insert into a Company Table and Keyword Table. I'm trying to write this through asp.net 2.0 (so the excel file is uploaded) and have tried to write my inserts like INSERT INTO [Company] ([Name], [address], [City], [State], [Phone]) VALUES (SELECT DISTINCT Name, Address, City, State, Phone FROM fctable ) But that doesn't seem to be working. This is the only way my client can get the info to me, and it will be changed probably twice per month, so I'd hate to have to try to manipulate an excel file 24 times a year to import. Any suggestions Appreciated
View Replies !
Set Approach Instead Of Cursor
Hi,I am trying a Set Approach instead of Using of Cursor (which works).I am attaching the SQL to create tables and the my Procedure, and apiece of code to execute the Procedure.I would like the Procedure ReplaceTags to work with 'a' the same aswith 'C'.Thanks in advance.Hareesh/*****************************//* Create Tables */IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =OBJECT_ID(N'GlobalTags') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)DROP TABLE GlobalTagsGOCREATE TABLE GlobalTags(Project VARCHAR(50) NULL,TagName VARCHAR(50) NULL,[Value] VARCHAR(50) NULL)GOIF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =OBJECT_ID(N'ProductDetails') and OBJECTPROPERTY(id, N'IsUserTable') =1)DROP TABLE ProductDetailsGOCREATE TABLE ProductDetails(Project VARCHAR(50) NULL,KeyName VARCHAR(50) NULL,[Value] VARCHAR(50) NULL)GO/*********************************//* Populate Tables */TRUNCATE TABLE GlobalTagsINSERT INTO GlobalTags (Project, TagName, Value)VALUES('ProjectName', 'FirstName', 'John')INSERT INTO GlobalTags (Project, TagName, Value)VALUES('ProjectName', 'LastName', 'Doe')INSERT INTO GlobalTags (Project, TagName, Value)VALUES('ProjectName', 'PhoneNo', '5248')INSERT INTO GlobalTags (Project, TagName, Value)VALUES('ProjectName', 'ZIPCode', '55555')TRUNCATE TABLE ProductDetailsINSERT INTO ProductDetails (Project, KeyName, Value)VALUES('ProjectName', 'FirstName', '%FirstName%')INSERT INTO ProductDetails (Project, KeyName, Value)VALUES('ProjectName', 'LastName', '%LastName%')INSERT INTO ProductDetails (Project, KeyName, Value)VALUES('ProjectName', 'PhoneNo', '%PhoneNo%')INSERT INTO ProductDetails (Project, KeyName, Value)VALUES('ProjectName', 'ZIPCode', '%ZIPCode%')/****************************//* Procedure */IF EXISTS (SELECT * FROM sysobjects WHERE name = 'ReplaceTags')DROP PROCEDURE ReplaceTagsGOCREATE PROCEDURE ReplaceTags(@aProjectName VARCHAR(50),@aProcessType CHAR(1))ASBEGINDECLARE @TagName VARCHAR(50)DECLARE @Value VARCHAR(50)IF @aProcessType = 'C'BEGINDECLARE REPLACE_CURSOR CURSOR FAST_FORWARD READ_ONLY FORSELECT TagName, Value FROM GlobalTagsWHERE Project = @aProjectNameOPEN REPLACE_CURSORFETCH NEXT FROM REPLACE_CURSOR INTO @TagName, @ValueWHILE (@@FETCH_STATUS = 0)BEGINUPDATE ProductDetailsSETValue =CASE WHEN CHARINDEX('%' + @TagName + '%', Value, 1) > 0THENREPLACE(Value, '%' + @TagName + '%', @Value)ELSEValueENDWHERE Project = @aProjectNameFETCH NEXT FROM REPLACE_CURSOR INTO @TagName, @ValueENDCLOSE REPLACE_CURSORDEALLOCATE REPLACE_CURSORENDELSEBEGINUPDATE ProductDetailsSETValue =CASE WHEN CHARINDEX('%' + GlobalTags.TagName + '%',ProductDetails.Value, 1) > 0 THENREPLACE(ProductDetails.Value, '%' +GlobalTags.TagName + '%', GlobalTags.Value)ELSEProductDetails.ValueENDFROM ProductDetails INNER JOIN GlobalTagsON (ProductDetails.Project = GlobalTags.Project)WHERE ProductDetails.Project = @aProjectNameENDEND/***********************************//* Run Procedure */EXECUTE ReplaceTags 'ProjectName', 'a'-- EXECUTE ReplaceTags 'ProjectName', 'C'SELECT * FROM GlobalTagsSELECT * FROM ProductDetails/* End*//***********************************/
View Replies !
What Is The Best Way To Approach Setup?
Guys/Gals, I am in the throws of developing a Sales & Marketing application. Here is the general overview of what I want to acheive. I am looking for the best way to setup the database and connections for optimal performance. I have to write an application, (VB front-end), using SQL Server. I have the following site settings to worry about. I have 15-20 workstations on an NT LAN network. Most of these are desktops, but some are laptops which are plugged in the when they are in the office, but may also need to access the application remotely when they are out on the road, (they are salesmen). I also have head office needing to access the application. They will have 20-30 users maybe needing access. Some of these users will also need to look at the application remotely. We are connected via a frame relay that handles our AS400 (JDEdwards) and Lotus Notes traffic at the moment. I will also have another two branches but not sure of the configuration for them yet. I was thinking of replication to the branches so as not to tie up the frame relay during the day but was not sure of the remote machines. How to handle via dialup? If people need some more clarification, please contact me via email. Thanks in advance, Shane.
View Replies !
MS SQL Server 7.0 And Approach
Environment NT Server 4.0 w/ SP4 SQL Server 7.0 w/ SP1 Win98 Client w/ Lotus Approach 9.5 I recently added SQL 7.0 to be a back end for my Approach front end. I transferred all the data from a dbase IV in approach to SQL. Most of the conversions worked ok. I have two big problems. 1) One particular repeat panel in Approach loses the children records of the master record. If I delete some of the records, more will appear. It's as if there is an imaginary limit of the number of records it can read in the repeat panel. I don't have this problem with any other records and children in repeat panels. I called Lotus and they don't have an answer. This is important because the children records need to be summed up so I can have a running total. 2) I original configured the clients to use the TCP/IP Netlib w/ the default port. I couldn't open enough databases so I changed to Multiprotocol. This allowed certain clients to open more databases, but others can't open additional databases. Also, after the change, the NT authentication login has had problems. I had to change to the SQL login to get all my clients back on line. Sometimes the same client can't open more than 10 databases while other times it will open 15. There is no consistent patten to when it can and can't open the additional databases. If anyone knows how to fix either of these problems, I would greatly appreciate any advice. I'm getting tired of my boss yelling at me. Thanks. Keith akumaboyz@aol.com
View Replies !
Should I Use A Trigger Or A Different Approach?
Lets say I have a dynamic table of 10 to 30 varchar(255) columns that I do not know the names of. Each of these columns is updated with either an 'OK' or an error msg. Here's the issue. I have a Status column (varchar(MAX)) which I want to be the concatenated sum of all the other columns. I'd like to do this via a trigger because the way I understand it, theres no way a trigger can skip over another update/trigger and write the wrong 'Status' value. Example Row 1: RandomColName1 = "1||Access Denied" RandomColName2 = "0||OK" RandomColName3 = "2||ID10T Error at Kbrd" Status = "[RandomColName1] Access Denied | [RandomColName3] = "ID10T Error at Kbrd" Example Row 2: RandomColName1 = "0||OK" RandomColName2 = "0||OK" RandomColName3 = "0||OK" Status = "0||OK" The rules: 1. There is no chance of someone updating the same column at the same time. 2. There's a good chance that other columns in the same row will be updated at the same time. 3. Sometimes a column can be updated every 5 secs. 4. About 100 to 5000 Rows I'm a SQL newb and this would be my first trigger to write (lol) but I feel if it could be done without causing a DEADLOCK then it should be done this way. Unless someone with more experience can shed some light With up to 300 .net clients over fast and slow links, I don't want to xfer the whole 5000 row / 50 column table back to the app and have it weed it out, especially if the client only needs 100 of the rows. On the same token, I don't want to make an individual call back to the SQL server should the client need 4000 rows. Thanks for the help!
View Replies !
Need Advice On Approach
I am new to DTS/SSIS and would like some input on an approach to solve a problem. I have a solution using SQL Server 2005 and another legacy solution running on Access. Status records are written to a table in SQL Server and as they are written, I need to write a record to a table in Access. This needs to be as real-time as possible. I thought about writting a managed stored procedure in C# so I could also access MS Access, but someone pointed me to DTS. Records are constantly written to the status table and need to remain in there even after they are processed. Can you recommend an approach or an article to read? Thanks, Steve
View Replies !
Question On An ETL Approach
Let me use a simplied example for what I'm trying to do. Say, I have the following source tables. T_EmpDept Emp_Name, Dept_Name John , IT Mary , IT Ted , HR T_Employee EmpID, Emp_Name 1 , John 2 , Mary 3 , Ted T_Dept DeptID, Dept_Name 1 , IT 2 , HR Now, I wan to populate a target table that looks like T_Target EmpID, DeptID 1 , 1 2 , 1 3 , 2 So, it's basically a lookup by name and translated to the appropriate keys. The way I've done this before, is I do one lookup at a time in serial (one data stream) and get the keys I need. But I want to do them in parallel (split the data stream in two), which I believe involves a Multicast Task and some sort of a Merge Task. I used to shy away from the parallel lookup because I never really understood how to bring the two data streams back together. So, the question I have is this. Are Multicast and Merge task the right tasks to use for this? I don't want to go researching something that may not be appropriate. Lastly, if someone has a link that can lay this out for me, I would appreciate it.
View Replies !
Best Approach For A Solution
I don't have much background with SSIS or SQLServer or development for that matter. I was thrust in to this and I apologize if my questions are not up to snuff in any way. I have built an SSIS package based on the import/export wizard and have customized it by adding a For Each Loop container for processing multiple source data files and it works great so far but I need to add some advanced functionality to make it more flexible. I'm having some problems with the best way to enhance the solution and I'm getting frustrated with variables and expressions, which is what I think I need to use. Hopefull what I need is something that's doable. The package processes mutliple source files using the For Each Loop. The data is stored in a destination table that is dropped and recreated each time. I've also added a couple of CREATE INDEX statements to the source SQL. I need to make the SQL DROP, CREATE TABLE and INDEX statements dynamic based on part of the filename string. I'm capturing the filename in the FOR EACH container using Variable Mapping. What I need to do is parse out part of the string from the filename and use it in the SQL statements. An example would be CREATE TABLE XXX_[stringvar] where [string] comes from the variable mapping done in the For Each loop. My thinking was to setup a new variable within the scope of the SSIS package and enable Evaluate as Expression and then build an expression for the varible where I parse out the string from the filename variable that already exists. So if the filename was filename.filetype and I needed the last 4 characters from the filename the string value would be whatever characters were in 'name' each time. But there isn't an expression builder editor built in to the variable properties expression element, so I'm not clear that I can actually create a variable in SSIS and then have it's value be set using an expression. It seems to me that if this was doable, then the expression builder I've seen elsewhere in SSIS would be visable. Then I was hoping I could edit the SQL behind the DROP and CREATE statements where I've hard coded the table name and insert the variable right in the SQL. My SQL isn't that strong and I don't know how to work with variables but I was thinking the SQL would look like CREATE TABLE XXX_@[stringvar].... I believe another approach would be to setup another string variable for the entire SQL statement and build the statement string dynamically. But I don't know if this is necessary or better. Anyway, I hope someone can help and that my original post is clear enough. Thanks
View Replies !
Configuration Approach
Background I use four database connections strings. I have about 30 packages that will use one or more of the connection stings. I store the connection strings in XML configuration files. I know that I can share configuration files across packages. Should I ? Have one configuration file with all 4 connection strings? If I use this approach will I get errors in the packages that only use 1 of the connection strings OR Create four separate configuration files (CnnString1.dtsconfig, CnnString2.dtsconfig, CnnString3.dtsconfig, CnnString4.dtsconfig) and use the appropriate ones for each package Take another approach
View Replies !
SCD Type 2 Approach Help
Hi , I am not sure about what approach i need for my ssis package to use either SCD type 2 wizard or Checksum transformation or any other optimal method to load the Data and Maintain the Historical Changes . The number of columns are ranging from 10 to 15 . and the number of rows are from 10,000 to 140000 what approach would help me to obtain better performance . Please let me know
View Replies !
What's The Best Way To Approach This Situation?
Hello everyone, I'm creating a database for a new application and I'm currently facing a design problem, regarding a business requierment for the membership module. The Membership Module of the application has several business requierments, specified by the client. One of them is the ability to add and remove details about their members. So far, I've created a schema, named Person, which will contain a number of tables responsible for everything related with the membership, as an individual. To help you guys understand the design I'm trying to implement, I'll post the fields of two of the tables that belong to the Person schema, as follows: Person.Base Id (uniqueidentifier) UniqueIdTypeId (uniqueidentifier) UniqueId (uniqueidentifier) Password (char(88)) PasswordSalt (char(10)) PasswordRecoveryQuestion (nvarchar(256)) [NULLABLE] PasswordRecoveryAnswer (char(88)) [NULLABLE] CreationDate (datetime) AuthenticationWindowStartDate (datetime) AuthenticationWindowAttemptCount (tinyint) IsActive (bit) IsBanned (bit) IsLocked (bit) Status (bit) Person.Emails PersonId (uniqueidentifier) EmailAddress (nvarchar(256)) CreationDate (datetime) ValidationCode (char(10)) ValidationDate (datetime) [NULLABLE] IsValid (bit) IsPrimary (bit) IsRollbackTarget (bit) Status (bit) So far so good. This design works great to preserve the data integrety. Nonetheless, this is where the problems start. Now, imagin you need to let someone from that company add an item to the user (through the application). Let's say we want to allow the company application manager to add an item to the person called "PreviousEmployer". Such item would then be used for statics, thus would probably need to be indexed. In order to meet this business requierment I would create some addicional tables. Let's get started: Person.CustomFields Id (uniqueidentifier) Name (nvarchar(50)) Description (nvarchar(3000)) [NULLABLE] Status (bit) Person.CustomField_Value FieldId (uniqueidentifier) Value (nvarchar(450)) This could work just fine if both are indexed (that's why the nvarchar size is set to 450). But I'm guessing this is far from the optimal solution for many reasons, one of them being the efficiency of the index if the company decides to go and use this for a flag (true or false [bit]). Another "solution" breaking scenario would be if the company wants to add the CV of the person, situation in which we were unlikely to be able to add a file in this datafield. How would you guys approach this issue? The bottom line is that the client needs to be able to add pretty mcuh any type of custom field and perform searches againts it. So, besides being a dynamic solution it needs to be efficient. Best regards and thanks in advance.
View Replies !
|
TRACKER
