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?
How would I make something like this work?
CREATE TRIGGER [tr_update_doc_count] ON [Documents]
FOR INSERT
AS
[Declared variables]
[I want to get the tablereference from the inserted information]
SELECT @tableref = Table_id, @Account_id = Account_id from inserted
[Then I get the Actual Table Name from the UsercreatedTables Table where it is stored]
SELECT @tablename = TableName from UsercreatedTables WHERE Table_id = @tableref
[Now I want to use the @tablename I got from UsercreatedTables in the query]
SELECT @Current_Doc_Count = Account_Docs from @tablename WHERE Account_id = @Account_id
I am not sure how to get it to use the variable @tablename for the table name in the last query.
It gives me an error stating that @tablename is invalid syntax.
Any help is much appreciated!
g'day gents, I run a select query against my mysql database using asp.net. The query looks like this: SELECT distinct(rubbet.ortnamn) as Bebyggelsenamn, count(rubbet.ortnamn) as 'Antal',min(rubbet.artal) as Beläggsår, rubbet.belaggsform as Beläggsform, rubbet.harad as Härad, rubbet.socken as Socken, rubbet.ovrigt as Övrigt, rubbet.filnamn as Filnamn, rubbet.path as Sökväg FROM rubbet WHERE rubbet.ortnamn like '%" + searchTextBox.Text + "%' and rubbet.harad like '" + DropDownList1.SelectedValue + "' and rubbet.socken like '" + DropDownList2.SelectedValue + "' and rubbet.ovrigt like '" + ovrigtRadioButtonList.SelectedValue + "' group by Bebyggelsenamn ORDER BY '" + sorteraDropDownList.SelectedValue + "' " + ordning + "; The query works just fine and runs smoothly. I only have one problem. If a user types for example monkey in the 'searchTextBox' the database finds all the instances of monkey, counts them and presents one line that states: Monkey, 138 instances, bla bla bla my problem is with the Min aggregate function. It selects the lowest rubbet.artal for Monkey but I want rubbet.belaggsform to get the data from the same row as Min(rubbet.artal) gets the data from. As it is now, rubbet.belaggsform selects its data from the row with the lowest primary key. For example the result could look like the following: Holmagården, 127, , 1873 when it should say: Holmagården, 127, Holmagaard, 1873 I'm only presenting the columns above, the rest of them are for creating links and what not. I guess I should write a sub query of some kind to pick the correct rubbet.belaggsform that corresponds to MIN(rubbet.artal) together with the other factors but I just can't get it to work.
Help me out here please.
Hi,
Situation: I have a web application that is running on a server. IT has told me that the application I developed is taking up enormous amount of ram. It seems that the amount of ram that is taken up, is slowly increasing. About 10 megs per hour (memory leak). Furthermore, he has informed me that the ram is taken up by the sql. My senior developer told me that I probably have sqlconnections open that are not closed. I have checked through the application and it seems they are all closed properly. But I may have missed some, as the application is pretty big.
Questions:
1. Is there an application or piece of code that can monitor how many sql connections I have open during runtime?
2. Could there be any other common causes to such a kind of memory leak.
Any help with this matter would be very much appreciated.
Thank you for your time.
Sincerely,
Jeff
Hi everybody.I'm having difficulties with a button handler I put together from a few examples around the internet.Here's my code: <script runat="server"> Sub Add_To_Cart(ByVal Src As Object, ByVal Args As EventArgs) Dim FVArtikel_id As Label = FormViewDisplay.FindControl("Artikel_idLabel") Dim FVArtikel_naam As Label = FormViewDisplay.FindControl("Artikel_naamLabel") Dim FVArtikel_prijs As Label = FormViewDisplay.FindControl("Artikel_prijsLabel") Dim DBConnection As OleDbConnection Dim DBCommand As OleDbCommand Dim SQLString As String Dim SQLAddString As String If Not Session("sid") Is Nothing Then DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:Documents and SettingsAdministratorBureaublad2ehandslego.nldatadb.mdb") DBConnection.Open() SQLString = "SELECT Count(*) FROM Orders WHERE sid = '" & CType(Session("sid"), String) & "' AND Artikel_id = '" & FVArtikel_id.Text & "'" DBCommand = New OleDbCommand(SQLString, DBConnection) DBCommand.Parameters.AddWithValue("@sid", Session("sid")) 'string? DBCommand.Parameters.AddWithValue("@Artikel_id", FVArtikel_id.Text) 'string? If DBCommand.ExecuteScalar() = 0 Then SQLAddString = "INSERT INTO Orders (sid, Order_datum, " & _ "Artikel_id, Order_artikel, Order_prijs, Order_hoeveelheid) VALUES (" & _ "'" & Session("sid") & "', " & _ "'" & Today & "', " & _ "'" & FVArtikel_id.Text & "', " & _ "'" & FVArtikel_naam.Text & "', " & _ "'" & FVArtikel_prijs.Text & "', 1)" DBCommand = New OleDbCommand(SQLAddString, DBConnection) DBCommand.Parameters.AddWithValue("@sid", Session("sid")) 'string? DBCommand.Parameters.AddWithValue("@Artikel_id", FVArtikel_id.Text) 'string? DBCommand.Parameters.AddWithValue("@Artikel_naam", FVArtikel_naam.Text) DBCommand.Parameters.AddWithValue("@Artikel_prijs", FVArtikel_prijs.Text) 'string? DBCommand.ExecuteNonQuery() End If DBConnection.Close() Src.Text = "Item Added" Src.ForeColor = Color.FromName("#990000") Src.BackColor = Color.FromName("#E0E0E0") Src.Font.Bold = True End If End Sub</script> I'm not getting any errors, it seems to me that i'm not getting a 'sid' value passed along but i don't know what to do about it.I've also already tried step debugging. This is my last resort. I hope you can help me.
View 3 Replies View RelatedI have a database with a dozen or so tables. No table constraints. Logic is all in stored procedures.
I have several Excel spreadsheets of data to import into the database, one speadsheet to a table. Each spreadsheet has additional data(columns) that each table has no interest in and should be ignored.
I would appreciate your thoughts on methods and best practices for loading this data to the database.
I am about to investigate SQL Server 2005 Express handling of XML. I am familiar with XML and XSL conversions and it seems to me that XSL conversion of Excel data to XML gives me a lot of flexibility prior to database import for shaping the data.
In short, importing data to the database from an XML source.
I am not famliar with SQL Server's XML capability and would appreciate thoughts on this while I look into it.
And of course alternate ways that I am overlooking.
Thanks
Hi, sorry if this is not the right place to post, any help or direction would be appreciated. I'm using SQLServer2005.
I'm having trouble writing a query and could do with some help. I'll illustrate the problem with an example.
Say I have 2 tables.
Table 1 - tbl_news:
ID
NewsTitle
linkedDoc1
linkedDoc2
1
my news title
5
3
Table 2 - tbl_docs:
ID
docTitle
1
doc1
2
doc2
3
doc3
4
doc4
5
doc5
Now, I'd like to write a query which returns bascially the first table, but which gets the doc title from table 2 using the IDs....how would I write this?
The end table would look like this:
ID
NewsTitle
linkedDoc1
linkedDoc2
1
my news title
doc5
doc3
Thanks so much!!
Hi,
Im a Jr DBA and have been given an assignment by my lead to find information on the following.
We are to migrate existing db of size 4TB to a
DELL PowerEdge 2950[Mem:Up to 32GB]
OS : Windows Server 2003 Std Edition X64 SP2
DB : SQL Server Enterprise Edition x64
I am to find on how to design the db to provide optimum performance,fail over and consider the growing factor of the db.
1)What would be the recommended RAID settings?
2)Placement of the tempdb ?
3)Should we do clustering and why ?
4)What Data partioning would do to help?
5)Any Other aspects to be considered for sizing db ?
6)Placement of data files and log file on separate physical disk ?
7)Indexing?
I have read many sites.I would appreaciate if someone could write suggestions and opinions based on their current db design spec or previous experience,by selecting best db design points.Thank You.
Hi -- I am fairly new to SSIS development, although I am starting to appreciate it more an more, especially since I have started getting into extending the object model. Here's my question:
I have a data flow that pulls data from any number of different delimited files with different numbers of columns. I have had no problem dealing with setting up run-time file locations and file names by using the expressions of a flat file data source, and i have been able to pretty easily deal with varying file delimiters by standardizing the files before they get into the data flow. however, I have not been able to come up with a solution that will allow my data source to discover its column info at run time, and then pass that information on to the data flow task. all i really care about is being able to properly parse the individual rows into individual column data by the flat file data source because the data flow itself is able to discover the actual data that the columns hold at run-time.
i would very much appreciate any feedback from anyone on possible solutions for this.
thanks!
I have the MS SQL2000 database failed to recover at computer restart. Now the database is marked suspect. How can I manage to recover the data?
Thank you.
It's arriving from ebuyer tomorrow for 32 quid. I already have aninstance of MSDE running on the laptop and ideally i would like SQLserver to be installed and use the current instance and not installany other additional services. [hope i got my terminology correcthere.] Basically all i would like is to use the Enterprise Managerwith my existing MSDE database. I was wondering if there is anything ishould be doing before I install. Should i shut down the service orleave it running for instance.Thanks for any tips,AndyB
View 2 Replies View RelatedSQL 2000 to SQL 2005 Upgrade Error - Database Down - Help Appreciated
I am upgrading a SQL 2000 standard database server to SQL 2005 standard on a windows 2003 server
I am logged in as domain admin and started the upgrade as 'sa'
The upgrade stops with the error:
Service MSSQLSERVICE can not be started. Verify you have sufficient privilages to start system services. The error code is (17185)
The error log shows:
2007-01-04 15:59:38.77 Server Authentication mode is MIXED.
2007-01-04 15:59:38.79 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
2007-01-04 15:59:38.79 Server (c) 2005 Microsoft Corporation.
2007-01-04 15:59:38.79 Server All rights reserved.
2007-01-04 15:59:38.79 Server Server process ID is 4188.
2007-01-04 15:59:38.79 Server Logging SQL Server messages in file 'F:SQLDataMSSQLlogERRORLOG'.
2007-01-04 15:59:38.79 Server This instance of SQL Server last reported using a process ID of 2980 at 1/4/2007 3:56:58 PM (local) 1/4/2007 2:56:58 AM (UTC). This is an informational message only; no user action is required.
2007-01-04 15:59:38.79 Server Registry startup parameters:
2007-01-04 15:59:38.79 Server -d F:SQLDataMSSQLdatamaster.mdf
2007-01-04 15:59:38.79 Server -e F:SQLDataMSSQLlogERRORLOG
2007-01-04 15:59:38.79 Server -l F:SQLDataMSSQLdatamastlog.ldf
2007-01-04 15:59:38.79 Server Command Line Startup Parameters:
2007-01-04 15:59:38.79 Server -s MSSQLSERVER
2007-01-04 15:59:38.79 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-01-04 15:59:38.79 Server Detected 4 CPUs. This is an informational message; no user action is required.
2007-01-04 15:59:38.83 Server Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
2007-01-04 15:59:39.02 Server Using the static lock allocation specified in the locks configuration option. Allocated 20000 Lock blocks and 20000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-01-04 15:59:39.02 Server Multinode configuration: node 0: CPU mask: 0x0000000a Active CPU mask: 0x0000000a. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2007-01-04 15:59:39.04 Server Multinode configuration: node 1: CPU mask: 0x00000005 Active CPU mask: 0x00000005. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2007-01-04 15:59:39.04 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-01-04 15:59:41.04 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-01-04 15:59:41.04 Server Database Mirroring Transport is disabled in the endpoint configuration.
2007-01-04 15:59:41.04 spid7s Starting up database 'master'.
2007-01-04 15:59:41.05 spid7s 1 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required.
2007-01-04 15:59:41.07 spid7s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.
2007-01-04 15:59:41.07 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2007-01-04 15:59:41.08 spid7s SQL Trace ID 1 was started by login "sa".
2007-01-04 15:59:41.08 spid7s Starting up database 'mssqlsystemresource'.
2007-01-04 15:59:41.11 spid7s Using 'dbghelp.dll' version '4.0.5'
2007-01-04 15:59:41.11 spid7s ***Stack Dump being sent to F:SQLDataMSSQLLOGSQLDump0035.txt
2007-01-04 15:59:41.11 spid7s SqlDumpExceptionHandler: Process 7 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2007-01-04 15:59:41.11 spid7s * *******************************************************************************
2007-01-04 15:59:41.11 spid7s *
2007-01-04 15:59:41.11 spid7s * BEGIN STACK DUMP:
2007-01-04 15:59:41.11 spid7s * 01/04/07 15:59:41 spid 7
2007-01-04 15:59:41.11 spid7s *
2007-01-04 15:59:41.11 spid7s *
2007-01-04 15:59:41.11 spid7s * Exception Address = 78144D3A Module(MSVCR80+00014D3A)
2007-01-04 15:59:41.11 spid7s * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
2007-01-04 15:59:41.11 spid7s * Access Violation occurred reading address 0000001E
2007-01-04 15:59:41.11 spid7s *
2007-01-04 15:59:41.11 spid7s *
2007-01-04 15:59:41.11 spid7s * MODULE BASE END SIZE
2007-01-04 15:59:41.11 spid7s * sqlservr 01000000 02BA7FFF 01ba8000
2007-01-04 15:59:41.11 spid7s * ntdll 7C800000 7C8BFFFF 000c0000
2007-01-04 15:59:41.11 spid7s * kernel32 77E40000 77F41FFF 00102000
2007-01-04 15:59:41.11 spid7s * MSVCR80 78130000 781CAFFF 0009b000
2007-01-04 15:59:41.11 spid7s * msvcrt 77BA0000 77BF9FFF 0005a000
2007-01-04 15:59:41.11 spid7s * MSVCP80 7C420000 7C4A6FFF 00087000
2007-01-04 15:59:41.11 spid7s * ADVAPI32 77F50000 77FEBFFF 0009c000
2007-01-04 15:59:41.11 spid7s * RPCRT4 77C50000 77CEEFFF 0009f000
2007-01-04 15:59:41.11 spid7s * USER32 77380000 77411FFF 00092000
2007-01-04 15:59:41.11 spid7s * GDI32 77C00000 77C47FFF 00048000
2007-01-04 15:59:41.11 spid7s * CRYPT32 761B0000 76242FFF 00093000
2007-01-04 15:59:41.11 spid7s * MSASN1 76190000 761A1FFF 00012000
2007-01-04 15:59:41.11 spid7s * Secur32 76F50000 76F62FFF 00013000
2007-01-04 15:59:41.11 spid7s * MSWSOCK 71B20000 71B60FFF 00041000
2007-01-04 15:59:41.11 spid7s * WS2_32 71C00000 71C16FFF 00017000
2007-01-04 15:59:41.11 spid7s * WS2HELP 71BF0000 71BF7FFF 00008000
2007-01-04 15:59:41.11 spid7s * USERENV 76920000 769E3FFF 000c4000
2007-01-04 15:59:41.11 spid7s * opends60 333E0000 333E6FFF 00007000
2007-01-04 15:59:41.11 spid7s * NETAPI32 71C40000 71C97FFF 00058000
2007-01-04 15:59:41.11 spid7s * SHELL32 7C8D0000 7D0D2FFF 00803000
2007-01-04 15:59:41.11 spid7s * SHLWAPI 77DA0000 77DF1FFF 00052000
2007-01-04 15:59:41.11 spid7s * comctl32 77420000 77522FFF 00103000
2007-01-04 15:59:41.11 spid7s * psapi 76B70000 76B7AFFF 0000b000
2007-01-04 15:59:41.11 spid7s * instapi 48060000 48069FFF 0000a000
2007-01-04 15:59:41.11 spid7s * sqlevn70 4F610000 4F7A0FFF 00191000
2007-01-04 15:59:41.11 spid7s * SQLOS 344D0000 344D4FFF 00005000
2007-01-04 15:59:41.11 spid7s * rsaenh 68000000 6802EFFF 0002f000
2007-01-04 15:59:41.11 spid7s * AUTHZ 76C40000 76C53FFF 00014000
2007-01-04 15:59:41.11 spid7s * MSCOREE 61FB0000 61FF4FFF 00045000
2007-01-04 15:59:41.11 spid7s * ole32 77670000 777A3FFF 00134000
2007-01-04 15:59:41.11 spid7s * msv1_0 76C90000 76CB6FFF 00027000
2007-01-04 15:59:41.11 spid7s * iphlpapi 76CF0000 76D09FFF 0001a000
2007-01-04 15:59:41.11 spid7s * kerberos 62220000 62277FFF 00058000
2007-01-04 15:59:41.11 spid7s * cryptdll 766E0000 766EBFFF 0000c000
2007-01-04 15:59:41.11 spid7s * schannel 76750000 76776FFF 00027000
2007-01-04 15:59:41.11 spid7s * COMRES 77010000 770D5FFF 000c6000
2007-01-04 15:59:41.11 spid7s * XOLEHLP 622E0000 622E5FFF 00006000
2007-01-04 15:59:41.11 spid7s * MSDTCPRX 622F0000 62367FFF 00078000
2007-01-04 15:59:41.11 spid7s * msvcp60 780C0000 78120FFF 00061000
2007-01-04 15:59:41.11 spid7s * MTXCLU 62370000 62388FFF 00019000
2007-01-04 15:59:41.11 spid7s * VERSION 77B90000 77B97FFF 00008000
2007-01-04 15:59:41.11 spid7s * WSOCK32 71BB0000 71BB8FFF 00009000
2007-01-04 15:59:41.11 spid7s * OLEAUT32 77D00000 77D8BFFF 0008c000
2007-01-04 15:59:41.11 spid7s * CLUSAPI 62390000 623A1FFF 00012000
2007-01-04 15:59:41.11 spid7s * RESUTILS 623B0000 623C2FFF 00013000
2007-01-04 15:59:41.11 spid7s * DNSAPI 76ED0000 76EF8FFF 00029000
2007-01-04 15:59:41.11 spid7s * winrnr 76F70000 76F76FFF 00007000
2007-01-04 15:59:41.11 spid7s * WLDAP32 76F10000 76F3DFFF 0002e000
2007-01-04 15:59:41.11 spid7s * rasadhlp 76F80000 76F84FFF 00005000
2007-01-04 15:59:41.11 spid7s * security 62800000 62803FFF 00004000
2007-01-04 15:59:41.11 spid7s * dbghelp 671B0000 672C7FFF 00118000
2007-01-04 15:59:41.11 spid7s *
2007-01-04 15:59:41.11 spid7s * Edi: 62FA0040: 62FA0028 671AFFF0 00000000 00000000 00000000 00000000
2007-01-04 15:59:41.11 spid7s * Esi: 0000001E:
2007-01-04 15:59:41.11 spid7s * Eax: 0416C05E: C0400096 C0400416 00000416 000062FA 00000000 00000000
2007-01-04 15:59:41.11 spid7s * Ebx: 0416C040: 0105F270 00002000 00000005 00000000 3368C1E0 00000000
2007-01-04 15:59:41.11 spid7s * Ecx: 0105B010: 458B0046 01F88348 00D3850F 458B0000 89C23B50 0F342444
2007-01-04 15:59:41.11 spid7s * Edx: 00000000:
2007-01-04 15:59:41.11 spid7s * Eip: 78144D3A: 24FFA5F3 144E5495 C78B9078 000003BA 04E98300 E0830C72
2007-01-04 15:59:41.11 spid7s * Ebp: 33D6E770: 33D6E790 01C65B97 62FA0040 0000001E 0416C040 0416C6DC
2007-01-04 15:59:41.11 spid7s * SegCs: 0000001B:
2007-01-04 15:59:41.11 spid7s * EFlags: 00010212: 0020006D 00690046 0065006C 005C0073 006F0043 006D006D
2007-01-04 15:59:41.11 spid7s * Esp: 33D6E768: 62FA0040 33D6E7A4 33D6E790 01C65B97 62FA0040 0000001E
2007-01-04 15:59:41.11 spid7s * SegSs: 00000023:
2007-01-04 15:59:41.11 spid7s * *******************************************************************************
2007-01-04 15:59:41.11 spid7s * -------------------------------------------------------------------------------
2007-01-04 15:59:41.11 spid7s * Short Stack Dump
2007-01-04 15:59:41.13 spid7s 78144D3A Module(MSVCR80+00014D3A)
2007-01-04 15:59:41.13 spid7s 01C65B97 Module(sqlservr+00C65B97)
2007-01-04 15:59:41.13 spid7s 01CA43B5 Module(sqlservr+00CA43B5)
2007-01-04 15:59:41.13 spid7s 01CA452E Module(sqlservr+00CA452E)
2007-01-04 15:59:41.13 spid7s 0217D3BD Module(sqlservr+0117D3BD)
2007-01-04 15:59:41.13 spid7s 0217B896 Module(sqlservr+0117B896)
2007-01-04 15:59:41.13 spid7s 0100889F Module(sqlservr+0000889F)
2007-01-04 15:59:41.13 spid7s 010089C5 Module(sqlservr+000089C5)
2007-01-04 15:59:41.13 spid7s 010086E7 Module(sqlservr+000086E7)
2007-01-04 15:59:41.13 spid7s 010D764A Module(sqlservr+000D764A)
2007-01-04 15:59:41.13 spid7s 010D7B71 Module(sqlservr+000D7B71)
2007-01-04 15:59:41.13 spid7s 010D746E Module(sqlservr+000D746E)
2007-01-04 15:59:41.13 spid7s 010D83F0 Module(sqlservr+000D83F0)
2007-01-04 15:59:41.13 spid7s 781329AA Module(MSVCR80+000029AA)
2007-01-04 15:59:41.13 spid7s 78132A36 Module(MSVCR80+00002A36)
2007-01-04 15:59:41.13 spid7s Stack Signature for the dump is 0x2BEDE9E0
2007-01-04 15:59:41.27 spid7s External dump process return code 0x20000001.
External dump process returned no errors.
2007-01-04 15:59:41.27 spid7s Error: 17185, Severity: 16, State: 1.
2007-01-04 15:59:41.27 spid7s Unable to update password policy.
2007-01-04 15:59:41.27 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Any clues?
Cheers
Paul
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???
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
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
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
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.
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
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 . ! .
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.
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
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
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
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.
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
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?
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
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
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.
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 !
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
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]
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