Getting Data From Two Disconnected Table !

May 4, 2008



Hi,

We have two disconnected tables as shown below.
Table A Table B
Fields Class_NO Class_NameT State Class_No ColA ColB ColC State

Values 1 x S 3 xO e e S
2 XR S 4 UI re er S
9 YU w re S

8 OP we we S


We want to display the data from table A and table B as below.



Class_No Class_Name State Calss_NO Cola ColB ColC State

1 x S 3 xo e e S
2 XR S 4 UI re er S
9 YU w re S
8 OP we we S

Can it be done ? What should be sql query.

View 8 Replies


ADVERTISEMENT

Disconnected Data Store Options

Oct 4, 2007

I have a .Net database application that we've successfully deployed in a connected environment. Now we have a client that has the need to store data on a central SQL Server and publish that data out to tablet PC's that will be able to disconnect from the central SQL Server. At some point those tablet PC's will come back in and connect to the central server via VPN and will need to push their changes back to the server. Some fundamental questions:

1) Am I correct in assuming that replication is the best way to accomplish this?

If so,

2) Which replication type sounds appropriate to the above scenario?

3) Am I correct in assuming that the tablet PC's will need some version of SQL Server to support editing of the data in the disconnected state?


My perhaps incorrect first take on this was that we could use a licensed SQL Server on the central server and SQL Server Express as a replication subscriber on the tablet PC's.

Any guidance greatly appreciated!

View 1 Replies View Related

Temporary Table Not Dropped After User Disconnected (SQL2005)

Oct 7, 2005

Hello all,

View 12 Replies View Related

Disconnected Recordset Error On OLE DB Destination Data Flow

Oct 2, 2007

I have an update query in an OLE DB Destination (access mode: SQL Command) that updates a table with an INNER JOIN from another table in another database. I'm getting the error, "No disconnected recordset available for the specified SQL statement". Does this have to do with the SQL query trying to access the other database? How can I get around this error?

View 4 Replies View Related

ADO Disconnected Recordset

Mar 10, 2004

Hi ...

This is a C++ / ADO / SQL question. Maybe not the right forum but I am guessing there are some programmers out there ...

I am trying to use ADO disconnected recordset to insert data into a sql table. I am using AddNew(vField, vValue) with UpdateBatch(). The code below does not throw any exceptions ... but does not add data to the table.

Any comments are appreciated,
Thanks,
Chris

