If your host doesn’t give you permission to create or run procedures, I think I found another way to do this using PREPARE/EXECUTE and querying the schema: SET @s = (SELECT IF( (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name=”table_name” AND table_schema = DATABASE() AND column_name=”col_name” ) > 0, “SELECT 1”, “ALTER TABLE table_name ADD col_name VARCHAR(100)” … Read more
At first check out that your linked server is in the list by this query select name from sys.servers If it not exists then try to add to the linked server EXEC sp_addlinkedserver @server=”SERVER_NAME” –or may be server ip address After that login to that linked server by EXEC sp_addlinkedsrvlogin ‘SERVER_NAME’ ,’false’ ,NULL ,’USER_NAME’ ,’PASSWORD’ … Read more
Well, in SQL Server 2012 you could use the new TRY_CAST(), but with SQL Server 2008, you should be able to use ISNUMERIC(), and then include handling for values that do not pass that test.
The error ERROR: missing FROM-clause entry for table “agency” …should hint that somewhere in your query you have mistakenly used agency as a table name, without pluralizing it. But where exactly did you do that? The only difference between working and non-working snippets of yours are these bits: joins(:agency). where(agency: {state: ‘active’}). …a-and both lines … Read more
Something like this should work: select proname, prosrc from pg_proc where prosrc like ‘%search text%’; see How to display the function, procedure, triggers source code in postgresql?
The only way (I’m fairly sure) to do this is using a CASE .. WHEN … construct: SELECT *, CASE type WHEN 1 THEN ‘abc’ WHEN 2 THEN ‘xyz’ END as stringType FROM orders You can also use the ELSE clause to specify a default, e.g. CASE type WHEN 1 THEN ‘abc’ WHEN 2 THEN … Read more
You could have two near-identical ORDER BY items, one ASC and one DESC, and extend your CASE statement to make one or other of them always equal a single value: ORDER BY CASE WHEN @OrderDirection = 0 THEN 1 ELSE CASE WHEN @OrderByColumn = ‘AddedDate’ THEN CONVERT(varchar(50), AddedDate) WHEN @OrderByColumn = ‘Visible’ THEN CONVERT(varchar(2), Visible) … Read more
A couple of the other answers touched on the problem, but this might explain. There really isn’t an order implied in SQL (set theory). So to refer to the “fifth row” requires you to introduce the concept Select * From ( Select Row_Number() Over (Order By SomeField) As RowNum , * From TheTable ) t2 … Read more