Cross Server Update Problem

Sep 11, 2006

The system has worked for two years.

SQL Server 2000 runs on Windows 2000 Server €œA€?.

Another instance of SQL Server 2000 was moved from Windows 2000 Server €œB€? old to Windows 2003 Server €œB€? new, by restoring a backed up copy from old to new.

The system has cross server updates, where Server €œA€? is updating records in very large tables on Server €œB€?.

After the move from Server €œB€? old to Server €œB€? new, suddenly the cross server updates fail, but only when they are conducted within a Begin and Commit Transaction batch.

The system works when I remove the Begin and Commit Transaction, and no longer have them run in a batch.

Also, I had a cross server update, having €œA€? update records in €œB€?, from a delete trigger residing on €œA€? table. Deletes against the €œA€? table fail so long as the trigger remains. Once I remove the delete trigger from the €œA€? table, I can now delete records on the €œA€? table.

Thanks. Dotnet Fellow

View 5 Replies


ADVERTISEMENT

Cross Server Update Problem

Sep 11, 2006

The system has worked for two years.SQL Server 2000 runs on Windows 2000 Server "A".Another instance of SQL Server 2000 was moved from Windows 2000 Server"B" old to Windows 2003 Server "B" new, by restoring a backedup copy from old to new.The system has cross server updates, where Server "A" is updatingrecords in very large tables on Server "B".After the move from Server "B" old to Server "B" new, suddenlythe cross server updates fail, but only when they are conducted withina Begin and Commit Transaction batch.The system works when I remove the Begin and Commit Transaction, and nolonger have them run in a batch.Also, I had a cross server update, having "A" update records in"B", from a delete trigger residing on "A" table. Deletesagainst the "A" table fail so long as the trigger remains. Once Iremove the delete trigger from the "A" table, I can now deleterecords on the "A" table.Thanks. Dotnet Fellow

View 9 Replies View Related

Information_shema Cross Server?

Oct 24, 2005

Hi All:

When I check out > 2000 customer databases cross 8 SQL 2000 servers, I wish I could use
"Select * from " + @server + "." + @databaseName + ".information_Schema.coulmns
where column_name = " + @column_name.

It did not works in this way.
However, why it works on
"Select * from serverA.master.information_schema.columns"?
It is also a cross server query but is on Master DB.

thanks
-D

View 3 Replies View Related

Cross Server Query

Jan 31, 2008

Hi all,

My support team inform me that my servers have been updated and rebooted on monday. Now I need to run some cross db queries today and I dont seem to be able to connect anymore. I am getting the (dreaded apparant but not surprisingly) Msg 17, Level 16, State 1, Line 1
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Now first thing I did was try to drop both linked servers and add again. I am thinking that the particular server has a dynamic ip which I have determined. Ive noticed a work around on MSFT but it say about using a connection string with IP.

Can anyone help me work this out? id appreciate it.

"Impossible is Nothing"

View 7 Replies View Related

Cross-tab Query In SQL Server

Oct 14, 2006

I have a table with results from several inspection tests. The Pass/Fail parameter in the cross-tab output would be Pass if all tests for that serial number are Pass, and Fail if any of the results for that serial number are Fail.

How would I create a cross-tab query in SQL Server?

Table for input to cross-tab query: [Inspection Data]
SerialNumber ParamName Result Pass/Fail

001 Test1 3.43 Pass

001 Test2 3.27 Pass

001 Test3 2.97 Pass

002 Test1 2.88 Pass

002 Test2 3.01 Pass

002 Test3 4.22 Fail

003 Test1 3.11 Pass

003 Test2 2.91 Pass

003 Test3 3.28 Pass

Cross-tab Query Output:

Test1 Test2 Test3 Pass/Fail
001 3.43 3.27 2.97 Pass
002 2.88 3.01 4.22 Fail
003 3.11 2.91 3.28 Pass
Thanks,
Sam

View 4 Replies View Related

Cross Tabs Problem In Sql Server

Apr 17, 2004

I want a query which is used in sql server like access query

