I have a series of commands that I need to run which decrypt, unzip, and rename
They are all one line commands when entered on the cmd prompt, is there an easy way to do this in ssis or should i write an exe and call them from there?
Thanks
Hi,
I wanted to know if there is a way to execute sql commands on the operating system's command line. If it is possible, then how do we do it ? For example to execute a SELECT * from Table statement what are we supposed to do ?
Thanks
S
I have downloaded and "run" these programs:
View 6 Replies View RelatedHi,
When I call certain reports from an ASP application, some prompt for password and some are not.
I wonder why is this happen?
Under the Directory Security of Reports under IIS 6, I already enable Anonymous Access for IUSR_XXXX with the correct password.
I also assign IUSR_XXXX as Browser in Report Manager of Reporting Services.
The enviroment is Windows Server 2003 standard edition, IIS 6, SQL Server 2005.
Thanks in advance.
Best regards,
Pop
All, I am developing the data mart refresh task in SSIS. I wanted to call the package in command prompt. I need help. Can any one give any tips on it. Thanks and appricate in advance.
Regards
Govind
I'm building a simple webform, except Visual Studio and my service provider have combined to drive me nutty.
First, I MUST use an ODBC connection to my remote SQL Server do to some unknown configuartion problem. I've been playing with Visual Studio for only a month, so normally when something goes wrong I can go look in the mirror and find the culprit. This is different. I've got a totally functional web form with a SQL Connection, but when I try to change it to an ODBC Connection, I get the following error.
An OdbcParameter with ParameterName '@CertHolder' is not contained by this OdbcParameterCollection
My coding is fine because I stole it straight from the walkthrough and it works. But the specifications that Visual Studio provide are quite suspect. Please note the failure to include some key "@" signs.
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.cmdUpdate = New System.Data.SqlClient.SqlCommand
Me.cmdGetAll = New System.Data.SqlClient.SqlCommand
Me.cmdSelect = New System.Data.SqlClient.SqlCommand
Me.OdbcConnection1 = New System.Data.Odbc.OdbcConnection
Me.OdbcGetAll = New System.Data.Odbc.OdbcCommand
Me.OdbcSelect = New System.Data.Odbc.OdbcCommand
Me.OdbcUpdate = New System.Data.Odbc.OdbcCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "this works fine"
'
'cmdUpdate
'
Me.cmdUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _
"City = @City, State = @State, Zip = @Zip, CertHolder = WHERE (CertHolder = @Cert" & _
"Holder)"
Me.cmdUpdate.Connection = Me.SqlConnection1
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 50, "Name"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 50, "Address"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Address2", System.Data.SqlDbType.NVarChar, 50, "Address2"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 50, "City"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@State", System.Data.SqlDbType.NVarChar, 50, "State"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Zip", System.Data.SqlDbType.NVarChar, 50, "Zip"))
Me.cmdUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing))
'
'cmdGetAll
'
Me.cmdGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData"
Me.cmdGetAll.Connection = Me.SqlConnection1
'
'cmdSelect
'
Me.cmdSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _
"HERE (CertHolder = @CertHolder)"
Me.cmdSelect.Connection = Me.SqlConnection1
Me.cmdSelect.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CertHolder", System.Data.SqlDbType.NVarChar, 50, "CertHolder"))
'
'OdbcConnection1
'
Me.OdbcConnection1.ConnectionString = "This works fine"
'
'OdbcGetAll
'
Me.OdbcGetAll.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData"
Me.OdbcGetAll.Connection = Me.OdbcConnection1
'
'OdbcSelect
'
Me.OdbcSelect.CommandText = "SELECT CertHolder, Name, Address, Address2, City, State, Zip FROM InsuranceData W" & _
"HERE CertHolder = @CertHolder"
Me.OdbcSelect.Connection = Me.OdbcConnection1
Me.OdbcSelect.Parameters.Add(New System.Data.Odbc.OdbcParameter("CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, "CertHolder"))
'
'OdbcUpdate
'
Me.OdbcUpdate.CommandText = "UPDATE InsuranceData SET Name = @Name, Address = @Address, Address2 = @Address2, " & _
"City = @City, State = @State, Zip = @Zip WHERE CertHolder = @CertHolder"
Me.OdbcUpdate.Connection = Me.OdbcConnection1
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Name", System.Data.Odbc.OdbcType.NVarChar, 50, "Name"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address", System.Data.Odbc.OdbcType.NVarChar, 50, "Address"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Address2", System.Data.Odbc.OdbcType.NVarChar, 50, "Address2"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("City", System.Data.Odbc.OdbcType.NVarChar, 50, "City"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("State", System.Data.Odbc.OdbcType.NVarChar, 50, "State"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Zip", System.Data.Odbc.OdbcType.NVarChar, 50, "Zip"))
Me.OdbcUpdate.Parameters.Add(New System.Data.Odbc.OdbcParameter("Original_CertHolder", System.Data.Odbc.OdbcType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CertHolder", System.Data.DataRowVersion.Original, Nothing))
I NEVER EVER TYPED ORIGINAL_CERTHOLDER IN THE SQL PREPARATION
End Sub
Protected WithEvents btnSave As System.Web.UI.WebControls.Button
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents ddlCertHolder As System.Web.UI.WebControls.DropDownList
Protected WithEvents txtName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtAddress As System.Web.UI.WebControls.TextBox
Protected WithEvents ddlCH As System.Web.UI.WebControls.DropDownList
Protected WithEvents cmdUpdate As System.Data.SqlClient.SqlCommand
Protected WithEvents cmdGetAll As System.Data.SqlClient.SqlCommand
Protected WithEvents cmdSelect As System.Data.SqlClient.SqlCommand
Protected WithEvents txtAddress2 As System.Web.UI.WebControls.TextBox
Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox
Protected WithEvents txtState As System.Web.UI.WebControls.TextBox
Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox
Protected WithEvents OdbcConnection1 As System.Data.Odbc.OdbcConnection
Protected WithEvents OdbcGetAll As System.Data.Odbc.OdbcCommand
Protected WithEvents OdbcSelect As System.Data.Odbc.OdbcCommand
Protected WithEvents OdbcUpdate As System.Data.Odbc.OdbcCommand
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Comments? Suggestions, I am not positive about how to fix this.
Hi All,
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
EXEC master.dbo.xp_cmdshell @cmd, no_output
SET @email = 'mailsend -f someone@mymail.com -d -smtp -t someone@mymail.com -sub "Error Log Errors" -m J:MyFolderErrorLogMsg.txt'
EXEC master.dbo.xp_cmdshell @email, no_output
Thanks.
What is the sql code to prompt for a value?
Can I do this in query analyzer or do I have to send the value from some other application?
Hi All,
I have these two commands that I execute at the end of my stored procedure. I get an email every time I execute this stored procedure whether the select statement returns a value or not. But I only want to get an email if select statement returns an Error value. How can I accomplish this?
set @cmd = 'osql -S server -U user -P psswd -q "set nocount on; select distinct(rtrim(col1)) from ##table where datediff(dd,col2,getdate()) = 1 and (col1 like ''%Error: %'')" -h-1 -w 1025 -o J:MyFolderErrorLogMsg.txt'
EXEC master.dbo.xp_cmdshell @cmd, no_output
SET @email = 'mailsend -f someone@mymail.com -d -smtp -t someone@mymail.com -sub "Error Log Errors" -m J:MyFolderErrorLogMsg.txt'
EXEC master.dbo.xp_cmdshell @email, no_output
Thanks.
How can I get all SQL commands in SqlServer? How can I trace this commands?
Thansk
Hello,
I finally got access to Northwind/pubs.
I would like to know when and why do we use the following set commands. The SQL Server BOL does not say why and when to use these commands.
Thanks in advance!!!
sqlnovice123
Option Default Setting
Set nocount OFF
Set rowcount 0
Set ansi_nulls ON
Set quoted_identifier ON
Hi guys,I wanna ask bout the problem with my web application. I'm doing a select a statement from table 1 and and with the query results i got, i need it to store the result on table 2. How will i do this? I need your tips and suggestions.
View 1 Replies View RelatedI'm running asp.net 2.0 and acessing MSSQL 2K. I am trying to run a query in which I need to set up variables first. I tried the following group of commands as shown, passing it to the SqlDataReader object, but it failed.. does anyone know how i can pass multiple SQL commands? MainQuery = "declare @MinGrades as Table(GradeID Bigint) " & _ " INSERT @MinGrades SELECT MIN(CreditGrades.SplitID) AS Expr1" & _ " FROM CreditGrades INNER JOIN" & _ " CreditGradeSplits ON CreditGradeSplits.CreditGradeSplitID = CreditGrades.SplitID " & _ " WHERE (CreditGrades.x0x30 = - 1 OR " & _ " CreditGrades.x0x30 >= 0) AND (CreditGrades.x1x30 = - 1 OR " & _ " CreditGrades.x1x30 >= 0) AND (CreditGrades.x2x30 = - 1 OR " & _ " CreditGrades.x2x30 >= 0) AND (CreditGrades.x3x30 = - 1 OR " & _ " CreditGrades.x3xNOD >= 0) " & _ " GROUP BY CreditGradeSplits.CreditGradeGroupID" & _ " Select * from @MinGrades "
View 2 Replies View RelatedHI,
I have a need where SQL Server should prompt me for a value when I run a Select statement.
Eg:
If I need the name of an employee by id number, SQL server should prompt me to enter a value for the ID when I run the statement like:
SELECT name FROM employee WHERE id=[Enter ID]
In oracle, it is possible by using "&" operator but I am not sure how it can be done in SQL Server. Is there any other way to get the prompts if not by the above method.
Thank you for your help in advance.
Sravan.
I am experiencing a situation where I issue a lengthy SQL command to MS SQL Server 7.0 through MTS and it "disappears" - no errors or recordsets are returned. The command is "SELECT * FROM CUSTOMERS WHERE LASTNAME LIKE 'SMITH%'". When I issue this command from SQL Query Analyzer it takes 27 seconds to return 87 rows. When I issue this exact same command through MTS it does not return at all.
I've used the SQL Profiler to analyze the requests. It shows the commands from MTS starting but they never stop (or at least the profiler never reports them as stopping). The same commands coming from Query Analyzer are reported as starting and stopping without fail.
Here's a twist: I can issue less demanding commands (ie, one that doesn't take so long to process) through MTS and they come back fine. For example, when a user logs into my application, I use an SQL statement to verify the user name and password and status the user as logged in. This is routed through MTS and it comes back fine in less than a second. Same application, same PC, same MTS and SQL server, same SQL database. The only difference is that the CUSTOMERS table has over 800,000 records and the USERS table has only 5 records.
PLEASE HELP!
Hi,
I am very new to sql server and I have been reading up on the dbcc commands. For instance, DBCC SHOWCONTIG where do I execute this command? I went into the cmd prompt and opened osql and typed:
1> DBCC SHOWCONTIG
2> GO
This gives me the information for what I assume is the master database. But, how do I use this command under the other databases?
Also, I have been trying to learn how to determine when to do index maintenance by using the index tuning wizard. But, I don't know what a workload is. Can someone point me in the right direction or give me some information about this topic.
Thanks,
Laura
Hi
Please can someone point me in the direction, i built a very badly designed database consisting of only one huge table when i first started databases, since learning about normalization i have designed and set up a new database which consists of many more tables instead of just the one. My question is where do i start in transfering the data from the old single tabled database to my new multi-tabled database?
I have MS SQL server 2005 managment studio if that helps, but want to transfer around 200,000 rows of data into the new database. Both new and old databases are on the same server.
thanks in advance
I have a job that runs between the hours of 10 PM and 9 AM. It launches a controller stored procedure that will call other stored procedure until the entire process is done.I would like the controlling stored procedure to only call the steps between the hours of 10PM and 9AM also.. So at 8:59 AM it will start the next step, but at 9:00 AM it will exit.
View 6 Replies View Relatedhi all
when i set up a new subcription ,i notice in hte replication monitor thet this subscription 'uninitialize subscription' and the are 70 undelevier commands.
how can i fixed this problem,or how can i remove those undeleveirs commands?
thanx
can anyone tell me what's sql prompt tool and how can it help in sql script? it finds error or what?
is it useful? its like visual studio?
thanks. pls reply.
Hi All,Is there way that commands executed in the query analyzer get loggedautomatically?TIAJoriz
View 3 Replies View Related
Hi,
we are using a development environment. what is the cost saving licence to buy sql bundle pro and sql prompt.
Please guide. we have eight programmers , do we need to buy licence for every one, for both the tools, and what is the cost
i saw the link in the internet, but not so clear.
Thanks
How i can display parameter prompt in other language like arabic
(right to left)
Regard
LPW
Hi,
Suppose there are two tables employee and salary and a,b are two instances of tables employee and salary.There are 20 records in each table.Then what will be the result of the following query
select a.*, b.* from employee a, salary b.
regards
arun
Hi al,
Are there commands that are not supported when mirroring is active? (truncate table, bcp, set identity insert on/off etc.)
Hi
Can we use
DBCC CHECKIDENT ('dbo.MyTable', RESEED, 0)
Command in Stored procedure. Which executes daily.
Thanks
Sridhar K
Hello, I am running SQL 2000 SP4 on a Windows 2003 standard edition server.
The SQL database gets changed/updated through a web interface. I was wondering if there was any way for me to see which SQL commands were being run on the database when changed like deleting/inserting users are being applied from the Web frontend?
Any help is appreciated, thanks.
Robert
When we send any SQL command to database engine , how exactly it gets executed?
View 7 Replies View RelatedHey everyone I dont know if this is the right area to post this but here it goesAn easy expamle of this that everyone is really fimaler with is myspace. When a new database entry is submitted then on the admin page it will say like new message(s).. how can I do this. It doesnt have to be per member. It is only going to be on the admin page.
View 1 Replies View RelatedHow can I make sure that a couple of commands are either all executed on the database or none of them. For example right now I have an insert, update and delete command. I'm calling each of them with a SqlCommand. So I am afraid that that one of them might be executed, then there's a bad connection and the other two are not. How can I prevent this so that only all commands or nothing is executed on the database?
View 2 Replies View RelatedIs there a way to have two sql update commands so I can use both during an update depending on which row it is?
View 1 Replies View RelatedHi everyoneI created a database graphically in VS2005.Now I want the text version of those DDL(create) commands. Question is where and how are those commands stored in SqlServer I have the management studio too. Any ideas??
View 2 Replies View RelatedPlease forgive me if my questions seem very simple. I'm trying to pick up vb.net, asp.net, and SQL all at the same time, and while making progress in some areas I'm finding other questions I have harder to figure out where to look for answers.
First question - if I want to join 2 tables do the fields I'm joining on have to have the same name? I know they have to be of the same type, but does the name itself matter? For instance in this line of code I try to join forums
objdataadapter.SelectCommand.CommandText = "select * from msg_forums" & "JOIN users ON msg_forums.forum_moderators = users.user_ID"
Second question nagging at me, since I specify the table <msg_forums and users> before the field names <forum_moderators and user_ID> does it matter which one comes first? See, my primary table in this line is msg_forums, so do I have to specify it's field first, or does that even matter (or do I have it backwards and need to specify the other one first)?
Last question, and of course the root of what's caused me to wonder about all of this. I'm getting an error similar to some of what I found in the forums, but it's slightly different in that it says there is a problem near "OR". As far as I can tell I've written this correctly, and so, I'm stumped. Here is the error followed by the code. Thanks a bunch for any assistance anyone offers. :-) Incorrect syntax near the keyword 'ON'.
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: Incorrect syntax near the keyword 'ON'.
'This time trying to learn an adapter instead of a reader, will also try a join
'Build the connectionDim StrConnection As String = (System.Configuration.ConfigurationManager.AppSettings("DbConnect"))
Dim objConnection As New SqlConnection(StrConnection)Dim objdataadapter As New SqlDataAdapter()
Dim objdataset As New DataSet()
'set the SelectCommand Properties objDataadapter.SelectCommand = New SqlCommand
objdataadapter.SelectCommand.Connection = objConnection
'try after renaming to fields to match
objdataadapter.SelectCommand.CommandText = "select * from msg_forums" & "JOIN users ON msg_forums.user_ID = users.user_ID"
'Try without specifying the table
'objdataadapter.SelectCommand.CommandText = "select * from msg_forums" & "JOIN users ON user_ID = user_ID"
'Original attempt
objdataadapter.SelectCommand.CommandText = "select * from msg_forums" & "JOIN users ON msg_forums.forum_moderators = users.user_ID"
objdataadapter.SelectCommand.CommandType = CommandType.Text
'open the database connection
objConnection.Open()
'Fill the dataset with object data giving the dataset to use and the table name (primary table even when pulling from a couple)objdataadapter.Fill(objdataset, "msg_forums")
'close connection
objConnection.Close()
'Set DataGridView properties
grdforums.AutoGenerateColumns = True
grdforums.DataSource = objdataset
grdforums.DataMember = "msg_forums"End Sub
End Class