void CTestApp::TestDatabaseUpdateBatch1a(void)
{
int nDataCount = 0;
long nIndex = 0;
long nIndex2 = 0;

CString csMessage;
CString csErrorMessage;
CString csTemp;
CString csSQL;

BOOL bIsOpen;
BOOL bIsEmpty;

long nCount = 0;
int nTemp = 0;
int nLimit = 0;

int nTempInt = 0;
long nTempLong = 0;
double nTempDouble = 0;

HRESULT hResult;

SYSTEMTIME st;


int i = 0;

string strTemp;

_variant_t sval;

_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;

COleSafeArray colesaFieldList;
COleSafeArray colesaDataList;

vector<COleSafeArray> *pvecDataList;

pvecDataList = new vector<COleSafeArray>;


COleDateTime oledtCurrentDate = COleDateTime::GetCurrentTime();

// Convert the OleDateTime to the varient
// COleVariant vCurrentDateTime(oledtCurrentDate);
COleVariant vCurrentDateTime;

CMxTextParse *pMxTextParse = NULL;

CMainFrame *pMainFrame = (CMainFrame *)AfxGetMainWnd();
CFrameWnd* pChild = pMainFrame->GetActiveFrame();
CTestMeteorlogixView* pView = (CTestMeteorlogixView*)pChild->GetActiveView();

pView->WriteLog("Start TestDatabaseUpdateBatch1a.");
pView->WriteLog("Load table using AddNew() and UpdateBatch().");


// Define ADO connection pointers
_ConnectionPtr pConnection = NULL;
_RecordsetPtr pRecordset = NULL;

try
{
// When we open the application we will open the ADO connection
pConnection.CreateInstance(__uuidof(Connection));

// Replace Data Source value with your server name.
bstr_t bstrConnect("Provider='sqloledb';Data Source='SQLDEV';"
"Initial Catalog='AlphaNumericData';"
"User Id=cmacgowan;Password=cmacgowan");

// Open the ado connection
pConnection->Open(bstrConnect,"","",adConnectUnspecified);

// Create an instance of the database
pRecordset.CreateInstance(__uuidof(Recordset));

// Select the correct sql string. Note that we are creating an
// empty string by doing a select on the primary key. We are only
// doing inserts and we do not want to bring data back from the
// server

csSQL = "SELECT * FROM dbo.AAMacgowanTest WHERE RecordId IS NULL";
// csSQL = "SELECT * FROM dbo.DICastRaw1Hr";


pRecordset->PutRefActiveConnection(pConnection);
pRecordset->CursorLocation = adUseClient;


pRecordset->Open(csSQL.AllocSysString(), vNull, adOpenStatic, adLockOptimistic, -1);

// Test to see if the recordset is connected
if(pRecordset->GetState() != adStateClosed)
{
// The recordset is connected, we will see if we are
// at the end

if((pRecordset->BOF) && (pRecordset->GetadoEOF()))
{
// The recordset is empty
bIsEmpty = false;
}


if(pRecordset->GetadoEOF())
{
bIsOpen = false;
}
else
{
// disconnect the database
pRecordset->PutRefActiveConnection(NULL);
}
}


// disconnect the database
// pRecordset->PutRefActiveConnection(NULL);

// Disassociate the connection from the recordset.
pRecordset->PutRefActiveConnection(NULL);

// Set the count
nCount = 1;

// now we will scroll through the file
while(nCount > 0)
{
nCount--;

nDataCount = 10;

// test that we got some data
if (nDataCount >= 0)
{
// Start the insert process
// m_pRecordset->AddNew();

COleSafeArray warningList;
//int index, listIndex = -1, bitIndex; // indexing variables
// long lowIndex, highIndex, arrayIndex[2];

VARIANT vFieldList[25];
VARIANT vValueList[25];

int nFieldIndex = 0;
int nValueIndex = 0;


// Setup the fields
vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Name");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Section");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Code");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Latitude");
nFieldIndex++;

vFieldList[nFieldIndex].vt = VT_BSTR;
vFieldList[nFieldIndex].bstrVal = ::SysAllocString(L"Longitude");
nFieldIndex++;


pView->WriteLog("Set data using AddNew() ...");

// COleDateTime is a wrapper for VARIANT's DATE type. COleVariant is
// a wrapper for VARIANTs themselves. If you need to create a
// variant, you can say:
COleDateTime oledtCurrentDate2 = COleDateTime::GetCurrentTime();

// Convert the OleDateTime to the varient
COleVariant vCurrentDateTime2(oledtCurrentDate2);

//Set the DATE variant data type.
memset(&st, 0, sizeof(SYSTEMTIME));
st.wYear = 2000;
st.wMonth = 1;
st.wDay = 1;
st.wHour = 12;

// vect is a vector of COleSafeArrays containing the records
for(i = 0; i < 10; i++)
{

// Setup the data
nValueIndex = 0;
vValueList[nValueIndex].vt = VT_BSTR;
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"BLUE");
nValueIndex++;

vValueList[nValueIndex].vt = VT_BSTR;
vValueList[nValueIndex].bstrVal = ::SysAllocString(L"KSTP");
nValueIndex++;

vValueList[nValueIndex].vt = VT_I4;
vValueList[nValueIndex].dblVal = 100 + nFieldIndex;
nValueIndex++;

vValueList[nValueIndex].vt = VT_R8;
vValueList[nValueIndex].dblVal = 11.11 + nFieldIndex;
nValueIndex++;

vValueList[nValueIndex].vt = VT_R8;
vValueList[nValueIndex].dblVal = 22.22 + nFieldIndex;
nValueIndex++;

// Add the record to the recordset
pRecordset->AddNew(vFieldList, vValueList);
}



pView->WriteLog("Call UpdateBatch().");

// Re-connect.
pRecordset->PutRefActiveConnection(pConnection);

// Send updates.
pRecordset->UpdateBatch(adAffectAll);

// Close the recordset and the connection
pRecordset->Close();
pConnection->Close();

}
}
}
catch(_com_error *e)
{
CString Error = e->ErrorMessage();
AfxMessageBox(e->ErrorMessage());
pView->WriteLog("Error processing TestDatabase().");
}
catch(...)
{
csMessage = "Undefined exception handled. Error message details ";

hResult = GetAdoErrorMessage(m_pConnection,
&csErrorMessage);

csMessage += csErrorMessage;
csMessage += "method: CTestMeteorlogixApp::OnTestDatabaseAdoBulkload()";

AfxMessageBox(csMessage);

}