TRANSFORM Sum(Q_DayBook.Debit) AS SumOfDebit
SELECT Q_DayBook.Purticular, Sum(Q_DayBook.Debit) AS [Total Of Debit]
FROM Q_DayBook
GROUP BY Q_DayBook.Purticular
PIVOT Q_DayBook.CDate;

View 1 Replies View Related

Cross Server Database Dumps

Oct 5, 2004

I am attempting to remember the string to dump/restore a database to a different physical server.

I have the database on Server 1 and want to back it up to server 2. Can anyone refresh my memory?

Thanks

View 3 Replies View Related

Cross Server Database Dump

Oct 5, 2004

I am attempting to remember the string to dump/restore a database to a different physical server.

I have the database on Server 1 and want to back it up to server 2. Can anyone refresh my memory?

Thanks

View 2 Replies View Related

CREATE Cross Tab Query Using Sql Server 6.5

Feb 1, 1999

hi, here I create a cross tab query in access 97 this is the code

TRANSFORM Count([1].ORD_NBR) AS [The Value]
SELECT [1].SRMGR_NAME, Count([1].ORD_NBR) AS [Total Of ORD_NBR]
FROM 1
GROUP BY [1].SRMGR_NAME
PIVOT [1].ASR_SUPP;

I export this table to sqlerver 6.5 and run the same code. IT DIDNOT WORK
My question is how can I implement crosstab query in sql server 6.5?

any one can help I really do appreciate

Ali

View 2 Replies View Related

Cross-server SELECT Query

May 2, 2006

Hi,

Recently the powers-that-be migrated the largest databases from one server to another, more powerful server while keeping some support data on the original server. My problem: I need to run queries on tables spanning both database servers. Unfortunately, I can't find any documentation on how to do this. Does anyone have any ideas?

Thanks!

View 1 Replies View Related

Cross-validation In SQL Server 2008

Nov 28, 2007

Hi, can someone please explain some more how to use cross-validation in SQL Server 2008 (CTP)? I read here that it "is under Accuracy Charts in Business Intelligence Development Studio, in addition to being accessible programmatically via a stored procedure call". What is the stored procedure and input parameters?
I'm actually interested in doing so inside an Integration Services package, in a data flow task.

Thanks,
Gustavo Frederico

View 8 Replies View Related

Cross Server Updating Via Trigger Issue

May 22, 2002

I need to update serverB.databaseB.tableB.columnB value
based on serverA.databaseA.tableA.columnA value change,

the update trigger in serverA.tableA works fine in
updating a testing databaseB.tableB.columnB in the same serverA.

serverB is as the linked server in ServerA and DTC is on.
when change the trigger to point to serverB.databaseB....
error:--------------------------------------------
Server: Msg 7395, Level 16, State 2, Procedure trInsUpdDel_InboundCannedMessages, Line 169
Unable to start a nested transaction for OLE DB provider 'SQLOLEDB'.
A nested transaction was required because the XACT_ABORT option was set to OFF.
[OLE/DB provider returned message: Cannot start more transactions on this session.]

thanks for the help
David

View 2 Replies View Related

Cross Server Data Compare Using Findstr

Nov 6, 1998

I am trying to compare the data from 2 different servers. It is the same table. I did a select * and put the data into 2 different text files. I then used the Windows NT findstr utility to compare them to find the differences. I used the command findstr /v /i /x /g:file1 file2 > file3, where file1 is the larger table, and file2 is the smaller one. I want to find the records in file2 which are different or do not exist. I recieved no data back. I have ASCII characters in the text files. My question is does anyone know of a better way to do this, or how to make this command work?

View 1 Replies View Related

Cross-validation In SQL Server 2008 - Log Likelihood

Dec 13, 2007

Hi! I am using the Cross Validation tab in BIDS. Can you explain why my Liklihood Log Score is a negative number (according to BOL - meaning it is worse than a random guess) when my lift chart shoes that the alogorithm is significantly better than a random guess.

