Query - Join:

What type of Query should I use to combine two sets of data into the same
recordset. Note that none of the companies in TableA are the same as the
companies in TableB (distinct sets of data).
TableA
Company, Sales, Members
Table B
Company, Members, Vacation
Query Should Produce
Company, Sales, Members Vacations"William" <da@.northernit.net> wrote in message
news:ikoke.16188$tM3.4933@.twister.nyroc.rr.com...
> What type of Query should I use to combine two sets of data into the same
> recordset. Note that none of the companies in TableA are the same as the
> companies in TableB (distinct sets of data).
> TableA
> Company, Sales, Members
> Table B
> Company, Members, Vacation
> Query Should Produce
> Company, Sales, Members Vacations
>
If you know that they are definitely distinct, then you could simply do the
following:
INSERT dbo.TableA (Company, Sales, Members, Vacations)
SELECT Company, NULL, Members, Vacation
FROM dbo.TableB
Rick Sawtell
MCT, MCSD, MCDBA|||Are there any matching values between the two tables (e.g. Members)? If so,
the
following might work:
Select Coalesce(TableA.Company, TableB.Company) As Company
, TableA.Sales
, Coalesce(TableA.Members, TableB.Members) As Members
, TableB.Vacation
From TableA
Full Join TableB
On TableA.Members = TableB.Members
If there is nothing in common, then you could get the results you want using
a
Union like so:
Select A.Company, A.Sales, A.Members, Null As Vacation
From TableA As A
Union
Select B.Company, Null, B.Members, B.Vacation
From TableB As B
Using Union will give you a distinct list across the two tables. Using Union
All
will give you all items from both tables even if a duplicate is produced.
Thomas
"William" <da@.northernit.net> wrote in message
news:ikoke.16188$tM3.4933@.twister.nyroc.rr.com...
> What type of Query should I use to combine two sets of data into the same
> recordset. Note that none of the companies in TableA are the same as the
> companies in TableB (distinct sets of data).
> TableA
> Company, Sales, Members
> Table B
> Company, Members, Vacation
> Query Should Produce
> Company, Sales, Members Vacations
>

Queries going to Suspend state with wait type ASYNC_NETWORK_IO

Recently we observed a problem. We are running stored procedure through our c# code. Three machines access the server and update or insert in the required tables in the server. If there is no data in the server, on installing first time our application usually our database is clean. then stored procedure works fine, it takes around 10 to 15 sec to execute. Next time if execute the time goes up to minutes like 15 mins. Next time it goes for hours around 4 hrs. Even to update 4 or 5 records it takes time. Initially we thought it was because of the size of the data and we tried to re tune on indexes, it did not solve. But now what we observe is even with less number of records in server also it wouldn't come of the execution for hours.
Now are executing the just the SP in the SQL manager studio to see the time. That one also is executing for hours. when looked at the activity monitor the process goes to suspend state with wait type ASYNC_NETWORK_IO.

When we comment one of the query is working fine.
Is this something to do with the query I am not sure. If that is the case it should not work every time.

The query makes any sence or is there any way to write it in better way

