Hello All I m trying to update a table whose col name will be read from another table. For e.g. Table1 gives the result: 'emp1', 1, 'John' 'emp2', 2, 'Mike' Now in the second table, i need to update the table with Col name = 'Emp1' and then from the second row (above), I need to update Col name= 'Emp2' I need to write one Update Statement which will handle all the cases. I tried Update Table2 set @VariableName = ....... but didnt work... How can i do that ?
I'm trying to transfer data from DB2 Database to SQL Server 2005.
Well, i used the OLE DB Source, the Data Conversion Component and the OLE DB Destination component.
I have five Data flows with this configuration above. But I am receiving an error message from one of them.
Please check below the error message:
"[Source Table TARTRATE [1]] Error: The value was too large to fit in the output column "ADJ_RATE_PCT" (60). "
"[Source Table TARTRATE [1]] Error: The "component "Source Table TARTRATE" (1)" failed because error code 0xC02090F8 occurred, and the error row disposition on "output column "ADJ_RATE_PCT" (60)" specifies failure on error. An error occurred on the specified object of the specified component."
How can I get the result stored in Inserted.ColName (by Output clause of insert command). In the documentation (BOL) for SQL 2005, there is written "returned to the processing application for use" in Output clause (first paragraph).
Question 2:
How to store Inserted.ColName into local variable when insert command is running in stored procedure.:
hello i am just starting to learn sql and know the basics, but now im looking for a good book to learn some more. A book that covers stored procedure would be very useful. If possible a book with q and a would be very good because i feel this tests if u understand what was just explaned. but if there is a good book without this it is ok. All sugestions welcome
hii am using vs2005 for development of web application for reporting with sqlexpress05 as back end .later when project is ready for deployment i have to deploy the project on remote hosting server where i have limited access and sqlserver2000 database to use.i want to ask is there are any limitation or problem of sqlexpress while deploying it on remote sqlservre 2000.and should i have to to continue with sqlexpress as back end.is there any problems for using dynamic database connections(by using smart tags) other than programaticaly connecting database to asp.net ie by writing code.i am new in developmentplease guide me, please guide
hello all..i have make a searching, but is not good. my code like that:Public Class getall Public Function getitem(ByVal id As String) As DataSet Dim con As SqlConnection = New SqlConnection("Data Source=BOYsqlexpress;Initial Catalog=GAMES;User ID=ha;Password=a") Dim ds As New DataSet() Dim adapter As New SqlDataAdapter("select * from [item] where name like '%" & id & "%'", con) Try con.Open() adapter.Fill(ds, "user") Return ds Catch ex As Exception Console.Write(ex.Message) Finally con.Close() con = Nothing End Try ' Next Return ds End Functionand class my item in database is containning dragon ball 3, counter strikeif i insert dragon, it can display dragon ball 3.but if i insert dragon 3, it not display dragon ball 3.it should display dragon ball 3 .how should i change my code?thx...
So, as you can see, at first it appears that I'm after a LEFT JOIN - meaning that the grandparents don't need to have child records to be returned, but, then it turns out that I need INNER JOINS - to limit grandparents when I choose children.
I'm wondering if there's a better SQL Editor than MS Query Analyzer on the market? I like a lot of the functionality provided by QA but want extra stuff like you get in VB6: intelli-sense (sytanx prompting), auto-complete (CTRL+Space provides list of sp's and tables, etc.) plus any other time saving features.
I've tried a few products but nothing quite hits the mark. Is there a program you use and recommend I trial?
Folks, i've got a table with a column; ACCOUNT VARCHAR(30). All the values numeric though. (leave abt the datatype yet). The column is clustered indexed.
SELECT * FROM MYTABLE WHERE LEFT(ACCOUNT,3)='123' execution plan shows CLUSTERED INDEX SCAN.
SELECT * FROM MYTABLE WHERE ACCOUNT LIKE '123%' execution plan shows CLUSTERED INDEX SEEK.
How, why. Why doesn't the optimizer works good for the first query?
Hi, ive got some work to do on SQL queries, the scenario is below and at the bottom is my attempt at answering in the questions: Could somebody simply tell me if the answer at the bottow are correct, if not what I have done wrong.
A local company that produces machine parts has decided to develop an in-house database system. They have identified the following tables: -
tblOrders OrderNo, CustomerNo, Date, OrderTotal
tblCustomers CustomerNo, Name, Street, Town, County, Postcode
tblParts PartNo, Description, UnitCost
tblItems OrderNo, PartNo, Quantity, ItemTotal
Create SQL queries to produce the following: -
a) Details of all orders over £1000 sorted by customer number.
b) A list of all part descriptions and their quantities appearing on order 39
c) Delete all orders placed by customers in Wrexham.
d) Archive all orders placed by customer Clarke into a new table called tblArchive.
e) Increase the price of all parts whose description includes the word “washer” by 4%.
These are my answer, which im not too sure if they are correct. If any1 could tell me if there correct or not that would be great, thanks.
a) SELECT * FROM tblOrders ORDERBY CustomerNO WHERE OrderTotal > 1000
b) SELECT tblParts.PartNo, tblParts.Description, tblItems.Quantity FROM tblItems INNER JOIN tblParts ON tblItems.PartNo = tblParts.PartNo; WHERE OrderNo = 39
c) DELETE tblOrders.* FROM tblOrders INNER JOIN tblCustomers ON tblOrders.CustomerID = tblCustomers.CustomerID WHERE Town = “Wrexham”
d) INSERT INTO tblArchive SELECT * FROM tblOrders INNER JOIN tblCustomers ON tblOrders.CustomerID = tblCustomers.CustomerID WHERE Name = “Clarke”
e) UPDATE tblParts SET UnitCost = [UnitCost]*1.04 WHERE Description LIKE “*washer” or Description LIKE “washer*” or Description LIKE “*washer*”
I'm about 6 weeks into SQL and SQL Server (7) - I was wondering whether you could share your opinions about which language to use as a programming tool for developing apps for & with SQL Server. I'm choosing between C++ (Visual) or JAVA.
I already know C and the DB-Libe contains a lot of it but I'm kinda trying to expand some horizons. I'm ok with either C++/VC++ or JAVA but I only have time to learn (or be good at) one.
Any suggestions? (I'd like to hear what you think even if you say neither C++ or JAVA - maybe VB? What's easy and marketable is what matters most.)
I've been a SQL Server dba for 5 or 6 years now. With the upcoming release (eventually, I'm sure) of Yukon/SQL2005, I've read that it's important for DBA's to pick up one of the .NET languages - I've figured, I'll try to learn VB.NET - I've had a little exposure to it, and can usually figure out what's going on in VB code I've read - however, I seriously doubt I could write anything in it from scratch - I want to learn it in a bad way - can you all recommend any self-paced books that will walk me thru it? I've never had any formal training with it, don't know a class from a DLL....Thanks in advance for your help!!
Hi guysWe have a following problem. For security reasons in each table in ourDB we have addition field which is calculated as hash value of allcolumns in particular row.Every time when some field in particular row is changed we create andcall select query from our application to obtain all fields for thisrow and then re-calculate and update the hash value again.Obviously such approach is very ineffective, the alternative is tocreate trigger on update event and then execute stored procedure whichwill re-calculate and update the hash value. The problem with thisapproach is that end user could then change the date in the tables andthen run this store procedure to adjust hash value.We are looking for some solution that could speed up the hash valueupdating without allowing authorized user to do itThanks in advance,Leon
hello, for a new job i might have to learn SQL. i've neverworked with databases or SQL, so i'll need to learn. cananybody advice me on what would be a good book to learnfrom? i'm quite an experienced programmer, so it doesn'thave to be a dummies guide, and preferably not a bulky booklike the "SQL bible" or something.oh, one of my 'favourite' computer books of all times is"thinking in Java" by bruce eckel, to give you an idea.mike--not sure if there's a better group to ask these questions
Hi all, I have couple of databases in access which i want to spilit them in backend and frontend, and then put the backend( just tables) in sql and keep frontend in access. which version of sql is good for me to do that? by the way there are more than 10 users want to access the front end for dataentry. all the databases and the sql server can be in the same server.
I'm looking to buy a book about SQL, T-SQL, stored procedures, etc. I'm a web developer, so I don't need a book for a SQL Server administrator. I need a book that tells me what I as a developer need to know about SQL. I'm currently using Visual Studio, SQL Server 2005, ASP.NET 2.0, and VB.NET (and fairly new to all of those) if that makes any difference. The bulk of my web developing experience has been with PHP and MySQL so this is new territory for me. I hope all that makes sense. I would appreciate any suggestions for such a book. Thanks! -Mathminded p.s. I bought a bunch of Wrox books and haven't had them long enough to decide if I like them: "Professional ASP.NET 2.0", "Professional ASP.NET 2.0 Security, Membership, and Role Management", "ASP.NET 2.0 Website Programming", and "Visual Basic 2005". I thought I'd check with the experts before investing in another book. :-)
I am new to SQL2005 and have been given the task of writing some SSIS packages to import some CSV files. I need to cleans the data as it is imported from my CSV files before it reaches my SQL DB. I am currently Googling the internet to discover how to do this. Can anyone recommend a good SSIS book? I am a C# developer, so a book that has lots of SSIS C# examples would be good. Any help appreciated. Regards, Paul.
HiI have a field containing numbers. I want to do some simple arithmetics with it, say value=value+1 or value=value-1 or or even value+2. What is to be done, is fixed at design time. I think this could be done by loading the row or record to my program and doing the calculations there. And then storing the record back. But this seems too complicated.Is there a single query doing that in data table.
hello, i am wondering, for what i use relations between tables? if i make sure that the data inserted into the table matches, for what i need table relationships? i have a table named Pictures with: PictureID, UserID, UserName, PictureName, Description... do i have to make relationships between UserID from Pictures and UserId from aspnet_Users? and the second table PicturesComments witch has: CommentID, PictureID, UserID, UserName... do i have to makre relationship between PictureID from PictureComments and PictureID from Pictures AND between UserID from Pictures and UserId from aspnet_Users? sorry for this stupid questions... thanks
I have a complex select statement that is used in several stored procedures. I decided that instead of having x number of T-SQL scripts with the same exact select statement that I would to put this query into a view and then do a select * from View. Recently an instructor told me that this was a bad idea and that anyone who uses a select * from anything should be fired. When I asked for his reasoning his response was to say the least abnoxious. I can understand why a Select * from Table might be a bad idea as the table definition can change, but the chances of a view changing seems much less likely. Is a view a good idea in this case? Is the Select * from View really a bad idea? Thanks
I have a simple table right now that has some rows listed like this:Table Name = TicketStatusTicketNumber TicketType Status Time1 Normal In 09/15/2005 10:50:213 Normal In 09/11/2005 19:25:101 Normal Out 09/15/2005 11:45:103 Normal Out 09/11/2005 20:27:092 Normal In 09/14/2005 17:25:101 Normal Pay 09/15/2005 11:15:152 Normal Out 09/14/2005 21:45:30What I want to do is select only 1 row per ticket number, and this row needs to be the row that has the LATEST time for that particular ticket number. Then I want to sort the results by ticket number decending. So for instance, the select I am looking for would bring me back ONLY the following rows in the following order: TicketNumber TicketType Status Time3 Normal Out 09/11/2005 20:27:092 Normal Out 09/14/2005 21:45:301 Normal Out 09/15/2005 11:45:10My issue is I do not know how to go about selecting ONLY 1 row per ticket number, and the row I select has to be the row with the latest date for that particular ticket number.Can any SQL gurus provide me with some code in order to do this? Thanks so much for the help guys!
We have some users who are nervous about our upgrading to SQL Server 7.0 even though we will use 6.5 compatibility mode initially while we work through 7.0 upgrade issues in the applications.
Has anyone had bad experiences with the 6.5 compatibility mode feature? Just how good is it?
Hi! I'm installing a new SQL Server machine. During NT Server installation our NT support guy converted the only 2GB FAT C: partition to NTFS. So as of right now all my 4 8GB drives are NTFS. I think it would be better to keep this C: partition in FAT because, as of my knowledge, having FAT boot partition can help to boot the machine in case of NT crash.
Is there anything that I'm really losing by this conversion to NTFS or I should not be worried so much about it? Does it put my SQL Server databases, database .dat files or NT Server in more danger situation in case of any crash? Or it's giving me some advantages? Thanks Ninel
Can anyone tell me if turning on the AUTO_UPDATE_STATISTICS option will have any adverse effects on performance? I've read that it is possible for it to adversely affect performance, because SQL Server “takes a quick break to update database statistics in the middle of the day”. The book does not explain why and I'm always leery of simply accepting something, simply because I’ve read it from a book. This is the first time that I’ve ever heard this and cannot find anything from SQL BOL or any other source.
Sorry if this has been posted before, but I performed a search on the archives and didn't find anything !
I have just been turned over a SQL 6.5 SP3 / NT 4.0 SP3 server that has several SQL errors in the event log. I need to find a good book to read/scan, so I can get up to speed on SQL.
Hey all, I`m trying to BCP in some data from a csv file. It has dates formatted like d/mm/yy and when run BCP it errors. Of course...SQL language is set to English(United States) so it wants it in mm/dd/yy. But from what I`ve read in the archive, from Sharon at least, is that only in BCP will it use the NT date setting which for me is English(Australian) or d/mm/yy. So I`m thinking, `that`s ok` my data`s dates like d/mm/yy and nt dates like d/mm/yy it`ll bcp in fine - but no.
What can I do? I cant change my sql setting and I cant change the way I dump my dates to the csv! Suggestions......
