I have to create database usign OSQL. i am usin a command like this
C:> osql -E -i db1.sql
But it doesnt create database and returns error saying user login
failed for administrator whereas I have no user for SQL server n it
uses by default . I also tried with sa username by entering it with -U
switch in command but still it gaves same error. Can anyone help me
I read that the there's no equivalent command of sqlcmd's -W in osql - we only have to resort to LTRIM(RTRIM(). However, just wondering if anyone has a better solution?
I also would like to create a table from a select statement that has UNION in it. For instance, in Oracle, it would be like:
Code Snippet
create table mytable as
select a,b,c from table1
union
select a,b,c from table2
How do i do this in SQLServer?
Hi
I need to do a restore similar to the Restore Database in SQL Enterprise Manager using OSQL. I created an app to do the OSQL Run script command and it works fine. Is this the best way to create a setup to restore the database? Any ideas please!!
Best Regards
Philip
Dear Sir/Madam,
Would you please help me to create a batch file to restore a database from a file located on a remote path to my MSDE installed on my workstation.
the original database location was on the D drive on the server
but when i want to restore it to my MSDE it will be in a different path which is C
I want to create a command batch file by just double clicking on it, it will restore the database. (i want a forced restoration).
Please proivde me with your help.
Regards,
HotBird
How can I create a new database on sql server using OSQL command incommand prompt. I have the sql file for database creation and I use thefollowing commandC:> osql -S servername -U sa -i db1.sqlit prompt my for password n i enter it since sa has a blank passwordbut after that it returns the following error.1> 2> 3> Msg 170, Level 15, State 1, Server servername, Procedure ,Line 2[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrectsyntax near 'COLLATE'.1> 2> 3> Msg 15010, Level 16, State 1, Server servername, Proceduresp_dboption, Line 95[Microsoft][ODBC SQL Server Driver][SQL Server]The database'db1' does not exist. Use sp_helpdb to show available databasesCan anyone help me on that?
View 3 Replies View RelatedI am using the osql utility to access my sql server express edition.
My problem is that i don't know how many database is in the sql server so can any teach me how to list out all the name of the database in the sql server using a sql statment?
Oh, and can anyone teach me how i can attach and detach a database in the sql server using sql statment.
Thanks for the help...
Can I use osql to known the database install on a server via 'guest'account ?I had over 300 servers with sql server all around France (differentversion : 6.5, 7 and 8)I need to check all database on each servers from my place.Of course, also it would have been to easy, I don't have all 'sa'password...Is that possible to use the 'guest' account to execute a query likethis via osql :Extract.sqlexec sp_helpdbExtract.cmdfor /f "usebackq tokens=1 delims=" %%i IN (ListServer.txt) DOosql-S%%i -Uguest -P -dmaster -i"C:ExtractionExtract.sql"-o"C:Extraction\%%i.rpt"Could I query 'master' with the 'guest' account ?Or any other ideas how to do this by an other way ?Thanks
View 1 Replies View Related
I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
Prontonet
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies View RelatedI have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
View 2 Replies View Related
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.
I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.
Any help would be appreciated. Thanks!
We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?
View 1 Replies View RelatedI am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
View 6 Replies View RelatedHi, My server went dead(problems with the hard disk), but I have a copy of the whole sql server directory including the database in a external hard disk. I have a new server now and I would like to copy this database (with the reports from reporting services too) from the external hard disk to my new server. can anybody help me please? Thanks.
Note : I have a plain copy of all the sql server directory with all the files including the database not a SQLServer backup done with the wizards.
Cn not do anything with my sql server, everything i trt to do i get this message, user does not have permision, etc, ,
I am running windows Vista Business, SQL SERVER 2005
so what going on here
got rid of my error about user login rights, it was all working yesterday. but for some reason i now get this error CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
i have this in my web.config file <connectionStrings> <add name="ConnectionStringTest" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf;Integrated Security=SSPI;Connect Timeout=30;User Instance=False" providerName="System.Data.SqlClient" /> </connectionStrings> <appSettings /> <system.web> <!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. --> <compilation debug="true" /> <identity impersonate="true"/> and my asp connection string is <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>" SelectCommand="SELECT [entryID], [compID], [emailAddy], [answer] FROM [entry]"></asp:SqlDataSource> if i set user instance to true i get a user permission error. it says on sql server management that i have dbo rights on my database, but it wont let me put datareader or write on this login. any ideas? its driving me insane
I have installed new SQLServer2012 instance and my domain user have sysadmin privileges on this instance. I have a restore procedure and it will execute WITH EXECUTE AS 'domainmy username', for all the developers have exec permissions to this procedure. But newly installed server this procedure was failing with the following message. But the same procedure executing fine on other servers.
Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Hi there
I am working on Visual Web Developer Express Edition 2005. When I right click on database explorer to create an SQL server database then I always find the option " Create New SQL Server database " Disabled.
Can any one tell me how to enable that option please ?
Cann someone pls tell me how to create a database in visual studio 2005. :o
View 6 Replies View RelatedI have an application that creates a new database during installation,and I've noticed some strange behavior. Once I've created thedatabase, I am able to immediately create tables and populate lookupdata, provided I remain connected to the server. If, however, Idisconnect and attempt to reconnect immediately, I'll get an errorsaying that my login is invalid for the new database.I can get around this by having my code simply wait 5 seconds beforeattempting to reconnect, but I'm curious to see if anybody here cangive an explaination for why this is happening. Here is a bit ofpseudo code to explain what I'm seeing:open new connectioncreate databasecreate tablespopulate tablesclose connection// open new connection /* can't do this yet, as it would break */for (int a=0; a<5; a++){Thread.Sleep(2000)try{open new connectionbreak;}catch{Debug("still waiting...");}}Running my version of this code, I'll see that "still waiting..."message go past 2-3 times before SQL Server wakes up and realizes thatI'm allowed to connect to it. Anybody know why?Thanks,Jason KesterExpat Software Consulting Serviceshttp://www.expatsoftware.com/---Get your own Travel Blog, with itinerary maps and photos!http://www.blogabond.com/
View 1 Replies View RelatedHi,
I'm using Visual Studio 2005 Pro and SQLExpress that comes with it.
I have my program running fine in XP Pro OS using a window user "Glen" (Computer administrator) with Administrator rights. This means that I installed VS 2005 using this window user "Glen"
I created another windows user "TestUser" (Limited account) in the same physical PC.
I tried to run the program and on the part that I need to access SQL table, I got the error [CREATE DATABASE permission denied in database 'master']
At the same time while using "TestUser" and running sqlcmd (to check if I can connect to SQL), I also got error HResult 0x2, Level 16, State 1.
I read alot on MSDN discussions and related links but it seems that I can't get the solution that I need.
SO HERE ARE MY QUESTIONS :
1. Am I allowed to run my program using user "TestUser" since SQL is installed using "Glen" windows user?
2. Do I need to add access rights to "TestUser" to allow the user to have CREATE rights? (Note : for security reason, I can add other access rights except Administrator)
Thanks in advance for all you help.
Hi, we are having problems getting osql to work. When we try and open it quickly opens and then closes again. Has anybody else had this problem?
View 1 Replies View RelatedHi all.
My first post here.
I want to distriburte a msde database. When doing so, I also want to make a new login, make a user, make the user the dbowner of the database i installed.
I found the Stored procedures I need to use, and I have tested that it works using OSQL.
What I want to do now is to make this automatic. After installing the database the OSQL commands should be executed and no user interference should be necessary. How can that be done ?
peet
Hi All,
I am trying to use OSQL (somthing I am new too)
I have the folllowing syntax, but it does not seam to work. Any ideas would be very gratfull.
osql -Usa -Ppassword -SServername -Q"select @@version"
Thanks in advance
Dave
Dave Dunckley says there is a law for the rich and a law for the poor and a law for
Dirty Davey.
Hello,
I have several sql scripts to be executed on a set schedule, with the output directed to a text file. If the schedule triggers this process daily, is it possible to append each days output to the same output file? I've researched the osql switches and various online sources...nothing really covers this.
This is done (relatively) easily with .vbs, but so far it looks tougher with TSQL.
Many thanks to all who respond!
is there some way to activate osql from a c++ app and stream theoutput to my program?
View 1 Replies View Relatedi'm trying to execute some scripts created by the express studio script wizard. i can connect with the studio, the website (asp worker) but i can't create the right cmdline for osql ..... this is my osql line ......
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn>osql -S (local)\SQLEXPRESS -U sa -P sablah -i run.sql.......
this is the error i'm getting in my logs
Error: 18456, Severity: 14, State: 16.
2006-12-22 15:30:45.11 Logon Login failed for user 'sa'. [CLIENT: <local machine>]
"Server=(local)\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";
the following is the working connection string for my aspworker.
"Server=(local)\SQLEXPRESS;Database=ggmi;User Id=sa;Password=cr79cr02;Trusted_connection=false;";
when i try to put in the trust conenction parameter is says that it conflicts with the user flag ,. probably because its a differant type of login process. any ideas?
I am a beginner in learning SQLServer 7.And I learn it by using its own help file.
The only problem troubles me is that after I do some options in database diagram,there's nothing happen.And then no matter what I do,database diagram panel is always blank.
Hi.. i have created a project which uses SQL 7 as a database and have finished it. MY supervisor wants me
to create a wizard that will be able to install the database on any NT 4.0 machine. How do i start and go about doing
it
Please help
thanks
I am using SQL 6.5. Can I create a database and Alter the same
database in one SQL Script?
Thanks in advance.
I installed IIS Manager to my Vista PC and then SQL Server 2005 Express and SQL Server Management Studio Express using Windows Authentication. I could successfully connect to SQL Server. However, when I tried to create a new database, I got the error:
CREATE DATABASE permission denied in database ‘master’
I searched mant forums thru web about this error but could not find a resolution. I have BUILTINUsers and sa in the Logins under Security. But BUILTINUsers is not in any Server Roles. I tried to add it to public or dbcreator but was not allowed to do so, while sa is only in sysadmin.
Anybody could help me solve this program? Thanks.
I installed IIS Manager to my Vista PC and then SQL Server 2005 Express and SQL Server Management Studio Express using Windows Authentication. I could successfully connect to SQL Server. However, when I tried to create a new database, I got the error:
CREATE DATABASE permission denied in database ‘master’
I searched mant forums thru web about this error but could not find a resolution. I have BUILTINUsers and sa in the Logins under Security. But BUILTINUsers is not in any Server Roles. I tried to add it to public or dbcreator but was not allowed to do so, while sa is only in sysadmin.
Anybody could help me solve this program? Thanks.