'UPDATE [server].[dbo].[DocumentMetadata] SET DocumentInfoID = b.DocumentInfoID, [Name] = b.[Name], MetadataType = b.MetadataType,
[Value] = b.[Value], ValueType = b.ValueType
FROM [server].[dbo].[DocumentMetadata] a WITH (UPDLOCK)
INNER JOIN (SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MACHINENAME.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @.ProjID + ''') b
ON a.DocumentInfoID = b.DocumentInfoID AND a.[Name] = b.[Name]'

'INSERT INTO [server].[dbo].[DocumentMetadata]
(DocumentInfoID, [Name], MetadataType, [Value], ValueType)
SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MYCLI.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @.ProjID + '''
AND Ltrim(rtrim(c.DocumentInfoID))+ ltrim(rtrim(c.[Name])) NOT IN
(SELECT Ltrim(rtrim(DocumentInfoID))+ ltrim(rtrim([Name])) FROM [server].[dbo].[DocumentMetadata])'

We have been fighting it out for so many days.
Can anybody help

Thanks
knvdssr

ASYNC_NETWORK_IO means SQL server is waiting for network...

How may rows your select statement for INSERT it is returning?

You are using ltrim and rtrim functions against column which result a table scan and it can't use index...

Try to change the query or just comment the query and test it to make it is the issue.

|||It appears you are using a LINKED SERVER to access another server?

If this is the case, try looking at activity monitor on the source. I would suspect you are getting a deadlock or lock problem or something on the linked server.

The wait you are seeing indicates it is waiting on the linked server to finish the query and send records.

|||When you use 4-part name, all of the data from the remote server is pulled across before the join is formulated. If the remote table is large, you will have to wait for a long time for all the data to cross over. Try using openquery() to only pull/update the necessary remote data.

You can lookup some examples here:
http://groups.google.com/groups?q=sqlserver%20oj%20openquery%20update|||

We commented the query which I mentioned earlier, to see if that one is causing the problem. Then the whole Stored procedure finished executing in minutes.

We changed the the query with our using the NOT IN in that query.

Now first time it is taking only 10 minutes for more than 100,000 records.

But the second one is not coming out at all. This time the server is having lot of rows because is already updated once.

All that we see is the process in suspend state or comes out as timed out.

If I am getting a dead lock on the linked server is there any way to solve it?

Could any body help please

|||

Again, take a look at openquery/openrowset. Bringing the entire resultset back is not recommended when the remote table is large.

Quantasoft

Hi

I am trying to set up an sql express (2005) database on quantasoft (qsh.eu). They assure me that they support this type of database and say to use user instances. There is also a connection string generator on their website. I have tried numerous times to get the connection to work, but every time it produces a runtime error with no details about the actual problem. Does anyone know how i can make this connection work?

connection string (database is in App_Data folder)

<add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;Database=transmission.qsh.eu_ce00d421-bc1c-4564-a6e3-755632a0d652;AttachDBFilename=|DataDirectory|Database.mdf" />

Hi,

As your hoster provide SQLExpress edition, you can upload your aspnetdb database onto the App_Data folder and make the connection string look like:

<add name="LocalSqlServer" connectionString="data source=ServerName\InstanceName;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />

In the string above, the servername and instancename can be got from your host and the AttachDBFilename is based on the .mdf filename you upload to your server.

Hope that helps. Thanks.

|||

Hi,

I am also using quantasoft and facing the same problem !. i am very new to asp.net and sql . i donot have any roles or membership on my webpage (but i would shurly like to have it someday!). ..i mean it is a simple webpage with 1 database(test.mdf) and 1 gridview which shows the database contents!.

Question:- WHAT IS ASPNETDB.mdf ??- I d'not know about it ,what this file is ? and what it is used for ? or where can i get this file ? or how can i make this file ? - Please tell me all about this file !

Thanks ! in advance. :)

|||

I hate to 'bump' this post but i still have not resolved this problem. I would be grateful if anyone could help me with this. I am now receiving this error:

An attempt to attach an auto-named database for file H:\Home\Webs\qsh.eu\transmission_24be67cf-3e4c-4852-b3ec-fc849ce5b009\www\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Qualified table name syntax

Im trying to write a generic data access layer that supports SQL CE and Im wondering if any type of schema qualifier can be placed in front of a table name when executing a sql statement.

I've tried soemthing like this

select * from dbo.Account

I get this error,

The table name is not valid. [ Token line number (if known) = 1,Token line offset (if known) = 19,Table name = account ]

It doesnt make really make sense to include a qualifier for sql ce but I just wanted to make sure that there wasnt some other syntax that I wasnt aware of.

Thanks.

Nope, as I describe in the book, the SQL engine for "SQL Server" Compact Edition is not SQL Server--it uses a subset so the concept of "ownership" or "schemas" does not exist--it simply confuses the little engine. See www.hitchhikerguides.net FMI.|||thanks for confirming my thoughts. I'll check out the guide.

Qty - decimal, money, or ...

which data type will be the best for the qty column?
- RDepends on the actual values. What kind of values do you expect?
ML
http://milambda.blogspot.com/|||Precision should have been specified in a requirements document. If that's
what you're working on, then you need to consider what things are to be
quantified. Are they inventory items that are purchased and sold in
individual packages? Are they purchased or sold by weight? By volume?
Remember that if you use a float data type that you must account for
rounding errors in calculations, which usually involves adding additional
code and invites the possibility that a programmer will fail to add that
code.
"Rock" <Rock@.discussions.microsoft.com> wrote in message
news:3F994D97-A01B-48B2-B555-510FF25A9DF2@.microsoft.com...
> which data type will be the best for the qty column?
> - R|||On Fri, 17 Feb 2006 01:30:27 -0800, "Rock"
<Rock@.discussions.microsoft.com> wrote:

