Completely new to the world of databases. I'm a designer who works primarily in Flash. In any case, I'm trying to manage an application that uses MS SQL and learn about the wonderful world of databases.
Ok, I modified a table (e.g. I added a column called "Rate") that had associated views (created by another developer). Noticed that my application went a little wonky as some of my variables within my app took on the value of the data in the "Rate" column. I checked one of the views and noticed that a column within the view (e.g. TutorID) was assuming the values in the "Rate" column. Note: The column TutorID had been blank before the change to the table. I'm completely lost as to why this is happening. Do I need to rebuild the view? Can I just reset the original view?
Thanks.
Oh yeah, I'm using SQL4X Manager J from Mac Guru (if that helps).
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
I have some rather large TSQL scripts I'd like to schedule as jobs. Unfortunately, the SysJobSteps table in the MSDB Database is limited to 3200 characters while I need it to be 5000. Does anyone know if it's possible to increase the size of this to allow for larger TSQL scripts?
I have a field called "assescode9" in a table called "Assessment" which has the length of "6" and I want to change this length to 7 or 8 in SQL. I tried right click on the field and modify but when I want to save it says you can not save the changes because it requires a Drop table and recreate or you do not have an option enabled to recreate the table, how do I do this?
I have never done dropping a table and recreate it?
I am trying to add a new field to a table using enterprise manager by right clicking on the table, choosing design table and adding the new field/column. But when I choose save changes, I receive an error message:
------------------------------------------------------------ Errors were encountered during the save process. Some of the database objects were not saved. 'Comments' Table -Unable to modify table ODBC error: [Microsoft][ODBC SQL Server Driver][SQL SERVER] ... ... Cannot drop the table 'dbo.Comments' because it is being used for replication -----------------------------------------------------------
If it helps I am trying to add a binary (bit) field and a text field of 50 characters. I think the replication it refers to is a merge replication (not sure though is there a way i can find out in EM?)
I have read some articles which refer to this problem and suggest using stored procedures but I really dont know how to do that, having never used them before. I need an idiots guide please!
Let me know if I need to provide more info. Thanks.
have to recordsets with the following with the following SQL
SELECT place, count(minus)as countminus order by place group by place
THE RESULT WOULD BE
PLACE countminus
ABC 10 DEF 50 HIJ 5 KLM 2 MNO 0
MY OTHER QUERY IS SELECT plus, count(plus) as countplus GROUP BY plus ORDER BY plus
THE RESULT WOULD BE:
PLUS COUNTPLUS
ABC 10 DEF 20
my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS...
with this condition..
I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF...
THE RESULT WOULD BE THIS.....
PLACE countminus COUNTPLUS
ABC 10 10 DEF 50 20 HIJ 5 0 KLM 2 0 MNO 0 0
I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYS
When I try to modify a table that I just created I get the following error message: - Unable to modify table ODBC error:[Mircrosoft][ODBC SQL Server Driver] Invalid cursor state.
SP3 has been applied to SQL Server 2000.
Can anyone help explain what is causing this error? There is sufficient space for the database and transaction log.
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
there is a table - TableA in my DB which iam using in a stored procedure now the table is normalised and the same date is spread in 6 diff tables. my admin is creating a View which is just like TableA.
my question is do i have to change anything in my stored procedure as TableA is a view and not a table anymore.
HiComplete newbie to sql server. I have a Companies table from which I wouldlike to create a Clients view as follows;SELECT ID, Company, CompanyTypeFROM dbo.CompaniesWHERE (CompanyType = N'Client')Such that when I insert a record into this view it automatically setsCompany Type to Client. Just so I can treat the view as just another tablewithout having to worry about setting the correct company type. Is there away to do this either via views or some other way?ThanksRegards
If anyone can help... I have a function that takes one parameter andbring back quite a lot of records. but with a conventional function thefield size has a limit of 8000 and i can not use field type 'text'. thelimit has been reached and the view does not bring back results. anoption i found was to use a table function. but with my limitedexperience with table udf's, I am failing to use it within a view. I ampassing a value from the view as a parameter to use in the function andit doesnt work.Please help.*** Sent via Developersdex http://www.developersdex.com ***
I have an existing table called OrderHeader with a column called ID. The ID column is currently set as an INT. I want to run a script to modify that column to be an IDENTIY. I've tried the following:
ALTER TABLE OrderHeader ALTER COLUMN [ID] INT IDENTITY
I am trying to modify data in a tble using the Stored Procedure below. It doesnt work properly. I seem to only be getting one character. I think it has something to do with me using "nchar" with the variables. The value I am changing is actually a string.
I have a DTS package package1 which imports an excel sheet into my DataBase.ANd its saved and scheduled and running fine.Actually the excel sheet will be located daily @ c:ruceexceldata.But now we wanted to change the location of the file to c:ruce1ewexceldata
I have a scheduled DTS package which gets the data from a text file and imports into a sql table.The table will be droped and created whenever the DTS package runs.previusly the table has 6 rows and now i wanted to add a column row_id which is having an idenity seed.So how can I modify my package so that I can get done with my requirements.
I have a query that I am working on that involves 2 tables. The query below is working correctly and bringing back the desired results, except I want to add 1 more column of data, and I'm not exactly sure how to write it.
What I want to add is the following data.
For each row that is brought back we want to have the COUNT(*) of users who joined the website (tbluserdetails) where their tbluserdteails.date is > the tblreferemails.referDate
Effectively we are attempting to track how well the "tell a friend" via email feature works, and converts to other joined members.
Any assistance is much appreciated!!
thanks once again mike123
SELECT CONVERT(varchar(10),referDate,112) AS referDate,
SUM ( CASE WHEN emailSendCount = 0 THEN 1 ELSE 0 END ) as '0', SUM ( CASE WHEN emailSendCount = 1 THEN 1 ELSE 0 END ) as '1', SUM ( CASE WHEN emailSendCount = 2 THEN 1 ELSE 0 END ) as '2', SUM ( CASE WHEN emailSendCount = 3 THEN 1 ELSE 0 END ) as '3', SUM ( CASE WHEN emailSendCount > 3 THEN 1 ELSE 0 END ) as '> 3', SUM ( CASE WHEN emailSendCount > 0 THEN 1 ELSE 0 END ) as 'totalSent',
count(*) as totalRefers, count(distinct(referUserID)) as totalUsers,
SUM ( CASE WHEN emailAddress like '%hotmail%' THEN 1 ELSE 0 END ) as 'hotmail', SUM ( CASE WHEN emailAddress like '%hotmail.co.uk%' THEN 1 ELSE 0 END ) as 'hotmail.co.uk', SUM ( CASE WHEN emailAddress like '%yahoo.ca%' THEN 1 ELSE 0 END ) as 'yahoo.ca', SUM ( CASE WHEN emailAddress like '%yahoo.co.uk%' THEN 1 ELSE 0 END ) as 'yahoo.co.uk', SUM ( CASE WHEN emailAddress like '%gmail%' THEN 1 ELSE 0 END ) as 'gmail', SUM ( CASE WHEN emailAddress like '%aol%' THEN 1 ELSE 0 END ) as 'aol', SUM ( CASE WHEN emailAddress like '%yahoo%' THEN 1 ELSE 0 END ) as 'yahoo',
SUM ( CASE WHEN referalTypeID = 1 THEN 1 ELSE 0 END ) as 'manual', SUM ( CASE WHEN referalTypeID = 2 THEN 1 ELSE 0 END ) as 'auto'
INSERT @Request SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL SELECT '2232362', 'Buck', 'SA055', 67, 195, Null
INSERT @Call SELECT 111, 1, 12123 UNION ALL SELECT 112, 1, 12123 UNION ALL SELECT 114, 2, 12123 UNION ALL SELECT 123, 2, 12123 UNION ALL SELECT 134, 3, 12123 UNION ALL SELECT 143, 1, 6532 UNION ALL SELECT 145, 1, 6532 UNION ALL SELECT 154, 2, 6532 UNION ALL SELECT 185, 2, 6532 UNION ALL SELECT 195, 3, 6532
INSERT @CallDetail SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL SELECT 6532, 1, '11/5/2007 12:12:34 PM' -- --select * from @Request Query written to achieve the requirement UPDATE r SET r.UniqueNo = p.RecID FROM @Request AS r INNER JOIN ( SELECT r.RequestID, ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallDetailID, c.CallType ORDER BY cd.EntryDt) AS RecID FROM @Request AS r INNER JOIN @Call AS c ON c.CallID = r.CallID INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID ) AS p ON p.RequestID = r.RequestID WHERE r.UniqueNo IS NULL
I am in a scenario where my tables are refreshed every morning by a batch update. I have built a few views off of one table. To increase speed I would like to take all the rows from one of the view s and insert them into their own table. I know this can be done with some T-SQL but I'm a noob to it and don't know how to specifically do it.Any detailed help would be greatly appreciated. -Nate
I'm designing a database for my final year project at University, and have run into a bit of a dilemma with one of the tables. Basically, it's an equipment loan database where both students and lecturers need to be able to book and borrow equipment. To avoid having two separate bookings tables (i.e. StudentBookings and LecturerBookings) I've got students and lecturers combined into one table called 'Borrowers'. The trouble comes because the clients want the lecturer storing in the booking information table, which can get quite messy because lecturer information is stored in the same table as borrower information meaning that I basically need to relate both the Booking.BorrowerID and Booking.LecturerID to Borrowers.BorrowerID.
In theory, I think this could be solved by creating a View called Lecturers that pulls the required information for Lecturers out of the Borrowers table and then link Booking.LecturerID to the ID of the view. In practice, I've got a couple of queries:
1) Is it possible to perform a JOIN on a view in the way that I would need to here?
2) To keep things nice and clear, is it possible to 'rename' the BorrowerID to LecturerID whilst creating the View?
Sorry if some of this isn't too clear. Please ask any questions to clarify what I mean if you need to.
Normally i can use Web Matrix to connect to a SQL table. e.g. Dim queryString As String = "SELECT [MyTable].* FROM [MyTable]"Is is possible to connect to a SQL View. The reason is because i have generated a view using UNIONS and JOINS and some columns are generated by concatenating columns together (ie.. has alias columns).I also need to use VB.NET to refer to these alias columns.
We have a table created by an application, and a view that joins the table with other tables.
For some reason we are now unable to drop the table or the view. In Enterprise Manager the drop table dialog comes up, we click 'Drop All' and then the hour glass comes up and never goes away. No errors are returned, the process just never returns control to the client, the same when trying to remove the view. Using Query Analyzer is no different.
However stopping and starting the server resolves the problem for a while, but eventually the same problem starts happening. The table is created, populated and dropped using stored procedures called from a web page via asp script. This process may occur numerous times and hasn't been a problem until the last day or so when the developer added a couple of smallint columns to the table.
Hi all, What's different from SELECT FROM A TABLE to SELECT FROM A VIEW? Which is faster? Can anyone explain my result following? Example for: -- TABLE: set statistics io on select * from TABLE_NAME -->Table 'TABLE_NAME'. Scan count 2, logical reads 106, physical reads 0, read-ahead reads 0
-- VIEW: Create view VIEW_NAME as SELECT * FROM TABLE_NAME set statistics io on select * from VIEW_NAME -->Table 'TABLE_NAME'. Scan count 1, logical reads 53, physical reads 0, read-ahead reads 0.
Can anyone help me with displaying several table properites at once. I know I can use sp_help 'tablename' to get one at time. What is the best method to get several databases at a time?
