;WITH MyCTE([Bf_ORGN_CD],[LEV5],[BF_ACTY_CD])

AS

(

SELECT 'AC_21234_2','AC_21200_1','402' UNION ALL

SELECT 'AC_21236_2','AC_21200_1','402' UNION ALL

SELECT 'AC_21238_2','AC_21200_1','402' UNION ALL

SELECT 'AC_29000_1','AC_29000_1','802' UNION ALL

SELECT 'AC_29988_1','AC_29988_1','801' UNION ALL

SELECT 'AC_40040_1','AC_40040_1','201' UNION ALL

SELECT 'AC_41061_1','CA_41061_1','207' UNION ALL

SELECT 'AC_41080_1','AC_41080_1','207' UNION ALL

SELECT 'AC_41196_1','AC_41196_1','207' UNION ALL

SELECT 'AC_42404_1','AC_42404_1','801'...

the convert function has an optional parameter when you are converting dates;

112 will give you the eight digit format you are looking for:

SELECT CONVERT(VARCHAR,getdate(),112) --20150803

SELECT Sub_date = CONVERT(VARCHAR,annual_date,112)

coolchaitu (8/2/2015)

---

Lowell Sir,

If these info are not found any books, how does the person who answers the question gets to know such things? Just curious to know. Please share the...

this is usually because the user was once assigned to have a default database that has been renamed or deleted. it's possible that the database exists, but permissions prevent the...

sqlguy-736318 (7/28/2015)

---

My question is more specific: For a given result set in a temp table, will "select top 1" and "delete top 1" operate on the same row in...

SQL does not have anything native to send anything to file.

something external like bcp or sqlcmd, a Common Language Runtime Procedure, Powershell or a proper programming language have the ability...

yeah, i know....

it has a purpose, really!

it comes from a big ol data warehouse db that gets copied once a month.; a previous job step deleted a ton of data,...

homebrew i do something very similar as far as restoring an existing database.

i have a SQL job with the steps to handle the takeover and restore as separate steps; this...

bryan.holmstrom (7/27/2015)

---

Thank you for the quick reply but I need the actual tables not the system tables.

Database = 'xxxCRM'

Example table = accountbase (field = 'modifiedon' or even 'createdon' for each...

i would use the table and column metadata to generate the desired queries,a nd run them individually.

select

'SELECT TOP 10 * FROM '

+ quotename(object_name(object_id))

...

does filtering on the is_ms_shipped column work for you? it sems to remove things like MSreplication_options and spt_values and stuff for me.

select *

from sys.objects

where is_ms_shipped = 0

that is normal, expected behavior.

by design, you can grant a person access to a stored procedure which may affect objects the enduser does not have access to. .

using ownership chaining,The...

Adam McArdle (7/27/2015)

---

We have our packages password-protected because they have credentials in them, and Project...

i've put together this cursor in the past that iterates through all windows groups in SQL, and enumerates their members.

finally, i account names that were found for admin vs user...

quite a few issues there.

when you call sp_send_dbmail, you cannot append stuff togetierh for the paramters, you have to do that in advance, it has to be a single assignment,...