>which data type will be the best for the qty column?
>- R
If it is always a whole number 3, 1534, 65432, then I use the
appropriately sized version of Int (generally int, possibly bigint).
If it is a decimal fraction of known size, say 145.75 I would use
Decimal.
If it is a decimal fraction that has to be more flexible, then float
may be required, but take Brian's warning to heart if you go that way.
Roy

q; how to write this trigger

I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update
or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>’Done’. If thi
s is
a success, I need to set T1.Status=’Done’ for the updated records. So th
is
should only be updated records.
T1 is a frequently inserted table, so there might be more than one record
coming there at the same time. How should I write my insert trigger and
correctly set T1.Status=’Done’, any example would be greatly appreciated
.On Wed, 1 Nov 2006 06:09:02 -0800, JIM.H. wrote:

>I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
>also have T2 in Database2 which has the same fields and some extra fields.
>Now based on the T1.Type=Insert or Update, I need to perform either update
>or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>Done. If this i
s
>a success, I need to set T1.Status=Done for the updated records. So this
>should only be updated records.
>T1 is a frequently inserted table, so there might be more than one record
>coming there at the same time. How should I write my insert trigger and
>correctly set T1.Status=Done, any example would be greatly appreciated.
Hi Jim,
CREATE TRIGGER JimsTrigger
ON T1 AFTER INSERT
AS
IF @.@.ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
INSERT INTO T2 (ID, Type, Status, ...)
SELECT ID, Type, Status, ...
FROM inserted
WHERE Type = 'Insert'
AND Status <> 'Done'
UPDATE T2
SET Type = i.Type,
Status = i.Status,
..
FROM inserted AS i
JOIN T2 ON T2.ID = i.ID
WHERE i.Type = 'Update'
AND i.Status <> 'Done'
UPDATE T1
SET Status = 'Done'
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.Type IN ('Update', 'Insert')
AND i.Status <> 'Done'
AND i.ID = T1.ID)
go
Untested, and you'll probably want to add error handling.
Hugo Kornelis, SQL Server MVP

q; how to write this trigger

I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update
or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>â'Doneâ'. If this is
a success, I need to set T1.Status=â'Doneâ' for the updated records. So this
should only be updated records.
T1 is a frequently inserted table, so there might be more than one record
coming there at the same time. How should I write my insert trigger and
correctly set T1.Status=â'Doneâ', any example would be greatly appreciated.On Wed, 1 Nov 2006 06:09:02 -0800, JIM.H. wrote:
>I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
>also have T2 in Database2 which has the same fields and some extra fields.
>Now based on the T1.Type=Insert or Update, I need to perform either update
>or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>?Done?. If this is
>a success, I need to set T1.Status=?Done? for the updated records. So this
>should only be updated records.
>T1 is a frequently inserted table, so there might be more than one record
>coming there at the same time. How should I write my insert trigger and
>correctly set T1.Status=?Done?, any example would be greatly appreciated.
Hi Jim,
CREATE TRIGGER JimsTrigger
ON T1 AFTER INSERT
AS
IF @.@.ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
INSERT INTO T2 (ID, Type, Status, ...)
SELECT ID, Type, Status, ...
FROM inserted
WHERE Type = 'Insert'
AND Status <> 'Done'
UPDATE T2
SET Type = i.Type,
Status = i.Status,
...
FROM inserted AS i
JOIN T2 ON T2.ID = i.ID
WHERE i.Type = 'Update'
AND i.Status <> 'Done'
UPDATE T1
SET Status = 'Done'
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.Type IN ('Update', 'Insert')
AND i.Status <> 'Done'
AND i.ID = T1.ID)
go
Untested, and you'll probably want to add error handling.
--
Hugo Kornelis, SQL Server MVP

Q: totals in matrix report

Hello,
How can I create a total at the bottom and on the right in a matrix type
report.
Thanks,
Jim.Right click on the column you want to have 'Total' and choose "Sub
total".
Henry
JIM.H. wrote:
> Hello,
> How can I create a total at the bottom and on the right in a matrix
type
> report.
> Thanks,
> Jim.