Hi:
When i try to execute the following stmt using sqlcmd, it is giving me error.
c:>sqlcmd -i "c:sqlconfigDDLLogging.sql" -v LogDir='c:sctclogs'
the error is
Sqlcmd: ':sctclogs'': Invalid argument. Enter '-?' for help.
but when i pass just -v LogDir='csctclogs' it work fine.
The problem is with ":" when i put colon it throws error.
Any help to execute the above command would great.
Thanking you,
Best regards,
Kumar
Hi,
I'm trying to create a page in a web browser where users can type the name of a company in a text box and to get details of the company. I can hard code like the below example and it brings up the details i'm looking for.
The problem is when I try to replicated this with a stored procedure it doesn't work.
Thanks for any help in advance,
Code:
select * from company
where companyname like 'companyx%'
Code:
procedure company_detail_search
(@company nvarchar)
as
select * from company
where company @comapany%
Hi, I have a DTS which has a connection, 3 destination text files and aProcess execution job which runs a simple batch file to concatenatethis 3 files into a 4th text file "Endeud.txt". I added an activeXcommand Job to input a date which is saved in a global variable. Ineed to add this date at the end of the Endeud.txt file (The date wouldbe the last line of Endeud.txt. How can I do this?
View 1 Replies View Related
I have created a stored procedure that will back up a database to a file then restore the database into a new db. The stored procedure accepts 2 parameters that determine the name of the old DB to be backed up and the name of the new DB to be created. The only problem now is that when you specify the name of a database that already eixists, the restore fails. I want to add an if statement that would check to see if the new db supplied exists, and if it does drop it before running the restore.
I thought the following would work but it does not
Code Block
declare @DB nvarchar (50)
set @DB = 'LDRPSsmap2k'
IF EXISTS (SELECT name FROM sys.databases WHERE name = @db)
Drop Database @db
I get When i run this
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '@db'.
Hi,
In Execute SQL Task, I'm facing following error when Result Set assigns value to the global variable.
SSIS package "Package.dtsx" starting.Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "IDCount": "Value does not fall within the expected range.".Task failed: Execute SQL TaskWarning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.SSIS package "Package.dtsx" finished: Failure.
My SQL statement is "Select count(*) as AliasNm from <TableName>" and my Result Set type is Single Row.
I mentioned in the Result Set's Result Name as AliasNm.
Help me to resolve this error.
Thanks in advance.
Suku
Help!
Attached proc is working fine. But how should I pass the value of @LexNexID without manul key in the value. How can I call this proc. to update comment in table tblSEMS_FTP_FINAL automatically
Thk
CREATE PROCEDURE DBO.SP_SEMS_ADD_COMMENT (
@lexnexid int,
@commentstring varchar(4000)
)
AS
DECLARE @Comment varchar(4000)
--DECLARE @CommentString varchar(4000)
--DECLARE @LexNexID INT
--SET @LexNexID = 1
SET @CommentString = ''
DECLARE Test CURSOR
FOR
SELECT
convert(varchar(4000),Comment) COMMENT
FROM dbo.TBLSEMS_FTP
WHERE LEXNEXID = @LexNexID
OPEN Test
FETCH NEXT FROM Test
INTO @Comment
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CommentString = @CommentString + @Comment + '{CR}{LF}' + ' '
FETCH NEXT FROM Test
INTO @Comment
END
CLOSE Test
DEALLOCATE Test
--PRINT @CommentString
update tblSEMS_FTP_FINAL
set comment = @CommentString
where lexnexid=@LexNexID
GO
Hi Guys,
Im trying to pass a value from a C# page to the corresponding ASP page.
I want to use this value in my SQL Query on the asp page within a Details View.
Ive made the variable public in c# page.
And im trying to concatenate it to my sql query within the <asp: SqlDataSource.
Thanks.
A beginner's question I'm sure. When visitors have logged into my site, how do I then use their UserName as a parameter in an SQL WHERE clause so that I can pull up their detailed information. I'm using VB if additional code is required.
Thanks in advance.
Hi,
how to pass paremeters in sqldataadapter? here iam checking authentication using sqlcommand and datareader
like that if iam checking authentication using dataset and sqldatareader how to use parameters?
SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz;data source=server");
cn.Open();
// string str = "select username from security where username='" + TextBox1.Text + "'and password='" + TextBox2.Text + "'";
string str = "select username from security where username=@username and password=@password";
SqlCommand cmd = new SqlCommand(str, cn);
cmd.Parameters.AddWithValue("@username", TextBox1.Text);
cmd.Parameters.AddWithValue("@password", TextBox2.Text);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
Session["username"] = TextBox1.Text;
FormsAuthentication.RedirectFromLoginPage(TextBox1.Text, false);
}
else
{
Label1.Visible = true;
Label1.Text = "Invalid Username/Password";
}
-------------------------------------------------------------------------------------
like that if iam checking authentication using dataset and sqldatareader how to use parameters?SqlConnection cn = new SqlConnection("user id=sa;password=abc;database=xyz; data source=server");
cn.Open();ds = new DataSet();
string str = "select username from security where username=@username, password=@password";da = new SqlDataAdapter(str, cn); //how to pass parameters for that
da.Fill(ds, "security");
Response.Redirect("dafault2.aspx");
I have a DDL with two values: 0 and 1. When I use
sqlcommand.Parameters.Add(New SqlParameter("@fld", SqlDbType.Bit, 1))
sqlcommandl.Parameters("@fld").Value = System.Boolean.Parse(DropDownList1.SelectedValue)
or
sqlcommand.Parameters.Add(New SqlParameter("@fld", SqlDbType.Bit, 1))
sqlcommandl.Parameters("@fld").Value = System.Convert.ToBoolean(DropDownList1.SelectedValue)
to insert the selected value in a sql2k database BIT field I am given this error:
System.FormatException: String was not recognized as a valid Boolean.
How can I solve this problem?
Hi,
i m tring to pass my xml string to my sql server but i receive empty value even tho my xml have it's value.
here's my code,
insert user(userName)
select xml_user
from OPENXML(@xmlHandle,'/NewDataSet',1)
with (xml_user nvarchar(20) 'table/user_name')
my XML code
<NewDataSet>
<table>
<user_name>dorris</user_name>
</table>
</NewDataSet>
Please advice on this matter.Thanks
Hi,
i manage to passs an xml string to sql server based on the reference site(http://www.eggheadcafe.com/articles/20030627c.asp by Robbe Morris). From his way, i can only pass XML in this format
<cart>
<order id="10">
<prod id="1" qty="1"/>
<prod id="2" qty="3"/>
</order>
</cart>
Note : the value is stores as attribute (<prog id="1") . I am just wonder can i actaully pass my xml string to sql Server with this format
<cart>
<order id="10">
<prod>
<id>1</id>
<qty>3</qty>
</prod>
</order>
</cart>
and how can i read the value from this format in my stored procedure?
Regards,
Dorris
Hi,I have a problem while passing a date value to datefunction.My query is like that ..declare @todate varchar(20),@FromDate varchar(20)set @todate='18/10/2005'set @FromDate = '11/10/2005'print DATEADD(yy, DATEDIFF(yy,0,convert(datetime,@todate)), 0)But i getting the error like this..Server: Msg 242, Level 16, State 3, Line 4The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.Can any body help me ..Thanks and RegardsAJOSH
View 1 Replies View RelatedHi,
I have a stored proc as follows:
Create stored procedure sp_test
@Cno nvarchar (1000) AS
SELECT * FROM Contracts WHERE Cno IN (@Cno)
How do I actually pass the parameters to the stored proc?
sp_test "'cn01','cn02','cn03','cn04'" doesn't work.
In VB.NET this doesn't work either:
dim s as string = "'cn01','cn02','cn03','cn04'"
cmd.parameters.add(new sqlparameter("@Cno",nvarchar,1000))
cmd.parameters("@Cno").value = s
I don't want to parse the string inside the proc and then Exec the parsed string.
TIA
Jeremy Holt
I would like to pass the name of a table in my update query. It goes something like :
set @Table = 'TableA'
UPDATE
@Table
SET
Company_id = @Company_id
However I get a syntax error when I try to pass the table name like this.
What i am trying to do is have the contact information to be displayed also. The contact information is in dbo.CONTSUPP under column 'contact'. Is there anyway to pass that 'contact' value up to the parent select statement. So the result will show Company, Address1,...,Source, contact.
SELECT Company, Address1, Address2,
Address3, City, State, Zip,
Country, Phone1, Fax, Source
FROM dbo.CONTACT1
WHERE dbo.CONTACT1.ACCOUNTNO IN (
SELECT ACCOUNTNO
FROM dbo.CONTSUPP
WHEREcontact LIKE '%test1%' OR
contact LIKE '%test2%' OR
contact LIKE '%test3%' OR
contact LIKE '%test4%'
GROUP BY ACCOUNTNO
HAVING COUNT(*) <= 1
)
I have a dts package file. I am able to execute the file thro C#. I want to pass userid, password, datasource through program. How to do this?
DTS.PackClass package = new DTS.PackClass();
string filename = @"c:abc.dts";
string password = null;
string packageID = null;
string versionID = null;
string name = "DTSPack";
object pVarPersistStfOfHost = null;
package.LoadFromStorageFile(filename, password, packageID,
versionID, name, ref pVarPersistStfOfHost);
package.Execute();
package.UnInitialize();
package = null;
Thanks
Jtamil
Hi ,
I have this situation,I need to store results of a Select Count(*) from TableA to a parameter and only if the value of the count(*) is greater than 50,000,a notification mail would be sent out to the table owner.Currently this is what I have done:
1) Use SQL task to include "Select Count(*) from TableA"
2) In parameter mapping tab of SQL Task, define the following
Parameter name : Table::Count
Direction: Input
Data Type: Numeric
Parameter Name : @Count
3) I have connected the SQL Task to a Send Mail Task [ Only If @count>50 000, then a mail should be sent out]
4) The constraints has been set as
Evaluation Operation : Expression and Constraints
Value: Success
Expression : @Count >"50000"
In SSIS tab > Variable
I have set the following
Variable Name : Count
DataType : Int32
Can someone advise me where I am going wrong, as when I execute the SQL Task turns greens and ends.Even when the count of tableA is greater than 50 000, no mails I sent out[send mail task does not get executed]
Thank You in Advance
I am pretty new to this and hope someone can help and I hope I can explain my problem.
I have a php form
<form action="cpesearch.php">
State: <select name="stname">
<option value ="ALL">all states</option>
<option value ="vic">VIC</option>
<option value ="sa">SA</option>etc etc etc
I am then connecting to my datatbase, assign the variables and doing the SQL query.
if ($dbc = mysql_connect ('localhost','root',''))
{
if (@mysql_select_db('database'))
{
$sname = $_GET['stname'];
$pname = $_GET['prname'];
$sql = 'SELECT * FROM `cpe` WHERE eventdate >= CURDATE() AND state = ''.$sname.'' AND presenter = ''.$pname.'' ORDER BY eventdate ASC';
$r = mysql_query($sql) or die("Query failed".mysql_error($dbc));
while ($row = mysql_fetch_array($r))
{
print "<div id="mainContent"> <p>".$row['state']."</p>".
"<p>".$row['eventdate']."</p>".
"<p>".$row['eventtitle']."</p>".
etc etc etc
So my problem is... I have an events table with presenters and state (and other info). I want to be able to select each presenter and state or all presenters and/or states. Not sure then how to pass the variable into my sql statement if they want to select ALL.
can i pass recordset object(which have 10 records) from vb6 to stored procedures, if yes what is the datatype i have to declare in stored procedure
View 8 Replies View RelatedNeed help.
I’m trying to pass inputs from one stored proc to another but I’m having problems in passing them.
First proc (input_passing) should pass the following inputs – FirstName, LastNmae and RecordID to the second proc (input_receive) – then the second proc will diplay the outcome – YES/NO if there is a match.
------------------------------------------------------------------------------------------
First proc (input_passing)
-------------------------------------------------------------------------------------------------------
DECLARE
@RecordIDVARCHAR(11),
@FirstNameVARCHAR(60),
@LastNameVARCHAR(60)
--DECLARE tbInputs CURSOR FOR
SELECT
RecordID,
FirstName,
LastName
FROM
tbInputs
OPEN tbInputs
FETCH NEXT FROM tbInputs
INTO @RecordID, @FirstName, @LastName
WHILE @@FETCH_STATUS = 0
BEGIN
--GET RECORD
SET @RecordID = RecordID from dbo.tbInputs)
END
FETCH NEXT FROM tbInputs
INTO @RecordID, @FirstName, @LastName
END
CLOSE tbInputs
DEALLOCATE tbInputs
exec input_receive
----------------------------------------------------------------------------------------------------------------- Second proc. (input_receive)
CREATE PROCEDURE input_receive
@RecordIDVARCHAR(11) = NULL,
@FirstNameVARCHAR(50) = NULL,
@LastNameVARCHAR(50) = NULL,
@RecordMatchBIT OUTPUT,
@MatchedOnVARCHAR(400) OUTPUT
AS
-- Declarations
DECLARE @CountINT
DECLARE@FoundBIT
DECLARE @AlertIDDECIMAL(18,0)
DECLARE@AlertCreateDateDATETIME
-- END Declarations
-- ******* Initializations **********
SET @Count = 0
SET @Found = 0
-- DEFAULT to No File Match
SET RecordMatch = 0
SET @MatchedOn = 'NO FOUND'
-- ******* END Initializations **********
-- First check to see if the RecordID Matches
IF( @RecordID IS NOT NULL AND @RecordID <> '' )
BEGIN
IF( CAST( REPLACE(@RecordID, '-', '') AS DECIMAL(18,0)) > 0 )
BEGIN
SELECT @Count = COUNT(*)
FROM tbAlert
WHERE REPLACE(RecordID, '-', '') = REPLACE(@RecordID, '-', '')
IF( @Count > 0 )
BEGIN
SET @RecordMatch = 1
SET @MatchedOn = 'FOUND : RecordID'
RETURN
END
END
END
Josephine
how can i pass report parameter via URL?
my jump to URL link is:
http://itreportsme096/Reports/Pages/Report.aspx?ItemPath=%2fDevelopment%2fhoneylette%2fdetailed+phone+calls
and i want to pass two parameters: billingmonth and phoneno
how can i do that?
I have a stored procedure named "processInventory" like the following.Depending on the passed in parameters, I would like to add a WHEREclause for "select" action. For example, if any varchar type ofparameter is passed in, the where clause would use "LIKE" operator. Forexample, "Select * from Main where [s/n] like @Serial. All other typeswill use "=" operator. For example, "Select * from Main where MAKE =@Make and Type = @type".How could this be achieved? Thanks.CREATE PROCEDURE processInventory@Action varchar(7),@ControlNumber int = null,@AssetTag int = null,@Serial varchar(50) = null,@Description varchar(50) = null,@Make int = null,@Type int = null,@Model int = null,@Status int = null,@Networked bit = null,@LoginName varchar(50) = null,@Shared bit = null,@Org varchar(15) = null,@RecordDate datetime = null,@LastUpdate datetime = null,@ManufactureDate datetime = null,@Comment ntext = nullASdeclare @processError intset @processError = 0if @Action = 'Select' goto selectInventoryelseIf @Action = 'Update'beginif @ControlNumber = null return(1) --Required parameter value notspecifiedelsegoto updateInventoryendelseif @Action = 'Insert'beginif @Serial = null return(1) --Required parameter value notspecifiedelsegoto InsertInventoryendelseif @Action = 'Delete'beginif @ControlNumber = null return(1) --Required parameter valuenot specifiedelse goto deleteInventoryendselectInventory:if @Serial <> nullbeginselect * from Main where [S/N] like @Serialif @@Error<>0beginset @processError = @@Errorreturn @processErrorendendelseif @ControlNumber <> nullbeginselect * from Main where ControlNumber = @ControlNumberif @@Error <>0beginset @processError = @@Errorreturn @processErrorendendelseselect top 100* from MainupdateInventory:update MAINset [Org Asset Tag] = @AssetTag, [S/N] = @Serial, [Description]= @Description, Make = @Make, Type = @Type,Model = @Model, Status = @Status, Networked = @Networked,LoginName = @LoginName, Shared = @Shared,Org = @Org, [Date Of Record] = @RecordDate, [Date LastUpdated] = @LastUpdate, [Manuf Date] = @ManufactureDate,Comments = @Commentwhere ControlNumber = @ControlNumberif @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelsereturn(0) -- successful updateinsertInventory:insert MAIN([Org Asset Tag], [S/N], [Description], Make, Type,Model, Status, Networked, LoginName, Shared,Org, [Date Of Record], [Date Last Updated], [ManufDate],Comments)values(@AssetTag, @Serial, @Description, @Make, @Type, @Model,@Status, @Networked, @LoginName, @Shared,@Org, @RecordDate, @LastUpdate, @ManufactureDate,@Comment)if @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelse return(0) -- successful insertdeleteInventory:delete MAIN where ControlNumber = @ControlNumberif @@ERROR <> 0beginset @processError = @@ERRORreturn @processErrorendelse return(0) -- successful deleteGO
View 4 Replies View Related
Hi,
I have a slight problem. I would like to pass some form of out of band data to every SP when called using ADO.NET.
A little more info is needed I think. I need to pass a unique identifier to ever SP my application calls and have that identifier available to use by the SP.
As my application has many many SP's in use I am trying to avoid adding an extra parameter to each and every one. I was really hoping for the ADO command object to have a property I could use to pass 'user defined' data to the database engine.
For example:
My SP's write to an audit trail table. The audit trail needs to record the user that executed the SP, this user might not be the same user that logged on to the database (depends on the connection string really). So, instead of adding an extra param to each SP, I want to access some data from the ADO command that was used to call the SP.
Can anyone help, or have I just confused everyone?
Graham
Still need help passing a criteria parameter query from a SQL Function (i.e. @StartDate) to a report header in Access Data Project where header = 'Transactions As Of [StartDate].
If anyone knows anywhere I can get help on this, I would really appreciate it. Thanks.
Is there a way to pass the "select all" option to a multi-select parameter from a URL string?
Thanks, Eva
Greetings,
I'm having SSIS block right now and thought I'd see if anyone had a simple solution to this.
What I have is SSN data coming in from several different tables, which needs to be inserted into a SurrogateSSN table which just uses an Identity field for the surrogate.
I normally build a seperate package for each detination table, so in each package that has SSN data in the source, I need to insert into the SurrogateSSN table (then I can use the surrogate values later in my package).
What I'm looking for is a simple solution if possible (preferrable using existing non-script tasks, or very short script), where I can pass in the SSN. Is there way to create a package that will accept a recordset (of SSN's) for this insert, that I can call from several different packages, and pass in the recordset of SSN's?
Thanks for your ideas
Hi,
This is pretty simple but I am doing somethin really stupid somewhere, I am trying to pass a value to a variable declared in a package using some SQL code, I declared a variable "var1" and assigned the scope to the package , then I dropped a execute SQL task within the package and wrote a simple sql code that would get the maximum id from a table, "select max(id) from table" , now I want to assign the variable this max value.so in the parameter mapping tab I select the variable give it a parameter name of 0 and size of -1 along with direction of "output". everythin looks simple but when I try to use this variable value in my next execute sql task it behaves weirdly, I am trying to update anothet table using this variable value, so I add another task and put this code in there " update table2 set id = ? " , and on the parameter mapping tab I set the direction as input, however when I look at the column it updated th column with a weird number , xomethin like "230072408" which doesnt mean anything. I know that tha max value of ID is 10.
Please help.
Thanks
I am passing System::ExecutionInstanceGuid to a stored procudure using the Execute SQL task (OLEDB connection). When it is being passed as a GUID (through the parameter mapping screen), the wrong value gets passed to the procedure. If i change the Parameter Data Type to VARCHAR, it passes the correct value.
This is also true for the PackageId.
Has anyone seen this?
Thanks,
Abe
I don't understand how to pass a parameter to a dts package from another. In SQL 2000 you can map a variable with a variable of the package you will call.
View 10 Replies View RelatedI was passing dynamic SQL to a parameter called 'SQL' and executing like =PARAMETERS!SQL.VALUE in the Generic Query Designer. Unfortunately my Dynamic SQL has grown to the point where it will no longer fit in a URL as it can exceed the 2048 limit. I've been tinkering with ways to get this into Reporting services using other methods but have thus been unsuccessful. Anyone have any ideas on this?
Hi all
I need to pass parameters when I call a .SQL file.
Is there a way to do this in SQL Server?
thanks!!!!
Hi guys...
As of now I'm working on a certian report which needs to pass a certain parameter to another dataset... I'm refering to the dataset of the same report. Can anyone tell me how this is done? I really need to know it to finish my report...
Thanks a bunch in advance!