Search results for query: *

Ignore the comment, it is not meant to be there. I got around the problem by accessing the tables in question directly and modifying the select query slightly. The reason I am doing all of this is because I want to write a stored procedure that will list all of the stored procedures within a...

Hi Joulius, Information_Schema.routines is a view (of the syscomments table). There are 150 rows in the syscomments table. There will not be a permissions issue. Tempdb has plenty of available space and is set to autogrow. I think this is a problem with the row size returned...

Afternoon, I get the following error when executing the code below. Server: Msg 510, Level 16, State 2, Line 9 Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint. declare @routine_catalog varchar(255) declare @routine_name...

Evening, Once you detach the database, simply move the physical .mdf file to the new location/hard drive. Then re-attach the database from its' new location. There may be a few more steps involved/files to move if your DB has not been kept in simple recovery model however. cheers, Thegeezza

Druer, A query is sufficient in this scenario thanks. FYI, the two data sets are static and are part of a data processing project. So one file is the universe and the other is a client data set. The data is being loosley matched at different levels to approximate project results. The reason...

Druer, you are correct. hmckillop the output for table A would update to: TableA Add1 postcode match_counter 27 Pettens Close AB23 8WZ 3 27 Pettens Close AB23 8WZ 3 Once more, everyones help is most appreciated.

Data should look like the below tables. Table A being the left table. TableA Add1 postcode match_counter 27 Pettens Close AB23 8WZ 3 61 Malden Hill KT3 4D2 2 57 Melrose W4 2PD 1 TableB Add1...

Would that not then apply the same count to every record in the left/parent table? There are other records, both on the left, with corresponding groups of matches on the right, in the full data sets.

Sure Thing. TableA Add1 postcode match_counter 27 Pettens Close AB23 8WZ TableB Add1 postcode 27 Pettens Close AB23 8WZ 27 Pettens Close AB23 8WZ 27 Pettens Close AB23 8WZ So match counter would be three for the correct query output/udpate value

Oops. Actually on my second look this, the code simply creates an incremental counter and applies this to the file. What I am looking to do is create a count for the number of times a record on the left is joined to the right table. So essentially a counter for the one to many relationship...

Yes that is exactly what I am looking for thank you. Are you able to explain the logic behind this part? set @vTempInt = @vTempInt + 1 ,match_counter = match_counter + @vTempInt For example, why is match_counter only updated once in my code, yet when you used a variable, the...

Afternoon, I want to perform an update to a record on the left hand side of my join, for the number of times the join condition is true. For exmaple: update A set match_counter = match_counter + 1 from TABLEA A left join TABLEB B on A.postcode = B.postcode However, the code above...

Ok, thank you.

Morning, If you have a query that you know update every row in a table, is there a significant performance benifit in aquireing an exclusive table lock within the query (as opposed to SQL Server escalating various lower level locks)? Hope this makes sense. Cheers, TheGeezza

Morning, Remeber, it is good practice to make use of the quotename() function to wrap your variables passed as parameters to avoid SQL injection to your code. Regards, TheGeezza

Morning, An approach I like to use that you may find useful is a concept of wrapping functionailty into tasks. I wrap blocks of SQL code into tasks, with each task defined in a task table. Depending on whether or not a task is sucessfull its' status is updated accoringly. There is also a job...

Afternoon, Thanks for your reply. I think the variable route is the best method as I am guessing it will be the most efficient. Many Thanks, TheGeezza

Afternoon, Essentially I want to increment an error counter each time an error is encountered executing a stored procedure. So for example..... exec @custom_error = sp_executesql @sql ..... will catch the return status from the procedure. What I would like to do is this following which of...

Hi, Have you considered using your stored procedure to create/calculate the data you require and add it as a record to a table? You could then simply create a view on this table. Cheers, Thegeezza

Excellent work r937, that's the ticket! Thank you all for your replies!!! cheers, Geez