csTemp.Format("Last Row %03d DIcastId = %s ", nIndex, strTemp.c_str());
pView->WriteLog(csTemp);

pView->WriteLog("End TestDatabaseUpdateBatch1.");

}

View 3 Replies View Related

Disconnected Record Sets

Oct 28, 1999

We're constructing a three-tier application. We want the middle tier to
extract a recordset from the database, disconnect from the database,
then pass the recordset to the front tier. After changes have been made
by the front tier, it will pass the recordset back to the middle tier,
which will reconnect, and update the database.
The problem:
Using stored procedures, the recordset is no longer available
once the connection has been closed. Is there any way, using stored
procedures, to be able to keep the recordset available after the
connection has been closed, either by preserving it, copying it, or
otherwise?

View 1 Replies View Related

Error This Server Has Been Disconnected

Sep 22, 2003

Hi ,
I have maintance plan to rebuld indexes and reorg data on all db
it fails with Error

[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has been disconnected. You must reconnect to perform this operation.

What could be the problem?

Thank you

Alex

View 10 Replies View Related

Linked Server Getting Disconnected?

Apr 6, 2015

I've an issue while calling Oracle Stored procedure from MS SQL Server 2012 using LINKED SERVER, It gets connected and do the execution, But sometimes, it was disconnected and says this message 'A severe error occurred on the current command. The result if any should be discarded'.

View 2 Replies View Related

Disconnected Mirror / In Recovery

Apr 4, 2007

Hello,

I'm having a problem with two mirrored databases, using SQL Server 2005 SP2, that autmatically failed over last night for unknown reasons. I was trying to fail them back over to the primary SQL server because it says the mirror is disconnected and out of sync. Other databases failed over too, but I was able to fail those back over without a problem. It's just these two. I removed the mirror from the secondary server thinking that this would allow me to restore the database back on the primary server, but that didn't help.



These two databases show a status of (Principal, Disconnected / In Recovery) and I still cannot do anything with the two databases on the primary server. When I try to pull up the properties for them, I get the following error:

"Database <Name> is enabled for Database Mirroring, but neither the partner nor the witness server instances are available: the database cannot be opened.(Microsoft SQL Server, Error: 955)"



I cannot delete, detach, Alter or do anything with the databases. If I could just delete them that would be fine so I can just do a restore, but I can't seem to do anything.



Does anyone know what I can do to resolve this problem.



Thanks in advance!



-Jay

View 4 Replies View Related

Disconnected Datasets Between MsSQL And MySQL

Dec 6, 2007

I have a mobile application written in vb.net using MsSQL and I want to use disconnected datasets to sync up to a remote MySQL database. is this possible? Its a two way sync, i.e I download and upload info.

Any suggestions, articles, etc would be great.

View 3 Replies View Related

Unable To Make Changes To Disconnected Recordset

May 16, 2007

Its been almost 10 years since I have had to do work with good-old RecordSet objects...



I am filling a RecordSet with data returned from a SQL server via a stored procedure. I then set the ActiveConnection property to Nothing in order to disconnect it so I can make changes to it.



But when I try to set the value on a given row I get back a "Multiple-step operation generated errors. Check each status value" error message. My understanding is that this is indicative of trying to use the wrong datatype. I have verified that the type is correct (I am dealing with integers) so I am at a loss for what the problem could be.



Here is my code:



Set rsPackages = CreateObject("ADODB.RecordSet")

rsPackages.CursorLocation = adUseClient

rsPackages.LockType = adLockBatchOptimistic



rsPackages.Open "EXECUTE stp_FetchPackageData", myConn



rsPackages.ActiveConnection = Nothing



Response.Write("Value: " & rsPackages("TotalCount")) ' returns 0



Response.Write("Data Type : " & rsPackages.Fields("TotalCount").Type) ' returns 3 = adInteger



rsPackages("TotalCount") = 1 ' throws multi-step error



Oddly enough, when I look at the Attribute property of the field I get back a value of 112. When you break it down I think that value indicates the row value is read-only? (Could that be my problem? Just a really bad/unhelpful error message?) But if I try to change it I get back a message saying cannot be done since RecordSet is already open.



Thanks,

Jason

View 2 Replies View Related

Mirror Disconnected - Database Upgraded

Mar 14, 2007

Hi

While a database upgrade schema changes were being made the Mirror became disconnected will this recover itself when it reconnects or will it be the case that we will have to copy the db files to the Mirror and set it up from scratch.

It was set up as a synchronise mirror

View 2 Replies View Related

How To Manage Concurrency Between Multiple, Disconnected Clients

Sep 25, 2007

I have a system use MS SQL 2005 & .NET 2.0, my tables don't have rowversion, but I heard SQL 2005 manage a rowversion by itself, can I use this to do a "ConflictDetection".All I try to do is I want to get a error when I try to update a row which been modified by someone else after I read row. Thanks.   

View 1 Replies View Related

Keeping PK's Unique Across (potentially) Disconnected Sites.

Apr 17, 2008

Hi All,

I'd like to throw this idea 'out there' to see if I'm missing something I'll later regret.

I'm looking to resolve a scalability issue within our point-of-sale program. Currently the PK on transactional tables (sales and orders) is created by the application layer using a 'MAX(PKCol) + 1' mechanism. Obviously this requires that all users of the system, whether they're local or remote, have current data at any time they wish to insert. It's this limitation I'd like to remove. Most sites are using MS SQL Server 2000. No sites use anything specific to a later version.

By having a PK that can be generated independently of a 'master' database we can overcome this issue. The PK values will need to be unique within a 'group' of shops and able to be generated by a program operating at any level. From 'head office' which manages a number of shops, to the server at a given shop and even the register / till itself should be able to create ID's while disconnected from the server (using a local database).

It seems there's three main ways to accomplish this:
- Identities,
- MachineID, CurrentPK composite.
- GUID's

Identities: I've ruled out identities as I believe the administration overhead of dealing with them makes them impractical (there may be several hundred registers and therefore as many ranges to be set up within a group).

MachineID, CurrentPK composite: The MachineID references a Machine table which has an entry for each ethernet MAC address which connects to the database. The reason I chose to store the MAC in another table rather than simply using it as column is that I'm fetching it from sysprocesses.net_address(nchar(12)) and believe it's computationally cheaper to use an int than a text column. This mechanism means that we can still expose the PK to the user in some cases (eg: InvoiceNumber printed on a receipt). When the local database is not up to date (usually due to network problems) there will be cases where the CurrentPK will be duplicated but kept unique since it's coupled with the new MachineID. The big drawback to this method is that all current code will need to be revised to deal with the composite keys (this will be a significant amount of development).

GUIDs: Ugly to look at and time-consuming to type. They're not something which you'd expose to a user unmodified so realistically this means altering existing code to use a new 'user friendly' number where the PK is currently exposed to them. The use of GUIDs rule-out the use of clustered indecies on tables they're the PK for lest most inserts cause a page split. The splits would also necessitate more frequent index defrags / rebuilds. Using a non-clustered index incurs a penalty Vs a non-fragmented clustered one (doesn't it?) so while this avoids page-splits it comes at a cost.

