Hi. Sorry if I am asking a stupid question since I am an absolutely beginner
in SQL Server. Here is the question . . .
About 13 hours ago, I got my SQL Server 2000 to index a table which has 104
million records. At first the CPU usage was high. But after an hour or two,
the process has seemed dead and the Enterprise Manager has had no response.
The CPU usuage dropped to zero and has been jumping between 0 to 5%. The
harddisk indicator has been blinking at a rate of roughly three times per
two seconds.
Is this normal? Has anyone got any idea how long the process would take? I
have assigned 1.8GB of RAM to the SQL service and is currently taking up
about 1GB.
Public Sub OpenConnexionSQL() ConnexionSQL = New SqlConnection(ConfigurationSettings.AppSettings("DataSourceSql").ToString) ConnexionSQL.Open() End Sub
Hi guys, I've been thinking about this problem now for some time but somehow I don't know if my "solution" for it is right. I'd like to read your opinion.
There is a Capital table with Capital_Nr, Capital_Name, Capital_Population, Country_Nr and Country_Name as attributes.
I know the table is chaotic so I brought it to 3NF :
Capital table : Capital_Nr, Capital_Name, Capital_Population, Country_Nr(foreign key)
Country table : Country_Nr and Country_Name
Ok so I guess the table should be now in 3NF, but what intrigues me is in what NF the table originally was. I tried then to use Codd's definition of 2NF : "a 1NF table is in 2NF if and only if none of its non-prime attributes are functionally dependent on a part (proper subset) of a candidate key". In my opinion the original candidate keys could only be {Capital_Nr},{Country_Nr} and {Country_Name}, each one of them single, i.e. separate from each other. So, as there is no composite candidate key, I can affirm that the original table was in 2NF. Am I right ?
I am wondering what normal disk I/O should be. i know it verys depending on use but im looking for an average.
here is an idea of what we have
there is about 10 centers doing replication to our primary server. we have about 80 users connecting directoy to our primary server using MS Dynamics through CITRIX. we have a few other apps use the database as well however i am fairly certin its Dynamics generating our disk IO Hardware wise we have a powerful blade connected to a raid 5 SAN with 15000 rpm disks. normaly the disk IO stays fairly low but every so often it goes crazy and im thinking it shouldn't
Below is a sample of our disk IO from perfmon over 2 minutes or so. as you can see everything looks ok untill 04/15/2008 10:12:49.470 when the Disk I/O % goes above 100%
Hey All, I'm trying to decide what's the 'best' to use. I've been designing and creating database for a while and have pretty much always used a surrogate key and not a normal one. I've finally had some free time to start studying more so in my spare time and read up and come accross a lot of guides, articles and stories that tout that normal keys should be used whenever possible as they're a better identifier and that surrogate keys should only be used when there is not a readily available normal key. Now perhaps I'd be open to accepting that but absolutely every database I come across tends to only use surrogate keys. For example I'm doing an authentication system from scratch and am looking at the User table. Now of course the user name has to be unique, should that be the primary key or should I have a seperate column with a guid or an incrementing int or the like as the primary key? I can certainly see that username could be used. I can also see how it may be easier when looking through the data tables to identify who/what a table is refering to with a surrogate key. However it still seems sort of sloppy, for lack of a better word, to me. Where now I could have somebody's username (or any other piece of data used for this purpose) spread accross a lot of other tables. And while writting this I just thought of the scenario that perhaps somebody needs their username changed, with this method now the ids need to be changed on all the related rows of all the other tables whereas with a surrogate key it wouldn't matter. Anyways I'm mostly looking for opinions on which way to go (not just with the user sample, but more in general).Thanks.
I've been running a long query which takes almost 39 seconds in Query Analyzer. After creating a Stored Procedure (with the same query) I expected to run it faster bcoz I heared that SP has a cache, and its a faster technique. But I didnt gain any performance improvments.
Can somebody clear my confusion, what I'm doing wrong.
We have a payroll database that needs to be backed up just before completing the payroll for that period. I need to create a batch file that a normal user can run that will tell the database to back up and then tell the user when it is done so they can continue working. Is there an easy way to do this without giving the users special permissions? I don't want to give them backup op status. Any help would be appreciated.
I will be taking over a database that has almost no pk's or relations(this is not my choice, but a vendors) Management is looking at stored procs to improve performance, but I am wondering if the db is in this state will there really be a gain. I am pushing for normalization first, but if anybody has any ideas or opinions I would appreciate
hi friends the below query is actually what type of join whether inner join or normal query..?????
if not exists(select 'x' from cobi_invoice_hdr h(nolock), fin_quick_code_met q(nolock) , ci_adjustment_drdoc_vw z (nolock) where h.tran_ou = @ctxt_ouinstance and h.invoce_cat = @category_tmp and d.so_no between @sonumberfrom and @sonumberto
and isnull(h.tran_amount,0) between @totalinvoiceamountfrom and @totalinvoiceamountto and h.tran_date between convert(varchar(10),@invoicedatefrom,120)and convert(varchar(10),@fininvoicedateto,120) and h.tran_no between @invoicenumberfrom and @invoicenumberto and h.bill_to_cust between @billtocodefrom and @customerto and h.fb_id = isnull(@fb,h.fb_id) and h.tran_currency = isnull(@currency,h.tran_currency) and h.createdby = isnull(@useridentity,h.createdby)
and EXISTS (select '*' from cobi_cust_custinfo_vw c(nolock) where h.bill_to_cust = c.custcode andc.ouid = @ou_tmp )
and z.status = q.parameter_text
and q.parameter_type = 'STATUS' and q.parameter_category = 'STATUS' and q.component_id = 'COBI' and q.parameter_code = @status_tmp and h.tran_no = z.documentno and q.language_id = @ctxt_language and z.language_id = @ctxt_language) begin 'No matching invoices found.' select @m_errorid = 514 -- Porselvi.J - COBIDMS412AT_000255 return end End
Is performance of web application (ASP.NET + SQL Server 2005 Wrg edition + Win Server 2003 Web edition) running on server with one core duo/4 CPU generally comparable to the performance of the same application running on the same server with 2/4 physical CPU’s?
My sql database is in suspect mode, please help me with step by step how can i change the suspect status to normal status, please dont provid eme the links of other site and help me in your own language
iam getting following error:
TITLE: Microsoft SQL Server Management Studio------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ADDITIONAL INFORMATION: Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=926&LinkId=20476 ------------------------------BUTTONS: OK------------------------------Thanks in advance
I Created table like this CREATE TABLE Table(sno int ,EName varchar(20)) I inserted three records like this INSERT INTO hazarath VALUES (1,'ragav') INSERT INTO hazarath VALUES (2,'ragavRavo') INSERT INTO hazarath VALUES (3,'ramu')
Now i want to alter sno column to identity column how can i alter to identity pls help me
So I got 2 classes one I wrote to interrogate databases using normal ADO:Mine:SqlConnection myConnection = new SqlConnection(m_sConnectionString);SqlCommand myCommand = new SqlCommand(sQuery, myConnection);myCommand.CommandTimeout = 120; // 60 Seconds TimeoutmyConnection.Open();SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);return result;Microsoft WaySqlDatabase dbSvc = new SqlDatabase(m_sConnectionString);DbCommand dbCommand = dbSvc.GetSqlStringCommand(sQuery);return ((SqlDataReader)dbSvc.ExecuteReader(dbCommand));What's faster?My way:SqlConnection myConnection = new SqlConnection(m_sConnectionString);SqlCommand myCommand = new SqlCommand(sQuery, myConnection);myCommand.CommandTimeout = 120; // 60 Seconds Timeout// Use a DataTable – required for default pagingSqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);DataTable myTable = new DataTable();myAdapter.Fill(myTable);myConnection.Close();myConnection.Dispose();myConnection = null;return (myTable);Microsoft Way:SqlDatabase dbSvc = new SqlDatabase(m_sConnectionString);DbCommand dbCommand = dbSvc.GetSqlStringCommand(sQuery);DataTable dtData = null;DataSet dsData = dbSvc.ExecuteDataSet(dbCommand);dtData = dsData.Tables[0];return (dtData);Comments? Ideas?Al
I was wondering if it is possible to reference a value in a previous row as a field in a normal select statement, for example:
SELECT a.user_id, a.name, b.user_id, b.name FROM Users a LEFT OUTER JOIN Users b WHERE (b.user_id = a.user_id - 1)
Unfortunantly my user_id values are not in sequence so I get null entries using that code. Instead of doing something as basic as -1 is there another way to reference the previous row?
I have an application that stores xml data in an unusal manor. Basically a SQL Key column and an XML string.The XML string is not really standard XML, but it is what it is, and I'm stuck with it. It is in the format;
<row key="Value.01" xml:space="preserve"><c1>FirstName</c1><c2>LastName</c2><c3>10 Street Address, City ST 012345-1234</c3><c4>5</c4><c5>50</c5><c6>500</c6></row>
I am able to pull values out via SELECT p.value('(./c1)[1]', 'VARCHAR(8000)') AS c1, p.value('(./c2)[1]', 'VARCHAR(8000)') AS c2 FROM dbo.UserXMLTable CROSS APPLY XMLRECORD.nodes('/row') t(p) where p.value('(./c1)[1]', 'VARCHAR(8000)') like 'First%'
However I've been struggling with selecting row with a LIKE clause. Something like ;
SELECT * FROM dbo.F_UserXMLTable where XMLRECORD.value('(./c1)[1]', 'VARCHAR(8000)') like 'First%'
I have tried a number of permutations of XML syntax but so far have been stumpled.
Please note "<row key="Value.01" xml:space="preserve">" has a <SP> in the name 'row key' .
I just ran across an interesting problem, that makes no sense. I built an SSIS package that updates a column, using an transformation script. Testing in Debug mode everything runs perfectly, but when I have SQL sever agent run the package it insert null into the new column.
Any thoughts or suggestions would be greatly appreciated.
As pointed out in the SQL Express blog recently http://blogs.msdn.com/sqlexpress/archive/2006/11/15/sql-express-sp2-and-windows-vista-uac.aspx (look for the section that starts "Watch out!"), the SQL Express SP2 argument ADDUSERASADMIN will not work correctly if the user is a normal user. If the user is a member of the BUILTINAdministrators group then Vista will prompt to elevate them to allow the Admin rights to be effective.
However, my reading of the blog post is that if they are normal users, Vista will prompt for the Administrator credentials. This effectively runs the install as the Administrator user. So the ADDUSERASADMIN argument works, but adds the Administrator rather than the normal user.
This is not what I need to happen. Is there any way around this? I have a ClickOnce application. Is there any way to restrict the ClickOnce install to require the user to be a member of the BUILTINAdministrator group?
did VARCHAR(20) SELECT top 10 CAST(did AS INT) FROM table Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.
SELECT CONVERT(INT, did) FROM table
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.I found a SQL Function that removes all the alphabetic characters from the string and just leaves the integers.
I have a stored proc I want to convert it to either a Normal Query using A while loop or a set based operation/recursive cte as I want to run it for multiple CompanyNames. I get the error message as An INSERT EXEC statement cannot be nested when I execute if for Multiple Companies using another Cursor
If I convert it to a Function I get the below error message
Invalid use of a side-effecting operator 'EXECUTE STRING' within a function
converting this query to a normal query or let me know if there is any change which need to done to work with multiple companynames.
CREATE PROC [dbo].[USPT] @CompanyName varchar(50),@tablename varchar(50) AS BEGIN -- EXEC [USPT] 'xyz corp','Sales Header' DECLARE @str1 VARCHAR (MAX) set @str1 = ' DECLARE @No VARCHAR (MAX)
I'm not a SQL Server expert, but need to understand something. I'm currently have SQL Server 2000 SP4 installed locally on my machine for application development.
Using sp_lock, I noticed that there are lots of shared database locks. After some study, i produced this interesting script:
use Master go sp_lock go use Experience go sp_lock go
which results in:
spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- ---------------- -------- ------ 52 1 85575343 0 TAB IS GRANT spid dbid ObjId IndId Type Resource Mode Status ------ ------ ----------- ------ ---- ---------------- -------- ------ 52 13 0 0 DB S GRANT 52 1 85575343 0 TAB IS GRANT
My question is about the extra lock added by the "Use Experience statement". It lasts as long as the Experience DB connection is open
Question 1: The way I read this is: the "use Experience" statement creates a shared database lock. Am I reading this right?
Question 2: I started looking into this because my application produces lots of locks that seem to live for 15-20 minutes. I have not noticed other adverse side effects of these locks. I have confirmed that nothing I have control over creates transactions, so I do not see why locks would be maintained. Should I be concerned about these locks? Is this expected behavior?
I plan to use Log Shipping on SQL 2000 to have warm standby database. I understand current procedure but have a question wether I can do full normal backups of my database server without screwing up my Log Shipping process? Also can I do transaction log backups as well (separate from the ones used for log shipping)?
Today I ended up in a situation where I had a process with total six "subthreads" (identified by different execution context) (seen in Activity Monitor). All of these had blocking=1. The server didn't function properly, I don't know the details of these problems, since I was not present at that time. We had to kill the processes. What is the process id 1, "RESOURCE MONITOR" in SQL Server 2005, seen in Activity Monitor? Is it fatal if some processes are blocking RESOURCE MONITOR? How can one end up in such situation, is it normal or a bug somewhere?
The server is a 64-bit Windows server having SQL Server 2005 SP1.
Yesterday I had a CLR stored procedure running on another server. The procedure uses System.Data.SqlClient.SqlConnection to access this server. The procedure started about 11.4.2007 22:22. The procedure created a connection to the SQL Server and created a select that should return 1,5 million rows. During fetching the rows (about after 800 000 rows) the procedure crashes to an error:"".NET Framework execution was aborted by escalation policy because of out of memory. " Naturally the procedure couldn't close the SQL Server connections, since it was forced to end.
The details if the processes as seen from Actívity Monitor (I only have screenshots so I can't copy-paste...):
The main process:
Process id: 69
status: suspended
open transactions: 1
command: SELECT
Application: .NET SqlClient Data Provider
Wait time: 578
Wait type: ASYNC_NETWORK_ID
CPU: 1375
Physical IO: 22
Memory usage: 2
Login time: 11.4.2007 22:22:05
Last batch: 11.4.2007 22:22:05
Blocked by: 0
Blocking: 1
Execution context: 0
Two "subthreads", there are five similar.
Process id: 69
status: suspended
open transactions: 0
command: SELECT
Application: .NET SqlClient Data Provider
Wait time: 35293046
Wait type: CXPACKET
CPU: 4875
Physical IO: 2214
Memory usage: 2
Login time: 11.4.2007 22:22:05
Last batch: 11.4.2007 22:22:05
Blocked by: 0
Blocking: 1
Execution context: 1
Process id: 69
status: suspended
open transactions: 0
command: SELECT
Application: .NET SqlClient Data Provider
Wait time: 35293031
Wait type: CXPACKET
CPU: 4875
Physical IO: 2210
Memory usage: 2
Login time: 11.4.2007 22:22:05
Last batch: 11.4.2007 22:22:05
Blocked by: 0
Blocking: 1
Execution context: 2
The rest three subthreads differ from the above by having different wait time, CPU, physical IO and execution context.
In SSRS I am trying to get a textbox value to hold text with a mixture of formatting, along the lines of "name (country)" where the "name" part is bold and the "(country)" is normally formated.
The reason I say this is because a subtotal of a dollar amount will take up more space than other values. Right now, I'm forced to make all columns the same larger width because it appears to be all wrapped into 1 column width setting. I can try to change the value of the subtotal column, "matrixcolumn4", but it reverts to the other value after I press enter to apply the changes.
Trying to clean up after installing several SQL servers on a server. Started by installing SQL2000 enterprise as a named instance. Then added a instalation of SQL2005 enterprise again as a named instance. Then renamed OS level Server / Computer name. Now want to rename the SQL Servers or drop and then re add.
Anyway a long story short I have searched many boards and tired a bunch of things but still I come back to How can I remove things that show up in the list when you use the osql /L command from a command prompt.
Here are several things I have either tried or checked with many different varaitions:
