Transform SELECT INTO HTML / XML / SQL SERVER / td=

Hi!

I have to say this is a little bit too hermetic for me, but it works.

This is an example of how to transform a SELECT into  HTML format.

Notice that it shows how to add color to each cell (td).

This example is priceless.

If you have any doubt, leave a comment.

Good luck!

SET @html = 

cast((

select

      td=NICK,'',

      td=REPLACE(RTRIM(CONVERT(CHAR(15) , CAST(unit AS MONEY) ,1)),'.00',''), '',

      td=RTRIM(CONVERT(CHAR(15) , CAST(sale AS MONEY) ,1)),'',

      td=REPLACE(RTRIM(CONVERT(CHAR(15) , CAST(TICKETS AS MONEY) ,1)),'.00',''), '',

      td=REPLACE(RTRIM(CONVERT(CHAR(15) , CAST(FLUXO AS MONEY) ,1)),'.00',''), '',

      td=PA, '',

      td=TM, '',

      td=CONVER, '',

      cast(case when GOAL_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=GOAL_P, '',

      cast(case when UNIT_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,0),UNIT_LY_DAY_P), '',

      cast(case when SALE_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,0),SALE_LY_DAY_P ),'',

      cast(case when TICKETS_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,0),TICKETS_LY_DAY_P), '',

      cast(case when FLUXO_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,0),FLUXO_LY_DAY_P), '', 

      cast(case when PA_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,0),PA_LY_DAY_P), '', 

      cast(case when TM_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,0),TM_LY_DAY_P), '', 

      cast(case when CONVER_LY_DAY_P < 0 then 'red' else 'blue' end as nvarchar(30)) as 'td/@bgcolor',

      td=CONVERT(NUMERIC(11,1),CONVER_LY_DAY_P),''

FROM [table]

WHERE field LIKE '??%'

ORDER BY convert(numeric(11,1),GOAL_P) DESC

FOR XML path('tr'),type) as nvarchar(max))

+'</table>'

A different way to create temporary table

Hi!

Today somebody teached me a new way to create temporary table and I found it really cool.

This is a way to create a temporary table cloning only the definition of the original table.

SELECT * INTO #TEMP_CLIENT FROM  CLIENT  WHERE 0 = 1

Isn't it cool?

Good luck!
 

You should use sqlcmd.exe using dos prompting to execute large sql files (GREATER THEN 1gb)

Whenever I have to execute a huge file, I use sqlcmd.exe via dos.

My strategy is

STEP 1

Create a specific user for that - something like USR_TOUGH_GUY

OWNED SCHEMAS
db_accessadmin

ROLE MEMBERS
db_accessadmin

STEP 2

Find the executable sqlcmd.exe in the machine where SQL SERVER is installed.

STEP 3

Execute
sqlcmd -S myServer\instanceName -U USR_TOUGH_GUY -P THE_PASSWORD_OF_MR_TOUGH_GUY  -i  C:\yourscript.sql


STEP 4

Disable mr TOUGH_GUY

Final considerations

You could do this using the SQLStudio but it will consume a lot of memory of the machine, if you are, for instance, trying to execute a 4GB file.

Good luck!

SRINK LOG TRANSACTION FILE OF YOUR DATABASE

USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO

I used the script above to create a JOB. It worked perfectly.

Há!

Microsoft SQL Server Error 18456 Login Failed for User

AFTER THE MANEUVER DON'T FORGET TO RESTART THE SERVER!!!!!!!!!!!!!!!!!!