Replication Scenario
Dec 17, 2006
Hi all,
I have a huge replication task I need to perform. The source table has over 250,000,000 records and everyday approximately 400,000 records are added to the system regularly.
Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software. It does have a field which is linked to another table holding the timestamp.
Here is the source table and the other table's structure for reference:
DataLog
Name Datatype Size
DataLogStampID int 4
Value float 8
QuantityID smallint 2
DataLogStamp
ID (Which is foreign key to DataLogStampID field in the above table) int 4
SourceID smallint 2
TimestampSoruceLT datatime 8
What I would like to know is, what is the best practice to handle such replication with MSSQL Server 2000.
I am thinking of developing a procedure which follows the following step using a temp table:
This procedure will run (say) every night.
The temp table will contain a timestamp field.
For one time only, I will run a snapshot replication, so that I can have the existing data into the destination db.
I will record this timestamp into a repl_status table which holds the timestamp of the last replication.
The procedure will read the record from repl_status and select all records from the source table which were added since this date and put them into a new temp table (this table will have the same structure as the source and destination tables) and then a snapshot replication will "add" these new records only every night to the dest. table. By using this method, I will only transfer the records which have been added since the last replication (last night - less records).
Any comments would be greatly appreciated.
Thanks for your time in advance,
Sinan Topuz
View 8 Replies
ADVERTISEMENT
Aug 23, 2005
Greetings to fellow fleetfingers:
View 1 Replies
View Related
Jul 23, 2005
I have a product that sits on a main server and wish to implementfunctionality to allow salesmen to come along, pick up a snapshot of thedatabase, go away and maybe modify/add to it and then come back and"synchronise" their data. I'm reading up on Merge Replication for thispurpose. But anyway, I created a publisher on my server and it went awayand generated a "rowguid" column on all of my tables (my tables all have anIdentity column key field). Now of course my "Inserts" no longer work, asthey expect a GUID. I would have expected SQL Server to automaticallygenerate a guid for new inserts (in a similar way to it's TIMESTAMP), but itappears it doesn't, despite the fact I have "(newid())" as the default forthe column. It always inserts the same value:{00000000-0000-0000-0000-000000000000}.So, back to basics, now I have a guid field for each record, how do I manageinserts?Thanks.
View 3 Replies
View Related
Jul 2, 2007
I have two sites. Site A and Site B
Each site has two databases
Site A
Db1
Db2
Site B
Db1
Db2
Site A Db1 has to perform transaction replication to Site A- Db2 and Site B- Db1 and Db2.
I started Site A as pubisher and distributor and Site A and Site B both as subscriber.
Site B is in a different geographical area (state).
----------
Please suggest the best scenario to save bandwidth and server load for Publisher, and Distributor.
-------
Earlier I thought that I will implement local replication between Site B - in between Db1 and Db2. The Sql Server does not let me set Db1 as publisher, and distributor for its local database Db2.
-------
P.S. My all databases need same transactions though they are connected to different hardware at different places. So please don't question that why I need four similar databases.
View 7 Replies
View Related
Dec 15, 2005
On the national Server: SQL 2005 Enterprise
On the mobile clients: SQL 2005 Workgroup.
I was asked to find a solution to such scenario
2 mobile subscriber S1 and S2 to the same simple merge publication P1 on server N
Day 1,
S1 and S2 both synch up with N and both go off to do fieldwork
Day3,
S1 and S2 both synch up with N.
S1 goes back to work
S2 shutdown the laptop and goes on 2-week vacation.
2 Weeks late
S1 Synch up wit the server and goes off to do fieldwork.
S2 meets S1 in the field. Their workfield is in the North Pole.
S2 has the laptop with data 2-weeks old but no longer can have access to the master publisher N to synch the replica and get latest changes.
S2 will have to sync with S1 since S1 database is fresh. The challenge is to have S2 and S1 replica identical
The Questions:
Is it possible for S2 to sync with S1?
if yes, How to go about it€¦ we need S1 and S2 to have identical replica on their machines?
Now that S1 and S2 are have identical databases and are both doing their fieldwork in the northpole. Can they both sync back with the national publisher N when they have access?
Keep in mind that S2 got its data updated from the replica on S1?
Thank you!
View 3 Replies
View Related
Jul 14, 2006
Dear all,
Since Windows Integrated Authentication does not work over proxy for Replication, could I still use SSL or SQL authentication over proxy? Thanks for any advice.
View 1 Replies
View Related
Apr 23, 2004
Consider the following scenario:
I got 2 webservers (2003) using NLB.
I want some kind of common data store (SQL Server).
I need this data store to be fault tolerant.
I don't have any more machines.
I don't have an enterprise licence for windows.
How do I solve this???
View 3 Replies
View Related
May 4, 2007
I have a company with 5 offices connected with a dedicated line (500+ employees).
They have five databases, one in each office.
Now they want a new application which will replace the old (VB6 on MySQL will now be VB.NET 2.0 with MS SQL Server 2005).
The want all the data availiable in all the offices. We will still have 5 databases in 5 offices, but all with the same data.
Sometimes the dedicated line is lost.
Now I have a few questions:
Will the database be still running while a line is down?
Will the database be still running if replication is of type Merge (instead of continuesly).
Is it good practice to use unique identifiers versus Numeric keys?
Is it good practice when the application uses the "master" database to save global things like "Suppliers" and the local database to add and change Orders?
Do you have some recommended reads?
Thx for your time!
Henri
~~~~
There's no place like 127.0.0.1
View 3 Replies
View Related
Aug 13, 2007
For High Protection mode (2servers, no witness, FULL safety), in the event of a catastrophic failure of the principal, can a remote (but well connected) mirror be forced to assume the principal role? The MS manual "Database Mirroring in SQL Server 2005" on p2 says the failover type for HP mode is manual only (as opposed to forced). Elsewhere it seems to seems suggest otherwise but there is no clear and unambiguous discussion describing the exact procedure.
Can anyone clarify this matter? Thanks,
Bud
View 3 Replies
View Related
Oct 29, 2006
I currently have a workflow "like" application which uses msmq to pass messages to each step transactionally. I'd like to take advantage to something like SSB to make the app scale. Here's how it works today:
Start Workflow WebService puts message on queue.
Windows Service has threaded listeners on each queue for the workflow.
The listeners do a transactional Receive off the queue. By doing this is in a transaction, we can enforce that only one listener will pick up and process the message. Once they have a message they call a .Net Assembly to perform some work. Once the work is performed succesfully, the listeners put the work context on the next queue using the same transaction as the receive and commit the transaction effectively removing the message from the receive queue permanently. If a fatal error occured during processing, the work context is moved to an error queue transactionaly. If the process blew up due to other reasons, the transaction is rolled back and the message reappears on the original queue so that another instance of the service can pick it up. Currently all this is running on one instance of the Windows service and cannot span more than one host machine since MSMQ did not support transactional reads off public queues without using DTC.
I saw the external activator sample and it looks great but I have a few questions. First, I'd have to modify it to call assemblies instead of external processes. No biggie there. One other requirement that one and only one receiver should process the message and that when the next message is sent to the next queue, it's done within the same transaction as the receive. I hope that this can be done without any deadlocks. I also saw mentioned somewhere that this messaging could advantage of the local SQLExpress instances so that even if the main SQL Server instance is down, the messages can go out. Last requirement is that our message is a blob (serialized .Net object) so how do we define that kind of message?
Thanks
Costas
View 6 Replies
View Related
Dec 9, 2005
Table1: WriterID, WriterName, WriterSurname
Table2: BookID, WriterID, BookName, Category
Table3: CategoryID, CategoryName
There will be one form. Which way i should use to enter data from one form to multiple tables with same WriterID.
1.) Should i use SqlCommand class and a Stored Procedure?
( Like that: http://www.aspnet101.com/aspnet101/tutorials.aspx?id=13 )
2.) Is there another way without a stored procedure? I can't imagine how to insert same form to multiple tables. In real scenario there are lots of table and all contains a column that holds same value such as WriterID.
View 1 Replies
View Related
Sep 22, 2005
Hi, I want to know the a solution for my Synchronization Scenario
I have a several client databses which are SQL Server 2005 Express and i have a master database which is SQL Server 2000 containing all the individual Client databases. All the individual client databases are kept seperately at the master location. I need to Synchronization the client database with its copy at the master database (something like Merge replication). Both the Client Copy as well as Master Copy could be Publishers & Subscribers.
Now the problem is Because of security & firewall issues, only the Client should have the ability to schedule & initiate the synchronization process with the master copy. Unfortunately SQL Server 2005 Express has only subscriber agent and not a publisher agent.
Any help on how to achieve this would be appreciated . Thank You
View 1 Replies
View Related
Oct 10, 2005
Hello All ..
This is the scenario I'm having :
-- I'm a beginner so bear the way I'm putting it ... sorry !
* I have a database with tables
- company: CompanyID, CompanyName
- Person: PersonID, PersonName, CompanyID (fk)
- Supplier: SupplierID, SupplierCode, SupplierName, CompanyID (fk)
In the Stored Procedures associated (insertCompany, insertPerson, insertSupplier), I want to check the existance of SupplierID .. which should be the 'Output' ...
There could be different ways to do it like:
1) - In the supplier stored procedure I can read the ID (SELECT) and :
if it exists (I save the existing SupplierID - to 'return' it at the end).
if it doesn't (I insert the Company, the Person and save the new SupplierID - to 'return' it at the end)
------------------------------------
2) - Other way is by doing multiple stored procedures,
. one SP that checks,
. another SP that do inserts
. and a main SP that calls the check SP and gets the values and base the results according to conditions (if - else)
3) it could be done (maybe) using Functions in SQL SERVER...
There should be some reasons why I need to go for one of the methods or another method !
I want to know the best practice for this scenario in terms of performance and other issues - consider a similar big scenario ..... !!!
I'll appreciate your help ...
Thanks in Advance . ! .
View 1 Replies
View Related
May 26, 2008
How can I be able to use "NewReceivedTime" as a variable?
Since having to create a new column using CASE statement in SQL would mean that user will not be able to use this new column name and having to receive error such as "Invalid Column Name: NewReceivedTime "
(case
when <value>
else <value>
end) as NewReceivedTime
I'm asking this because, I would want to use "NewReceivedTime" I've created to equate to another Time Column like
NewReceivedTime = LogDateTime something like that.
Thanks.
View 1 Replies
View Related
May 11, 2006
Hi,
i hav a text file in this format..
currency,exchangerate(INR),date
dollar,45,20/04/2006
dollar,46,22/04/2006
britishpound,65,20/04/06
dirham,12,20/04/06..etc..
now,i want that using this as source.. 2 tables should be created and filled with appropriate data..
CurrencyMaster..Currencyid(PK),Currencyname
CurrencyDailyRate..ID,Currencyid(FK),rate,date
how can i do it using SSIS?
thks
View 1 Replies
View Related
May 16, 2007
What I'd like to do is use SQL Express as a back-end for an existing Access 2003 application. The application is currently a single-file Access solution. I'm just investigating options to separate the front-end from the back-end so that the users can access a single data store from multiple PCs. We're only talking about maybe 5 users total, and really no more than 2 accessing it simultaneously at any given time (although the same setup exists in several offices). Right now they are just opening the Access db from a fileshare. This has resulted in corruption on a few occasions, which is part of the reason for wanting to replace the current solution with something that will be a little more robust. I'm wondering if there is a way to deploy a SQL Express db on a fileshare so that it can be connected to by the Access front-end. While we can install things on their desktops, we can't install anything on the file server, we can just put files there. Is there any way that I could make that solution work, or should I just stick with separating the Access front-end and back-end?
Thanks,
Kris
View 1 Replies
View Related
Jul 20, 2006
Hi,
I am faced with a scenario where I have to predict the time period that will
required to solve a particular problem depending on various factors like
Problem Type, Severity Level, Resource Availability etc.
I would like to know which algorithm is best suited for the
above scenario and the reasons for the same.
View 3 Replies
View Related
Dec 24, 2007
Hi,
I'm about to implement a DB Mirroring on my production server.and i'm wondering about the scenario what will happen if.
1. if my primary server suddenly die.how i can operate the mirror DB without losing data?
2. when the primary server comes to life, how i make the primary server to lead again with the DB Mirroring?(do i need to recreate all procedures of DB Mirroring again)
3. how can i be sure that the DB mirroring is indeed mirrored on the drp server?
4. what about alerting - can i be notified via email if the DB Mirroring isn't sync? or if the drp server is down and other issues that can be occur .
Thx
View 9 Replies
View Related
Aug 10, 2006
Hi,
I have a product basket scenario in where I have to recommend contracts to a customer based on the product and the quantity he/she buys. Product Quantity is an important factor which administers the user in the purchase of a particular contract
I have the following tables with me.
Customer product transaction table, Customer Contract transaction table but there is no direct relationship between contract and product in the database. The only way the two can be linked is through the customer.
If I create a mining structure with Customer-Product information as the nested table and Customer-Contract information as the nested table with customer being the link between the two, the model is showing some irreverent contract recommendations.
what is the solution for the above problem? Is it because the is no direct relationship between the product and the contract?
How can I overcome this problem?
View 6 Replies
View Related
Apr 25, 2007
Hello All
We have Asynchronous DB Mirroring established for our Production database
which runs on SQL Server 2005.
For the DR Test we plan to do the following during the period of low activity :
1. Pause the mirroring on the Principal Server.
2. Break the mirror on the Mirror Server
3. Take on offline backup on the Mirror.
4. Bring the Mirror Database up.
5. Run the DR Tests on the Mirrored Database
6. Restore the Offline Backup taken in step 3.
7. Reestablish the mirror from the Principal to Mirror.
8. Resume Mirroring on the Principal Server.
9. Verify Mirroring is happening on the Mirror Server
Can u please let me know if this plan is feasible and if there should be any modifications to the plan that are required. Any other suggestions/input is appreciated.
TIA
Best,
Jay
View 1 Replies
View Related
Oct 3, 2007
we have a pretty vast t-sql statement that queries out production db and inserts results into a reporting table on the same db. the problem we're having is that during this process there are several locking processes engaged (key, table, schema, etc). i thought by moving this entire process to a seperate (reporting) database i could bypass this locking problem. however, the same problem exist in the reporting database. is there a way to disable all locking (this is just a reporting database so im not worried about the data integrity. any suggestions to disable all locking? by the way its a sql 2000 server.
example:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
//Select cnttrtydayspast
insert into tampa_pmr ( hprop, sCategory, sSubCategory1, sSubCategory2, sSubCategory3, dtMonth, sMonth, sYear, iValue, iSort1, iSort2, iSort3, iSort4, iLevel)
(select d.hprop, d.sCategory, d.sSubCategory1, d.sSubCategory2, d.sSubCategory3, d.dtMonth, d.sMonth, d.sYear, d.iValue, d.iSort1, d.iSort2, d.iSort3, d.iSort4, d.iLevel
from
(select
p.hmy hProp,
'Avg Days For Outstanding Receivable' sCategory,
'Number of Households' SSUBCATEGORY1,
'z' SSUBCATEGORY2,
'z' SSUBCATEGORY3,
'#begMonth#' DTMONTH,
month('#begMonth#') SMONTH,
year('#begMonth#') SYEAR,
isnull(SUM(isnull(cnt.cntHmy,0)), 0) IVALUE,
9 ISORT1,
80 ISORT2,
0 ISORT3,
0 ISORT4,
2 ILEVEL
from property p
left join
(select amt.property_hmy phmy, count (distinct amt.thmy) cntHmy from (
select
p.hmy property_hmy
,t.hmyperson thmy
,tr.stotalamount - (sum(case sign(convert(INTEGER,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begMonth#')+1, 0))-trr.upostdate))when 1 then d.samount when 0 then d.samount else 0 end))
remain_amount
from property p
inner join trans tr on(p.hmy = tr.hprop and tr.hAccrualAcct is not null)
inner join tenant t on(t.hmyperson = tr.hperson and tr.upostdate < DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begMonth#')+1, 0)))
left outer join detail d on (d.hchkorchg = tr.hmy )
left outer join trans trr on (trr.hmy = d.hinvorrec )
where
tr.itype = 7
and datediff("dd",tr.sdateoccurred,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begmonth#')+1, 0))) <=30
#Conditions#
group by
p.hmy
,t.hmyperson
,tr.hmy-700000000
,tr.stotalamount
having tr.stotalamount <> sum(case sign(convert(INTEGER,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begMonth#')+1, 0))-trr.upostdate)) when 1 then d.samount when 0 then d.samount else 0 end )
AND SIGN (tr.stotalamount - (sum(case sign(convert(INTEGER,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begmonth#')+1, 0))-trr.upostdate))when 1 then d.samount when 0 then d.samount else 0 end)) - ISNULL(( SELECT /* available prepays */
sum(isnull(d.samount,0)) tran_amountpaid
from property p
inner join tenant t1 on t1.hproperty = p.hmy
inner join unit u on u.hmy = t1.hUnit
inner join trans tr on tr.hperson = t1.hmyperson and tr.hmy between 600000000 and 699999999
and tr.upostDate <= '#begmonth#'
inner join detail d on d.hinvorrec = tr.hmy
inner join acct a on d.hacct = a.hmy and d.hAcct IS NOT NULL
left outer join trans trc on trc.hmy = d.hchkorchg
Where 1=1
and exists (select detail.hmy from detail
left outer join trans trc on trc.hmy = hchkorchg
where hinvorrec = tr.hmy
and (hchkorchg is null or trc.upostdate > '#begmonth#'))
and (d.hchkorchg is null or trc.upostdate > '#begmonth#')
AND T1.HMYPERSON = T.HMYPERSON),0)) = 1
) amt
where amt.thmy not in
(
select
isnull(t.hmyperson,0) thmy
from property p
inner join trans tr on(p.hmy = tr.hprop and tr.hAccrualAcct is not null)
inner join tenant t on(t.hmyperson = tr.hperson and tr.upostdate < DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begMonth#')+1, 0)))
left outer join detail d on (d.hchkorchg = tr.hmy )
left outer join trans trr on (trr.hmy = d.hinvorrec )
where
tr.itype = 7
and datediff("dd",tr.sdateoccurred,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begmonth#')+1, 0))) > 30
#Conditions#
group by
p.hmy
,t.hmyperson
,tr.hmy-700000000
,tr.stotalamount
having tr.stotalamount <> sum(case sign(convert(INTEGER,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begMonth#')+1, 0))-trr.upostdate)) when 1 then d.samount when 0 then d.samount else 0 end )
AND SIGN (tr.stotalamount - (sum(case sign(convert(INTEGER,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begmonth#')+1, 0))-trr.upostdate))when 1 then d.samount when 0 then d.samount else 0 end)) - ISNULL(( SELECT /* available prepays */
sum(isnull(d.samount,0)) tran_amountpaid
from property p
inner join tenant t1 on t1.hproperty = p.hmy
inner join unit u on u.hmy = t1.hUnit
inner join trans tr on tr.hperson = t1.hmyperson and tr.hmy between 600000000 and 699999999
and tr.upostDate <= '#begmonth#'
inner join detail d on d.hinvorrec = tr.hmy
inner join acct a on d.hacct = a.hmy and d.hAcct IS NOT NULL
left outer join trans trc on trc.hmy = d.hchkorchg
Where 1=1
and exists (select detail.hmy from detail
left outer join trans trc on trc.hmy = hchkorchg
where hinvorrec = tr.hmy
and (hchkorchg is null or trc.upostdate > '#begmonth#'))
and (d.hchkorchg is null or trc.upostdate > '#begmonth#')
AND T1.HMYPERSON = T.HMYPERSON),0)) = 1
)
and amt.remain_amount > 0
group by amt.property_hmy
)cnt on cnt.pHmy = p.hmy
where 1=1
and p.itype = 3
#Conditions#
Group By p.hmy
) d
where 1=1)
//End cnttrtydayspast
View 1 Replies
View Related
Jun 5, 2007
Hi all,
We are about to start a project that will involve three websites, each of which will have its own public domain name and two of which will act as e-commerce applications. Both e-commerce applications will only contain relatively small amounts of data (few thousand products, up to 15000 customer records with basic order history) and will be synchronised with a central data server at another site that will be responsible for handling payments, order processing, invoicing, etc.
The sites will take approx 500 hits every 24 hours and we will need to manage the DNS on the hosting server.
My question is, would we be ok to host these sites on a Web Edition of Windows 2003 Server with SQL Express, or is there any clear reason that we should go with the Standard editions of Windows Server and/or SQL?
Regards,
Stephen.
View 1 Replies
View Related
Aug 26, 2002
Server: Msg 9004, Level 21, State 1, Line 1
The log for database ' ' is corrupt.
Connection Broken
How do i continue to perform a successful restore if one of the logs is corrupt? do i perform dbcc?
View 2 Replies
View Related
Jan 10, 2008
Hello !
Please, I am new on SQL Server,
1.It's possible following scenario ?
I want to implement an application with SQL Server Express installed on a PS as server. Other PCs - from network or from http network - could connect to this sse database (my intention is to open a port on the server for using the internet network).
If yes, please give me an example of adequate string connection to use in application.
If no, what amount from this scenario could be implemented ? (And also, if you can, an sample of connection string).
2. Do you know what are the limitations of using SQL Server Developer Edition (~50$ price) in this scenario ?
Thank you very much !
View 8 Replies
View Related
Mar 20, 2008
I have a table as follows:
PID (PK), int
Postcode, nchar
Address1, nvarchar
Address2, nvarchar
Address3, nvarchar
Address4, nvarchar
Address5, nvarchar
I have a view on the table. The Output of the view is PID, Postcode, ADDRESS (which is Address1 - Address5 concatenated into one string with spaces between notnull values)
I use a sproc to search the view for an address based on a free-text search. Sproc as below. Can anyone advise how I should index the table? I have never created an index before and need to speed up the search, which at the moment is simply to slow. I have 1.8 million records in the table.
Here is the sproc:
SELECT PID, Upper(Postcode) as 'Postcode', ADDRESS
FROM vw_Postcode
WHERE
Address Like '%' + REPLACE(REPLACE(@address,',','%'),' ','%') + '%'
OR Postcode Like '%' + REPLACE(REPLACE(@address,',','%'),' ','%')+ '%'
Regards MArco
View 3 Replies
View Related
Dec 13, 2005
Hello all,
I am a newbie when it comes to replication. I have read the BOL but I was overwhilemed with the amount of information about replication.
We are planning to have a server with sql server 2005 and disconnected laptops with sql server express. How would replication work in this scenario assuming we are using merge replication:
Table AA [ a replicated table on the server and 9 columns]
View 3 Replies
View Related
Jul 10, 2006
Hi,
I'm new to the express edition and not that expert about SQL Server generally, so I want to get the answer to this qquestion in details - if possible - and as simple as it coule be . Is the following scenario acheivable using MS SQL Server XE:
One server running WindowsXP+MS SQL Server Express Edition, about 10 users with an application to connect to the db in server and work within it.
would this scenario be available using XE?
thanks in advance for replying ,
moemen ahmed
View 1 Replies
View Related
Oct 1, 2007
I'm new to SSB, so please bear with me. Our application requirements are:
1) Web app gathers user input from a web UI.
2) Web app calls a stored procedure, passing in the user input gathered in step (1).
3) Procedure issues queries to multiple data sources (SQL Server 2005 db's) derived from the user input.
4) Procedure waits for replies from these multiple data sources, governed by a timeout in case a data source is unavailable or slow to respond.
5) Procedure aggregates the data returned in step (4) from multiple data sources into an XML document.
6) Procedure returns the XML document as an output parameter.
This is different than the usual SSB asynchronous application paradigm. In particular, I'm wondering:
How can I setup a synchronous dialog, where the procedure that issues the SEND waits for a reply from the target service? I don't want the initiator procedure to end after SENDing, but rather wait for the replies to those messages so it can aggregate the data from the reply message bodies.
Thanks - Dana Reed
View 4 Replies
View Related
May 24, 2006
SELECT InventoryID,InventorySubTypeID,Make,Model,SerialNumber,OriginalCost,NetValue,InventoryStatusID, isnull(null ,0) [Adjustment Amount],isnull(null ,0) [New Net Value] ,null [Change Status To],null [Comments] from Inventory_Profile where InventoryID in (inventoryIds)
this scenario the inventoryids is a collection of ids like below
SELECT InventoryID,InventorySubTypeID,Make,Model,SerialNumber,OriginalCost,NetValue,InventoryStatusID, isnull(null ,0) [Adjustment Amount],isnull(null ,0) [New Net Value] ,null [Change Status To],null [Comments] from Inventory_Profile where InventoryID in (1,2,3,4,5)
this scenario i want to pass the ids at run time as a string in C#
Also how will i take the string in the DA class(C#) to execute it
guide me please
View 4 Replies
View Related
Jan 22, 2007
Hi to all
I want to know how achieve the following scenario, I will highly encourage if anyone knows the solution please mention it through steps. The scenario is I have a table named €œCreditCardTable €? with following schema , the table is pretty much what one expect from a simple credit card application that will present form to user who fills the application of credit card and data going to be filled into the table. Later an analyst will review the data and will be accepting or rejecting the Credit Card along with some other fields as seen in the schema.
-----Schema --------
CustomerName Bigint
Age int
DOB DateTime
LimitAssigned Bigint
Analystname varchar
Salary varchar
Gender bit
Qualification varchar
Country varchar
JobTitle varchar
MartialStatus varchar
IsApproved bit
IsDeclined bit
Now what I want is that by using Microsoft Detaining Features after giving it the data t should purpose analyst the solution i-e (reject / approve ) the credit card application . Now I gather than there are many algorithms in SQL Server 2005 Analysis Services that can be used (Decision Trees , Association Rules ) , I research on the two I just mentioned and want to use Decision Tree for this purpose , but there is a bottleneck that is mentioned in a book named €œDetaining with SQL Server 2005 by Wiely€?. It says that the rules that the Decision Tree will build and use are not available to end user in contrast to Association Rule algorithm which provide the rules. I essentially need the rules by which the decision has been made. Please I really need help on these issue to summarize I need the following
1) With the table €œCreditCardTable€? I need to perform Decision Trees and Association Rules Algorithm on it , please provide me steps to do so , moreover please also mention how to specify Query (DMX I think) to get the final result i-e approve or reject based on the data .
2) The rules on basis of which the Algorithm makes it decision, please provide insights on how to achieve this by both Decision Trees and Association Rules Algorithm.
I really need an urgent reply on this , you can also email me at razi_rais@yahoo.com , thanks for your feedback.
View 6 Replies
View Related
Jan 16, 2007
Background:
I'm a young developer working on a project that involves merge replication between SQL Server 2005 Mobile Edition and SQL Server 2005 and I've been having a hard time wrapping my head around exactly how to implement the best filters for the subscription(s) and publication(s).
Users of our application are Auction goers who collect data about AuctionItems in a mobile db and sync that information with a central db once he or she is finished. The central db is, obviously, pre-populated with most of the information about any/all AuctionItems. Central db information is available to view/change via web access.
Multiple users can change the same Auction data so there will be overlapping partitions.
What I would like to do is:
Present the user with a list of all available Auctions for the next 2 weeks (select * from Auctions where blah blah blah). This is a simple publication to create. Now, I'd like to be able allow the user to select AuctionID 4, AuctionID 3, AuctionID 12 and then, via a seperate AuctionListing publication only download AuctionItems that apply to those IDs.
Obviously, the publication has to be created and include the AuctionItem table and all necessary related tables/columns. But how do I create a parameterized filter based on that AuctionID?
Can a subscription be "dynamically created" once those AuctionIDs are known? Obviously, I don't want to bog my mobile devices down with tens of thousands of auction listings to auctions the user is not planning to attend.
Basically, my question is: am I wasting my time or is there some clever manipulation of HOST_NAME(), SUSER_NAME(), both, or some other method that I've missed that can get only those Auctions where the the ID matches one selected by the user at runtime?
View 4 Replies
View Related
Jul 20, 2007
For my website, I need to grab information from sources located on different servers. This will give me me various datasets that I need to combine into one, main dataset to then bind to my grid. Each dataset has the following fields:
Name || Blocked
I need the main dataset that is to be used on the grid to have the following structure:
Name || Blocked 1 || Blocked 2 || Blocked 3 || etc.......
The name field I can take from one of the datasets as the list of fields will be the same. However, I need to take the data from each of the blocked fields in the various datasets that are returned and then combine them to be the Blocked 1, etc columns that you can see above.
I'm making this post to ask for any advice on the best way to do this. Any info would be most appreciated.
Thanks,
James
View 11 Replies
View Related
Oct 24, 2007
Hi,i am using sql 2005 and this is what i am trying to do..i have multiple table say tbl1,tbl2,tbl3 etc and each one of have columns say col1,col2,col3,col4,col5 etc... and table_total.Each of these table contain same forignKey,say col2, and gets populated different time.In table_total i need to store the sum of particular column/columns(like sum of col3 from tbl1 and sum of col3 and also sum of col4 from tbl2 )values in a given row,for a given foriegn key under the respective column in table_total. Something like table_total has columns sum_col3tbl1, sum_col4tbl2, sum_col4tbl3, sum_col5tbl2 likewise..table_toal should get updated at the same time,whenever tbl1,tbl2,tbl3 etc..are getting updated,so that it always has most updated value.Can someone suggest me how to achive this? thanks...
View 2 Replies
View Related