Also, the BOL definition for truenegative and falsenegative are hard for me to interpret. If I have a target state of "Yes", can you please put those definitions in yes/no terms for me?

Thanks.
Ann

View 3 Replies View Related

Is Cross-Validation (or Rotation Estimation) Available In SQL Server?

Mar 28, 2007

Dear forum users,

I am a newbie in using MS SQL server with analysis services.
There seems to be no 'cross-validation' tool in MS SQL
which is frequently used in data mining and even statistics.
Is there anyone having similar difficulties?
Is there any solution like a small scripts to divide
the given dataset with multiple folds?
Your valuable comments and feedbacks would be appreciated.

Minnetongka

View 4 Replies View Related

SQL Server 2012 :: Using Cross Apply To UNPIVOT Data

Jan 15, 2014

I was reading Kenneth Fisher's and Dwain Camps' articles on unpivoting using cross apply... And I can actually get them to work....

CREATE TABLE #TxCycle(
Cycle INT NOT NULL,
PatientID INT NOT NULL,
ALOPECIA TINYINT,
Causality1 TINYINT,
Relatedness1 TINYINT,

[Code] ....

The one thing I was wondering was this: how do I extract the symptom names from the field list without knowing them all beforehand? Dwain does this

-- DDL and sample data for UNPIVOT Example 2
CREATE TABLE #Suppliers
(ID INT, Product VARCHAR(500)
,Supplier1 VARCHAR(500), Supplier2 VARCHAR(500), Supplier3 VARCHAR(500)
,City1 VARCHAR(500), City2 VARCHAR(500), City3 VARCHAR(500))

Can this be adapted if you don't know all the column names beforehand? (Likely not). Back in the dark ages, when I was working on a database like this, it was in Access, and I could loop over the fields collection and evaluate each field name. (Yes, I know you're not supposed to store information in field names, but I inherited that mess!)

View 7 Replies View Related

SQL Server 2014 :: Cross-database Function Call

Jul 2, 2015

I have 2 databases on the same server

One has a function, the other has the tables and views

using dba

select dbo.function(t.column) as x from dbb.dbo.table as t

gives m an invalid object name of dbo.table

using dba

select top 1 * from dbb.dbo.table works fine.

also if i create the function on the same db it works.

View 4 Replies View Related

SQL Server 2008 :: SSIS Package In Cross Domain?

Jul 3, 2015

I have created SSIS package to transfer table data(1500 rows tbl size=140 MB).

To transfer data across the network its taking around 1 hr.

I have tried by removing indexes and constraints on destination table.

How should I improve the speed as the table is very small.

View 0 Replies View Related

SQL Server 2008 :: Cross Apply With Parameterized Function?

Sep 9, 2015

I'm unable to reproduce the error. when they upgrade their OS and SQL EXPRESS to a more recent version the error disappears.

The error is: Incorrect syntax near '.'

the query in question resembles this:

Select column1, column2
from Table1 T
cross apply function(t.column4,t.column5) F
where column3 = 'XXXX'

I made sure that the compatibility level is greater than 90 this error is happening on SQL2005 SP2 as well as SQL2008 with SP2 (but not all clients are suffering from the same problem)

Can it be the .net framework? Although the machines had .net framework 3.52.

Can the OS be an issue? The OS' seem to be old, Windows Server 2008 SP2

I've tried to reproduce the error by setting up virtual machines with same OS and SQL but, again, can't reproduce.

View 9 Replies View Related

SQL Server 2014 :: Writing A Cross Join Query With One Table?

Jul 19, 2015

writing a cross join query with one table:

Cities(City_name, X_coordinate, Y_coordinate)

the result should be all combinations without reverse column returns

SELECT * FROM [dbo].[Cities] as P1
Cross JOIN [dbo].[cities] as p2
where (p1.City_name != p2.City_name) and ???

for example if there are three Cities as A,B,C the result should be: A->B, A->C, B->C (without the returns B->A, C->A, C->B)

View 8 Replies View Related

SQL Server 2014 :: Column Store Query Reverting To Row Mode With CROSS JOIN

May 20, 2015