After all that I think the best solution is to use GUIDs with a non-clustered index for each of the PK's. While it might not be the fastest of the options (slower reads/joins Vs composite PK) it will be significantly faster to develop while maintaining acceptable performance.

Thoughts?

View 14 Replies View Related

OLEDB Errors Using MS Access And Disconnected Recordset

Mar 31, 2004

Hello, Code below returns the following error:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done

<%
Const adLockBatchOptimistic = 4
Const adUseClient = 3

strDataBase = "somedb.mdb"

set cnTraining = server.CreateObject("ADODB.Connection")

cnnstr="Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & server.mappath(strDataBase) & ";Persist Security Info=False"

cnTraining.Mode = 3
cnTraining.Open cnnstr

set rsSearch = Server.CreateObject("ADODB.Recordset")
rsSearch.CursorLocation = adUseClient
rsSearch.LockType = adLockBatchOptimistic

sSql = "some sql stmt" 'this works fine on its own

rsSearch.Open sSql, cnTraining

set rsSearch.ActiveConnection = nothing

cnTraining.Close
set cnTraining = nothing

%>

My goal is to get a disconnected recordset. The problem here occurs when i try to use the adUseClient value (3) for the CursorLocation. If I don't use a 3 in the CursorLocation it works fine. Hoewever i'm almost certain i have to use the 3 in order to disconnect the recordset. Any ideas? Is my connection string not set up properly to get a disconnected recordset?

