I am having a problem with my team mates believing that primary keys need to be unique. I have the following data listed below and I am getting a violation of a primary key constraint. The primary keys for this table are request, business_req_id and test_case_id. I beleive the error is happening due to the data not being unique. Am I correct?
I am having a problem with my team mates believing that primary keys need to be unique. I have the following data listed below and I am getting a violation of a primary key constraint. The primary keys for this table are request, business_req_id and test_case_id. I beleive the error is happening due to the data not being unique. Am I correct?
How do I select * remaining Records of a table that don't fall true to the 2nd Select statement? Select * From zz_2007_Booth_Visitors
NOT IN
Select * From zz_2007_Booth_Visitors Where [Product Interest - Actuators] Is Null And [Product Interest - Other Actuator] Is Null And [Product Interest - Chillers] Is Null And [Product Interest - Other Chiller] Is Null And [Product Interest - Electronic Products] Is Null And [Product Interest - Other network interfaces] Is Null And [Product Interest - Fittings] Is Null And [Product Interest - High Vacuum] Is Null And [Product Interest - Other high vacuum actuators] Is Null And [Product Interest - Pick& Place and Transfer] Is Null And [Product Interest - Teflon Products] Is Null And [Product Interest - Training] Is Null And [Product Interest - Valves& Manifolds] Is Null
I have always heard that much of t-sql is based on "set theory". I had Set theory in high school and I remember it as being simple Unions, Intersections, Differences of Sets. By a Set I mean a collection such as {2,5,7,8,9, ...) That could well described a single row in a table. By unioning several of these rows we could end up with a table.
But how does that relate to t-sql such as
select * from <table name> Where <condition 1> ?
Is it simply that the result returned by the query is a Set? (if so, a Set is simply being used as a synonym for a Collection. No set theory involved.)
I have been tasked with creating a Data Warehouse.
Problem is that old storage vs reporting debate.
I have determined that the data that I will recieve and store will be like follows (simplified form) for expandability
KEY FldKEy FldData DateTime AuditTrail
Daily I will use this data based on use input process this data into the following format and say if fldkey/ flddata open a cycle. populate row with null close date if fldkey/ flddata closes cycle update row with date
If fldkey/ flddata changes a cutable value update row
if fldkey/ flddata changes a cutable value (type 2 table) insert a row into detail update value and obsolete previous row.
KEY DateStart DateEnd FLDDATA1 FLDDATE2 Op_Cl_IND HEADER Record
Problem: FLDKey is a finite count however the max is undefined.
IS there any way to solve the problem of not being able to nail down users to tell you what they want to cut by. What I have been instructed by mgr (old IDMS) is that they wish to see all on the FldData and have the ability to cut by all of it. However the Flddata could be anything (cannot be indexed).
400,000,000 rows at least.
Do I need to nail the users down or am I am missing something.
I understand that there is much involved in figuring up I/Othroughput, but I'm hoping the answer to the following question willbe a simple A, B OR C.Given the configuration below, what hardware component wouldbottleneck first.A. Drive Spindle ThroughputB. SCSI ControllerC. Other Component(if so what component?)Configuration:Controller: Ultra320RAID Config: RAID 5Drives on RAID: 14 - 73gb/15k - SCSI DrivesType of Writes: RandomQuestion Background:I currently have the configuration above on a local box. Our companyhas ordered a SAN and and has space set aside. What I'm trying toacertain is whether or not I'd be better off staying local or ifputting my DB on a SAN would be more beneficial.Other Notes:I've determined using a formula that determines the max theoreticalI/O operations/sec for the # of drives (spindles) and RAID Config...but I've not been able to establish a relationship betweenOperation/sec -> MB/Sec. I'm sure this is for obvious reasons...If anyone has had a related scenario, I'd be interested in hearingyour thoughts.Thanks,Ornac
Hi, our application is failing sometimes, with some select queries. After making traces in the database, I found the following error: Missing join predicate. I googled that, and I only found this useless tip:
Missing Join Predicate: Indicates whether or not the query in question has a join predicate. If not, this can cause the Query Optimizer to produce a less than optimized query plan. The fix to this is to add a join predicate.
So, I dont know what a join predicate is... maybe I used it, but I don't know it by that name.
My co worker designed a database where retail items can be placed in multiple catagories. This seems odd to me..... In general, Isnt it more normal than not to be only one catarory for each item? For example, lets say I was selling a bowling ball with a picture of Mickey Mouse on it. I can then find this item in the "Mickey Mouse" catagory or in the "bowling ball" catagory but in the database the bowling ball has only one catagoryID. When I worked for a multi-million dollar corporate retail store , an item was listed once in only one catagroy. But i am sure items can be viewed
I know there isnt a single rule, I am just looking for a solution. How should the database sturucture be built with this in mind starting out with what is listed below???? Mabey an attributes table?
I have a general theory question for best practices about upsizing an msaccess 2003 split database design to use SQL server instead of the .mdb for data storage.
My data has grown close to msaccess limit, and ive started experiencing lost connections and corruptions frequently. So the next step is to upsize to SQL.
So far DTS seems to do a better job with bringing in the tables and data (then the upsizewizard).
Does any one have a suggestion on how to deal with the front end connecting the sql backend ?
i understand an ADP project file uses OLE connection, is that a better solution then linked tables directly into SQL threw ODBC ?
Reason i ask is the linked tables seem to not break as many things inside the code.
I like writing concise and compact sql code without cursors if possible. My current dilemma has me stuck though.I have 3 tables, but one of them is optionally used and contains a key element of TimeOut to determine which Anesthesia CrnaID to use. It is the optionally used part that has me stumped.
Surgery table CaseID Patient (Sample data: 101,SallyDoe 102,JohnDoe)
AnesthesiaTime table (this table is optionally used, only if the crna's take a break on long cases) CaseID CrnaID TimeIn TimeOut (Sample data: 102,Jack,0800,1030 102,Bart,1030,1130 102,Jack,1130,1215)
Select Patient INNER JOIN Anesthesia produced too many case results. So, I figured out there is an AnesthesiaTime table that only gets used if the anesthesia guys take time-outs. That doesn't happen all the time. I could use TOP 1 on the Anesthesia table, but technically I need to read the AnesthesiaTime table and locate the last time and pull that crna, Jack. I'm not sure how to deal with an optional table. I believe the IF Exists will be pertinent, but not sure of how to build this query. I've tried subquery without success.
This morning I can not connect to our SQL Server 7.0 whatever from client or server. The error message which I list below:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++ A connection could not be estabished to server--Timeout expired Please verfy SQL Server is running and check your SQL Server registration properties and try again. ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++
We use windows NT authentication. We did not do any change on NT. The SQL Server daily schedule job usally stoped at 10:00AM, but today from the Window NT Task Manager, we can see that the SQL Server is still running untill now.
hi, I have settup up sql mail and did the following: 1. created an E-mail account and configured Out look by creating a pop3 mail profile. tested it by sending and receiving mail, that is ook 2. I Created one domain account for MSsqlserver and Sql Agent service. both services use same account and start automatically in the control panel-services 3. I used the profile that I created in outlook to test the sql mail but got an error: Error 22030 : A MAPI error ( error number:273) occurred: MapiLogon Ex Failed due to MAPI Error 273: MAPI Logon Failed
I really do not know what went wrong. I followed the steps from bol and still having a problem. Am I missing something.
I do have a valid email account I do have a valid domain account I tested outlook using the email account and it worked. so why sql server does not recognise MAPI.
My next question, How to configure MAPI in Sql server if what I did was wrong.
Hi, I have 2 windows 2000 server in cluster with sql server 2000 enterprise edition installed. I have activated the Server-Requested Encryption by using the sql server network utility (Force Protocol Encryption). After this, I have stoped sql server service. But I can't start it at this moment. The error is: 19015: The encrypton is required but no available certificat has been found.
Hello, I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query. Ex. how can the following be implemented using the same query but in sql server? Access ------ SELECT MAT_Charts.YYYYMM FROM MAT_Charts WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key])) GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested: [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
I am getting error in sysindexes when i run dbcc checkdb on a production db. the error is Server: Msg 8928, Level 16, State 1, Line 1 please help me to remove this all the options of dbcc checkdb as well as table are not helping me
Hi Guys i write a thread before asking for help with reading an uploaded csv file, i have my code, it reads the csv file and currently displays it in a datagrid but what i actually want is to take the read information and import it into my sql express db. Heres the code<script runat="server"> Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) If (IsPostBack) Then Grid1.Visible = True Else Grid1.Visible = False End If End Sub Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) 'Save the uploaded file to an "Uploads" directory ' that already exists in the file system of the ' currently executing ASP.NET application. ' Creating an "Uploads" directory isolates uploaded ' files in a separate directory. This helps prevent ' users from overwriting existing application files by ' uploading files with names like "Web.config". Dim saveDir As String = "Data"
' Get the physical file system path for the currently ' executing application. Dim appPath As String = Request.PhysicalApplicationPath
' Before attempting to save the file, verify ' that the FileUpload control contains a file. If (FileUpload1.HasFile) Then Dim savePath As String = appPath + saveDir + FileUpload1.FileName
' Call the SaveAs method to save the ' uploaded file to the specified path. ' This example does not perform all ' the necessary error checking. ' If a file with the same name ' already exists in the specified path, ' the uploaded file overwrites it. FileUpload1.SaveAs(savePath)
' Notify the user that the file was uploaded successfully. UploadStatusLabel.Text = "Your file was uploaded successfully."
Else ' Notify the user that a file was not uploaded. UploadStatusLabel.Text = "You did not specify a file to upload." End If
End Sub
Sub DisplayButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:inetpubwwwrootMerlinLocalPostOfficeAppData;Extended Properties=""text;HDR=NO;FMT=Delimited""" Dim objConn As New OleDbConnection(sConnectionString) objConn.Open() Dim objCmdSelect As New OleDbCommand("SELECT * FROM csv.txt", objConn) Dim objAdapter1 As New OleDbDataAdapter() objAdapter1.SelectCommand = objCmdSelect Dim objDataset1 As New DataSet() objAdapter1.Fill(objDataset1, "csv.txt") Grid1.DataSource = objDataset1.Tables(0).DefaultView Grid1.DataBind() objConn.Close() End Sub
</script> My csv file does not have headers so the default value is F1, F2, F3 F4, F5,F6, F7, my database has the following columns, ID,AddressLine1,AddressLine2,AddressLine3,AddressLine4,AddressLine5,AddressLine6,Postcode I need to know how to import the information direct into the db rather than displaying it on the page, ive tried but im really new to this and cant get it to work. I cant use DTS or bulk insert as the server this will go on doesnt have sql on it, the db is an MDF file so is transportable with the app. Thanks for your help
Hello I'm trying to execute the following INSERT INTO @dbName.dbo.AP_TransMain SELECT * FROM Inserted WHERE Pay_Id=Inserted.Pay_Id but its giving me an error becuase of @dbName which is a variable decleared as the followingDECLARE @dbName as varchar(100) the Database name is assigned to @dbName but i can't take the value of @dbName to combine it to the rest of the statment INSERT INTO @dbName.dbo.AP_TransMain SELECT * FROM Inserted WHERE Pay_Id=Inserted.Pay_Id
Hi, I am trying to make a backup of my database using Sql-DMO
When I do the backup on my server, my code runs fine. But when I backup from across the network from another computer, the database is saved on the server and not on the computer from where I am executing my application. How set SQLl-DMO to backup on the computer from where I am?
here is my code (found on the net)... Any suggestions greatly appreciated!
Cursor = Cursors.WaitCursor 'create an instance of a server class Dim my_srv As SQLDMO._SQLServer = New SQLDMO.SQLServerClass 'connect to the server my_srv.Connect("servername", "userid", "password")
'create a backup class instance Dim my_backup As SQLDMO.Backup = New SQLDMO.BackupClass 'set the backup device = files property my_backup.Devices = my_backup.Files 'set the files property to the File Name text box my_backup.Files = Me.txtFilePath.Text 'set the database to the chosen database my_backup.Database = "MYDB" 'perform the backup my_backup.SQLBackup(my_srv) MsgBox("Database successfully backed up.", MsgBoxStyle.Information) Cursor = Cursors.Default
I've a bactch file that is ran by the user and it looks for files and if it doesnot exist it waits for it and when it exsits, through isql it truncates the table and bcp in the file. In bcp files the loginname used in isql has dbo rights to truncate the table and the batch file also has password. The user who runs the script can also view the batch file and can know the password, Is there any way I could rewrite it so that the user cann't view the password or other way like SQL DMO or DTS package
Can any one tell me what is the database log size for a datafiles (tables and Indexes 500MB) On What basis database log size is estimated for the Database Creation of 500MB ? On What percentage will be the dump file size and log file size with respect to Database file size?? Can any body help!!!!!!!!!! Thanks in Advance
I've a table which has clustered index on one column and I tried to run begin transaction and then 2000 insert statements and then commit transaction, the transaction took 15 min and then it hung up the table, I could not access the table I had to stop the process, the only info I received was the table had 4517 locks, I've no clue how it got so many locks, there was db backup running as I had scheduled 15 min backup. could anyone help me that inside each open transaction how many insert statement is advisible and how many maximum locks does sql server works on a table and what could have gone wrong in my situation.
Hi, I am trying to do DTS for transfering data from one server to other server,both are on T1 link,after some time DTS failed by giving error message saying that " Error at destination for Row number 3357844,Errors encountered so far in this test:1,,,Invalid charector value for cast Specification " that table have 24 million rows..
How can i solve this problem,Please help me in this.this is urgent please
I have a field 'slnumber' defined as int.Values are like 70000,70001,70002 etc.I need to export this field using bcp to a text file and corresponding field in the text file should have 10 characters and should read like 0000070000,0000070001,0000070002 etc.How do we achieve this format? Any help is greatly appreciated. Thanks. Reddy.
I've made major changes to all the tbls in my db. I created new tables and rename the old ones as table1Old and in table1 I've added 2 columns. I want the data from table1Old to table1(my new modified table). Can anyone guide me how can I do this. insert into table1 select * from table1Old.
It gives me Insert error
Server: Msg 213, Level 16, State 4, Line 1 Insert Error: Column name or number of supplied values does not match table definition.
I tried to run a query on production server and it gave me this error.
Msg 10008, Level 9, State 10008 Possible network error: Bad token from SQL Server: Datastream processing out of sync. General network error. Check your documentation. Msg 10005, Level 1, State 10005 DBPROCESS is dead or not enabled. Msg 10018, Level 9, State 10018 Possible network error: Error in closing network connection. General network error. Check your documentation.
Please help. I am completely clueless about what to do and company DBA is out of city.