I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.

-- The purpose of this script is to demonstrate that
-- two queries against a columnstore index that each execute in batch mode
-- will continue to execute in batch mode when inner joined.
-- However, one of the queries will execute in row mode when cross-joined.

-- Create function to return 0 to n rows
IF OBJECT_ID('dbo.IntCount') IS NOT NULL
DROP FUNCTION dbo.IntCount;

[Code] .....

View 6 Replies View Related

DB Engine :: Linked Server - Getting Error When Performing Cross Instance Query With Joins

Apr 26, 2015

I've successfully created a Linked Server that connects a local DB Engine with another DB Engine through an ip over an extranet. I am able to run simple Select statement queries on the Local DB Engine and get results from the linked server. However when attempting to perform more complex queries that join tables from the linked server with tables from the local DB server, I get the following error message after several minutes of execution:

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Protocol error in TDS stream".
OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg -1, Level 16, State 1, Line 0

Session Provider: Physical connection is not usable [xFFFFFFFF].

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg -1, Level 16, State 1, Line 0

Session Provider: Physical connection is not usable [xFFFFFFFF].

OLE DB provider "SQLNCLI11" for linked server "<ip of Linked Server>" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0

TCP Provider: An existing connection was forcibly closed by the remote host.

How I can resolve it. I've read on Distributed Transactions but I understand that it only applies to manipulation statements?

Both are SQL servers. Linked Server is SQL2008R2 if not mistaken. Local DB Engine is SQL2014.

View 3 Replies View Related

SQL Server 2012 :: CROSS APPLY Returning Records From Left Recordset Even When No Matching Record In Right One

Oct 7, 2014

Following is the query that I'm running:

create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);

select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;

Below is the result for the same:

idname sal
1John500
1ken500
2paulNULL

Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.

One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output. I'm running this query on SQL Server 2012.

View 6 Replies View Related

Update On Machine Runs Immediately, Update On Linked Server Takes 8 Minutes

Jan 2, 2008

What's up with this?

This takes like 0 secs to complete:

update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')

but From linked server this takes 8 minutes????!!!??!:

update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159

where enc_id in

('C24E6640-D2CC-45C6-8C74-74F6466FA262',

'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',

'D7FBD152-F7AE-449C-A875-C85B5F6BB462')


What settings or whatever would cause this to take so much longer from the linked server?

Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.

2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.

View 5 Replies View Related

SQL Server 2000 - Issue W/ UPDATE - Single Row Update Returns 2 Different Messages

Nov 11, 2007

I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000.  I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process.   Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue.  In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine. 
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically).  When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense.  There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks 
 

View 2 Replies View Related

SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE

Apr 16, 2015

If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?

EXAMPLE:

CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,

[Code] ....

If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)

INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE

View 9 Replies View Related

Report Builder And Report Server Cross Domain Communication

Dec 19, 2006



I have client machine in one domain, say A domain , on which I have installed .net 2.0 without any problem. I have report server running in other domain (B domain) where I have deployed few models which will be used from domain A to create ad hoc reports by launching Report Builder. I can access other reports in domain B from A

But when I click on Report Builder ..it tries to connect to server in domain B but fails to connect and shows following log in a text pop up

I know, problem is because of cross domain communication but question is then how to fix it ? what changes I need to do for that in firewall, domain etc etc settings?

Here is the error, I have highlighted lines which might give some clue:

------------------------------------------------------------------------------------------

PLATFORM VERSION INFO
Windows : 5.2.3790.65536 (Win32NT)
Common Language Runtime : 2.0.50727.42
System.Deployment.dll : 2.0.50727.42 (RTM.050727-4200)
mscorwks.dll : 2.0.50727.42 (RTM.050727-4200)
dfdll.dll : 2.0.50727.42 (RTM.050727-4200)
dfshim.dll : 2.0.50727.42 (RTM.050727-4200)

SOURCES
Deployment url : http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application