View 2 Replies View Related

Mirroring Partners Disconnected After Network Outage

Oct 25, 2007

We have mirroring set up on SQL 2005(SP2) on windows server 2003 servers for 6 production servers to DR servers. It is high performance mode(asynchonous). We are using fully qualified server names with default 5022 port. Prod and DR are using same domain service account.

We had network outage for about 30 minutes last weekend and after that network was restored back to normal. Also few SQL 2000 servers have logshipping and it automatically started syncing up after network was restored. However mirroring didn't start automatically. Partners were in disconnected state and endpoints were stopped. For one database, log grew to 40 GB using most of the disk space.

Then we maually had to run

ALTER ENDPOINT Mirroring STATE = STARTED

and then it started syncing up.

Now the question is why it doesn't start syncing up automatically after network is back to normal and recognize the partner?
Is there anyway we can setup timeout or any parameter like that to specify how many times or how long partners try to connect with no luck and then give up?

Mintu

View 6 Replies View Related

After Moving To SQL2005, Disconnected Recordsets Are Ready-only

Feb 16, 2006

We're using ADO disconnected recordsets. On SQL 2000, we could update these on the client (without propagating the changes to the server) even if the underlying view or table was non-updatable.

When running our apps against SQL 2005 (using the same client-side environment), we can no longer change any attributes of those disconnected recordsets, that connect to a non-updatable database object (the rest of the app runs fine, we can update all updatable database objects through disconnected recordsets) . Does SQL 2005 respond to such calls differently from SQL 2000, so that ADO recordsets are built in a new way (which makes them read-only in our setting)?

Thanks for any suggestions.

Rene

View 1 Replies View Related

.NET Class To Hold Single Disconnected Record? Nothing Smaller Than DataSet?

Jul 9, 2007

What is the most efficient standalone .NET class that can hold a single disconnected record?  The class must also retain column names, but other schema is not relevant (.NET data type is sufficient).If I understand System.Data.Common.DbDataRecord, it provides an interface on a DbDataReader, and has no storage of its own.I'm familiar with DataSet, is that the only .NET-standard class to do this? Thank you,Shannon 

View 7 Replies View Related

SQL Express 2005 Snapshot Replication On Disconnected/Closed Networks

Jul 9, 2007

I am having trouble setting up my Pull Subscription and I am new to replication.



I have several servers hosting a databased website that will be the same, except for user input and traffic. Quite simply, I need to copy most tables, SPs and data from network to network. I can't use FTP/Web synch ... as I mentioned the networks do not touch eachother or the internet.



On server Web1, it was easy to create a Publication called Pub via the wizard for my database: TheDB. Then on Web1, again, I added a Subscription to the Publication, indicating my second server, Web2, and the same database name: TheDB (I have already backed up and restored TheDB to all my servers). Here's one of the sp's I ran on Web1:



use [TheDB]

exec sp_addsubscription @publication = N'Pub', @subscriber = N'Web2'', @destination_db = N'TheDB', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'

GO



This is where I feel stuck. Using the wizard on Web2 doesn't allow me to see Web1. So I tried the following on Web2:



use [TheDB]

exec sp_addpullsubscription @publisher = N'Web1', @publication = N'Pub', @publisher_db = N'TheDB', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 1



exec sp_addpullsubscription_agent @publisher = N'Web1', @publisher_db = N'TheDB', @publication = N'Pub', @distributor = N'Web1', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 0, @active_start_date = 0, @active_end_date = 19950101, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'True', @job_login = null, @job_password = null, @publication_type = 0

GO



I copied the snapshot folder, ie. 20070709134423, onto CD and moved it into Web2's default replication folder, but I always receive: cannot connect to Distibutor. I've tried using an Alias, as well, but don't understand exactly how I should point that either. I checked the publication's PAL and my Web2 user has rights and is an owner of the Web2 TheDB database.



Any help is appreciated.



Nate

View 10 Replies View Related

SQL Server Admin 2014 :: Disconnected From Listener In Availability Group (When Primary Failed)

Jun 27, 2015

I setup an availability Group. (Only 2 servers - Primary And secondary) -- 21 , 22

I also define an listener . IP .. 23

1- In First step I connected To Listener (23) And in a while I inserted A record to a table .

While 1=1
insert into Tbl_T1(f1,f2) Values (1,2)

2- in second, I Stop the primary .

- I expected this while whitout disconnect, continue.

3- The while code stopped whit this message :

Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

4- I execute again the script, And it worked in new primary.

My questions :

1- is the listener disconnected between switched primary and secondary ? OR have we data loss between switching?

2- I did some huge update on Primary that fill the Log fiel space. And in last Update I got this error :

Msg 9002, Level 17, State 2, Line 27

The transaction log for database 'Your_DB' is full due to 'LOG_BACKUP'.

Is this (Fill All space) a reason to switch primary? Or not ?

View 2 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

Integration Services :: Purge Data In Transaction Table Or Delete Some Data And Store In Separate Table

Aug 18, 2015

How to purge data  in transaction table or we can delete some data and store in separate table in data warehouse?

View 7 Replies View Related

Mirror And Witness Connection In A Disconnected State Immediately After Adding Witness Server To Mirror Session.

Jan 31, 2008


After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.

The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.

ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'

After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.

The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.

Any idea's why the state between Mirror and Witness Connection remains Disconnected?

Thanks,

View 9 Replies View Related

Connected Model Vs. Disconnected Model

Aug 10, 2005

hi !!i have a question about the connected and disconnected model to access the Sql server DB.......i know that there is better to choose one rather than the other in some situantions and there is no better model in all cases...... os i hope you can help me to decide what shall i choose...i will use the DB to connect to Web services and read data from the DB and wrtie some data back.......i do not know that to use ..... i hope you advise me and tell me about the rules that will allow me to choose  what model to choose .... i appreciate your help!!Thanks !!!

View 2 Replies View Related

Stored Procedure To Copy Table 1 To Table 2 Appending The Data To Table 2.

Jan 26, 2006

Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.

View 2 Replies View Related

Create Table From Text File, Extract Data, Create New Table From Extracted Data....

May 3, 2004

Hello all,

Please help....

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.

View 3 Replies View Related

Updating Data In One Table Based On Other Table Data

Apr 20, 2003

I'm not sure if this could be done, but if anyone has any insight on how to do this please let me know...

Currently, I have a table that has a field of Categories. I recently created a Category table in which each category has it's own ID. I need to replace the Data that was in my original table with the new ID's based on the actual category name... Is there any way of doing this without manaul data entry?

View 2 Replies View Related

Updating Data In One Table Based On Other Table Data

Apr 20, 2003

I'm not sure if this could be done, but if anyone has any insight on how to do this please let me know...

Currently, I have a table that has a field of Categories. I recently created a Category table in which each category has it's own ID. I need to replace the Data that was in my original table with the new ID's based on the actual category name... Is there any way of doing this without manaul data entry?

View 2 Replies View Related

How To Take Data Out Of Table, Restructure The Table And Then Put The Data Back In

Oct 26, 2005

