Desperate For Even Partial Recovery
Feb 12, 2008
I have been asked to see what I can recover from a development server whose database became suspect during a power failure.
One developer from another group who's time is limited tried to repair the database using checkdb, but it is still suspect.
There are no recent backups of anything whatsoever. Needless to say, we are lacking in DBA skills here. At this point, we don't care whether we get the data back, but we are desperate to recover the table definitions, user-defined functions and stored procedures -- if not all of them than most of them; if not most of them than some.
What are our options here?
Are their any good third-party tools to help us with this problem?
View 20 Replies
ADVERTISEMENT
Jul 19, 2007
Hi all I've written an app using VWDE and SSE 2005 but it wont work on my hosts. I'm sure the connection string is correct and my hosts say it's all setup ok over there. I get this message: The SSE Provider did not find the database file specified
in the connection string. At the configured trust level (below High
trust level), the SSE provider can not automatically create the
database file. And my hosts says that the db should be fine but some of the VB might not work, this is the line that's causing the issues (red): Line 15: Session("username") = loggedInNameLine 16: Line 17: a = Roles.GetRolesForUser(loggedInName)Line 18: Line 19: For Each j In aThis works fine in SSE 2005, should it work on SQL Server too? Can anyone suggest an alternative? ThanksMark
View 21 Replies
View Related
Aug 26, 2002
Can someone help me. here is a diagram of what I need:
http://www.sfasource.com/stuff/relationship.gif
Thanks in advance,
Kevin
View 1 Replies
View Related
Jul 20, 2005
Hello all,I am a total newbie to SQL. I created this sp and then with C++,called it. My return value was 100 (which it was)CREATE PROCEDURE sp_StoreIPs@IPSource varchar(16),@IPTarget varchar(16),@TimeDate varchar(20),@Name varchar(250)asdeclare @iReturn intSet @iReturn = 100return @iReturnGOThen when I added a INSERT statement like ...CREATE PROCEDURE sp_StoreIPs@IPSource varchar(16),@IPTarget varchar(16),@TimeDate varchar(20),@Name varchar(250)asdeclare @iReturn intInsert into LookUP (IPSource, IPTarget,TimeDate, Name) Values(@IPSource,@IPTarget,@TimeDate,@Name)Set @iReturn = 100return @iReturnGOMy return value was 0. I am assuming the the INSERT statement isreturning the 0 but how can I get around this?ThanksRalph Kraussewww.consiliumsoft.comUse the START button? Then you need CSFastRunII...A new kind of application launcher integrated in the taskbar!ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg
View 2 Replies
View Related
Aug 1, 2006
after we download sp1 and hit runj, we got "Unspecific Error Occur" popup window, with message:The following unexpected error occur:" then without any detail. here is the hotfix log:
Please help
08/01/2006 14:33:51.967 ================================================================================
08/01/2006 14:33:51.967 Hotfix package launched
08/01/2006 14:33:51.967 Successfully opened registry key: SOFTWAREMicrosoftWindowsCurrentVersion
08/01/2006 14:33:51.967 Successfully read registry key: CommonFilesDir, string value = C:Program Files (x86)Common Files
08/01/2006 14:33:51.967 Successfully opened registry key: SOFTWAREMicrosoftWindowsCurrentVersion
08/01/2006 14:33:51.967 Successfully read registry key: ProgramFilesDir, string value = C:Program Files (x86)
08/01/2006 14:33:52.014 Successfully opened registry key: SOFTWAREMicrosoftWindowsCurrentVersion
08/01/2006 14:33:52.014 Successfully read registry key: CommonFilesDir, string value = C:Program Files (x86)Common Files
08/01/2006 14:33:52.014 Successfully opened registry key: SOFTWAREMicrosoftWindowsCurrentVersion
08/01/2006 14:33:52.014 Successfully read registry key: ProgramFilesDir, string value = C:Program Files (x86)
08/01/2006 14:33:52.014 Local Computer:
08/01/2006 14:33:52.014 Target Details: ISSQL05
08/01/2006 14:33:52.014 commonfilesdir = C:Program Files (x86)Common Files
08/01/2006 14:33:52.014 lcidsupportdir = e:1af2e497cde069647713331033
08/01/2006 14:33:52.014 programfilesdir = C:Program Files (x86)
08/01/2006 14:33:52.014 supportdir = \ISSQL05e$1af2e497cde06964771333
08/01/2006 14:33:52.014 supportdirlocal = e:1af2e497cde06964771333
08/01/2006 14:33:52.014 windir = C:WINDOWS
08/01/2006 14:33:52.014 winsysdir = C:WINDOWSsystem32
08/01/2006 14:33:52.014
08/01/2006 14:33:52.014 Enumerating applicable products for this patch
08/01/2006 14:33:53.530 The patch installation could not proceed due to unexpected errors
08/01/2006 14:33:53.530
08/01/2006 14:33:53.530 Product Status Summary:
08/01/2006 14:33:53.780 Hotfix package closed
View 3 Replies
View Related
Apr 29, 2002
A database with 1 mdf and 2 ldf has been detached from SQL Server 7.0 . Then removed the log files ( they are gone , unable to recover ) and there's no backup at all . Now I want to attach the database with the same mdf , but got error msg - 'Device activation error'. It seems like it's looking for one of the log files.
Is there any way to recover the db ?
I guess NOT , isn't it ?
I don't understand why it doesn't work with sp_attach_single_file_db and sp_attach_db . I actually tested it with a dummy database with 1 log file , and it worked - a new log file was recreated. Thus I performed in production server. Don't understand why it doesn't work.
thanks in advance.
View 4 Replies
View Related
Jul 2, 2003
I have been down for 10 days now and don’t know where else to turn. If you can’t help, pls point me to where I can get help.
Here are the facts I have posted several times to the MS SQL NGs.
(Server: Win2K Adv Server, SQL 2K w/sp3a,
Wkstn: Win2K pro, SQL 2K w/sp3a)
1) SQL server is running and I can access all the dbs when on the server.
2) All the workstations login to the server just fine. Server is configured for Active Directory.
3) SQL server has only the default instance installed and is in mixed authentication mode.
4) I have registered SQL Server in the Active Directory, (right click on the server node in the SQL Server Enterprise Manager and select the Properties item from the popup menu … Active Directory tab etc - as per the article "SQL Server 2000 Active Directory Integration”.
5) When in EM on the workstation, I try to create a sql server registration
a) With Windows authentication - but I get this error:
SQL server registration failed because of the connection failure
displayed below. Do you wish to register anyway?
Login failed for user '(null)'. Reason: Not associated with a trusted SQL server connection.
b) With user = sa and pwd = sa (both are the correct values and case)
SQL server registration failed because of the connection failure
displayed below. Do you wish to register anyway?
Login failed for user 'sa'
5) I have checked out
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B269587 and done
everything it says.
Help - I haven't been able to connect for several days.
I have tried deleting the registrations several times - same results.
I have tried uninstalling sql 2K and reinstalling 3x and tested before and
after installing sp3a - same results.
Anyone have any ideas?
View 10 Replies
View Related
Oct 1, 2007
sp_msforeachdb @command1= 'USE ? exec sp_Depends "dbo.style"'
So im trying to return everything that uses that dbo.style im getting this error I just want to show where that table is being used and suggestions. I know that feature is undocumented. This also doesnt seem to be searching all the Databases when I search it.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'master'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'tempdb'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'msdb'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'GoldMine_Sales_and_Marketing'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'CustomerConcerns'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'Aggregate'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OLD_BUDGET'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'CreditCardData'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DELSA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'COZUM'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DelSolNet'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DelSolNet2'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DYNAMICS'.
Object does not reference any object, and no objects reference it.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'HUNTB'.
In the current database, the specified object is referenced by the following:
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'MASDEV'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'MASDEVSmall'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OLDOnlineOrdering'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OnlineRMA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OnlineStore'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DELSO'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'Reporting'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'SSE'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'STTHO'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'TMPLT'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'WHAVL'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'QSCHQ'.
In the current database, the specified object is referenced by the following:
In the current database, the specified object is referenced by the following:
In the current database, the specified object is referenced by the following:
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'SOLKD_DATA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'SOLKD_NET'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'ALOHA_DATA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'ALOHA_NET'.
View 6 Replies
View Related
Mar 29, 2007
hello ppl.......
've installed sql 2005 xpress with advanced services......also installed the toolkit that came along..
all 'm interested in is to perform certain data mining operations on the adventureworks db..
;ve also installed the advn works db.........
now here is the prob.......
jus like how the sql online book instructed me i first started off with the datamining tutorial..where i was told to first OPEN BIDS select new PROJECT then make sure that the analysis Services template was selected and then go ahead with selection of the particular sln file....
the problem is that I DON'T SEE ANY ANALYSIS SERVICE TEMPLATE....all i c is a REPORT template..
i really need help guys..... 'm new to .net really feeling helpless...
please hurry guys....
thx a ton..
hanish
View 5 Replies
View Related
Jul 20, 2005
Hi All,I am close before throwing the machine out of the window.I tried to establish a linked server on our SQL2000 server pointing to a DB.EXEC sp_addlinkedserver@server = 'DB2',@srvproduct = 'IBM OLE DB Provider for DB2 Servers',@provider = 'IBMDADB2',@provstr='DSN=JDE_PROD;UID=XXXX;PWD=XXX;DATABASE=J DE_PROD;'EXEC sp_addlinkedsrvlogin 'DB2', false, NULL, 'XXX', 'XXXX'If I query the LS using query analyzer or the Enterprise Manager both startto hang, never responding again.To be honest I am quite new to this topic but I read a lot and also tried alot ;)Some articles talk about SNA Servers to be installed first, others aboutsome client tools.I am confused by now...Could anybody please be so kind and tell me how to connect the DB2 as alinked server?Is my statement wrong, and if so how should it look like? Why does theapplication start to hang? Do I really need to install additional softwareto do so?Our DB2 expert also taked about software necessary to access the DB2mainframe. Any ideas?Thanks a lot in advance and belive me, any hints are highly appreciated ;)Seize the DayAndyAndreas BretlJoin Bytes!
View 4 Replies
View Related
May 11, 2007
After try and error for several months, finally my SQL2000 LS works.
i gave my self a little celebration for that.
but after running well for a day, just one day !
this morning it give me Out of Sync.
error messages:
failure /4305
activity : Load
[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set
begins at LSN 7000000026200001, which is too late to apply to the database. An earlier
log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
activity : Copy
failure / 32
The process cannot access the file because it is being used by another process.
i just dont understand, how come the load & copy process failed, after it works for a day.
View 6 Replies
View Related
Mar 5, 2007
Hi ,
Technically i am a network administrator, and know very little about SQL 2005, but it has come under my responsibility to manage this box and a lot depends on fixing this problem for me.
Error:
Service Broker needs to access the master key in the database 'Prototype'. Error code:25. The master key has to exist and the service master key encryption is required.
Also, i get the following both in windows event viewer and SQL logs;
Error:
Source: Logon
Message
Error: 18456, Severity: 14, State: 5.
Login failed for user 'logadmin'. [CLIENT: 192.168.1.2] Event ID 18456;
Login failed for user 'admin'. [CLIENT: 192.168.1.34] Event ID 18456
Symptoms:
While users are working and try to run a new query, their credentials are not accepted anymore. The account they are using is has "sysadmin" rights.
Once they restart the machine, they can run multiple queries, and then the same thign over again.
Please help me with this one, for i have pretty much exhausted google etc. I mean there are various blogs with same prblem but not a straighforward fix.
I would appreciate your help very very much.
Best Regards,
Umer.
View 3 Replies
View Related
Aug 31, 2006
I hope anyone can help me with this. I am having problems connecting to the aspnet database. It
all started when I noticed that I could not log in to my webapplication
anymore under debug mode, although I was able to log in when remotely
accessing the website. I got the error message that the machineaspnet
user did not have access to the database: "Login failed for user
'GM-FEDORSASPNET' "This was highly peculiar and rather annoying so I tried several things to straighten this out, but it only made things worse!I
read that this had to with permissions of the database in question, al
though I could not understand why permissions suddenly had changed. I
tried to change the rights by attaching it to Management Studio Express
and defining an aspnet login, but that failed because a login under
that name was not accepted. I tried to make a copy of aspnet.mdf, delete the original and rename the copy to aspnet.mdf. When that didn't work, I then deleted the aspnet.mdf, counting on it that it would be regenerated automatically, but alas. I located the aspnet_regsql.exe tool to regenerate the database, but got the following error message: System.Web.HttpException:
Unable to connect to SQL Server database. --->
System.Data.SqlClient.SqlException: An error has occurred while
establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)What can I do?
View 4 Replies
View Related
Jan 18, 2008
Good morning all. I need some help with a stored proc that is driving me up a wall. It's probably something stairing me right in the face but I can't see it!
I keep getting the following error on the procedure that I'm working on:
SQL-DMO (ODBC SQLSTATE: 42000)
ERROR 156: Incorrect syntax near keyword 'AS'
Must declare the variable '@signid'
Must declare the variable '@signid'
Here's the code:
CREATE PROCEDURE [dbo].[ws_savesignature2db]
@xml as text='',
@image as image='',
@imageformat as varchar(20)='',
@imagename as varchar(40)='',
@imagesize as int=0,
AS
insert into signaturetable
([image], imageformat, imagename, imagesize)
values
(@image, @imageformat,@imagename,@imagesize)
/*
if your XML field datatype is Text or nTEXT, use the code below in Sql Server 2000.
If you use Sql Server 2005, you can use varchar(max) or nvarchar(max) which will be
much easier and you do not need the code.
*/
select @signid = @@IDENTITY
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR([xml])
FROM ws_signature
WHERE signid= @signid
if @ptrval is not null
WRITETEXT ws_signature.xml @ptrval @xml
GO
Any help you can give would be a life saver!!!!
Thanks in advance.
Jim
View 3 Replies
View Related
Aug 17, 2015
We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
View 6 Replies
View Related
Dec 23, 2014
is bulk logged recovery model support point in time recovery
View 9 Replies
View Related
Sep 17, 2015
Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
View 4 Replies
View Related
Aug 5, 2007
I own a DELL dimesnsion 4600 desktop. I purchased it with windows xp HOME although I have upgraded to windows xp professional. I am trying to install Office Enterprise 2007 with the disk i purchased. My computer will not pick it up though. I know the cd works because I tried it on another computer but didnt intsall it. That computer read the disk. My computer will not. any help? desperate need
View 1 Replies
View Related
May 5, 2015
in the process of migrating a big db from server 1 to server 2, we had to roll back the change. I started with taking a full db backup and restoring it on server 2 with norecovery, and then a couple logs with norecovery, and then the last log with recovery.
Is there some way to continue this chain now, I mean to change the db to norecovery, or other way to restore logs.Â
I dont want to do a new full backup.
If I try to do a log restore now i get the message:
Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
View 6 Replies
View Related
Sep 19, 2015
We have 3 replica AG setup. 2 replicas are in sync/automatic failover, the other(DR Server, different subnet) in asynchronous/manual mode…All these replicas were on sql server 2012, Recently we upgraded DR server to 2014. Since then we have a problem, the AG databases in 2014 instance went into ‘Synchronizing/ in recovery’ state…The SQL server error log has message, the recovery couldn’t start for the database ‘XYZ’…We tried to create a new database and add it to AG , it works for fine for other two 2012 replicas, but on 2014 we see the same issue
View 3 Replies
View Related
Jul 20, 2005
Does anyone have any basic, simple scripts of sp's that I can give mycomputer operators to use to monitor for serious conditions on our sqlservers? We are new in the ms-sql arena, a small shop and we cantreally purchase any tools to monitor these servers, but we need somebasic checks that we can use to make sure a server is performing in areasonable fashion, no blocks, cpu<xx%, memory not pegged, IO working,etc...and we need to be able to do this without bugging ourprogrammers every time we are wondering why things are slowwnig down.We are a split shop between as400s and sql servers, and our operationsstaff has no problem performing this type of duty on the 400s. I thinksince the sql servers are so easy to deploy into production, the ideaof how to monitor these things health got lost in the shuffle andallowing the programmers to do it just serves to destabilize ourenvironment.Please help! Our shop has turned to chaos since we went live withMSSQL centric applications and everytime one programmer "resolves anissue" some other programmers application starts to act up.
View 6 Replies
View Related
Nov 1, 2015
We have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
View 2 Replies
View Related
Sep 10, 2015
If you are doing a disaster recovery of an entire SQL 2005 cluster, can you just install SQL server and restore the system database to get the configuration?
View 4 Replies
View Related
Apr 19, 1999
I'm an SQL novice, but I know this must be a common problem.
I'm trying to select a recordset (using ASP), but I know I only want part of the recordset, and am not sure how to limit it ahead of time.
For example, the query will return about 500 rows, but I know I only want to use a small section of these records.
I want to give the user the ability to navigate through small sections of these 500 rows without having to get all rows all the time.
I know ahead of time which rows to get, but have no idea how to limit the recordset before I get it (there is no fields in the database to help).
This is what I'm doing now. "select * from xyz where id=xxx order by date desc;" I know I only want the first 10, or 10-20, or 400-410.
The way I'm doing it now, I'm getting the whole recordset each time, doing a "rs.move x" where x is where I want to start.
This is really a waste of network traffic and memory since my SQL server is on a different machine as the web server running ASP.
How do I do this?
Please email me if you could at pmt@vantagenet.com
View 1 Replies
View Related
Sep 26, 2004
Hello - I was wondering if anyone knew how to do this -
I have a database with a field for Id, LName, GName, DOB. In the LName field, some of the names have * placed after the names. Is there a way I can search for the entries in LName with the * in the record?
Thank you!
Liz
View 3 Replies
View Related
Sep 30, 2007
Hi Guys,
I have started a project using Linq for sql and SSCE. Everything goes well until I realize that SSCE can only run in full trust. Linq for sql is planned to support partial trust scenario. Any plans for such a support in Sql Compact Edition?
Dany
View 1 Replies
View Related
Apr 12, 2008
The following code accepts a couple of parameters and creates a temp table to hold unpaid schedule rows (like invoices). Then is takes the payment amount passed and starts paying the oldest ones first, until it runs out of money or pays a partial. When it pays it inserts a row in the applieds table with the schedule_ID, Receipt_ID (payment id), applied amount, and applied date.
What I need help with is: Say there is a partial payment from a previous payment, I need to pay that one, but only the unpaid part. Then continue to pay other schedule rows...
I appreciate any help,
@PaymentAmount money,@PledgeID Int,@Receipt_ID IntAS-- Get unpaid reminder rows for the passed in pledge id and put them into the temp tableDeclare @temp_oldest Table(PledgeSchedule_ID int,ReminderDueDate datetime,AmountDue money)INSERT INTO @temp_oldest SELECT PledgeSchedule_ID, PledgeDueDate, AmountDueFROM tblPledgeReminderScheduleWHERE (dbo.tblPledgeReminderSchedule.Pledge_ID=@PledgeID) AND (dbo.tblPledgeReminderSchedule.ReceivedDate IS NULL) -- AND (dbo.tblPledgeReminderSchedule.PledgeDueDate < GETDATE())WHILE((SELECT Count(*) FROM @temp_oldest)>0)BEGIN-- If the payment is greater or equal to the amount due for the current row, do thisIF(@PaymentAmount >= (SELECT Top 1 AmountDue FROM @temp_oldest))BEGIN-- Update the reminder row with todays dateUPDATE tblPledgeReminderSchedule SET ReceivedDate = GETDATE()WHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest)-- Insert a row to track applied payment and reminder row associatedINSERT INTO tblPledgeReminderSchedule_Applieds (PledgeSchedule_ID,Receipt_ID,Applied_Amount,Applied_Date)(SELECT Top 1 PledgeSchedule_ID,@Receipt_ID,AmountDue,GETDATE() FROM @temp_oldestWHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest))-- Subtract the amountdue from the paymentamount and reset itSET @PaymentAmount = (@PaymentAmount - (SELECT Top 1 AmountDue FROM @temp_oldest))ENDELSEIF(@PaymentAmount < (SELECT Top 1 AmountDue FROM @temp_oldest))BEGIN -- Insert a row to track applied PARTIAL payment and reminder row associatedINSERT INTO tblPledgeReminderSchedule_Applieds (PledgeSchedule_ID,Receipt_ID,Applied_Amount,Applied_Date)(SELECT Top 1 PledgeSchedule_ID,@Receipt_ID,@PaymentAmount,GETDATE() FROM @temp_oldestWHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest))BREAKENDELSEIF @PaymentAmount = 0-- Delete all rows from temp tableDELETE FROM @temp_oldestELSE-- Delete only the current row from the temo tableprint @PaymentAmountDELETE FROM @temp_oldest WHERE PledgeSchedule_ID = (SELECT Top 1 PledgeSchedule_ID FROM @temp_oldest)END
View 1 Replies
View Related
May 6, 2005
I was just wondering if anyone could tell me how to do a search for a partial data match. Say one data field is 123, 234, 345, 456 and another is 111, 222, 333, 444 and another is 555, 666, 777, 888 and I want to search for the unique number 234 but not the whole number 123, 234, 345, 456 ... is there any way to do that or does every search have to be exactly like the data in the field?
Thanks for any help.
Dennis
View 4 Replies
View Related
Jun 15, 2000
In our database we have the concept of 'Companies' each company has an entry in a tblCompanyControls using a field lCompanyNumber to uniquely identify it.
Company specific data is then grouped within additional tables using this
lCompanyNumber.
Thus all the departments for a particular company (eg 4) exist in a table
tblDepartments with lCompanyNumber =4. The same applies for pensions, pay
elements, employees and so on.
Each employee has various attributes stored in several tables, thus the
main data is stored in tblEmployees with an lCompanyNumber to illustrate
the company they belong to. And a system generated lUniqueID that is the 1
to many relationship to tables such as tblEmployeePayElements,
tblEmployeePensionSchemes.
These in turn have a primary key that establishes a 1 to many with the
period data for pay elements etc.
What we want to do is replicate only the data for a specific company, for
tblEmployees, tblDepartments this looks straightforward as i just set a
filter like 'tblEmployees.lCompanyNumber = 4'. My problem is how do I
replicate just the tblEmployeePayElements for those employees that are in
the specific company as the table does not contain the lCompanyNumber. Can
you replicate a view? I an quite ignorant of replication and would really
appreciate some pointers. Note DRI is not in use.
View 1 Replies
View Related
Sep 18, 1998
I am are running SQLSERVER SP4 on WINNT SP3.
I am serious problem of partial update my query is
something like
bEGIN tRAN
declare cursor...
SElect * from tableA where flag = null
open cursor
fetch first...
while @@FETCH_STATUS = 0
BEGIN
UPDATE TABLE tableB ..
.
.
.
fetch next
END
CLOSE ...
DEALLOCATE..
COMMIT TRAN
The problem is the cursor select retrieves
says about 10000 rows, goes thru the
loop for 1000 rows and just terminates without
giving an error message,or rolling back
in case of errors, but comes out as successfully
completed.
I am at loss as what could be the problems..
any suggestions welcome..
Thanks in advance,
Balajee.
View 2 Replies
View Related
Dec 13, 2007
Could someone please help me? I am trying to pull in a partial string (the last six characters of the field, to be exact).
This is an example of my code:
select *
into #temp_2
from #temp_1 a, Server2.DBa.dbo.table2 r
where r.field1r = a.field1a and
r.field3r = a.field3a (field3a is where I need just the last 6 characters)
To be more specific:
r.field3r looks like 000884
a.field3a looks like 17445000884
So- I just want to pull in the 000884 off of a.field3a
View 2 Replies
View Related
Jan 30, 2006
Guys, i have a table that one of the columns (Email To) is
a concatenated list of email addresses separated by semi colons ";".
i.e.:
rrb7@yahoo.com;richard.butcher@sthou.com;administr ator@sthou.com
etc like that.
each row varies with one exception. administrator@sthou.com is in each one.
is there a simple way thru sql or T-SQL to delete that "administrator@sthou.com" part? or should i call each row individually into say, a VB.net form using a split with the deliminator ";"
and then looping thru and updating each row?
thanks again for any easy answer
rik
View 7 Replies
View Related
Oct 2, 2013
We have here 3 tables which are linked by Order number. there is one more table we need to use to get the Shipping zone code. This column however is 10 pos. ( the order number on that table)whilst the others are all 8. We want to join on MHORDR in the table MFH1MHL0, then we are done.
SELECT
ALL T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#,
T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT#
FROM ASTDTA.OEORHDOH T01 LEFT OUTER JOIN
ASTDTA.OEIND1 T02
ON T01.OHORD# = T02.IDORD# LEFT OUTER JOIN
ASTDTA.OEINHDIH T03
ON T01.OHORD# = T03.IHORD#
WHERE T01.OHOSTC = 'CL'
AND T01.OHORDD >= 20120101
ORDER BY T01.OHORD# ASC
View 5 Replies
View Related
