How To Get Reference From CompositePrimaryKey Field ???...
Mar 8, 2007
I have one table name Magazine_Details, it has a composite primary keyCreate table Magazine_Details
(
MagazineTitle varchar(60),
MagazineType varchar(20),
IssueType varchar(25),
IssueNumber varchar(10),
Qnty int,
Qnty_in_Hand int,
[Date] datetime
constraint PK_Magazine_Details_Composite primary key(MagazineTitle,IssueNumber)
)
Another table is Magazine_Issued and i want reference from Magazine_Details table. but i am auable to create foreign key constaraint in Magazine_Issued table.create table Magazine_Issued
(
Member_ID varchar(100),
MagazineTitle varchar(60) references Magazine_Details(MagazineTitle),
IssueNumber varchar(10) references Magazine_Details(IssueNumber),
IssueDate datetime,
Magazine_Status varchar(10)
)
Is it possible to reference the data in another field within the same table?
Let's say that one column has a calculation, and a second column has a different calculation. Can the third column find the difference between the first two columns?
(I'm wondering if this is possible to do within the Layout View, not from the Data View)
I have a table that contains a Ref field and a TransactionDate field. For each Ref field there are mutliple Transactions. I am trying to put together a query that selects the Latest Transaction date for each Ref field in the table.
I tried:
SELECT * FROM tabMediaTransaction WHERE (TransactionDate = (SELECT MAX(TransactionDate) AS Expr1 FROM tabMediaTransaction AS tabMediaTransaction_1))
But this only returns 1 record for 1 Ref field. I need 1 record for each Ref field
My question is simple, I'd like to do something I do in Jet ANSI-89 SQL. Mind you I'm just adding numbers here - they are not actual columns in 'SomeTable'
SELECT 1 AS A, 2 AS B, A+B AS C FROM SomeTable
The Jet engine evaluates and does arithmetic on the Aliased column names - handy when they contain their own functions. The resultset would show:
A B C 1 2 3
However from what I can tell SQL Server 2005 is not picking this up. Is their an equivalent?
I am trying to eliminate a scalar function by rewriting a view. I currently have a structure similar to the following:
With FirstQuery as (SELECT Trial, SBOI, TBOI, FROM TU WHERE SBOI Between 22000 and 22999 and TBOI < 10000), strt as (SELECT TOP 1 TimeOfEvent, Trial, SBOI, TBOI From TU ORDER BY TimeOfEvent),
[Code] ....
What I trying to do is for the Trial, SBOI, and TBOI in FirstQuery, find the Start Time and Stop Time. TimeOfEvent is an integer in milliseconds. Say I have the following data:
TrialID SBOI TBOI TimeOfEvent A 22000 5000 5 A 22000 5000 10 B 22000 5000 8 B 22000 5000 15
So the DISTINCT output would be:
Trial SBOI TBOI StartTime StopTime A 22000 5000 5 10 B 22000 5000 8 15
The problem I am having is that strt is selecting the smallest time period. It doesn't seem to care what the current record in cte is. I thought that by using my joins, it would make it select smallest TimeOfEvent for the Trial, SBOI, and TBOI that are selected in cte. Obviously that is not the case. So, I was trying to add a WHERE to the strt Select Statement such as
WHERE TrialID = FirstQuery.TrialID and SBOI = FirstQuery.SBOI and TBOI = FirstQuery.TBOI but it isn't working either.
I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:
Error system.NullReferenceException: Object refrence not set to an instance reference.
In PreExecute section TextReader = new system.io.streamreader(" file name") In the CreateNewOutputRows: dim nextLine as string nextLine = textReader.ReadLine
[code]...
is there something which i did not install or what can be the error?
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
I am trying to drag data from Informix to Sql Server. When I kick off the package using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them as datetime but it failed.
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
DECLARE Loan_cursor CURSOR FOR SELECT Loan_No,store FROM loan WHERE maturity_date > '2001-04-30' and loan_no like 'ABL%'
OPEN Loan_cursor
-- Perform the first fetch. FETCH NEXT FROM Loan_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN Declare @LoanNo varchar(12) ** Set @LoanNo = Loan_No
-- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM Loan_cursor END
CLOSE Loan_cursor DEALLOCATE Loan_cursor
when l run the cursor l get the error Server: Msg 207, Level 16, State 3, Line 15 Invalid column name 'Loan_No'. . If l reference it as Set @LoanNo = LoanTable.Loan_No l get the error
Server: Msg 107, Level 16, State 3, Line 15 The column prefix 'loan' does not match with a table name or alias name used in the query. All l'm trying to do is to compare the loan number that l get from the cursor to the value in the loans table. Maybe reference is a better word
Just i modified one sub stored procedures which is not getting affected in the main stored procedures
Create procedure main as begin set nocount on
--First sp exec Data_transfer_sp
--Second sp Exec Clearance_sp
set nocount off end
In data_transfer_sp,i have commented the select statement but still iam use to the select result while execution the main sp. Note:I have compiled the data_transfer_sp after making comment .Txs in advance
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
I'm importing an Access database to SQL Server 2000. The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import? I would appreciate any pointers.
We have a stock code table with a description field and a brand field - when the data was entered, some of the records were entered with the brand field in the description field.
ie. Code Description Brand ABC1 BLANK DVD SONY ABC2 SONY BLANK DVD SONY
what I need to do is identify where the Brand is in the Description field ...
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
i've a reasonable amount of experience with MS Access and less experience with SQL Server. I've just written an .NET application that uses an SQL Server database. I need to collate lots of data from around the company in the simplest way, that can then be loaded into the SQL Server database.
I decided to collect the info in Excel because that's what most people know best and is the quickest to use. The idea being i could just copy and paste the records directly into the SQL Server database table (in the same format) using the SQL Server Management Studio, for example.
Trouble is, i have a problem with line feed characters. If an Excel cell contains a chunk of text with line breaks (Chr(10) or Chr(13)) then the copy'n'paste doesn't work - only the text up to the first line break is pasted into the SQL Server database cell. The rest is not pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a memo field into SQL Server database, but with exactly the same problem. I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases interacting together, can someone suggest the simplest way of transferring the data without getting this problem with the line feeds? I don't want to spend hours writing scripts/programs when it's just this linefeed problem that is preventing the whole lot just being cut'n'pasted in 5 seconds!