Hi AllWonder if you could help, I have a bog standard table called STOCKPRICESthat has served me well for a while, but now I need to change the structureof it and because a number of users have used it in it's present form I needto so the following in SQL script:a) Grab a snapshot of the current SQL data.b) Re-structure the STOCKPRICES table.c) Post this grabbed data back, but in the new format.My script plan was to firstly to rename the current STOCKPRICES table toSTOCKPRICESOLD (you can do this can't you), create a new STOCKPRICES tablein the new format and then somehow extract the data from STOCKPRICESOLD andsquirt it into STOCKPRICES.The current schema for STOCKPRICES is as follows:# --------------------------------------------------# Table structure for table 'STOCKPRICES'# --------------------------------------------------DROP TABLE IF EXISTS `STOCKPRICES`;CREATE TABLE `STOCKPRICES` (`STOCKID` VARCHAR(30),`CURRENCYID` VARCHAR(30),`HDNETAMOUNT` DECIMAL(10,3) DEFAULT 0,`HDTAXAMOUNT` DECIMAL(10,3) DEFAULT 0,`RRPNETAMOUNT` DECIMAL(10,3) DEFAULT 0,`RRPTAXAMOUNT` DECIMAL(10,3) DEFAULT 0,`NETAMOUNT` DECIMAL(10,3) DEFAULT 0,`TAXAMOUNT` DECIMAL(10,3) DEFAULT 0,INDEX `indxCUURENCYID` (`CURRENCYID`),INDEX `indxSTOCKID` (`STOCKID`));Like I said it's very basic.My new table wants to be like the following:# --------------------------------------------------# Table structure for NEW table 'STOCKPRICES'# --------------------------------------------------DROP TABLE IF EXISTS `STOCKPRICES`;CREATE TABLE `STOCKPRICES` (`STOCKID` VARCHAR(30),`CURRENCYID` VARCHAR(30),`PRICELEVELID` VARCHAR(30),`NETAMOUNT` DECIMAL(10,3) DEFAULT 0,`TAXAMOUNT` DECIMAL(10,3) DEFAULT 0,INDEX `indxPRICELEVELID` (`PRICELEVELID`),INDEX `indxCUURENCYID` (`CURRENCYID`),INDEX `indxSTOCKID` (`STOCKID`));The new re-structure means that PRICELEVELID will include a unique referenceto the HD, RRP, standard prices (plus 3 others that I'm going to create).I know this probably very simple data architecture to you guys, but I'm sureyou can appreciate why I need to change the structure to this method so thatI'm not creating redundant data fields if the user only enters a standardprice I won't be storing nothing for the 2 x HD and 2 x RRP price fields.I don't think I've got a problem renaming the old one and re-creating thenew one, but how do I get the data from one to another?My problem is that I have:code, currency, hdnet, hdtax, rrpnet, rrptax, net, taxIVP GBP 2.00 0.35 200.00 35.00 100.00 17.50etc...and I need to get it into the format:code, currency, pricelevelid, net, taxIVP GBP hd 2.00 0.35IVP GBP rrp 200.00 35.00IVP GBP standard 100.00 17.50etc...Any ideas?RgdsLaphan

View 4 Replies View Related

Red X Show On Replication Monitor (publications Is Disconnected From Replication Monitor)

Jan 30, 2007

I have setup transactional replication everything on one box. later(two or three weeks later), Replication monitor is show red X Under my publishers (publications is disconnected). this is SQL2005.

Everyone known how to fix this problem?

Thanks,

Frank

View 1 Replies View Related

How Do I Enter NULL In A Table Cell In The Enterprise Manager UI For Table Data Entry?

Sep 9, 2005

I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column.  Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit".  I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it.  I want to get back to <NULL>.Anybody know?Thank you.Tom

View 1 Replies View Related

Transpose Source Data From A System Via Metadata Lookup Table Into Destination Table

Apr 1, 2014

I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.

Source Data Table:

Table Name: Source

SrcID AGE City Date
01 32 London 01-01-2013
02 35 Lagos 02-01-2013
03 36 NY 03-01-2013

Metadata Table:

Table Name:Metadata

MetaID Column_Name Column_type
11 AGE col_integer
22 City col_character
33 Date col_date

Destination table:

The source data to be loaded into the destination table(as shown below):

Table Name: Destination

SrcID MetaID col_int col_char col_date
01 11 32 - -
01 22 - London -
01 33 - - 01-01-2013
02 11 35 - -
02 22 - Lagos -
02 33 - - 02-01-2013
03 11 36 - -
03 22 - NY -
03 33 - - 03-01-2013

View 7 Replies View Related

SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table

Aug 12, 2014

I want Compare two Table data and insert changed field to the third table ...

View 9 Replies View Related







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