Trying to pull data from 2 tables into a query


I'm trying to create a SQL query to pull together a job info table and a customer info table in an Access DB. We can't have a unique ID on the customer that we can store in the job table because the customer info is dumped from Exchange and the ID (row number) in the cust_List table will change each time we export from Exchange.

What I need to do is go through jobs, get the CO_ID, the CONTACT_LN and the CONTACT_FN and then match it against Customer_List>Account, Lastname, Firstname.

Any ideas
Posted On: Friday 26th of October 2012 12:03:10 AM Total Views:  326
View Complete with Replies

RELATED TOPICS OF Microsoft SQL Server PROGRAMMING LANGUAGE




Permission denied when writing to database database

I get the follwoing error when writing to the db in sql. how can i change the access writes can u give me steps on how to do it cheers System.Data.SqlClient.SqlException: INSERT permission denied on object 'tbl_courses', database 'admissions', owner 'dbo'. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.Newcourses_aspx.populateDB(Object o, EventArgs ee) in http://localhost/iris/Newcourses.aspx:line 58
VIEWS ON THIS POST

147

Posted on:

Friday 26th October 2012
View Replies!

database user cannot be dropped

Hi..I just restored a databae from backup. After restoring, as usual, from the Enterprises manager, I want to delete the database user and readd again from the DB login name. But when I tried to delete the DB user, it prompts me the following. "the selected user cannot be dropped because the user owns objects" why I never encountered this b4 I restored DB again but problem still the same. Any ideas
VIEWS ON THIS POST

212

Posted on:

Friday 26th October 2012
View Replies!

Updating data across databases

Not sure if this is the right place for this question, but will give it a try. I am using MS Server 2000 I need to run and update query that will pull information from a table in a database on server A and then update a different table in a database on server B. I am running into some problems though. The Query looks something like this Code: UPDATE Member SET Member_Number = Member_Number WHERE RTRIM(Member.FirstName) = RTRIM(Member.First_Name) AND RTRIM(Member.LastName) = RTRIM(Member.Last_Name) AND Member.Member_Number NOT IN (SELECT RTRIM(Member.First_Name), RTRIM(Member.Last_Name), Member.member_Number FROM Member GROUP BY RTRIM(Member.First_Name), RTRIM(Member.Last_Name) Having Count(*) > 1) But when I try doing the import/Export data it gets mad by saying that FirstName and LastName are not columns
VIEWS ON THIS POST

136

Posted on:

Friday 26th October 2012
View Replies!

Copying data from one table to another table

I need to copy data from one table into another table. The field names are NOT the same: I need to INSERT Table 1 fields: cdescurl cname cdescription cimageurl category cprice INTO Table 2: product_url name description image_url category price How do I do this Thank you in advance for any help you can give me!
VIEWS ON THIS POST

316

Posted on:

Friday 26th October 2012
View Replies!

Populating a database with data from db if a value exists

Hi all, I have a table where I want to populate it with data (certain fields) from another table. But I only want to populate the table if there is data in a certain field in the table I wish to get the data from. How exactly would I do this test Would an I have to perform an IF statement to test if the field contains a value first
VIEWS ON THIS POST

132

Posted on:

Friday 26th October 2012
View Replies!

dynamic/variable database name

Hi I have a trigger which retrieves database names from a table. I need to use this retrieved database name in another sql insert statement as a variable e.g. set @mydbname = Select .... from.. (to get the database name) then.. insert into @mydbname.dbo.emplTable At the moment it reads @mydbname as the string "@mydbname" not the value the variable holds I need the database name as a variable because i have to write to the correct database (there are 15)
VIEWS ON THIS POST

210

Posted on:

Friday 26th October 2012
View Replies!

Help with MS SQL Server - updating data from 2 tables

Hi all, Please forgive me, but I'm new to MS SQL Server and I have few questions. First of all I'd like to know if there is a way to update one table with the data from another. Let's say I have 2 tables. TableA table contains the voucher numbers; TableB table contains the image numbers. Is there a way to merge Voucher numbers from TableA table to Image numbers in TableB table if we assume that there is a primary key Voucher Number in both tables If yes- what would be the query structure for that Please forgive me if you think that this question is silly, I have to say I'm not just new to SQL I'm VERY new to it Thank you in advance.
VIEWS ON THIS POST

346

Posted on:

Friday 26th October 2012
View Replies!

Will database shrinking affect the data?

Hi all, does the shrinking of the database affect the data in the database, such as will it change the data, or deleting the data away Thanx
VIEWS ON THIS POST

161

Posted on:

Friday 26th October 2012
View Replies!

Import excel data through ASP page into MS SQL database

I'm working on a warranty database for my company. Each week we receive an excel file with all of the new warranty returns from GM, Ford and such. What I want to do is have a user go to a web page select the excel file and have it import into MS SQL. Currently they e-mail the file to me and I import it with a DTS pacage I made. I realize the excel file needs to be formatted, X number of columns with specific names and data type, but how do I import it into SQL \t\t Any ideas on how to start or a pointers in the right direction would be much appreciated.
VIEWS ON THIS POST

155

Posted on:

Friday 26th October 2012
View Replies!

Shrinking of database and backup

Hi all, I am new to the backing up and shrinking of the database. so i need some help here.. How does the shrinking of the database help in terms of the database performance How long does it take to shrink and backup a database with 4GB of data Thanx alot...
VIEWS ON THIS POST

162

Posted on:

Friday 26th October 2012
View Replies!

copying data from on machine to another machine

, Am new to Sql . i need to copy the database from one machine to another machine. Both machines are not on network. Those two are individual machines and having sql server on both machines. I want to upload the data. So can any one help me out with this pls
VIEWS ON THIS POST

131

Posted on:

Friday 26th October 2012
View Replies!

Function not returning all the data, just some

I've put together a "getvalues" function to return specific information from a table or query. I pass it a list of requested fields separated by a pipe | character. The subroutine splits the fields into an array and gets the data from the fetched record, and puts together a data return variable filled with the specific data, again separated by a pipe. The record is fetched fine but the data is not accessed from it correctly. It does work fine in Access but not SQL! The basic function works like this call returndata("data1|data2|..." function returndata(requestedfields) requestedfieldarray = split(requestedfields,"|") for i = 0 to ubound(requestedfieldarray) builddata = builddata & record(requestedfieldarray(i)) & "|" next returndata = builddata end function some of fields come back fine, some do not. I also find that if I hit each field early in the function For Each objField In record.Fields scratchdata = objfield.value Next Then the function works fine. But I'd rather not use that overhead to make things work. I'm just upgrading from Access to SQL but that shouldn't have anything to do with it (I think). Anybody have any thoughts\t
VIEWS ON THIS POST

141

Posted on:

Friday 26th October 2012
View Replies!

data amount selected.

Hi all, is it possible to cout size of data selected form mssql database table for each databse user. Lets say Scott seleceted 100 rows from table tiger which is 50Kb. Could use sample statement
VIEWS ON THIS POST

140

Posted on:

Friday 26th October 2012
View Replies!

joining a view to match main data in search query

I am currently building a telephone directory, i'm at the stage where I have the search functions runing smoothly. Except the way I have the database set up is that the main table holds the business type as a numeral which was entered from another table with coresponding names. I have built views to tie these two tables together. Now once a person searchs the listings they currently recieve a page of results, it is here that the problem occurs. I need to to in my sql statment tie the main table of data to the view to pull the business type name out instead of the business type numeral which is contained within the main data table. Here is my current Sql Statement: SELECT * FROM dbo.MAIN, dbo.VIEW_BUSINESS_NAME WHERE ENTRY_TYPE LIKE 'varSearchType' AND LAST_NAME LIKE '%varName%' AND INITIAL LIKE '%varInitial%' AND STREET_NAME LIKE 'varStreet' AND STATE LIKE 'varState' AND REGION LIKE 'varRegion' AND BUSINESS_TYPE LIKE 'varBusinessType' AND dbo.VIEW_BUSINESS_NAME.Expr1 = dbo.MAIN.BUSINESS_TYPE ORDER BY LAST_NAME It is being put together in dreamweaver mx. This is returning a multiple list of the one business repeated for as many listings as there are for that business type! Hope this makes sense it's very early in the morning.
VIEWS ON THIS POST

229

Posted on:

Friday 26th October 2012
View Replies!

ODBC -- connection to database failed

all, Our last IT person created an intranet website with .asp and has linked an ODBC with SQL Server 2005 Query. Here is how it's suppose to work: there is a page where employees can input an order# and it's suppose to search the ODBC. Instead of searching the following error appears on the page: "Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Microsoft Access Driver] ODBC--connection to 'MAXDAT' failed. /home/plannedshippingresults.asp, line 49" However if I go to the System DSN from Administrative Tools, I can test the connection and it works. Here is the Code he wrote for the plannedshippingresults.asp page (The Italicized/Underlined part is Line 49): Any help would be greatly appreciated. I just started messing with this 2 days ago, and im not very fluent with ODBC, SQL, or .ASP. If you need more information, please ask.
VIEWS ON THIS POST