ERROR SUMMARY
Below is a summary of the errors, details of these errors are listed later in the log.
* Activation of http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application resulted in exception. Following failure messages were detected:
+ Downloading http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application did not succeed.
+ The remote server returned an error: (500) Internal Server Error.

COMPONENT STORE TRANSACTION FAILURE SUMMARY
No transaction error was detected.

WARNINGS
There were no warnings during this operation.

OPERATION PROGRESS STATUS
* [12/19/2006 6:49:58 AM] : Activation of http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application has started.

ERROR DETAILS
Following errors were detected during this operation.
* [12/19/2006 6:50:14 AM] System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Downloading http://192.168.1.197/ReportServer/ReportBuilder/ReportBuilder.application did not succeed.
- Source: System.Deployment
- Stack trace:
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState)
at System.Deployment.Application.DownloadManager.DownloadManifestAsRawFile(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirectBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestBypass(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, SubscriptionState& subState, IDownloadNotification notification, DownloadOptions options)
at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut)
at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
--- Inner Exception ---
System.Net.WebException
- The remote server returned an error: (500) Internal Server Error.
- Source: System
- Stack trace:
at System.Net.HttpWebRequest.GetResponse()
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)

COMPONENT STORE TRANSACTION DETAILS
No transaction information is available.

View 1 Replies View Related

Update Function: Why SQL Server Update An Empty String With 0?

May 13, 2008

I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.

View 23 Replies View Related

SQL Server 2012 :: Update Statement Will Not Update Data Beyond 7 Million Plus Rows Out Of 38 Millions Rows

Dec 12, 2014

I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).

SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0

View 5 Replies View Related

T-sql Cross Tab

Jul 20, 2004

I should've posted this thread here....

http://www.dbforums.com/t1005070.html

View 1 Replies View Related

Cross Tab?

May 18, 2004

I need to format the following tables:


tbEmployees
EmployeeID | fName | lName
-------------------------------------------
jdoe | Joe | Doe
bsmith | Blake | Smith

tbDepartments
DepartmentID | Department
-------------------------------
ENG | Engineering
DET | Detailing

tbDepartmentEmployees
fkEmployee ID | fkEmployeeID
------------------------------
ENG | jdoe
DET | bsmith

tbProjects
ProjectID |
---------------
1001

tbProjectTeam
fkProjectID | fkEmployeeID | fkDepartmentID
-------------------------------------------
1001 | jdoe | ENG
1001 | bsmith | DET


To the following view :


vProjects
ProjectID | Engineer | Detailer
-------------------------------------
1001 | Joe Doe | Blake Smith


Any Ideas?
Mike B

View 1 Replies View Related

Cross Tab

Jun 17, 2008

Is it possible change to cross tab using code?

Before code:

NAMETYPEVALUE
AAAHEIGHT66
AAAWEIGHT198

After code

NAMEHEIGHTWEIGHT
AAA66198

View 5 Replies View Related

Cross Tab

Oct 20, 2006

Hi,
I'm just starting to learn SQL, just on the querying side and have what I think is quite a tricky function. It may be simple for you guys.

I want to make a report based on three tables. Enrolment, Course and Student. Its pretty easy until I need to crosstab it.

It's more complex than this (more fields but this gives the gist) Basically I want AcademicYearId, StudentID, coursecode, completionstatusid which would give the following.

AcademicYearId StudentId coursecode completionstatusid
05/06 912345 FWFT1 CONTINUING
05/06 914587 FWFT1 CONTINUING
05/06 987654 FWFT1 WITHDRAWN
05/06 954376 FWFT1 CONTINUING
05/06 934512 FWFT1 TRANSFERRED
05/06 923000 FWFT1 TRANSFERRED
04/05 945109 FWFT1 COMPLETED

I have managed to group it by academicyearid, coursecode and completionstatusid and done the counts ok but I want to twist the completionstatusid so each type of status has its own column like this

Year CourseCode Students Continuing Withdrawn Transferred Completed
05/06 FWFT1 6 3 1 2 0
04/05 FWFT1 1 0 0 0 1

Can anyone help me with the the extra columns please.

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved