Syntax For Osql For Executing Sp With Parameters.
Dec 15, 2004Can anyone tell me syntax for executing a stored procedure using osql which accepts 4 input parameters which are char data types?
I am getting syntax errors all the time..
ADVERTISEMENT
Executing Osql Commands Through Batch File
Sep 13, 2006Hello
I have a script ,which runs with osql
The script is :
osql -E
declare @cmd nvarchar(1000)
declare @cmd2 nvarchar(1000)
declare @state1 varchar(100)
declare @message varchar(100)
set @message = ''
-- Build command to determine state of SQLSERVERAGENT service on Master Server
SET @CMD = 'create table #state (state varchar(2000))' + char(10) +
'declare @cmdx varchar(1000)' + char(10) +
'insert into #state EXEC master..xp_servicecontrol ''''QueryState'''', ''''SQLSERVERAGENT''''' +
+ char(10) + 'select @state=state from #state' + char(10) +
'drop table #state'
-- Build command to execute command that determines state of service being monitored
set @cmd2 = 'declare @state varchar(100)' + char(10) +
'exec ' + rtrim(@@servername) + '.master.dbo.sp_executesql N''' + @CMD + ''',' +
'N''@state varchar(100) out'',' +
'@state out' + char(10) +
'set @state1 = @state'
-- Execute command and return state of service being monitored
exec master.dbo.sp_executesql @cmd2,N'@state1 varchar(100) out',@state1 out
-- Is the service that was monitored not
IF (UPPER(@state1) <> 'RUNNING.')
--if @state1 <1 'Running.'
begin
-- Display message that primary monitor is down
select @message = @message+char(13)+ @@servername + ' -' + 'Sql Server Agent Not Running'+char(13)
print 'Master server "' + rtrim(@@servername) + '" for monitoring is not available.'
exec master.dbo.xp_smtp_sendmail
It works fine when I run it on the command line prompt.
And I receive a mail , if the server agent is running.
But when I save it as bat file and try to run , it stops and doesnot even give an error.
Can anyone let me know what I can do.
Thanks
Osql Syntax Help
Oct 8, 2004Dear friends,
I was trying to run this script from a dos batch file under win xp but it's not working. Please help with the syntax.
================================================== =======
OSQL -U sa -P samsde -S firebird /Q "RESTORE DATABASE NAVIMEX FROM DISK = 'C:30704' WITH
REPLACE, MOVE'NAVIMEX_Data' TO 'C:Program FilesMicrosoft SQL
ServerMSSQLDataNAVIMEX_Data.MDF',MOVE 'NAVIMEX_Log' TO 'C:Program FilesMicrosoft SQL
ServerMSSQLDataNAVIMEX_LOG.LDF'" QUIT
================================================== ========
Please provide me with the correct syntax to be put in a 'restore.bat' file :rolleyes:
Thanks in advance.
HotBird
Correct Syntax For Query Using Osql.exe In MSDE Setup
Nov 10, 2003I have the following .bat file that I call to register users to my MSDE instance...
echo osql -E -S %COMPUTERNAME%OfficiumInstance -Q "sp_grantlogin '" + %COMPUTERNAME% + "ASPNET'"
echo osql -E -S %COMPUTERNAME%OfficiumInstance -d Officium -Q "sp_grantdbaccess '" + %COMPUTERNAME% + "ASPNET'"
echo osql -E -S %COMPUTERNAME%OfficiumInstance -d Officium -Q "sp_addrolemember 'db_owner', '" + %COMPUTERNAME% + "ASPNET'"
My question is how do I correctly append %COMPUTERNAME% as a string to
I have tried using + signs...
"sp_grantlogin '" + %COMPUTERNAME% + "ASPNET'"
But that doesn't work.
I have looked for .bat file tutorials on the web but can't find the correct way to do this.
Greg
Syntax Error Executing Maintenance Plan Job
Dec 6, 2007I'm running SQL 2005 SP2 on a cluster. I have a maintenance plan that backs up databases. When I execute the job associated with the plan, I get the following error:
Unable to start execution of step 1 (reason: line(1): Syntax error). The step failed.
This only occurs when node 1 is active. When node 2 is active, the job runs with no problem.
I edited the job, and it references the package
Maintenance PlansPlanName
If I add a leading "" to the package path so that is reads
Maintenance PlansPlanName
then the job runs successfully on both nodes. So the question is why would this fail on one node and not the other? I know there are SSIS issues when applying SP2 on a cluster, so we applied it on each node. SSIS is running version 9.0.3042 on each node.
Thanks,
Tom
Incorrect Syntax Near The Keyword 'with' When Executing From A Website Project
May 9, 2008Hi, we have a problem when executing a script. When i execute the script in my SSMS it works fine. However we want to roll out the script to our customers. For this we have created a custom program. What this program does is read out an xml file. The xml file contains what type of action needs to be taken and the content. In this situation the action is execute a sql script and the content is the script.
An SqlCommand is used to establish a connection and the script is executed through it, but then it returns an error: Incorrect syntax near the keyword 'with'.
Other querries execute perfectly in the program we made, but not this one. The script is as follows:
ALTER PROC [dbo].[usp_get_report_school_year]
@school_id INT
AS
WITH report AS (
SELECT sy.description,
se.type,
se.scol_date,
COUNT(DISTINCT ss.student_id) number_of_students,
SUM(CONVERT(INT, meets_general_standard)) number_met_standard
FROM t_school_year sy
INNER JOIN t_scol_score ss
ON sy.school_year_id = ss.school_year_id
INNER JOIN t_scol_entry se
ON se.scol_entry_id = ss.scol_entry_id
INNER JOIN t_m2m_user_school_group m2m
ON ss.student_id = m2m.user_id
INNER JOIN t_school_group sg
ON m2m.group_id = sg.group_id
AND se.scol_entry_id = sg.scol_entry_id
WHERE ss.school_id = @school_id
AND sg.grade <> '1'
GROUP BY sy.description, se.type, se.scol_date
)
SELECT report.description,
report.type,
report.number_of_students,
report.number_met_standard,
ISNULL(MAX(ss.standard_percentage), 0) standard_percentage
FROM report
LEFT JOIN t_scol_standard ss
ON ss.begin_date <= report.scol_date
AND (ss.expiration_date >= report.scol_date OR ss.expiration_date IS NULL)
LEFT JOIN t_school s
ON ss.school_type_id = s.school_type_id
WHERE s.school_id = @school_id
GROUP BY report.description,
report.type,
report.number_of_students,
report.number_met_standard
Suggestions on other threads involved adding a semicolon at the start end and other places, that didnt work as well. Anyone any suggestions on a possible solution?
Thanks in advance,
Marcel Joemmanbaks
Executing Stored Procedure With Parameters Using Execute Sql Task
Jul 12, 2006
Hi ,
I am trying to exectue a stored procedure through execute sql task which takes two input parameters table name and filegroup to create a table and it also returns a value.
i am using the oledb connection
but i am getting the following error
[Execute SQL Task] Error: Executing the query "declare @rtn_value int exec spm_Utopia_FinanceUsage_Create_MonthlyTable ?,? select @rtn_value as rtn_value " failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
i tried with ado.net connection by directly giving the parameter names.
it will create a table but the the table name is something different from what i specified
Please do help as soon as possible
Thanks
Manish
Executing Oracle Stored Procedure With Output Parameters Using ADO.NET Connection
Apr 11, 2007I am a bit confused by an issue that I am having with executing an Oracle stored procedure (with an output parameter) using an ADO.NET connection object. I am able to get this working using an OLEDB connection, but I have no idea why the ADO.NET connection doesn't work. (Bug, by design, or my ignorance?) Actually, I can even get this to work if I use the .NET Providers for OLE DBMicrosoft OLE DB Provider for ORACLE if we set the connectionType to ADO.NET. This is the error that I am receiving:
[Execute SQL Task] Error: Executing the query "pkg_utility_read.test_out_var " failed with the following error: "The OracleParameterCollection only accepts non-null OracleParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
It is also worth mentioning that the ORACLE stored procedure has an out parameter with a NUMBER datatype which I think maps to the ADO.NET Int32 datatype. I guess OLE DB datatypes are more closely mapped to ORACLE datatypes. In OLE DB you can set the parameter to double and the ORACLE stored procedure to NUMBER and it works.
Any help on this would be most appriciated.
Update Parameters Syntax
May 12, 2008Hi All,The following code runs without error but does not update the database. Therefore I must be missing something.I am sure that this a simple task but as newbie to asp.net its got me stumpted. Protected Sub updatePOInfo_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles updatePOInfo.Updating Dim quantity As Integer Dim weight As Integer Dim packed As Integer quantity = CInt(bagsOnPallet.Text) weight = CInt(lbsInBags.SelectedValue) packed = weight * quantity e.Command.Parameters("@packedLbs").Value += packed e.Command.Parameters("@AvailableLbs").Value -= packed End Sub
View 4 Replies View RelatedHow To Use N Syntax For Unicode Parameters
May 10, 2004Hi all,
I had two Nvarchar(255) columns & retreive it like this
DIM CN1 AS SQLCONNECTION
DIM DR1 AS SQLDATAADAPTER
DIM DTABLE AS DATATABLE
DIM DTSET AS DATASET
DTSET=NEW DATASET()
DR1=NEW SQLDATAADAPTER("SP_PRO1",CN1)
DR1.SELECTCOMMAND.COMMANDTYPE=COMMANDTYPE.STOREDPROCEDURE
DR1.SELECTCOMMAND.PARAMETERS.ADD("@TYPE1", REQUEST.QUERYSTRING("PID"))
DR1.SELECTCOMMAND.PARAMETERS.ADD("@TYPE1", REQUEST.QUERYSTRING("UID"))
DR1.FILL(DTSET,CINT(INTCURRINDEX.TEXT),CINT (INTPAGESIZE.TEXT),"SP_PRO1")
DL1.DATASOURCE=DTSET.TABLES(0).DEFAULTVIEW
DL1.DATABIND()
CN1.CLOSE()
END SUB
How to insert "N" National Code to parameter? I try it N"@TYPE1" or "N@TYPE1" with failed. Any ideas or advise? Thanks a lot.
Aris
Incorrect Syntax Near '?' When Trying To Use Parameters
Feb 23, 2007I must be missing something simple. I have the following code that is not too complicated. I am trying to read a session variable (referenced in the <selectparameters> section) and use it to filter my SELECT statement. The select statement runs fine and displays everything in the gridview control until I put the "WHERE PackagingItemNo = ?" clause in. Then I get the error message in the title. I've tried using quotes, brackets, etc. to see if there's some syntax issue I'm missing here but I'm lost. I see numerous code examples that look identical to mine. What am I missing?
I'm mostly an Oracle and PL/SQL type so I'm a little lost here...
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BorderColor="Black"
AllowPaging="true" DataKeyNames="InBoundID" BorderStyle="Solid" BorderWidth="1px"
Width="100%" AllowSorting="True" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display.">
<HeaderStyle HorizontalAlign="Left" />
<Columns>
<asp:BoundField DataField="PackagingItemNo" HeaderText="Pack.Item#" SortExpression="PackagingItemNo" />
<asp:BoundField DataField="QuantityShipped" HeaderText="L" SortExpression="QuantityShipped" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDBConnectionString1 %>"
ProviderName="<%$ ConnectionStrings:MyDBConnectionString1.ProviderName %>"
SelectCommand="SELECT [InBoundID], [ShipID], [ShipItemID], [LocationID], [ArtisanShipNo], [ArrivalDate], [ArrivalTime], [ShipMode], [PackagingItemNo], [QuantityIn], [QuantityShipped], [QuantityClaimed], [ContainerType], [UnloadInvoicedYN], [CarrierName], [BillOfLading], [ShippingPointName], [ShippingPointState], [ReleaseNumber], [ProfileFlag], [TagFlag], [ActiveYN], [WHouseUserID], [UpdatedOn], [UpdateIs] FROM [WHouseInBound] WHERE PackagingItemNo = ?">
<SelectParameters>
<asp:SessionParameter Name="PackItemNo" SessionField="PackagingItemNo" DefaultValue="12345" />
</SelectParameters>
</asp:SqlDataSource>
Correct Syntax For Parameters When Creating SQL Job
Dec 30, 2007I'm trying to create a SQL job in SQL Server and am a little unclear about the formatting.Here's a snippet from the stored procedure that creates the job:CREATE PROCEDURE [dbo].[spArchive] @DB varchar(30), @Date DateTimeAS EXEC msdb.dbo.sp_add_jobstep @job_name = 'ArchiveIncentives' , @step_id = 1 , @step_name = 'ArchiveAHD' , @subsystem = 'TSQL' , @command = 'spArchiveAHD ''@Date''' , @on_success_action = 3 , @on_fail_action = 2 , @database_name = '@DB' , @retry_attempts = 1 In this case, the job will be calling this stored procedure:CREATE PROCEDURE [dbo].[spArchiveAHD] ( @dtArchiveBefore DateTime)AS I'm unclear about these lines: @command = 'spArchiveAHD ''@Date''' @database_name = '@DB' Do they look correct to you or should I drop some/all of the apostrophes?Robert W.
View 5 Replies View RelatedInsert Syntax When Passing Input Parameters
Dec 27, 2000I'm trying something like this:
CREATE PROCEDURE Add_Junk @Dist char, @CheckNo int =null OUTPUT AS
Set NoCount On
BEGIN TRANSACTION
INSERT INTO Junk (Dist)
VALUES (@Dist)
COMMIT TRANSACTION
select @CheckNo=@@IDENTITY
If what I pass is "416" I only get the "4" in my database and nothing else.
I don't get an error message.
What is wrong with my syntax?
PS I'm using Microsoft SQL 7.0
Syntax Error - Parameters In A Derieved Query
Jun 1, 2006I am geting syntax error with the following query. I created a connection with SQL Native Client as Provider. I looks there is bug when we try to parameterise the derieved query
Select [EvidentiaryDataDefinitionHistoryID]
, EvidentiaryDataDefinitionID, Name, ModifiedUTCDate
From HistoryEvidentiaryDataDefinition HEDD (nolock)
Inner Join
(Select max([EvidentiaryDataDefinitionHistoryID]) AS EDDHistoryID
from HistoryEvidentiaryDataDefinition (nolock)
where ModifiedUTCDate >= ? and ModifiedUTCDate < ?
group by EvidentiaryDataDefinitionID,ModifiedUTCDate) T1
On T1.EDDHistoryID = HEDD.EvidentiaryDataDefinitionHistoryID
Regards
Koya
T-SQL Error In Creating A Stored Procedure That Has Three Parameters: Incorrect Syntax Near 'WHERE'
Feb 17, 2008Hi all,
I copied the the following code from a book to the query editor of my SQL Server Management Studio Express (SSMSE):
///--MuCh14spInvTotal3.sql--///
USE AP --AP Database is installed in the SSMSE--
GO
CREATE PROC spInvTotal3
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
AS
IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate)
SELECT @InvTotal = SUM(InvoiceTotal)
FROM Invoices JOIN Vendors
WHERE (InvoiceDate >= @DateVar) AND
(VendorName LIKE @VendorVar)
GO
///////////////////////////////////////////////////////////////
Then I executed it and I got the following error:
Msg 156, Level 15, State 1, Procedure spInvTotal3, Line 12
Incorrect syntax near the keyword 'WHERE'.
I do not know what wrong with it and how to correct this problem.
Please help and advise.
Thanks,
Scott Chang
Execute SQL Task: Executing The Query Exec (?) Failed With The Following Error: Syntax Error Or Access Violation. Possible F
Jan 23, 2008Hi,
I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties
Result Set : None
ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
Thanks in Advance.
Management Studio Express && SQLcmd: Syntax Errors In Executing A MS-DOS Batch File && A Microsoft SQL Server Query File?
Nov 2, 2007Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.
Thanks in advance,
Scott Chang
Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu
Jan 24, 2008
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Any ideas for this issue?
Thanks a lot
Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window
Jan 23, 2008Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
View 2 Replies View RelatedDifferent Results When Executing From .NET Component Compare To Executing From SQL Management Studio
Oct 10, 2006Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
View 2 Replies View RelatedPassing Parameters To Batch File And Executing Batch File Loop
Aug 7, 2007HELP,
I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.
Please help
Phil
Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?
May 20, 2008Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
Osql
Jun 28, 2001Hi, 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 RelatedOsql
Oct 25, 2004Hi 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
OSQL Help
May 15, 2008Hi 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.
OSQL
Mar 11, 2008Hello,
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!
Osql
Mar 12, 2008is there some way to activate osql from a c++ app and stream theoutput to my program?
View 1 Replies View RelatedOsql
Dec 22, 2006i'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?
Incorrect Syntax When There Appears To Be No Syntax Errors.
Dec 14, 2003I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
Which Is Faster? Conditional Within JOIN Syntax Or WHERE Syntax?
Mar 31, 2008Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
Converting Rrom Access Syntax To Sql Syntax
Sep 23, 2007
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
Osql Utility
Jul 28, 2004using osql utility i have converted a table in database to excel format. I have also chinese data in my database . The problem is while converting the data into excel format ,the chinese data is not comming in excel sheet.
Does anybody have an idea about this .
For converting i have used
declare @x varchar(300)
set @x = 'osql -S Servername -U username -P passwd -q "select * from northwind..region order by cand_index" -w 3000 -s "," -o C:directoryfilename.csv'
Thanks
Osql Utility
Aug 14, 2004Hi all
I am using osql utility in sql server 2000 to convert data in database to excel sheet. My datas contain both english and chinese . While converting to excel sheet english datas are comming but the datas in chinese is comming like (?) this. Does any body has any idea to solve this problem. is there any way to set font in osql utility.
set @x = 'osql -S servername -U username -P password -q "select * from
northwind..region" -w 3000 -s "," -o C:Downloaddownload.csv'
This is what i have used.
Note: I tried giving chinese font in excel still it is not working.
Its really urgent pls reply
thanks