Bytes - answers

The INSERT statement conflicted with the FOREIGN KEY constraint

waqasahmd — Fri, 20 Nov 2009 12:24:45 GMT

Hi

i have two tables

1- Person
2- Account

In Account table i have key accountID which is a FK in Person table.

when i run the insert query as

insert into

Person(FirstName2)

values ('waq')

as i did not enter any data for account field so it gives me the eror:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Person_Account". The conflict occurred in database "abc", table "dbo.Account", column 'AccountId'.

can somebody help me with that as i want to insert the valuse in Person table whether i have accountID or not

Thanx

How to convert rows into different columns

kirankumarn — Fri, 20 Nov 2009 09:56:03 GMT

How to convert rows into different columns

Thanks in advance

how i convert dbf file int sql file

yasir115 — Thu, 19 Nov 2009 13:23:37 GMT

i have file that have .dbf extension and i want to convert into sql format i used this for some purpose
is any one tell me how this done?

Can I calculate between different datasets?

jedieagle — Wed, 18 Nov 2009 08:52:20 GMT

I am working on a report where I use the same query with different data sources. both databases are build the same way and I can show the data from both datasets. At the moment it looks like this:

Expand|Select|Wrap|Line Numbers

	
DATASET1 TABLE         DATASET2 TABLE

4234                        23423

52226                     2342342

55522                       14234

6788                       234234

22568                       23532

Of course each table belongs to different dataset. I was wondering if it is possible to calculate the difference between the upper colums, so it would look like this

Expand|Select|Wrap|Line Numbers

	
DATASET1 TABLE        DATASET2 TABLE     DIFFERENCE

4234                       23423             -19189

52226                    2342342           -2290116

55522                      14234              41288 

6788                      234234            -227446

22568                      23532               -964

and the also color the cell red or green :) I am using the 2005 version

Query Engine Error after Forced Protocol Encryption on Crystal Report

jeffstl — Tue, 17 Nov 2009 15:39:57 GMT

After enabling forced protocol encryption on SQL server there are a handful of crystal reports that have the following error when you try to run them

Query Engine Error: '08S01:[Microsoft][ODBC SQL Server Driver]Communication link failure

Turn the forced protocol encryption off on SQL Server and they work again just fine. I have tried re-choosing the data source of the .rpt files, and tried turning on Integrated Security before loading the reports with no luck

Has anyone had this problem? Is there something that can be done to let these reports run with forced protocol encryption?

How to query untyped xml data

GinaMarano — Mon, 16 Nov 2009 19:50:50 GMT

I have a varchar/text field that contains untyped xml. I don't want to use schema. How would one query this:

I want the "value" for 'IgnoreFailure'

