May 17, 2013 at 7:50 am
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.
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'select'.
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 advance
Taggs
Insert 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.)*8/1024)
FROM sys.master_files
where sys.databases.database_id = sys.master_files.database_id and type=0),
(SELECT (SUM(sys.master_files.)*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
May 17, 2013 at 7:52 am
You don't need the values keyword when using a select as the source for your insert.
Insert 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.)*8/1024)
FROM sys.master_files
where sys.databases.database_id = sys.master_files.database_id and type=0),
(SELECT (SUM(sys.master_files.)*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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 17, 2013 at 7:59 am
Many thanks, You are a star.
I can't believe it was that easy a fix.
Taggs
May 17, 2013 at 8:49 am
You are welcome. When you don't do this stuff all the time it is the easy things like that which can drive you nuts. 😉
Glad you were able to get it figured out.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 17, 2013 at 11:29 am
Sean Lange (5/17/2013)
When you don't do this stuff all the time it is the easy things like that which can drive you nuts. 😉
Indeed, I'm in my SQL Server just about every day, and sometimes I am still flummoxed by the simple things I overlook.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
May 20, 2013 at 7:32 am
Greg Snidow (5/17/2013)
Sean Lange (5/17/2013)
When you don't do this stuff all the time it is the easy things like that which can drive you nuts. 😉Indeed, I'm in my SQL Server just about every day, and sometimes I am still flummoxed by the simple things I overlook.
Me too. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2013 at 7:48 am
Thanks Guys
I think your right.... sometimes it just takes a fresh pair of eyes to spot what is staring you in the face
:w00t:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply