i had a problem in inserting data in a table with foriegn key

i hav following 2 tables n i am able to insert data into the second table having a foriegn key

create table Customer_Details(
Customer_ID integer primary key,
Customer_First_Name varchar(75),
Customer_Last_Name varchar(75),
Address varchar(100))

create table Account_Details(
Account_No integer primary key,
Customer_ID integer foreign key references
Customer_Details(Customer_ID),
Debit float,Credit float,Balance float )hi
can u please put ur insert and also when a column is PK it cant be null. when u do not put any think then that means it also can be a null value but pk can't be null at all.

i cannot solve it

i have 2 tables.the first contains lastname,phone1,adress,etc...the second contains firstname,phone1. I want to insert the firstname from the second table into the first table where phone1 one is identical.
thank you..Do you want to insert a new row or update an existing row?|||Originally posted by r123456
Do you want to insert a new row or update an existing row?
thank you for your reply
i want to update the row.
Your statements add and unfortunatelly dont make all the comparisons,though the compared fields are of the same data type|||update table2
set firstname =
(select firstname
from table1 t1
where key = t1.key);|||Originally posted by r123456
update table2
set firstname =
(select firstname
from table1 t1
where key = t1.key);
ANALYSER'S MSG
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

thank you|||You need to join the tables on a candidate key. If you replaced key with 'phone' then the query is returning multiple phone numbers meaning a phone number is not unique. Join on the primary key as this will ensure only a single row is returned.|||Originally posted by r123456
You need to join the tables on a candidate key. If you replaced key with 'phone' then the query is returning multiple phone numbers meaning a phone number is not unique. Join on the primary key as this will ensure only a single row is returned.

i set [phone] as primary key and is accepted.
Although the querry doesnt write all the fields i selected in my table
.... it seems it doesnt make the comparison.

thank you.|||Select *
from table1 t1
INNER JOIN
table2 t2 ON
t1.phone = t2.phone;

If this does not return macthing rows then the data types of phone in both tables are not identical;|||Originally posted by r123456
Select *
from table1 t1
INNER JOIN
table2 t2 ON
t1.phone = t2.phone;

If this does not return macthing rows then the data types of phone in both tables are not identical;

thanks a lot for your help

I am trying to work out datediff for just mon-fri between datefiel

I am trying to find the number of working days between two date fields.
I have datediff(dd, first date, second date) as turnaround time,
But this includes saturdays and sundays in the output. I need to find the
number of days, only including monday to friday, between the two date fields.
It helps to have a Calendar table in your database:
CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
Populate it with as many years as you'll ever need:
INSERT INTO Calendar (caldate) VALUES ('20000101')
WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Set the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
You'll probably want to record any public holidays in the same way.
Now you can easily compute the number of working days between two dates:
SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @.first_date AND @.second_date
AND workingday = 'Y' ;
David Portas
SQL Server MVP
|||Hi Shaun,
Check if this helps...
SELECT DATEDIFF(dd, 'startdate', 'enddate')
- DATEDIFF(ww, 'startdate', 'enddate') *2
Thanks
Yogish

I am trying to work out datediff for just mon-fri between datefiel

I am trying to find the number of working days between two date fields.
I have datediff(dd, first date, second date) as turnaround time,
But this includes saturdays and sundays in the output. I need to find the
number of days, only including monday to friday, between the two date fields
.It helps to have a Calendar table in your database:
CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
Populate it with as many years as you'll ever need:
INSERT INTO Calendar (caldate) VALUES ('20000101')
WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate)
,
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Set the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
You'll probably want to record any public holidays in the same way.
Now you can easily compute the number of working days between two dates:
SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @.first_date AND @.second_date
AND workingday = 'Y' ;
David Portas
SQL Server MVP
--|||Hi Shaun,
Check if this helps...
SELECT DATEDIFF(dd, 'startdate', 'enddate')
- DATEDIFF(ww, 'startdate', 'enddate') *2
Thanks
Yogish

I am trying to work out datediff for just mon-fri between datefiel

I am trying to find the number of working days between two date fields.
I have datediff(dd, first date, second date) as turnaround time,
But this includes saturdays and sundays in the output. I need to find the
number of days, only including monday to friday, between the two date fields.It helps to have a Calendar table in your database:
CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT 'Y')
Populate it with as many years as you'll ever need:
INSERT INTO Calendar (caldate) VALUES ('20000101')
WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar
Set the non-working days:
UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,caldate) IN ('Saturday','Sunday')
You'll probably want to record any public holidays in the same way.
Now you can easily compute the number of working days between two dates:
SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @.first_date AND @.second_date
AND workingday = 'Y' ;
--
David Portas
SQL Server MVP
--|||Hi Shaun,
Check if this helps...
SELECT DATEDIFF(dd, 'startdate', 'enddate')
- DATEDIFF(ww, 'startdate', 'enddate') *2
--
Thanks
Yogish

