I am in the process of creating a windows installer. I am using SQL Server 2005 for my application. I have generated the script for my database. I want to run that script and create my database on the client machine (client has installed SQL 2005). Do you have any solution for this kind of issue?
When run an installer I can run a bat file. Can I create a bat file and create my database? Does SQL Server2005 provide any tool for this?
If I run the following command in a Query window it works:
RESTORE DATABASE CIS_Source_Data_Test FROM DISK = 'y:CIS_Source_Data_backup_2015_06_17_085557_7782407.bak' WITH RECOVERY, REPLACE
If I dynamically put together the command and store it in variable @cmd and then execute it using exec sp_executesql @cmd or exec (@cmd) it does not work. I get the following:
Msg 2745, Level 16, State 2, Procedure CIS_Source_Data_Refresh, Line 92 Process ID 62 has raised user error 50000, severity 20. SQL Server is terminating this process. Msg 50000, Level 20, State 1, Procedure CIS_Source_Data_Refresh, Line 92 RESTORE DATABASE is terminating abnormally. Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Why it won't work when I try to create and run it dynamically?
I am using the SQLCMD function to execute a particular storded procedure every minute. The task schedular calls this batch file to run the stored procedure every minute. The issue is it opens up a command window every minute and is annoying. How can i run this job using sqlcmd and still not have the window pop up.
Is there a way to create a service that can run the batch files without the user knowing that it is running. I am trying to replicate what sql server agent used to do wth msde. i am using sql server express now.
The MSDN Library topic Using the sqlcmd utility(SQL Server Express) states "To access the sqlcmd utility, click Start, click Run, and type sqlcmd.exe."
A command prompt window opens with "SQLCMD" in the title bar. I cannot enter anything in the command prompt window and the window remains open for only about five or six seconds.
The version I have installed is MS SQL Server 2005 Express with SP2. The cumulative updates package 3 does not list this problem, so I did not install it.
I have a cube where i would like to define some actions. I have done report Actions but they are limiting in terms of what i am trying to achieve.I want a popup window from the action from relevant cells at the moment i can use the urlaction type to open the link in a new window which is ok but i want this as a popup.
Hello All, I'm currently in the process of developing a new PO system for work. What I need to accomplish is a SQL MAX command to find the largest PO number and then add 1 to that number. I'm then saving this in a session state so users can create multiple items for that same PO number. Here's what I have so far:
1 protected void Page_Load(object sender, EventArgs e) 2 { 3 // connection string to your database 4 SqlConnection mySqlConnection = new SqlConnection("server=(local)\SQLEXPRESS;database=Purchasing;Integrated Security=SSPI;"); 5 6 // create command object to execute query 7 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); 8 9 // set command text to the INSERT sql query 10 mySqlCommand.CommandText = "SELECT MAX(PONumber)FROM ItemMaster;"; 11 12 // open connection 13 mySqlConnection.Open(); 14 15 // execute query 16 17 int newPO1 = (int) mySqlCommand.ExecuteScalar(); 18 int newPO = newPO1 + 1; 19 20 // close connection 21 mySqlConnection.Close(); 22 23 //Response.Write(newPO); 24 Session["newPO"] = newPO.ToString(); 25 26 }
I copied and modified the ExecuteScalar() command from another thread in another fourm, but continue to receive this error:
System.InvalidCastException: Specified cast is not valid.Source Error:
Line 30: // execute query Line 31: Line 32: int newPO1 = (int) mySqlCommand.ExecuteScalar(); Line 33: int newPO = newPO1 + 1; Line 34:
I'm not sure what i'm doing wrong, any help to point me in the right direction would be greatly appreciated.
I was given a task of coming up with the script to recreate an existing database using a command line. I would use this script in case when the server is down and I can't get to Query Analyzer or EM to recreate it. I am not sure where to start. Any ideas are greatly appreciated.
This is a really simple question. When I set my variable (the one will hold my SQL Command) property "EvaluateAsExpression=True", I don't get the option to edit the expression, I was expecting to get a little button that would open the "Property Expressions Editor" if pressed.
Am I doing anything wrong? Should I edit my SQL Command somewhere else and then Copy+Paste on my variable expression?
I need to pass a ServerName as a field to my query and I'm doing that by looping through a list of servers...
Hi All,What is the SQL command for creatinga direcotry c:mydatadata1 on my server.Thanks in advanceJohn S*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi All,I am new to MS SQL Server.I am using MS SQL 2000.I have a problem increating a table by using Select command.I have table called "test"and i want to create another table with the same structure and rows.Itried with the following commandcreate Table test1 as select * from test;But it give an syntax error.I have tried the same command in Oraclebut i was working.Does MS SQL 2000 Server supports this kind of Query.Please help me to solve the problem or any other methods to performthis operation.Thanks in AdvanceKevin
Hi I have problem in configuring my web project on Window server 2000. I have created the web application using Visual Studio 2005 tools. I have configured Window server 2000 with the .Net Framework Version 2.0. I can access the page which doesn't have database access. The connection string in the web config file is giving me the problem that connection string can't be found. Does any one have any idea? My database connection string looks like this. <connectionStrings> <add name="etzAusBldConnectionString" connectionString="Data Source=CASCADE;Initial Catalog=etzAusBldBaseData1;User ID=sa1;Password=****" providerName="System.Data.SqlClient" /> </connectionStrings> Moe
Does anyone know why a backup database statement aborts a job in a stored procedure so that you can't handle the error with @@error, but when you run the same stored procedure in a query window the error handling does get run?
In MSDN, it says that it is recommended to use windows authentication to connect to SQL Server rather than use mixed authentication.
I create user deltasqluser on windows OS, and I specify in my webform ASP.NET script below :
protected System.Web.UI.WebControls.Label Label1; private string _connString = @"data source=deltasql2000;initial catalog=northwind;integrated security=false;user id=deltasqluser"; /* comment : I login to my windows as deltakoronx, and I want to every user (including me), connected to sql server through IIS, will be identified as deltasqluser not as user's login (impersonate) */ private void Page_Load(object sender, System.EventArgs e) { SqlConnection conn = new SqlConnection(_connString); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = "select suser_sname()";
at web.config, I add : <identity impersonate="false" userName="deltasqluser" password="" />
at IIS webApplication1's properties, tab "Directory Security", at "Authentication and access control" section, I checked "enable anonymous access" with user : DELTAIUSR_DELTA and checked "Integrated Windows Authentication",
at query analyzer, I login as "sa" and execute script below : exec sp_grantdbaccess 'deltasqluser','northwind'
when I run the ASP.NET script, error at conn.Open(); with error message : Login failed for user 'NT AUTHORITYNETWORK SERVICE'.
What should I do so that IIS login to SQL Server as user deltasqluser not as "NT AUTHORITYNETWORK SERVICE" ?
SELECT TOP 1 @From = CONVERT(char,CreateDate,101) FROM CustomerInfo WHERE TicketNum = (SELECT TOP 1 TicketNum FROM CustomerInfo WHERE CreateDate <= DATEADD(mm, -30, CURRENT_TIMESTAMP) ORDER BY CreateDate DESC) SELECT @To = CONVERT(char,GETDATE(),101)
SET @DBName = 'Archive_SafeHelp' CREATE DATABASE @DBName + ' ' + @From + ' ' + @To END
I am trying to create a database based on the name contained in the variables. I get the error 'Incorrect syntax near '@DBName'. How do i accomplish this?
What does the shrink command exactly? I start the shrink command in the Enterprise Manager of SQL7/2000. Does it include re-indexing of all tables? And is it the same command as auto-shrink can be selected in the db properties?
Is there a way by which we can execute the backing up of database from the command prompt......
Suppose i need to do the below task. I can write a stored procedure and schedule it. But the situation demands to run from the command prompt. Is there a way out.
-- Create the backup device for the full MyNwind backup.
Commonly, to upgrade a database numerous scripts need to be applied. These scripts make both schema and data changes. Often, the data changes need to be interleaved with schema changes (for example, it is not possible to create a unique index until the column has been populated with unique values). The scripts will contain numerous GO commands as required for the definition of stored procedures, etc.
I am wondering what is the best way to do error handling with such scripts. I use SET XACT_ABORT on at the beginning, but when one batch fails, it just skips on to the next GO command. And of course, it rolls back my transaction having the side effect of running the subsequent batch without a transaction. Is there any way to avoid this? I would like a solution that would let me splice together scripts that I get from various different developers (and in some cases automated tools) with minimal difficulty even though the coding styles may be a little different.
The possible solution that comes to mind is RAISERROR with a severity greater than or equal to 20. I'm not thrilled that I have to use WITH LOG on this command. I tried the KILL command, but that won't let me terminate my own SPID.
I have created a package which uses the OLE DB Command as the target where I write the sql command to insert data into the table. The issue which I am facing is, while at the OLE DB COmmand , the package fails. I notices that it is not able to get the input columns which are mapped to the target columns.
The same package works fine when the target is on Oracle database or a SQL Server database.
For DB2, i have tried using the Microsoft OLE DB Driver for Db2, as the IBM DB2 Driver doesnt work for insert properly.
Any suggestion regarding this would be really helpful.
Label3.Text = command4.Parameters["@UserName"].Value.ToString(); }Exception Details: System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.Line 48: Line 49: conn.Open(); Line 50: command4.ExecuteReader(); Line 51: conn.Close(); and here is my stored procedure ALTER PROCEDURE dbo.myStoredProcedure --
(@ProductID int, @UserName varchar(255) OUTPUT)
AS
SET @UserName = (SELECT UserId FROM Products WHERE ProductID = @ProductID) what is wrong? i never seen that error and i don't know from where could it came from please help me, thanks
I am in the process of writing a stored procedure that takes data from a table, within a cursor, and puts that data to the network server via the xp_cmdshell procedure. The xp_cmdshell command is at the end of the cursor in the SP.
I have worked with the admins on getting the proper permissions to execute this procedure and have tested it by running the procedure directly in Query Analyzer and by calling it in Query Analyzer successfully.
However when I try to call this procedure from another programming envirnoment, once the xp_cmdshell process is called, the procedure locks up and blocks the rest of the process, including the other software programming environment. When I look at the error log, it indicates that correct command, as a matter of fact I have copied the log file command into Query Analyzer and ran the procedure successfully. So for some reason, the statement is not being committed or there is some kind of threading issue I do not know how to address.
If anyone out there has any suggestions or has a simular problem in the past and knows how to address it, it would be greatly appreciated.
Hello,The PRINT command works fine on Query Analyzer.However, when I used it with other Database Client,eg: Aqua Data Studio, nothing got printed out.Is there a way to make it work?Thanks in advance.
at 9.00am -- full backup at 9.15am - Transaction log backup at 9.30am - Transaction log backup
Suppose at 9.20am(say) the user deletes a table and i want to restore that particular table,i will use STOPAT clause when im restoring the transaction backup at 9.30am (WITH RECOVERY).But my doubt is how to find out the exact time the user has deleted the table.I have even checked the SQLServer Error log file but it doesnt give the exact date and time stamp of the deletion activity tht has taken place so that i can use that in STOPAT clause of RESTORE LOG command.
Please tell me where should i look for date and timestamp for all the Deletions/Updations done on a particular database.
I'm trying to update a table in MyDB1 with a value in MyDB2 on the same SQL server (2000)
UPDATE MyDB1.dbo.Users SET MyDB1.dbo.Users.InstantASP_UserID = MyDB2.dbo.InstantASP_Users.UserID FROM MyDB2 INNER JOIN dbo.aspnet_Membership a ON a.UserId = MyDB1.dbo.Users.UserID WHERE MyDB2.dbo.InstantASP_Users.EmailAddress = a.Email
I don't want to have to create a linked server. It's on the same box.
I was working on my Lab Environment and i had a DB which was mirrored in synchronous mode without witness server. I truncated one of the tables in this DB and did a failover to the mirror server. The table got truncated in the mirror too. Though we know that truncate is a non logged operation how does this mechanism work? Can someone please expain me??? Thanks in advance. Your replies will be very much helpful to me.
Any command which will make create script for my entire database including all sp's , tables, functions..i dont want to backup the db ..i want to create scripts for creating an entire database schema (only the structure and not data)
