[T-SQL] Insert statment |
- Insert statment
- Order by SP_SEND_MAIL
- help to resolved: Login security to asign to perticular user
- Database backup striping
- OUTPUT CLAUSE - MERGE STATEMENT
| Posted: 17 May 2013 12:50 AM PDT Hi,I don't spend enough time SQL coding so I am wondering of someone can help me with my statement please, as I am struggling a little.I'm getting the following error from the statement below.[i][b]Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'select'.[/b][/i]If you run the select on its own it works fine. When you add the Insert on is when it starts to fail.Thanks in advanceTaggsInsert into SQL_ENVIRONMENT.dbo.DatabaseSizing (instancename,databasename,db_datafile,db_logfile,db_datasize,dateofsize)values select convert(nvarchar(50),SERVERPROPERTY ('Servername')), convert(nvarchar(50),sys.databases.name), (SELECT (SUM(sys.master_files.[size])*8/1024) FROM sys.master_files where sys.databases.database_id = sys.master_files.database_id and type=0), (SELECT (SUM(sys.master_files.[size])*8/1024) FROM sys.master_files where sys.databases.database_id = sys.master_files.database_id and type=1), -- (convert(float,fileproperty(sysfiles.name,'SpaceUsed'))) * (8192.0/1048576) MB_Used, Null, getdate() FROM sys.databases left join sysfiles on sys.databases.name = sysfiles.name WHERE sys.databases.database_id>4 ORDER BY sys.databases.name |
| Posted: 16 May 2013 09:10 PM PDT MorningI am attempting to send a query result via DB mail and where as it is working fine, I want to order the file by a particular column. When I add the order by clause in I get the below errorMsg 22050, Level 16, State 1, Line 0Error formatting query, probably invalid parametersMsg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504Query execution failed: Msg 207, Level 16, State 1, Server PROMETHEUS, Line 33Invalid column name 'trantype'.Msg 104, Level 16, State 1, Server PROMETHEUS, Line 33ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.My Query is as follows:EXEC msdb.dbo.sp_send_dbmail@recipients=N'',--@copy_recipients=N'',@subject='',@body='', @profile_name ='standard',@query ='SET NOCOUNT ON; select ''[Polsdate]'' ,''[Affinity]'' ,''[Title]'' ,''[Forename]'' ,''[Surname]'' ,''[Address1]'' ,''[Address2]'' ,''[Address3]'' ,''[Address4]'' ,''[Postcode]'' ,''[DOB]'' ,''[Cover]'' ,''[Reg]'' ,''[Cover Description]'' ,''[trantype]'' union all select top 100 cast([Polsdate] as varchar) ,cast([Affinity] as varchar) ,cast([Title] as varchar) ,cast([Forename] as varchar) ,cast([Surname] as varchar) ,cast([Address1] as varchar) ,cast([Address2] as varchar) ,cast([Address3] as varchar) ,cast([Address4] as varchar) ,cast([Postcode] as varchar) ,cast([DOB] as varchar) ,cast([Cover] as varchar) ,cast([Reg] as varchar) ,cast([Cover Description] as varchar) ,cast([Trantype] as varchar) from db where insertdate = CONVERT(VARCHAR(10), GETDATE(), 105) [b]order by [trantype][/b]' , @attach_query_result_as_file = 1,@query_attachment_filename ='TEST.CSV',@query_result_header=0,@query_result_separator=' ',@query_result_no_padding=1 |
| help to resolved: Login security to asign to perticular user Posted: 16 May 2013 06:10 PM PDT Hi !!!I have a table like team listTBL_Teamlist:userid superid101a AA1102b AA1201c AA2Hear i need to asign to the perticular user is having a rights like 1.READ 2. write 3. read & write in front end application:this is like providing a security if anybody having the idea on this pls help me ie appriciated :-): |
| Posted: 16 May 2013 03:01 AM PDT Hello Pros,I manage 100 + SQL servers and wanted to find a way to backup all databases to multiple .bak files (striping) regardless of the DB size. Below is the closes one I got on google. can anyone shed some light\share script that can accomplish this. Thanks in advance for your help.DECLARE @sql VARCHAR(max);SELECT @sql = COALESCE(@sql,'')+ 'BACKUP DATABASE '+db.name+' '+CHAR(13)+ 'TO DISK = ''c:\'+db.name+'_1.bak'','+CHAR(13)+ 'DISK = ''c:\'+db.name+'_2.bak'','+CHAR(13)+ 'DISK = ''c:\'+db.name+'_3.bak'','+CHAR(13)+ 'DISK = ''c:\'+db.name+'_5.bak''with INIT, compression'+CHAR(13)+CHAR(13)FROM sys.databases dbWHERE name NOT IN ('tempdb','pubs','AdventureWorks','AdventureWorksDW')exec(@sql)---PRINT(@sql) |
| OUTPUT CLAUSE - MERGE STATEMENT Posted: 07 Apr 2012 01:10 AM PDT Does anyone have a basic example of the use of the OUTPUT CLAUSE in a MERGE Statement?I found examples with INSERT & DELETE nut no UPDATE? |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment