|
Having Now I remember, thanks!
|
# ? Mar 9, 2009 04:15 |
|
|
# ? Jun 8, 2024 07:29 |
|
I'm a little lost. I'm trying to write a stored procedure for DB2 from a stored procedure in SQL Server 2005. Basically, I have to make the Enterprise Library work with DB2. I was able to convert the tables, but the stored procedures are killing me. SQL Server 2005: code:
code:
Edited for solution: It turns out that DB2 throws the error on the previous line, not the one it hangs on processing.. So it should have been "IF (CatLogID IS NULL) Then". fez2 fucked around with this message at 18:20 on Mar 16, 2009 |
# ? Mar 9, 2009 19:02 |
|
fez2 posted:I'm a little lost. I'm trying to write a stored procedure for DB2 from a stored procedure in SQL Server 2005. What errors are you getting? I did a bit of DB2 stuff on my last project (and in fact, we implemented the ASP.NET Membership Provider, which is heavily tied to SQL Server out of the box, in DB2) but honestly we stayed away from DB2 stored procedures in general because of some weird issue with the particular iSeries version we were using and the data provider we had. So, we mostly went with dynamic SQL
|
# ? Mar 9, 2009 19:59 |
|
No Safe Word posted:What errors are you getting? I did a bit of DB2 stuff on my last project (and in fact, we implemented the ASP.NET Membership Provider, which is heavily tied to SQL Server out of the box, in DB2) but honestly we stayed away from DB2 stored procedures in general because of some weird issue with the particular iSeries version we were using and the data provider we had. So, we mostly went with dynamic SQL In Data Studio, the deploy errors read like so: code:
Note that I've changed the procedure some.. I have no idea how to make this monster happy. quote:CREATE PROCEDURE INSERTCATEGORYLOG ( IN Cat_ID INTEGER, IN Log_ID INTEGER, OUT CatLogID INTEGER ) I've been reading the IBM Redbook on SQL Server to DB2, but it isn't very helpful.
|
# ? Mar 9, 2009 20:09 |
|
Using the GROUP_CONCAT, I'd like to do a big OR statement in my query:code:
code:
How do I make sure the query actually reads this. The output of it now is something completely wrong. It gives me a row that its not even in the "ranked_nid = 1" category! 8286 and 8289 are temporary numbers that I will give later from outside the SQL.
|
# ? Mar 10, 2009 05:12 |
|
code:
|
# ? Mar 10, 2009 05:16 |
|
EDIT: It worked, sorry man. Whoops, Bad copy and paste. But is there a way to do the "OR = xyz" in a query like that though? cannibustacap fucked around with this message at 07:02 on Mar 10, 2009 |
# ? Mar 10, 2009 05:28 |
|
Okay, so now I have a new but related issue When I use the GROUP_CONCAT, it seems to only return 1 single row. Here is what I got: code:
code:
code:
code:
My hope is that it can return all 4 rows with the "bids" column intact for all of them. Really appreciate your help so much guys EDIT: For easy viewing and not breaking the tables, I reduced the line "WHERE (bc_bests....)" since it is rather long and not needed. But that explains why there is 4 and not 2 rows in the "good" query EDIT2: Ohhhhh you know what I notice? The long "bids" cell contains ALL the bids and is only returning the first title. More updated question: So how do I make sure that the bids are separate for each title? cannibustacap fucked around with this message at 07:40 on Mar 10, 2009 |
# ? Mar 10, 2009 07:31 |
|
code:
|
# ? Mar 10, 2009 11:47 |
|
Jethro posted:
Sweet it worked! You guys are fuckin rad! One thing I had to do was add the "bc_Bests.ranked_rank = 1" line outside the subquery also or it repeated data. Sweeeeeeeeeeeeeeet! cannibustacap fucked around with this message at 21:27 on Mar 10, 2009 |
# ? Mar 10, 2009 21:23 |
|
I'm working on a project for a small company that keeps their data in an mssql database. They've provided me with a .bak backup copy of their database to use locally on my test machine. However, in order to restore the file to my SQL Server 2005 Express install, I need to specify the username and password they gave me. How exactly can I do that? The script I'm using is: code:
code:
|
# ? Mar 10, 2009 22:04 |
|
If you're running the script in SSMS, then you enter the username and password when you create the connection to the server.
|
# ? Mar 10, 2009 22:16 |
|
I am using SSMS. It won't let me enter that specific login and pass when I connect to the server. The server I'm using is my localhost, though. Do IU need to create a dummy user or something? How do I do that?
|
# ? Mar 10, 2009 22:20 |
|
It looks like the problem is SQL Server can't open the backup file. Set the permissions for YourComputerName\NetworkService or Everyone to allow read or full access for that .bak file.
|
# ? Mar 10, 2009 22:27 |
|
I'm not sure what exactly you mean. Are you talking about windows permissions? The file does have full read and write access in windows, as far as I can tell.
|
# ? Mar 10, 2009 22:39 |
|
Apparently it doesn't. Give SYSTEM full permissions to the file, or Network Service, depending. Neither are covered by "Users".
|
# ? Mar 10, 2009 22:45 |
|
Turns out you were wrong, but you were right. It was trying to tell me that I didn't have permissions to the place where the DB was being restored TO. A win7 Quirk I'm sure.
|
# ? Mar 10, 2009 22:50 |
|
Ah, that makes sense since you don't appear to be using the default SQL data folder (which is fine). I just assumed you were restoring to a folder SQL had access to.
|
# ? Mar 10, 2009 22:54 |
|
I'm having problems showing records based on a character not being present. (mysql) Example:code:
SELECT Company,Email from table where Email like '%@%' Now, the tough part is getting the list of the omitted records. Is there a way to select contents of an entire table and subtract a search that I do know how to perform? Some records will be NULL, and others might just be a space, while others are just words that wouldn't have an @ sign. Any clues?
|
# ? Mar 11, 2009 00:10 |
|
What's wrong with:code:
|
# ? Mar 11, 2009 00:14 |
|
If MySQL does ANSI nulls, then that won't return rows where `email` is null.
|
# ? Mar 11, 2009 01:09 |
|
Victor posted:If MySQL does ANSI nulls, then that won't return rows where `email` is null. Good catch -- I never knew that. Easy enough to add a test for at least.
|
# ? Mar 11, 2009 01:53 |
|
Edit: Actually found the answer to my question, this link has a nice writeup on how to handle external procedures in transactions. Quick question about transactions and stored procedures, say I have a transaction like so (SQL 2005): code:
Just-In-Timeberlake fucked around with this message at 21:52 on Mar 11, 2009 |
# ? Mar 11, 2009 21:35 |
|
Nevett posted:Hey, I need a bit of help with my dumb database project. seconding the urgh about the table design, but have you tried something like this: select movie from movietagvalues where movie in (select movie from movietagvalues where tag = 'year' and tagvalue = '2008') and tag = 'genre' and tagvalue='action' group by movie i'm not great at writing sql without testing it though, so that might not work at all
|
# ? Mar 11, 2009 23:51 |
|
golgo13sf posted:Now, in the stored procedure EXTERN_PROC(), do any errors "bubble up" to the calling stored procedure and rollback the transaction, or do I have to implement a TRY/CATCH block in EXTERN_PROC() and have a RAISERROR() in EXTERN_PROC()?
|
# ? Mar 12, 2009 11:57 |
|
Victor posted:Try/Catch helps some, but I honestly do not know if the transaction will be rolled back if your CATCH block is hit. I advise you to test this stuff. That's how I figure out a lot of things... edit: Unless you rollback the tran in there. Here's a safer pattern: code:
SLOSifl fucked around with this message at 16:45 on Mar 12, 2009 |
# ? Mar 12, 2009 16:40 |
|
I need to do a find and replace on XML data stored as a image column in a vendor supplied table in a SQL Server 2005 database. This appears to be the old SQL Server 2000 method of storing XML data, but I'm having problems finding information about it. This appears to be close to what I need: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6014
|
# ? Mar 12, 2009 17:39 |
|
Victor posted:I'm actually not entirely sure. I've always had the feeling that the error handling/transaction rollback semantics of TSQL were ugly and different depending on who raised the error (whether it was the server or user code). Try/Catch helps some, but I honestly do not know if the transaction will be rolled back if your CATCH block is hit. I advise you to test this stuff. That's how I figure out a lot of things... If I'm understanding the info in the link I posted, you have to put a RAISERROR() in the CATCH for the calling procedure to know that an error occurred. Basically (again from what I got from the article, haven't fully tested it yet) your external procedure might be called by itself instead of from another procedure, and conceivably might need to be transactional as well. So you have the external procedure check to see if @@TRANCOUNT > 0, if it is, then the external procedure has no business managing the transaction, just raise an error and let the calling procedure manage the transaction. If @@TRANCOUNT = 0, then start a transaction, since the external procedure was called by itself. Again, haven't tested the code yet.
|
# ? Mar 13, 2009 01:43 |
|
I have a database which is fairly bad in terms of organization and need to clean it up. It basically contains information about Puerto Rico restaurants. As of right now all the information is kept in a single table, so there's a ton of data duplication. I propose the following example, a restaurant called "La Ensalada" which has 6 locations appears 12 times on our restaurant table. 6 for each location and then 2 for each translation. There's some information that pertains to the restaurant chain itself (the kind of food it serves and the price, for example). Most of the information is part of the location, and some of this info needs to be translated since our site is bilingual. My idea is splitting it into 3 tables: Restaurant RestaurantLocation RestaurantLocationTranslation The issue comes because I have some fields that need to be both in the Location and each LocationTranslation (think of it as a default and then a translated override) like the street address (I need a default in order to do geocoding). Leading to some, but not much, data duplication. Keeping in mind that some of the translated columns need to be full text indexable, but not the ones that are duplicated (ie. Description doesn't need a default in RestaurantLocation, needs to be full-text but street address doesn't need full-text but needs a default). The question is, am I doing this right? Or should I maybe drop the RestaurantLocationTranslation table and instead use XML or something. This is in SQL Server 2005 (could switch to 2008).
|
# ? Mar 16, 2009 16:38 |
|
deimos posted:The question is, am I doing this right? I would go one step farther and make a "Language" table. (assuming geocode always works for English..) LanguageID, LanguageText, GeoCode_bit Restaurant can have universal stuff like the phone number, an id, the manager's name, relative price ($$$/$$$$). Restaurant_Location_Language would then have RestaurantID, LanguageID, Address, Description, MenuXML That would clear out your duplication and you could give users a drop down pretty easily for each restaurant to see available languages.
|
# ? Mar 16, 2009 18:28 |
|
"INSERT INTO allPosts (postURL) SELECT postURL FROM shortURLS" trying to add postURL fields from the shortURLS table to the postURL fields in the allPosts table postURL is a unique field in the allPosts table I'm getting a: "Duplicate entry 'http://www.exampleurl.com' for key 2" any ideas?
|
# ? Mar 17, 2009 00:01 |
|
Are you trying to update existing rows or is allPosts empty? If you're just dumping a single column from a table into allPosts (which is empty), then you need DISTINCT after the SELECT.
|
# ? Mar 17, 2009 03:31 |
|
I'm trying to write a trigger (on SQL Server 2005) that should be stupidly simple, but I've never written a trigger before and can't figure it out. What I want to do, is every time an INSERT or UPDATE statement is executed on a particular table, is to insert a copy of the row(s) affected into another table. Thing is, reading through MSDN I can't figure out any way to reference the affected rows or the statement that fired the trigger.
|
# ? Mar 17, 2009 16:09 |
|
INSERT INTO WhateverTable ( Column1, Column2, Column3 ) SELECT [Column1], [Column2], [Column3] FROM INSERTED
|
# ? Mar 17, 2009 16:18 |
|
There's no UPDATED table, though. And will that work with a batch operation?
|
# ? Mar 17, 2009 16:21 |
|
No, there's no UPDATED pseudo-table. It's always called INSERTED. Yes, it'll work on a batch operation.
|
# ? Mar 17, 2009 16:24 |
|
Thanks, I didn't realize updates also used the INSERTED table.
|
# ? Mar 17, 2009 16:35 |
|
I am using sql server 2008 and storing the datetime in one of my tables using the GETUTCDATE() function. Is there anyway to pull the time out of the database so that is not a literal string value but instead an integer value, like the number of seconds from the epoch, etc? After looking at all the date time functions on MSDN, I don't see a way to do this, but it seems like it would be something a lot of people would use.
|
# ? Mar 18, 2009 19:33 |
|
Subotai posted:I am using sql server 2008 and storing the datetime in one of my tables using the GETUTCDATE() function. Is there anyway to pull the time out of the database so that is not a literal string value but instead an integer value, like the number of seconds from the epoch, etc? After looking at all the date time functions on MSDN, I don't see a way to do this, but it seems like it would be something a lot of people would use. You might need to compute it. As far as I know that is your only option in Oracle. code:
|
# ? Mar 18, 2009 19:52 |
|
|
# ? Jun 8, 2024 07:29 |
|
Subotai posted:I am using sql server 2008 and storing the datetime in one of my tables using the GETUTCDATE() function. Is there anyway to pull the time out of the database so that is not a literal string value but instead an integer value, like the number of seconds from the epoch, etc? After looking at all the date time functions on MSDN, I don't see a way to do this, but it seems like it would be something a lot of people would use. You can just use the datediff function: code:
code:
|
# ? Mar 18, 2009 20:43 |