executing sql file

I want to know,

is there any method in SQL Server using "SQL Server Management Studio" to execute the .sql file? (Using query)

I know about osql & isql Utilities & i try this also as

EXEC xp_cmdshell 'osql -S vsnet1 -U sa -P sysadm -d aaa -i c:\ACCOUNTS.sql'

its working fine but it uses the dos command shell.

i too try the stored procedures (of others peson`s) like

sp_ExecuteSQLFromFile (i dont want this as it having some limits)

Is there any direct way to execute a .sql file? (as in case of Oracle RUN, START, @. )

Hope for help

Regards,

thanks.

Gurpreet S. Gill

I don't think Management studio provides any other way of executing the .sql file.

The most common way is to use sqlcmd from cmd prompt...

type in

sqlcmd /? from command prompt for more help

|||

Imtiaz--

I cant use the DOS prompt. i know about these commands like sqlcmd, isql, osq

but i want from SQL Server Managment

Regards,

Thanks.

Gurpreet S. Gill

|||

This sounds like it might help you out... you can enable "SQLCMD mode" in SQL Server Management Studio.

Steps:

1) Open SQL Server Management Studio.
2) Open a query window
3) Click the Query menu
4) Click SQLCMD Mode.

Kimberly Tripp does some great demos with SQLCMD. Not sure if it's on a webcast you can watch OnDemand though. Here are some BOL articles you can read.

Editing SQLCMD Scripts with Query Editor
http://msdn2.microsoft.com/en-us/library/ms174187.aspx

SQLCMD Mode
http://msdn2.microsoft.com/en-us/library/ms170411.aspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Paul--

Thanks

that`s really gr8.

This is what i want.

Regards,

Thanks.

Gurpreet S. Gill

executing OPENQUERY

Hi,

I'm trying to execute an openquery statement from SQL Server 2005 against a linked server AS2005 (both on same machine) from Management Studio.

If I use a one connection with full rights I have no problem. But if I use the specific connection for that application I got this error:

OLE DB provider "MSOLAP.3" for linked server "ASLOCAL2" returned message "The following system error occurred: A specified logon session does not exist. It may already have been terminated. .".

Msg 7303, Level 16, State 1, Line 5

Cannot initialize the data source object of OLE DB provider "MSOLAP.3" for linked server "ASLOCAL2".

"Ad hoc data-mining query" is checked.

MSOLAP Provider has "Allow inprocess" checked.

Code Snippet

SELECT * FROM OPENQUERY(ASLOCAL2,'SELECT non empty {[Measures].[Prices Avg] ,[Measures].[Prices Max] ,[Measures].[Prices Min] } ON COLUMNS,non empty [Tbl DW Dim Type].[Name].&[1] ON ROWS FROM [DW DEV] ')

Any ideas ?

Thnx.

Have you tried running a profiler trace against SSAS while trying to run the OPENQUERY() ?

I am wondering if it is a permissions issue for the account that the application is using for the connection.

|||The only difference between these tow accounts is that one account is Windows account and the other one is Sql server account.|||

Well that is probably your issue. SSAS only supports windows authentication. You would need to have the linked server setup to authenticate using the current login's security context, which works fine for windows accounts, but if you use a sql account, SQL Server will fall back to authenticating against the SSAS server using the account that the SQL Server is running under, which probably does not have permissions to query the cubes.

So your choices are to either only use windows accounts against the linked server or to make sure that the account that SQL Server is running under has access to the appropriate objects in SSAS.

|||

You're right.

I changed the way that the (web)application is connecting to the Sql server (until now I used sql account) and now I am using an windows account.

Execute Stored Procedure menu not appearing

Hi,

I have created an SQL Server instance in SQL Server Management Studio. I have a few databases, and stored procedures in them. When I right click on the stored procedure, I have the menu for "New Stored Procedure, Modify, Script Procedure as, and so on". But, I could not see the "Execute Stored Procedure" item.

Could any one help to find out what could be the problem and solve it?

Thanks,
Luke.Stored procedures in 2000 must be executed from a utility such as Query Analyzer.
I think the 2005 management interface allows you to execute them directly and submit parameters, but I'd have to check to be sure.
Regardless, it is not good to be doing these types of activities in the GUI. Use QA instead.

Execute SQL greyed out in Query Designer?

If I right click in a query pane and select Query Designer in SQL management Studio, or select an existing query and do the same I get the query designer and can monkey about with the SQL add tables etc...

But I can't Execute the SQL or see the results. These options are greyed out. This is a pain as I keep having to select the SQL and paste it back into the query window, easy enough but when it takes a coulpe of minutes to untangle the tables as they are laid out in the visual editor this is just a very painful way of debugging the bad joins and stuff my web team keep putting in.

Is there any way of getting a visual query designer I can actively work in?

many thanks

Steve

I've had exactly the same problem and after a bit of research came across this:

http://www.developermania.com/newsgroups/item/130965/Query_Designer.aspx

Its basically a reply to a posting last year from Keith Wilson at microsoft that seems to indicate that this is functionality they didn't implement in the original release and that was beyond the service pack. I get the impression therefore that the menu selections are simply acting as placeholders for functionality that has not been built yet?!!

There's a few posts out there concerning this issue but I've not come across a solution. Let me know if you have better luck!

Regards

Rick Edwards

|||

The functionality is there to execute the query, but not available in the mode the designer is running in when you design a query in the T-SQL script editor. The general idea was that you would run the query in the editor itself, not in the dialog hosting the designer.

The "open table"/"open view" functionality is really the query designer as well, but its running in a more capable mode. When you right click on a table or view in object explorer and select the "Open..." menu item, the query designer will start with "select * from {my table}" as the SQL text, but with only the results pane being shown. You can use this as a trick to get to a more functional version of the query designer. Create an empty table (so there are no results to fetch), then open it. Once you have the table open, you can show the SQL, Diagram, or Query By Example panes by clicking the appropriate button in the toolbar, delete the existing text and do what you need to do.

I know this is a pretty baroque way to get to the query designer. If you find yourself using this functionality often, you might consider going to the http://connect.microsoft.com/sqlserver site and suggesting that we provide a more direct way to get to the more fully-functional version of the query designer. We use customer feedback like this when we are considering which features to implement in future versions.

Hope this helps,
Steve

|||

OK its a bit of an obscure way of getting there but I find that pretty acceptable. The greyed out option confused the hell out of me though.

Many thanks for taking the time to answer this one. The trick of getting at the full query designer is going to save me a huge amount of time.

Execute SQL greyed out in Query Designer?

If I right click in a query pane and select Query Designer in SQL management Studio, or select an existing query and do the same I get the query designer and can monkey about with the SQL add tables etc...

But I can't Execute the SQL or see the results. These options are greyed out. This is a pain as I keep having to select the SQL and paste it back into the query window, easy enough but when it takes a coulpe of minutes to untangle the tables as they are laid out in the visual editor this is just a very painful way of debugging the bad joins and stuff my web team keep putting in.

Is there any way of getting a visual query designer I can actively work in?

many thanks

Steve

I've had exactly the same problem and after a bit of research came across this:

http://www.developermania.com/newsgroups/item/130965/Query_Designer.aspx

Its basically a reply to a posting last year from Keith Wilson at microsoft that seems to indicate that this is functionality they didn't implement in the original release and that was beyond the service pack. I get the impression therefore that the menu selections are simply acting as placeholders for functionality that has not been built yet?!!

There's a few posts out there concerning this issue but I've not come across a solution. Let me know if you have better luck!

Regards

Rick Edwards

|||

The functionality is there to execute the query, but not available in the mode the designer is running in when you design a query in the T-SQL script editor. The general idea was that you would run the query in the editor itself, not in the dialog hosting the designer.

The "open table"/"open view" functionality is really the query designer as well, but its running in a more capable mode. When you right click on a table or view in object explorer and select the "Open..." menu item, the query designer will start with "select * from {my table}" as the SQL text, but with only the results pane being shown. You can use this as a trick to get to a more functional version of the query designer. Create an empty table (so there are no results to fetch), then open it. Once you have the table open, you can show the SQL, Diagram, or Query By Example panes by clicking the appropriate button in the toolbar, delete the existing text and do what you need to do.

I know this is a pretty baroque way to get to the query designer. If you find yourself using this functionality often, you might consider going to the http://connect.microsoft.com/sqlserver site and suggesting that we provide a more direct way to get to the more fully-functional version of the query designer. We use customer feedback like this when we are considering which features to implement in future versions.

Hope this helps,
Steve

|||

OK its a bit of an obscure way of getting there but I find that pretty acceptable. The greyed out option confused the hell out of me though.

Many thanks for taking the time to answer this one. The trick of getting at the full query designer is going to save me a huge amount of time.

Execute check sql syntax programatically

HI, Is there a way to check sql syntax from a .Net application? Sql server management studio is made in .Net and it has that option. Is teher a way to know how it is implemented in SSMS?

Thank you,
Ccote

Hi Ccote,

SSMS performs some farly rudimentary syntax checks, but not a complete TSQL syntax validation. This is done via TSQL command

SET PARSEONLY ON

This functionality is also exposed in SMO via ParseOnly option:

Code Snippet

Server.ConnectionContext.ExecuteNonQuery(myquery, ExecutionTypes.NoCommands);

|||HI, Thank you for your answer!|||Not being very familiar with VB.Net, how would I caputure the output of the ParseOnly result into a variable?