179

Posted on:

Wednesday 7th November 2012
View Replies!

Problem putting a DateTime variable into a datetime database field (SQL Server 08, VS

For a university project I am creating a basic car hire website. ASP.NET is all new to me, so if you could keep replies simply explained I'd appreciate it! For the final stage of the car hire process, I take variables stored in sessions: string theUser = User.Identity.Name.ToString(); DateTime pickUp = Convert.ToDateTime(Session["pickUp"]); DateTime dropOff = Convert.ToDateTime(Session["dropOff"]); string carReg = (String)Session["carReg"]; double pricePerNight = Convert.ToDouble(Session["carPrice"]); double totalPrice = noOfDays * thePricePerNight; to insert into the Hire table, where the datatypes are as follows: UserName - varchar(MAX) RegNo - varchar(50) PickUpDate - datetime ReturnDate - datetime Price - money But when I execute the insert statement, I get the following error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. My code for inserting into the database is as follows: //Add details to Hire table string connectionString = WebConfigurationManager.ConnectionStrings["AdminCarsConnectionString"].ConnectionString; SqlConnection con = new SqlConnection(connectionString); con.Open(); SqlCommand cmd; string query = "INSERT INTO Hire (UserName, RegNo, PickUpDate, ReturnDate, Price)"; query += "VALUES ('" + theUser + "', '" + carReg + "', '" + dtPickUp + "', '" + dtDropOff + "', '" + totalPrice + "')"; cmd = new SqlCommand(query, con); cmd.ExecuteNonQuery(); lblCust.Visible = false; lblPrice.Visible = false; btnConfirm.Visible = false; lblHire.Text = "The process is complete."; I experimented by changing the datatype in the table to varchar, and inserting string values of the dates instead and that worked perfectly - however I need to keep these values as datetime, because of a query that checks for time clashes at an earlier stage in the hire process, which uses the datetime values in the Hire table (this works just fine, using the datetime variables from the session). Do you have any advice on how I could solve this problem The values that I am trying to insert are datetime type, and the field I want to insert them into is datetime type, so I don't know why it has a conversion problem. I hope someone can help soon because this is so close to being finished, and my deadline is in two days...
VIEWS ON THIS POST

183

Posted on:

Wednesday 7th November 2012
View Replies!

One or multiple databases for multiple apps that share data?

It sounds like your database is not getting hit very hard, so you probably don't need to worry about it too much. You can always scale the hardware if you need to. If it does get to the point that a single server (or cluster of servers) can't handle the load, You can do something like this: 1. Separate the distinct application databases to their own servers 2. create a single server for the common structures. 3. Use replication or SSIS to push changes to the common database out to the application databases. 4. Any changes to the common database should occur in the main one, not the copies--that way there is only one source for the data and the rest can be wiped and replaced if they get out of sync. Fairly complex, but if you can't get acceptable performance any other way, it may be worth it.
VIEWS ON THIS POST

137

Posted on:

Wednesday 7th November 2012
View Replies!

Grant permissions on the database

, i am using SQL Server 2005. i have created a database by name database_Lakshmi. i want to grant all privileges on my database to another user. for that i have written a query GRANT ALL PRIVILEGES ON database_Lakshmi TO developer IDENTIFIED BY 'developerPassword' when i try executing the query the error message that i get is........ Incorrect syntax near 'IDENTIFIED' Could somebody please let me know how to rectify it.
VIEWS ON THIS POST

143

Posted on:

Wednesday 7th November 2012
View Replies!

Unsure how to pull this ORDER BY off

s I have a table, we'll call it people In people, people can have the field is_cool as true. Now, some people in people, know cool people, so their field know_coolguy_id is equal to another someone on people, which his is_cool is True. When I get all the records from people, I sometimes wanna ORDER BY know_coolguy_id. Problem is, it sorts by the id (number) and not the person's name field. What I need basically, is "ORDER BY the NAME field of the cool people in this awesome table" Any ideas
VIEWS ON THIS POST

152

Posted on:

Wednesday 7th November 2012
View Replies!

Insert image into SQL database column

- I am trying to update an existing row in a table by inserting a jpg or gif image into a column called Photo. The SQL data field is set as image. When I build, I don't get any error messages but the upload doesn't work. I modified code from the MS site (streaming BLOBS page) I would really appreciate troubleshooting help with the code below or an alternative approach.
VIEWS ON THIS POST

215

Posted on:

Wednesday 7th November 2012
View Replies!