Hyphen padding in t-sql query result

A newbie question here:

In T-SQL my query to text file results in the padded second line containing a set of hyphens. How do I turn that off so that just the first line contains the headers and the results immediately follow in line 2.

Thanks!

You need to describe your problem a bit. It would help to have relevant code, data definition, what the incorrect results look like and the correct results look like. It would also help to have the associated sample data.|||

Certainly.

I'm composing a job in sql server using sqlcmd. Here's my command-line

EXEC master..xp_cmdshell 'sqlcmd -X -S server -q "SET NOCOUNT ON SELECT * FROM pubs.dbo.test" -s , -w 255 -u -o "\\server\output.csv"'

Output is just fine with the exception of that pesky line 2 of padded hyphens. Appears like this:

department,FundCode,Month

-,--,--,
DM ,SAZXZ , 8

DM ,ESJXZ , 7
DM ,DGLXS , 9
GN ,DGLXZ , 8

|||

That 'pesky line 2' is part of the output 'header'.

You can turn the headers off (that will include the line 1 with the column names) by adding this switch to the SQLCmd command line:

-h-1

(No space between the h and the -1.)

You probably don't need line 1 or 'pesky line 2' in your application.

As far as I am aware, those are your choices...

|||

Plus, if you don't like their output, it is pretty easy to build a console app to get the results. I had to do it once to deal with Text values that were very large and I couldn't get sqlcmd to do.

In 2005, you might even be able to write a CLR stored procedure (albeit an unsafe one) that will do the work directly that you have complete control over.

|||I was aware of the -h switch but I'd prefer to keep the headers to be explicit. If sqlcmd doesn't support it then I'll have to write something up. Thanks for your input everyone!|||This is kind of a cheesy solution, but it works.

Use this command:

EXEC master..xp_cmdshell 'sqlcmd -X -S server -q "SET NOCOUNT ON Select 'department','FundCode','Month' SELECT * FROM pubs.dbo.test" -s , -w 255 -u -o "\\server\output.csv" -h-1'

Notice the header switch (-h) has a value of -1 which means that it is off. The first Select statement returns one more row of results which contains the "headers"

Your output should look like this:

department,FundCode,Month

DM ,SAZXZ , 8

DM ,ESJXZ , 7
DM ,DGLXS , 9
GN ,DGLXZ , 8

|||

Brilliant! I had to use double single-quotes (e.g., ''Department'') to get it to parse correctly but this is what I needed.

Not cheesy at all. I too hide my inelegance with cleverness.

Hyperlink report in new page..Urgent

I am working with two reports. The first reprot has a hyperlink to a textbox which opens a second report. This all works fine, but the when the hyperlink is clicked the second report open on top of the first report. I want the second report to open in a another seperate window, so that i can view two reports at the same time.

Can you please help me .. it's urgent.

Hi

try this I found it on Google

=void(window.open('" & Globals!ReportServerUrl & "?/Directory/ReportName&ParameterName=" & Parameters!ParameterName.Value & "&rs:Format=HTML4.0','_blank','resizable=yes'))"

it worked for me.

(you have to deploy it to test it)

|||

forgot.

use ="java_script: (before void, remove the _ )

="java_script:void(window.open('" & Globals!ReportServerUrl & "?/Directory/ReportName&ParameterName=" & Parameters!ParameterName.Value & "&rs:Format=HTML4.0','_blank','resizable=yes'))"

|||

I use

="java_script:void(window.open('" & "http://server06/ReportServer$Server062005" & "/Pages/ReportViewer.aspx?%2fAccount+Receivable%2fFinishGoodAtyAndNetModelType&Customer="& Fields!Customer.Value & "&Period=" & Fields!Period.Value & "&rs:Format=HTML4.0','_blank','resizable=yes'))"

but when clicking the link:

http://server06/ReportServer$Server062005?%2fAccount+Receivable%2fFinishGoodAtyAndNetModelType&Customer=HUONG+THUY&Period=200602&rs%3aFormat=HTML4.0%27%2c%27_blank%27%2c%27resizable%3dyes%27))

and web page display the error mesaage:

Reporting Services Error

---

An attempt has been made to use a rendering extension that is not registered for this report server. (rsRenderingExtensionNotFound) Get Online Help|||i tried

="java_script:void(window.open('" & Globals!ReportServerUrl & "?/Directory/ReportName&ParameterName=" & Parameters!ParameterName.Value & "&rs:Format=HTML4.0','_blank','resizable=yes'))"

with removing the "_" from java_script...

but stil it does not work..

i have the sp1 & sp2 intalled with hot fix..

please help