Hi, we have a huge table of companies (17 million entries) for which we want to find duplicates according to search critera (based on phone number). The query runs very slow (5 minutes+) Here is a simplified version of the query, but the problem is the same : SELECT C1.* FROM dbo.Company AS C1 WITH(NOLOCK) INNER JOIN dbo.Company AS C2 ...
hello everyone. Im current implementing the permission like this: Permission UserPermision User ------------------- ---------------------- ----------------- PNAME PID PID UID PVAL UID UNAME This concept work fine, but at somepoint, we will want to add a Permissio...
I am trying to develop a report in SQL Server Reporting Services using parameters. I have no problem creating the parameters and running the reports, but I always have to select an item from each of the parameters to make it run. Can I have the option to leave one of the parameters blank and still have the report run? Right now when I ...
I have a database table containing fields RACE, ETHNICITY and ETH. I need to evaluate RACE and ETHNICITY fields and populate ETH using a series of cases: if Race = W and Ethncity = 1 then ETH = Caucasian etc. Can someone advise me on the best way to structure a stored procedure to accomplish this? ...
I have upload my website to a shared hosting, set-up everything and my asp.net 3.5 app won't connect to SQL Server database on the shared hosting provider. This is the error I get: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify...
I have a table to log site visits and it has over 100,000 records. There don't seem to be any performance issues, but should large log type tables be regularly moving records to an archive table and clearing out the current table? ...
I have a table with 117000 or so records. I need to perform a search that checks 3 separate fields for a given string pattern. My where clause is as follows: field1 LIKE '%' + @DESC + '%' OR field2 LIKE '%' + @DESC + '%' OR field3 LIKE '%' + @DESC + '%' This seems to take about 24 seconds regardless of input... Is there a better way ...
I have a job that performs several validation checks on rows in several tables in a database. If the check fails it logs the failure in a table that. The information that is logged includes the table name, a uniqueidentifier value of the row that failed, which check it failed, and which job was being run at the time. Here's the simpli...
I'm working on the next update for StackQL. One thing I want to do is have the ability to query over several releases. So when I loaded the October data, for example, I didn't delete the old September database. It's still out there. In fact, you can even still query it by including the database name like this: select top 10 * from...
select A.regYear, A.regMonth, isnull(A.regCount,0), isnull(B.regCount,0), isnull(C.regCount,0), isnull(D.regCount,0), isnull(E.regCount,0), isnull(F.regCount,0), isnull(G.regCount,0),isnull(H.regCount,0),isnull(I.regCount,0),isnull(J.regCount,0), isnull(K.regCount,0),isnull(L.regCount,0),isnull(M.regCount,0),isnull(N.regCount,0), isnull(...
I installed SQL Server 2005, and I want to create a database but I can't. When I right click on the Database Diagrams folder for my database in SQL Server Management Studio 2005, I don't see any choice for creating database diagram. I see two things: Working With sql server 2000 Diagrams Refresh ...
i'm using JDBC to connect to SQL Server 2005 locally using integrated security. it's getting failed with the following error code Error: 17806, Severity: 20, State: 2. Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 127.0.0.1...
I'm trying to send e-mails to contacts stored in SQL Server database. The e-mails contain images. I tried the code below, EXEC msdb.dbo.sp_send_dbmail @recipients = '[email protected];', @subject = 'Sending Mail from SQL Server Test', @file_attachments = 'C:\inetpub\wwwroot\pagerror.gif', @body=N'<img src="pagerror.gif" />'...
Hi, When deleting a row by its PrimaryKey from a table, I get about 44472 logical reads. Now the table has 5-6 child tables that link their ForeignKeys to the PK of the table I want to delete from. I'm not sure what to do to improve the performance of the delete. Any suggestions ? Edit : I added the queryplan for the delete http://...
In my Windows Forms application (on startup) I use the ping command to check if both the Internet connection and my SQL Server are alive. Is there any "better" way-command I should use for accomplishing the above task in the .NET framework 2.0? ...
I've got quite a robust development environment regarding my SQL database Schema and Data (everything is source controlled, deployment is automated, etc), but when it comes to SSIS packages, the process is quite tedious and totally manual. How can I automate the SSIS package upgrade process (right now, I copy my packages manually to ea...
I just need to make a SQL table of time zones - at this point, my only need is to populate a drop-down list on an online form. I'm envisioning something simple with columns for ISO code, name, UTC offset, and perhaps a list of representative cities. I thought I would be able to easily find something online to copy-and-paste, but haven't ...
Hi everyone, does anyone know a way how I could set through mapping the default value of a column so for e.g. when I generate DB from mappings I would have DateTime column having getdate() as default value? I tried so far this (looks exactlly like what I need) but it doesn't work this.Map(x => x.LastPersistedOn, "DateModified") ...
What are the difference between a #myTable and a declare @myable table ...
Hi, I need to move the transaction log for a database I have just created using aspnet_regsql. Is it possible to move the transaction log using sqlcmd or any other command line tool? kind regards, ...