DECLARE @x XML
select @x = myColumn from myTable where recordid = 1
select @x.query(...

~Gina_M~

TSQL query

jabeek — Mon, 16 Nov 2009 05:01:53 GMT

I had to write a valid TSQL query that does all the following in a transaction and will rollback if any system error is thrown.

� Create the following tables Customer, Order and Address where Customer can have many Orders and many Addresses. Order can only have one Address. Customer will have a column called CustomerNum of type NVARCHAR(3).
� Develop a SQL function that provides a unique, incremented CustomerNum in the form �001�, �002�, �010� etc. Using that function, insert 10 new Customer rows. Note: the function needs to be as efficient as possible. Add any indexes that might be needed.
� Insert 1 Address per Customer.
� Insert 2 Orders per Customer.
� Ensure each Order has a valid Address from the corresponding Customer.
� Ensure that if the tables already exist that they are dropped from the database.
� Ensure all tables use primary keys and are of type GUID.

Any quick response is really appreciated.

role permission of tempdb due to server restart

ctatvasoft — Sat, 14 Nov 2009 07:34:54 GMT

in our application that need to create some tables in tempdb. The user
(call it APP) is not sa. Everytime that the server need to be rebooted, the
db_owner role on tempdb for user APP disappear. So...

1. Is there any setting to ensure that the the db_owner role on tempdb for
user APP will remain after server reboot?

2. If not,
Is there any solustion to run a script at server startup?
And does anybody have a script that will create the db_owner role on tempdb
for user APP(or some pointer on how)?

Chirag Vyas
Project Manager
Software Outsourcing India
Web Development Company India

Want to track the user who last modified the procedure

madankarmukta — Thu, 12 Nov 2009 12:40:53 GMT

Hi all,

I want to track the user who lastly modified the procedure.Can anybody guide me how can i achieve this ?.. I want to write the query for the same.

Thanks!

How can i convert this query into sql server whereas this is working in MS ACCESS

Prashant Pratap Kandari — Thu, 12 Nov 2009 05:33:52 GMT

SELECT tblProposals.District_Code, tblProposals.PFT_Code, tblProposals.Village_code, tblProposals.Activity_code, tblProposals.SubActivity_code, tblProposals.GSGrant, tblProposals.GSLoan, tblProposals.OwnContribution
FROM tblProposals WHERE (((tblProposals.Level)='1') AND (tblProposals.GSGrant=0) AND (tblProposals.GSLoan=0) AND tblproposals.Activity_Code in ('01','02','04','05','06','07','08','09','12','13' ,'14') and iif(tblproposals.Activity_Code='02',tblproposals.S ubActivity_Code in('01','02','04','05','06','07','08','11','12'), iif(tblproposals.Activity_Code='01',tblproposals.S ubActivity_Code in('01','02','04','05','06','08'),iif(tblproposals .Activity_Code='04',tblproposals.SubActivity_Code in('03','07','08'),iif(tblproposals.Activity_Code= '13',tblproposals.SubActivity_Code in('03','05','08'),iif(tblproposals.Activity_Code= '14',tblproposals.SubActivity_Code in('02'),iif(tblproposals.Activity_Code='08',tblpr oposals.Activity_Code in('08'),iif(tblproposals.Activity_Code='05',tblpr oposals.Activity_Code in('05'),iif(tblproposals.Activity_Code='06',tblpr oposals.Activity_Code in('06'),iif(tblproposals.Activity_Code='07',tblpr oposals.Activity_Code in('07'),iif(tblproposals.Activity_Code='09',tblpr oposals.Activity_Code in('09'),iif(tblproposals.Activity_Code='12',tblpr oposals.Activity_Code in('12')))))))))))))

Insert table values from other table

JoeKid09 — Thu, 12 Nov 2009 04:17:54 GMT

Hi Guys,

Thanks in advance for your help. I unpivoted a table which I want to insert into another table. Below is my script. I got an error; anyone can see where my error is.

insert into MASTER_FINANCIALS (
[ISIN]
,[Ticker]
,[Company_Name]
,FINANCIAL_DESC
,FINANCIAL_VALUES
,KPI_CATEGORY
,DATE_PERIOD
(
SELECT
[ISIN]
,[Ticker]
,[Company_Name]
,FINANCIAL_DESC
,FINANCIAL_VALUES
, 'KPI_MARGINS' AS KPI_CATEGORY
, 'Q42007' AS DATE_PERIOD

FROM
(
SELECT [Ticker]
,[ISIN]
,[Company_Name]
,[Operating_Margin]
,[EBITDA_Margin]
,[Net_Profit_Margin]
FROM [CRDA_MASTER].[dbo].[Margins]
) P
UNPIVOT
( FINANCIAL_VALUES FOR FINANCIAL_DESC IN
([Operating_Margin]
,[EBITDA_Margin]
,[Net_Profit_Margin]
)
)AS UNPVT
) FORECAST
GO

thanks agina!

sql query

gm000 — Tue, 10 Nov 2009 20:50:10 GMT

hi
thanks for your reply i was very helpful for me regarding this i have a problem which is this

i m using this query it works
SELECT AuthorId, UrlUserName, Urlid, UrlAddress, ChooseMedia, Title, Description, ChooseThumbnail, ChooseTopic, Datetime,
(SELECT COUNT(CommentId) AS Expr1
FROM CommentTable
WHERE (CommentUrlid = Submitnewtable.Urlid)) AS cnt, DATEDIFF(second, Datetime, GETDATE()) AS diff
FROM Submitnewtable
WHERE (Title LIKE '%.com%')
BUT I WANT TO USE A TEXTBOX.TEXT OR STRINGVARIABLE IN THE PLACE OF '%.com%' FOR SEACHING purpose
i also used FREETEXT funciton for searching but it not working and give error which is fulltext index.My database table is not fulltext index enabled and i tried to enable fulltext index but i failed please guide me regarding above query

thanks

Return all items in an order then add the shipping cost to only one item

cday119 — Tue, 10 Nov 2009 19:27:20 GMT

So I am having a difficult time with some clients. The database is set up like this

Expand|Select|Wrap|Line Numbers

	
tblOrder               tblOrder_Item_Bridge              tblItem

internal_id            item_id                             item_id

ship_cost             internal_id                         item desc

item_id                                                    cost  

                                                           quantity

Pretty simple

Now the clients want something like this

Expand|Select|Wrap|Line Numbers

	
internal_id  item desc        quantity   cost           ship_cost        

1643089    EGRET             3          7.75         $13.15     

1643089    EGSPSAE           4           3.5        $0.00

the problem is they want the first line to inluce the shipping cost and every line after that, the shipping cost is zero. Andy ideas on how to do this? here is the query that generates a report of every ordered item with the orders ship cost:

Expand|Select|Wrap|Line Numbers

	
SELECT     tblOrder.internal_id, tblOrder.order_number,  tblItem.quantity, tblOrder.ship_cost, 

FROM       tblOrder INNER JOIN

           items ON tblOrder.internal_id = tblOrder_Item_Bridge .order_Id INNER JOIN

           item ON tblOrder_Item_Bridge.items_Id = tblItem.items_Id

That generates this:

Expand|Select|Wrap|Line Numbers

	
internal_id  item desc        quantity   cost           ship_cost        

1643089    EGRET             3          7.75         $13.15     

1643089    EGSPSAE           4           3.5        $13.15

Raise Error problem when connecting to SQL Server through ODBC

kctan — Tue, 10 Nov 2009 01:41:02 GMT

Hi,

I have a VB.Net system that connect to SQL Server through ODBC. When I try to raiseerror from an IF...ELSE statement, it does not triggered the exception handling.

I have done some raise error testing at my side. Below is my finding:

1. RaiseError did not triggered exception:

Expand|Select|Wrap|Line Numbers

	
       CREATE PROC xxx

       AS

         IF 1=1

           BEGIN

             raiserror (70000,15,15)

           END

2. RaiseError triggered exception:

Expand|Select|Wrap|Line Numbers

	
       CREATE PROC xxx

       AS

         IF 1=1

           BEGIN

             raiserror (70000,15,15)

             raiserror (70000,15,15)

           END

Expand|Select|Wrap|Line Numbers

	
       CREATE PROC xxx

       AS

       BEGIN

         raiserror (70000,15,15)

       END

Any idea why it does not work in IF...ELSE statement?

Thank you.

KC

UPDATE and SELECT Combination

rickcf — Mon, 09 Nov 2009 23:00:17 GMT

I have two tables. Table A is the main table I need to update and table B is the update table with the new data. Table B contains only two fields- fielda (also contained in table A for the linking but also is the field that needs changed) and a second field (fieldb) which is the NEW value of fielda that I need to change in TableA. Thus I need a combination UPDATE/SELECT statement to link the two tables togeather to update table A with the new values (fieldb) from table B. I need a start on how I can do this. Thanks.