I have a problem with multiple databases on a server. I originally read another post that went into detail on this same error. After reading the post I decided to re-index the table to see if that would fix my issue. I started on Saturday by re-indexing a table on database 'a' - the process completed after that; I received another error on database 'b' on Monday i subsequently re-indexed all indexes on this database; today I have received the same message on database 'c' and 'd'. All post that I have read seem to point to an issue with only one database and not multiple databases. I have approximately 100 databases on this cluster - SQL 2000. The physical server logs only error shown is '000009ac.00001744::2007/12/29-15:12:58.848 ERR [CP] CppReadCheckpoint unable to copy file U:Disk12Partition1d61e68e-a60f-4ca6-b9ae-df3b893e4bb2 0000001.CPT to C:DOCUME~1mscsLOCALS~1TempCLS302F.tmp, error 3
000009ac.00001744::2007/12/29-15:12:58.848 ERR [CP] CppReadCheckpoint - Was that due to quorum resource not being up ???
' before corruption of database 'a'.
Here is the entire error if you think it will help. The error has been the same on all databases: 12/28/2007 6:23 AM A possible database consistency problem has been detected on database ‘ substituted_name'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database 'substituted_name'..
When I run the DBCC CHECKDB command I got these errors on Substite name database:
DBCC results for 'FVD_A'.
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 165575628, Index ID 2. Keys out of order on page (1:12743984), slots 97 and 98.
There are 72920059 rows in 2281375 pages for object 'FVD_A'.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'FVD_A' (object ID 165575628).
DBCC results for 'FVD_CVS322'.
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 181575685, Index ID 2. Keys out of order on page (1:7250519), slots 30 and 31.
I am at a loss. I believe we may have a bigger issue, but the SAN Administrator and Server Admin are saying there is nothing they can do. Do I have a true SQL Server issue or something else.
I am currently investigating the best way to handle partition swapping where the partition scheme is built on several file groups. In order to swap partitions, both partitions need to be on the same file group. In addition, the file groups and partitions are automatically updated once a month meaning that there is a scheduled job that adds a new file group, file group files, and updates the related partition objects.If I am forced to create a new non-partition table on the same file group as the partition of the target table, what would be the best way to create the target table without hard coding the full table definition and clustered index? I know that I can query the systables and syscolumns views to reconstruct a basic SQL statement to build the table definition and views Is there a SQL Server function that I can use to just give me the object definitions? Unfortunately, the OBJECT_ DEFINITION function doesn't apply to tables or indexes. URL....
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes ( class_id INT IDENTITY PRIMARY KEY NOT NULL,
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades ( class_id INT PRIMARY KEY NOT NULL,
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Both tblRoom and tblEquipment have the red line error which when I highlight say the they both reference an invalid table!
Both tables are there and have primary keys defined as ID & Type. I have searched around and all I could find was that there maybe a permission problem.
Hi,My application needs to calculate the sort order of an index key(whether the index key is descending or ascending). The user mayconnect to MS7 or MS2K servers.As far as I know, the descending indices are supported in version 8i.e SQL 2000. In MS2K, I can get the index key information by SELECTINDEXKEY_PROPERTY(table_ID , index_ID , key_ID , IsDescending) whichreturns 1 if key is descending. But as this is only for MS2K, it willfail and report error for above query in case connected to SQL server7.Now, in the application we avoided using server version dependentcode. So, can you please suggest me other way to do the same so thatthe query should not produce an error if connected to MS7 server butshould work for MS-2000 server.Regards,Uday
What is the general consensus regarding indexing of foreign keys in tables?
I have a lot of queries that perform inner joins from the 'primary key' table to a 'foreign key' table. I was wondering if there are any special considerations to take into account.
For example, I think in some RDBMS, foreign keys are sort of automatically assigned indexes? Is there anything that SQL Server automagically does in the background when a foreign key constraint is created that may affect whether something should be indexed?
I am trying to access data from a database to print a report. The code I am using says I have a syntax error regarding the object person_info. Person_Info is a table value function on the SQL Server (2005). Does anybody see a problem with this line of code? Thanks for your help! Regards, Steve "INNER JOIN (SELECT * Person_Info FROM (" & Session("current_project") & ")) ON pc.personID_fk=pe.personID_pk " & _
I have a database table which needs to make the Index "ParentREF, UniqueName" unique - but this fails because duplicate keys are found. Thus I now need to cleanup these duplicate rows - but I cannot just delete the duplicates, because they might have rows in detail tables. This means that all duplicate rows needs an update on the "UniqueName" value - but not the first (valid) one!
I can find those rows by
SELECT OID, UniqueName, ParentREF, CreatedUTC, ModifiedUTC FROM dbo.CmsContent AS table0 WHERE EXISTS ( SELECT OID, UniqueName, ParentREF FROM dbo.CmsContent AS table1 WHERE table0.ParentREF = table1.ParentREF AND table0.UniqueName = table1.UniqueName AND table0.OID != table1.OID ) ORDER BY ParentREF, UniqueName, ModifiedUTC desc
...but I struggle to make the required SQL (SP?) to update the "invalid" rows. Note: the "valid" row is the one with the newest ModifiedUTC value - this row must kept unchanged!
ATM the preferred (cause easiest) way is to rename the invalid rows with UniqueName = OID because if I use any other name I risk to create another double entry.
Does any one has any clue for this error ? I did went through a lot of articles on this error but none helped . I am working in Visual studie 2005 and trying to upload image in sql database through a simple form. Here is the code
using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Web.Configuration; using System.IO; public partial class Binary_frmUpload : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) { }protected void btnUpload_Click(object sender, EventArgs e) {if (FileUpload.HasFile == false) { // No file uploaded!lblUploadDetails.Text = "Please first select a file to upload..."; } else {string str1 = FileUpload.PostedFile.FileName; string str2 = FileUpload.PostedFile.ContentType; string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString; //Initialize SQL Server Connection SqlConnection con = new SqlConnection(connectionString); //Set insert query string qry = "insert into Officers (Picture,PictureType ,PicttureTitle) values(@ImageData, @PictureType, @PictureTitle)"; //Initialize SqlCommand object for insert. SqlCommand cmd = new SqlCommand(qry, con); //We are passing Original Image Path and Image byte data as sql parameters. cmd.Parameters.Add(new SqlParameter("@PictureTitle", str1)); cmd.Parameters.Add(new SqlParameter("@PictureType", str2));Stream imgStream = FileUpload.PostedFile.InputStream; int imgLen = FileUpload.PostedFile.ContentLength;byte[] ImageBytes = new byte[imgLen]; cmd.Parameters.Add(new SqlParameter("@ImageData", ImageBytes)); //Open connection and execute insert query. con.Open(); cmd.ExecuteNonQuery(); con.Close(); //Close form and return to list or images.
} } }
Object reference not set to an instance of an object. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.Source Error:
Line 32: Line 33: string str2 = FileUpload.PostedFile.ContentType; Line 34: string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString; Line 35: Line 36: //Initialize SQL Server Connection Source File: c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs Line: 34 Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.] Binary_frmUpload.btnUpload_Click(Object sender, EventArgs e) in c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs:34 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Not sure if this is the right forum to post this question to, so if it's not, please accept my apologies.
I'm working in SQL Server 2005 with a database that was migrated to 2005 from SQL Server 2000. I have to alter a trigger on a table for some functionality changes, and when I modify the trigger and then access it through the application the database is working with, I receive this error:
There was a error in the [stored procedure name] procedure. Error Number: -2147217900 Error Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The definition of object '[trigger name]' has changed since it was compiled.
[stored procedure name] and [trigger name] are where the actual names appear in the message.
I've tried running sp_recompile on the trigger, stored procedure, and table that are associated with this, and nothing works. I have dropped the trigger, which allows the save process to complete (but doesn't perform the required functionality, of course), and then re-created the trigger, but the error message still comes up. The compatibility level for the database is SQL Server 2000 (80) (as it was migrated from SQL Server 2000 as I mentioned above).
Has anyone seen this, and if so, how can I fix it?
Help! I have posted this before and I had hoped that the VS2005 SP1 would help my problem. It didn't. My code is shown below. I have dropped a sqlconnection, sqldataadapter and a strongly-typed dataset from the toolbox onto the component designer for my page and written my code. It compiles without any errors but at runtine I receive the system error "Object reference not set to an instance of an object." The error occurs at the first line where the sqldataadapter is mentioned. I have shufflled the code and the error still occurs at first mention of the dataadapter. I have set parameters to a simple string such as "myemail." It hasn't helped. I have used the "Dim" statement as "Dim DaAuthorLogin as System.Data.SqlClient.SqlDataadapter and Dim DaAuthorLogin as New ......) at the start of the private sub generated by the event requiring the data. Nothing helps. Here is my simple code to select data from a sqlserver 2000 database. Why do I continue to get this error? Partial Class AuthorLogin Inherits System.Web.UI.Page Protected WithEvents AuthorInformation As System.Web.UI.Page #Region " Web Form Designer Generated Code " 'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand Me.DaAuthorLogin = New System.Data.SqlClient.SqlDataAdapter Me.MDData = New System.Data.SqlClient.SqlConnection Me.DsAuthorLogin = New MedicalDecisions.DsAuthorLogin CType(Me.DsAuthorLogin, System.ComponentModel.ISupportInitialize).BeginInit() ' 'SqlSelectCommand1 ' Me.SqlSelectCommand1.CommandText = "SELECT AuthorAlias, AuthorEmail, AuthorPassword, LastName, PreferredName" & Global.Microsoft.VisualBasic.ChrW(13) & Global.Microsoft.VisualBasic.ChrW(10) & "FRO" & _ "M T_Author" & Global.Microsoft.VisualBasic.ChrW(13) & Global.Microsoft.VisualBasic.ChrW(10) & "WHERE (AuthorEmail = @AuthorEmail) AND (AuthorPassword =" & _ " @AuthorPassword)" Me.SqlSelectCommand1.Connection = Me.MDData Me.SqlSelectCommand1.Parameters.AddRange(New System.Data.SqlClient.SqlParameter() {New System.Data.SqlClient.SqlParameter("@AuthorEmail", System.Data.SqlDbType.NVarChar, 50, "AuthorEmail"), New System.Data.SqlClient.SqlParameter("@AuthorPassword", System.Data.SqlDbType.NVarChar, 50, "AuthorPassword")}) ' 'DaAuthorLogin ' Me.DaAuthorLogin.SelectCommand = Me.SqlSelectCommand1 Me.DaAuthorLogin.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "T_Author", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("AuthorAlias", "AuthorAlias"), New System.Data.Common.DataColumnMapping("AuthorEmail", "AuthorEmail"), New System.Data.Common.DataColumnMapping("AuthorPassword", "AuthorPassword"), New System.Data.Common.DataColumnMapping("LastName", "LastName"), New System.Data.Common.DataColumnMapping("PreferredName", "PreferredName")})}) ' 'MDData ' Me.MDData.ConnectionString = "Data Source=CIS1022DAVID;Initial Catalog=CGData;Integrated Security=True;Pooling" & _ "=False" Me.MDData.FireInfoMessageEventOnUserErrors = False ' 'DsAuthorLogin ' Me.DsAuthorLogin.DataSetName = "DsAuthorLogin" Me.DsAuthorLogin.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema CType(Me.DsAuthorLogin, System.ComponentModel.ISupportInitialize).EndInit() End Sub Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand Friend WithEvents MDData As System.Data.SqlClient.SqlConnection Friend WithEvents DaAuthorLogin As System.Data.SqlClient.SqlDataAdapter Friend WithEvents DsAuthorLogin As MedicalDecisions.DsAuthorLogin 'NOTE: The following placeholder declaration is required by the Web Form Designer. 'Do not delete or move it. Private designerPlaceholderDeclaration As System.Object Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub #End Region Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) 'no code here End Sub Private Sub AuthorLoginRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AuthorLoginRegister.Click 'for new author registration Response.Redirect("AuthorInformation.aspx") End Sub Private Sub AuthorLoginBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AuthorLoginBack.Click 'to navigate back Response.Redirect("MainPaths.aspx") End Sub Protected Sub AuthorLoginPassword_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AuthorLoginPassword.TextChanged 'pass the parameters to the dataadapter and return dataset DaAuthorLogin.SelectCommand.Parameters("@AuthorEmail").Value = AuthorLoginEmail.Text DaAuthorLogin.SelectCommand.Parameters("@AuthorPassword").Value = AuthorLoginPassword.Text MDData.Open() DaAuthorLogin.Fill(DsAuthorLogin, "T_Author") MDData.Close() 'set session objects If DsAuthorLogin.T_Author.Rows.Count > 0 Then Session("AuthorAlias") = DsAuthorLogin.T_Author(0).AuthorAlias Session("LastName") = DsAuthorLogin.T_Author(0).LastName Session("PreferredName") = DsAuthorLogin.T_Author(0).PreferredName Response.Redirect("AuthorPaths.aspx") Else : AuthorLoginNotValid.Visible = True AuthorLoginEmail.Text = "" AuthorLoginPassword.Text = "" End If End Sub End Class
I am trying to develop a custom algorithm. I have implemented and tested training methods, however I fail at prediction phase. When I try to run a prediction query against a model created with my algorithm I get:
Executing the query ... Obtained object of type: Microsoft.AnalysisServices.AdomdClient.AdomdDataReader COM error: COM error: DMPluginWrapper; Object reference not set to an instance of an object.. Execution complete
I know this is not very descriptive, but I have seen that algorith doesn't even executes my Predict(..) function (I can test this by logging to a text file) So the problem is this, when I run prediction query DMPluginWrapper gives exception -I think- even before calling my custom method.
As I said it is not a very descriptive message but I hope I have hit a general issue.
I'm getting the following error on our merge contents table in one of our DBs:
Msg 8952, Level 16, State 1, Line 1 Table error: Database 'ks241', index 'MSmerge_contents.nc3MSmerge_contents' (ID 1950682047) (index ID 3). Extra or invalid key for the keys: Msg 8956, Level 16, State 1, Line 1 Index row (1:1893:42) with values (partchangegen = NULL and tablenick = 17665001 and rowguid = AEAE7256-F571-478E-B2B4-D142B47C38C1) points to the data row identified by (). Msg 8952, Level 16, State 1, Line 1 Table error: Database 'ks241', index 'MSmerge_contents.nc3MSmerge_contents' (ID 1950682047) (index ID 3). Extra or invalid key for the keys: Msg 8956, Level 16, State 1, Line 1 Index row (1:1893:66) with values (partchangegen = NULL and tablenick = 17665001 and rowguid = B94F1591-33B0-4625-A269-DB9B8B05BCCE) points to the data row identified by (). CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'MSmerge_contents' (object ID 1950682047). repair_fast is the minimum repair level for the errors found by DBCC CHECKTABLE (ks241.dbo.MSmerge_contents ).
There are no other corruption issues on the server that I can find. I do not believe there are any current hardware issues, though I am not on site.
What would be the best way to resolve this? Is recreating or defrag/rebuild the index an option, here? Or can I just run a repair on this? Since it's a merge table, I'm a little gun shy, having limited experience with replication.
Thanks.
____________________________________________________________________________________ "Believe in those who are seeking the truth. Doubt those who say they have found it." -Andre Gide
Just installed VS 2005 & SQLServer 2005 clients on my workstation. When trying to create a new Integration Services Project and start work in the designer receive the MICROSOFT VISUAL STUDIO 'Object reference not set to an instance of an object.' dialog box with message "Creating project 'Integration Services project1'...project creation failed."
Previously I had SQLServer 2000 client with the little VS tool that came with it installed. Uninstalled these prior to installing the 2005 tools (VS and SQLServer).
I'm not finding any information on corrective action for this error.
I am trying to execute this code feom Script task while excuting its giving me error that "Object reference not set to an instance of an object." The assemblies Iam referening in this code are there in GAC. Any idea abt this.
Thanks,
Public Sub Main()
Dim remoteUri As String
Dim fireAgain As Boolean
Dim uriVarName As String
Dim fileVarName As String
Dim httpConnection As Microsoft.SqlServer.Dts.Runtime.HttpClientConnection
Dim emptyBytes(0) As Byte
Dim SessionID As String
Dim CusAuth As CustomAuth
Try
' Determine the correct variables to read for URI and filename
uriVarName = "vsReportUri"
fileVarName = "vsReportDownloadFilename"
' create SessionID for use with HD Custom authentication
CusAuth = New CustomAuth(ASCIIEncoding.ASCII.GetBytes(Dts.Variables("in_vsBatchKey").Value.ToString()))
For the life of me I cannot figure out why i'm getting the "Object Refence Not Set To an Instance of An Object" error after I try and create a Report Services project in Business Intelligence Studio. I've followed all of the steps in similar posts such as checking the ReportServer/web.config file but everything checks out.
Does anyone have any other ideas as to how I can get rid of this annoying error and start creating some reports?
There are certain errors that does not come during compilation and it shows during execution because of late-binding concept implemented in SQL Server from 7.0 version.
The problem is when Tables are not there, SPs are created. And there is no option where we can set like 'Validate object resolution during compilation"
There are lot of SPs that are in invalid status because the tables are really not there and SP needs to be modified to reflect the correct table name. In Oracle, if I have to find the list of objects that are in invalid state (because of object resolution problems), it was possible. How do I do it in SQL?
I need a listing of all objects in my database that is in invalid state. Searched in NET but there seems to be no supporting tool also that lists invalid objects.
Pl let me know whether there exists a way by which I can get to know the invalid object lists in my SQL 2005 database
When I add a new item to project in VS2005: Project-->Add New Item --> Sql Database I got a error msg "object reference not set to an instance of an object" after click "Add" button. The SQL Server Express is running. How do I fix this problem?
i have created a fact table which has unique cluster index as below,
CREATE UNIQUE CLUSTERED INDEX [FactSales_SalesID] ON [dbo].[FactSales] (salesid ASC) WITH (DATA_COMPRESSION = PAGE) GO however later when i add CLUSTERED COLUMNSTORE INDEXES : CREATE CLUSTERED COLUMNSTORE INDEX CSI_FactSales ON dbo.FactSales WITH (DATA_COMPRESSION = COLUMNSTORE) GO
it prompts error.
Msg 35372, Level 16, State 3, Line 167 You cannot create more than one clustered index on table 'dbo.FactSales'. Consider creating a new clustered index using 'with (drop_existing = on)' option.
I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname...Also I am seeing table not found error while quering tables under dw schema, after the change.
When I operate like this: "Select APP_DATA node -> Add New Item -> Sql Database ->create a new aspnet.mdf file .However ,I get a error dialogbox like this :: object reference not set to an instance of object .Can someone who know how to solve this problem help me ?Thanks a lot !
I'm new to ASP.Net and am trying to pull data from the db and populate an excel spreadsheet. I kget this error:Object reference not set to an instance of an object.on this:line 55: For i = 0 To dr.FieldCount - 1Here is my code:Protected Sub On_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click Dim i As Integer Dim strLine As String, filePath, fileName, fileExcel Dim objFileStream As FileStream Dim objStreamWriter As StreamWriter Dim nRandom As Random = New Random(DateTime.Now.Millisecond) Dim Location As String Dim SDate As String Dim EDate As String Location = Request.QueryString("Location") SDate = Request.QueryString("StartDate") EDate = Request.QueryString("EndDate") 'Dim fs As Object, myFile As Object Dim cnn As SqlConnection = New SqlConnection("server=**.**.**.**;uid=******;pwd=******database=***") 'Create a pseudo-random file name. fileExcel = "NR_Report" & nRandom.Next().ToString() & ".xls" 'Set a virtual folder to save the file. 'Make sure that you change the application name to match your folder. filePath = "********" fileName = filePath & "" & fileExcel 'Use FileStream to create the .xls file. objFileStream = New FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write) objStreamWriter = New StreamWriter(objFileStream) 'Use a DataReader to connect to the Pubs database. cnn.Open() Dim sql As String = "select [Tech Number] = u.user_techNo, Team = u.user_team, Name = upper(u.user_firstName) + ' ' + upper(u.user_lastName), [Total Jobs] = SUM(nr.nr_totaljobs), [Total IRDs] = SUM(nr.nr_totalirds), [IRDs Connected] = SUM(nr.nr_irdsConnected), [IRDs Non-Responding] = SUM(nr.nr_irdsNonResponding), [Percent Non-Responding] = case when SUM(nr.nr_irdsNonResponding) = 0 then 0.00 else round(SUM(cast(nr.nr_irdsNonResponding as decimal) ) / SUM(cast(nr.nr_irdsConnected as decimal)) * 100.00, 2) end from tblUsers u , tblNonResponders nr where u.user_techNo = nr.nr_irtech and u.user_techNo is not null and u.user_team is not null and u.user_office = " & Location & " and u.user_fireDate is null and u.user_suspend is null and nr.nr_reportweek between " & SDate & " and " & EDate & " group by u.user_techno, u.user_firstName, u.user_lastName, u.user_team order by u.user_team" Dim cmd As SqlCommand = New SqlCommand(sql, cnn) Dim dr As SqlDataReader 'Try 'dr = cmd.ExecuteReader() 'Catch ex As SqlException 'Response.Write(ex.Message) 'End Try 'Enumerate the field names and records that are used to build the file. For i = 0 To dr.FieldCount - 1 strLine = strLine & dr.GetName(i).ToString & Chr(9) Next 'Write the field name information to file. objStreamWriter.WriteLine(strLine) 'Reinitialize the string for data. strLine = "" 'Enumerate the database that is used to populate the file. While dr.Read() For i = 0 To dr.FieldCount - 1 strLine = strLine & dr.GetValue(i) & Chr(9) Next objStreamWriter.WriteLine(strLine) strLine = "" End While 'Clean up. dr.Close() cnn.Close() objStreamWriter.Close() objFileStream.Close() End SubThanks in advance for any help.
I have 1 client who keeps running into the following error on the subscriber and merge agents >
€œCannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.€?
Last time we got this error I ran a reindex on table MSmerge_genhistory on the publisher database, I then successfully generated a new snapshot and the subscribers started to synchronize again. This time around I keep getting the error even after I follow these steps (I also ran all the jobs to clean up replication). The last time I ran into this error I created a job to reindex msmerge_genhistory on a nightly bases in an effort to avoid this problem. Can somebody please provide me with a workaround and also the reason why this error occurs in the first place.
I have been recently redesigning my tables - creating FK relationships from child tables to the PK userid in the Users table. The specifics of what I did and why can be seen here:
Cannot insert duplicate key row in object 'dbo.lastlogin' with unique index 'IX_lastlogin'. The statement has been terminated.
Or, for that matter, SavedSearches or any other table where I need to insert the same userid twice. I can see why I would want to avoid duplicates in the Users table. But, for lastlogin, savedsearches, and a few of my other tables, the same user may account for multiple rows.
Any suggestions as to where I messed up and how to deal with this?
Thanks.
DBO.USERS
Code Snippet
CREATE TABLE [dbo].[users]( [userid] [int] IDENTITY(1,1) NOT NULL, [lastname] [varchar](50) NULL, [firstname] [varchar](50) NULL, [email] [varchar](50) NOT NULL, [alternateemail] [varchar](50) NULL, [password] [varchar](50) NOT NULL, [role] [varchar](10) NOT NULL, [securityquestion] [varchar](50) NOT NULL, [securityanswer] [varchar](50) NOT NULL, [zipcode] [int] NOT NULL, [birthmonth] [tinyint] NOT NULL, [birthday] [tinyint] NOT NULL, [birthyear] [int] NOT NULL, [gender] [varchar](10) NULL, [city] [varchar](50) NULL, [state] [varchar](50) NULL, [country] [varchar](50) NULL, [registerdate] [datetime] NOT NULL, [editdate] [datetime] NULL, [confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)), CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [userid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_email] UNIQUE NONCLUSTERED ( [email] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO SET ANSI_PADDING OFF
/****** Object: Table [dbo].[lastlogin] Script Date: 08/22/2007 14:16:16 ******/ SET ANSI_NULLS ON
GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[savedsearches] WITH NOCHECK ADD CONSTRAINT [FK_savedsearches_users] FOREIGN KEY([userid]) REFERENCES [dbo].[users] ([userid]) GO ALTER TABLE [dbo].[savedsearches] CHECK CONSTRAINT [FK_savedsearches_users] GO SET QUOTED_IDENTIFIER ON GO
The following insert statement returned the error in the subject because userid = 32 already exists in the Users table.
CREATE TABLE [dbo].[lastlogin]( [lastloginid] [int] IDENTITY(1,1) NOT NULL, [userid] [int] NOT NULL, [date] [datetime] NOT NULL, [status] [bit] NOT NULL CONSTRAINT [DF_lastlogin_status] DEFAULT ((0)), [activity] [datetime] NOT NULL CONSTRAINT [DF_lastlogin_activity] DEFAULT (getutcdate()), [online] AS (case when [status]=(1) AND datediff(minute,[activity],getutcdate())<(30) then (1) else (0) end), CONSTRAINT [PK_lastlogin] PRIMARY KEY CLUSTERED ( [date] ASC, [userid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO ALTER TABLE [dbo].[lastlogin] WITH NOCHECK ADD CONSTRAINT [FK_lastlogin_users] FOREIGN KEY([userid]) REFERENCES [dbo].[users] ([userid]) GO ALTER TABLE [dbo].[lastlogin] CHECK CONSTRAINT [FK_lastlogin_users]
In the SQL Server 2014 Management Studio's object explorer indexed views with schemabinding don't have an index node. Thus, you can create and drop Indexes for views only via T-SQL. The SQL Server Management Studio 2012 still shows the index node.
Is there some Management Studio setting in version 12.0.2000.8 which I am missing?
We are developing a project that is expected to hold TB of data and the back end used is SQL Server 2005.
I have the following problem
I have applied Nonclustered index over a column on a table.
Designed a SP for insertion which caters for updation incase the criteria based on the input is met.
The logic goes like this
Incase there exists a row containing the value of the column that is indexed for uniqueness, there should be updation. If not there should be a new row created.
However often there is an error message that is placed above. This happens only on some of the SPs and only on rare occasions.
Can any body tell me if there is any problem with the SQL Server 2005
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers. Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'. The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused: I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Hi everyone, When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?? In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing: dbo.Categories dbo.CustomerCustomerDemo dbo.CustomerDemographics dbo.Customers dbo.Employees dbo.EmployeeTerritories dbo.Order Details dbo.Orders dbo.Products dbo.Regions dbo.Shippers dbo.Suppliers dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing: dbo.Categories New Table... Modify Open Table Script Table as |> CREATYE To |> DROP To |> SELECT To |> INSERT To |> New Query Editor Window File.... Clipboard UPDATE To |> DELETE to |> From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance, Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools] FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId) Or CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
