Tag Info

Hot answers tagged

5

Sure : select * from database_n.dbo.Table_1 UNION ALL select * from database_n+1.dbo.Table_1 UNION ALL select * from database_n+2.dbo.Table_1 -- etc.


3

You can convert your string to XML and use a cast as xs:int ? to check if the value is an integer or not. declare @T table(C varchar(100)); insert into @T(C) values ('Widget (1234)'), ('Different Widget (123)'), ('Many Widgets (2x) (4567)'); select T.C, T2.X.value('(for $n in X return $n/text()[1] cast as xs:int ?)[1]', 'int') as Value from @T as T ...


3

If the data you need is always at the end of the string, would something like this work? declare @test varchar(200); set @test= 'Many Widgets (2x) (4567)'; select substring(@test, (len(@test) - charindex('(',reverse(@test))) + 2, charindex('(',reverse(@test)) - 2 ); We can reverse the string, find the ...


2

Would this cause any impact on application side? Even if you run BPA or Upgrade Advisor, they are not going to catch everything that would potentially break your application (if it is using deprecated stuff). The best way is to take a full backup of your database in question, restore it on a test machine with newer compatiblity mode and do a regression ...


2

A cleaner way uses EXCEPT: SELECT a.fld FROM tbl1 a EXCEPT SELECT b.fld FROM tbl2 b Using the left join method: Select distinct a.fld from tbl1 a left join tbl2 b on a.fld= b.fld where b.fld IS NULL You're currently looking for records where a.fld= b.fld is true AND a.fld<> b.fld is true which, of course, is impossible.


2

Instead of using a JOIN on your table how about using NOT EXISTS: SELECT DISTINCT a.fld FROM tbl1 a WHERE NOT EXISTS (SELECT 1 FROM tbl2 b WHERE a.fld = b.fld); This will return all rows in tbl1 where the fld value doesn't exist in tbl2


2

Assuming "Table 1" is tbl1a, this should give you what you want: SELECT DISTINCT a.fld FROM tbl1 a LEFT JOIN tbl2 b ON a.fld = b.fld WHERE b.fld IS NULL


1

With this solution you can easily know which jobs are on the queue to run with a simple select to a table. Also, if the server goes down for some reason, cleaning the jobs that are waiting to run is as easy as a truncate on a table. And still if you want them to run you needen't do anything and they'll run orderly. Create a table (Say, ...


1

In more research and diving into the msdn site documentation I have found and am now using. sp_add_schedule, sp_attach_schedule and sp_detach_schedule, sp_delete_schedule so when the job sees that others are running ATM I am doing this: exec msdb.dbo.sp_add_schedule [MSDN link][1] @schedule_name = @TempScheduleName, @enabled = 1, @freq_interval = ...


1

Yep, indeed you can! So you are hoping to have one job complete and the last step of that job would be to kick off another job? Here is the command to kick off the next job: sp_start_job E.g. The first job has a schedule and final step of the job is to run: exec msdb.dbo.sp_start_job N'My Second Job'; "My Second Job" doesn't have a schedule, relying on ...



Only top voted, non community-wiki answers of a minimum length are eligible