Hello, I'm quite new to T-SQL, but since I'm trying to create a statistics page on database contents (Counting savesets in Enterprise Vault saveset Databases) I prefer to do the coding in the databases.
I create temp tables for the distinct partitions in the saveset table. Then I pass 2 variables to the EXEC function, but it seems unable to pass the ['+@idpartition+']-variable as a value:
Declare @EVBase varchar(20)
Declare @IdPartition INT
Set @EVBase=(SELECT EVMbxName from Servers)
Set @IdPartition=(SELECT TOP 1 Dist_Partitions FROM TEMP_EV1)
EXEC('
SELECT COUNT (IdPartition)FROM ['+@evbase+']..SAVESET
SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentity
WHERE [IdPartition] = ['+@idpartition+'] AND StoreIdentifier IS NULL')
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name '0'.
If I change the last line to: WHERE [IdPartition] = 2 AND StoreIdentifier IS NULL')
The script runs fine - but I need the value from the table. Any help will be appreciated.
In this line, @BaseName varchar(50) is polulated by a cursor that queries a table for names of other databases. In this first example it works as predicted: EXEC('SELECT COUNT (IdPartition) FROM '+@BaseName+'..SAVESET SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentity WHERE [IdPartition] = 0 AND StoreIdentifier IS NULL') If I create this as an SP (I want the output into another table) CREATE PROCEDURE GetPArtitionItems @BaseName varchar(50),@IdPartition int, @PartitionItems int OUTPUT AS SELECT COUNT (IdPartition) FROM ['+@BaseName+']..SAVESETSS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentityWHERE [IdPartition] = @IdPartition AND StoreIdentifier IS NULL GODeclare @PartitionItems intEXECUTE GetPartitionItems 'evmailboxstore1',0,@PartitionItems OUTPUT --EvMailboxStore1 is another table in the same database. I get: Server: Msg 208, Level 16, State 1, Procedure GetPArtitionItems, Line 7Invalid object name ''+@BaseName+'..SAVESET' In this case the value is not passed into the @baseName-variable. What do I do wrong? Thanks in advance - Tim Kuhnell
I've tried declaring and setting variables in my sql statement and then trying to use them instead of defining a column directly - sorry quite hard to explain, i'll do a simple example
eg
DECLARE @column DECLARE @value SET @column = 'col1' SET @value = 'bloggs'
Select * FROM table1 WHERE @column = @value
It keeps returning no results even though i've tried
Select * FROM table1 WHERE col1 = 'bloggs' -- which returns results
I realise its the column which is not being selected, but there must be a way by using a variable?
I would like to use variables to set the table name and some column names of a SQL Query in a stored procedure (the variable values will come from a webpage)... something like this:ALTER PROCEDURE dbo.usp_SelectWorkHours @DayName varchar,@DayIDName varchar AS BEGINSELECT @DayName.InTime1, @DayName.OutTime1, @DayName.InTime2, @DayName.OutTime2 FROM @DayName INNER JOINWorkHours ON @DayName.@DayIDName = @DayName.@DayIDName INNER JOINEmployees ON WorkHours.WorkHoursID = Employees.WorkHoursID END RETURN ...is this possible?? if so how? Thanks
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user)) BEGIN DROP PROCEDURE InsertTimeCard END go /********************************************************************************************************* ** PROC NAME : InsertTimeCardHoursWorked ** ** AUTHOR : Demetrius Powers ** ** TODO/ISSUES ** ------------------------------------------------------------------------------------ ** ** ** MODIFICATIONS ** ------------------------------------------------------------------------------------ ** Name Date Comment ** ------------------------------------------------------------------------------------ ** Powers 12/11/2007 -Initial Creation *********************************************************************************************************/ CREATE PROCEDURE InsertTimeCard @DateCreated DateTime, @EmployeeID int, @DateEntered DateTime, @SerializedXML text, @Result int output as declare @NewTimeCardID int select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards -- proc settings SET NOCOUNT ON
-- local variables DECLARE @intDoc int DECLARE @bolOpen bit SET @bolOpen = 0 --Prepare the XML document to be loaded EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML -- check for error IF @@ERROR <> 0 GOTO ErrorHandler --The document was prepared so set the boolean indicator so we know to close it if an error occurs. SET @bolOpen = 1
--Create temp variable to store values inthe XML document DECLARE @tempXMLTimeCardExpense TABLE ( TimeCardExpenseID int not null identity(1,1), TimeCardID int, ExpenseRptID int, ExpenseDate datetime, ProjectID int, ExpenseDescription nvarchar(510), ExpenseAmount money, ExpenseCodeID int, AttachedRct bit, SubmittoExpRep bit ) DECLARE @tempXMLTimeCardWorked TABLE ( TimeCardDetailID int not null identity(1,1), TimeCardID int, DateWorked DateTime, ProjectID int, WorkDescription nvarchar(510), BillableHours float, BillingRate money, WorkCodeID int, Location nvarchar(50) ) -- begin trans BEGIN TRANSACTION insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid) values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0) -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep) SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2) WITH ( ExpenseRptID int 'ExpenseRptID', ExpenseDate datetime 'ExpenseDate', ProjectID int 'ProjectID', ExpenseDescription nvarchar(510) 'ExpenseDescription', ExpenseAmount money 'ExpenseAmount', ExpenseCodeID int 'ExpenseCodeID', AttachedRct bit 'AttachedRct', SubmittoExpRep bit 'SubmittoExpRep') -- check for error IF @@ERROR <> 0 GOTO ErrorHandler
-- remove XML doc from memory EXEC sp_xml_removedocument @intDoc SET @bolOpen = 0
-- commit transaction, and exit COMMIT TRANSACTION set @Result = @NewTimeCardID RETURN 0
-- Error Handler ErrorHandler: -- see if transaction is open IF @@TRANCOUNT > 0 BEGIN -- rollback tran ROLLBACK TRANSACTION END -- set failure values SET @Result = -1 RETURN -1
What I'm trying to accomplish is to execute a SQL string via exec and inside it set the value of a local variable. I understand that I cannot do this the way I'm currently doing it because an Executed string runs in a scope of its own so local variables are invisible. And sure enough this is the error I get. So how do I make this work?
Code snip:
declare @ErrMessage as varchar(1000) set @Sql = 'if exists ( select * from ' + @TargetTable + ' where (' + @ValueField + '=' + '''' + @NewValue + '''' set @Sql = @Sql + ' and ' + @TagField + '= ' + '''' + @Tag + '''' + '))' + @CRLF set @Sql = @Sql + 'set @ErrMessage = ''Insertion could not be performed. ' + @NewValue + ' is already an entry in the table. '''
So what I want is check if a certain table has entries...what table? I don't know, there are tens that this check will apply to. And if that tavle has an entry that satisfies the where clause then assign the appropriate error message to @ErrMessage.
I understand sp_executesql might do the trick because it allows passing local params back and forth.
Any ideas on how to make this work? I appreciate the effort.
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
I have a cursor which populates a variable with the name of each user table within my DB. I'm trying to copy the tables one at a time by using a command like this: SELECT * INTO @NewTable FROM @OrigTable Query analyzer tells me that there's incorrect syntax near the keyword 'INTO'. This seems fair enough to me as I assume it's trying to put the data into the variable rather than the table name which is held within the variable. Does any know how I can get around this? Are there any alternative ways of copying the table structure (preferable without the data)?
I am writing an sproc for creating a table, but I don't know how I can use parameter values as object names (for example the name of the table needs to be [@sFile1+'_'+@sLinkName+'_'+@sFile2']. It seems that I could concatenate my whole CREATE TABLE string into a single variable and use EXEC to run it, but I'd prefer to be able to do it in the context of the sProc (I read that EXEC always has the current user's permissions). What is a good technique?
CREATE TABLE [@sFile1+'_'+@sLinkName+'_'+@sFile2] ( [GID_ID]uniqueidentifierROWGUIDCOLNOT NULL, ['GID_'+@sFile1]uniqueidentifierNOT NULL, ['GID_'+@sFile2]uniqueidentifierNOT NULL, CONSTRAINT ['LNK_'+@sFile1+'_'+@sLinkname+'_'+@sFile2] FOREIGN KEY(['GID_'+@sFile2]) REFERENCES [@sFile2](GID_ID) ON UPDATE NO ACTION ON DELETE NO ACTION NOT FOR REPLICATION, CONSTRAINT ['LNK_'+@sFile2+'_'+@sLinkname+'_'+@sFile1] FOREIGN KEY(['GID_'+@sFile1]) REFERENCES [@sFile1](GID_ID) ON UPDATE NO ACTION ON DELETE NO ACTION NOT FOR REPLICATION )
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.
Hi, I am new to using SQL for anything more in depth than querying and reporting.
I am trying to create a series of SQL scripts which will be used across several customer sites so need to be easily customisable. What I want to do is have all of the table names, field names and customisable items handled by variables which will be declared and set at the beginning of the script, making them easy to find and change. The problem I am having at the moment is with creating a new table using variables for table name and field names, can anyone help?
set @a_fieldid1 = 'newFieldid' set @a_tmptbl = 'newTable'
create table @a_tmptbl ( @a_fieldid1 varchar(15), value float, counter INT);
insert into @a_tmptbl values ( "foobar", 21.76, 1);
select * from @a_tmptbl;
The error I am getting is:
quote:Server: Msg 170, Level 15, State 1, Line 8 Line 8: Incorrect syntax near '@a_tmptbl'. Server: Msg 137, Level 15, State 1, Line 10 Must declare the variable '@a_tmptbl'. Server: Msg 137, Level 15, State 1, Line 12 Must declare the variable '@a_tmptbl'.
Hi, I have created a view. However this view needs a specific variable that a user has inputted in Access. The variable I need to pass into the first view is a DocketID..Similar to a StoreID. I then must run the second view, which also depends on the same DocketID. Finally I must run a final query that takes the result from the second view, which takes the results of the first view. Basically, I want to pass the variable from my VBA application to the SQL statement. I have been reading, and it seems that like in VB I must declare a local variable in a DECLARE statement. Is that right? How would I pass the variable that can be held in the VBA application ex. DocketId = 220 and pass that 220 to my SQL statement which is running is SQL 7.0. Most of the statement will be running in SQL, not the VBA application.
I am executing: -------------------------------- DECLARE @x AS int
SET @lcQuery = 'UPDATE ... WHERE .... ' + ' SET @x = @@ROWCOUNT' EXEC (@lcQuery)
PRINT @x -------------------------------- @x does NOT return a value, because it is "local" to the lcQuery execution. As a matter of fact, to execute it, I have to write:
SET @lcQuery = 'DECLARE @x AS int ' + 'UPDATE ... WHERE .... ' + 'SET @x = @@ROWCOUNT' EXEC (@lcQuery)
How can I pass the variable @x to the progam from within EXEC (@lcQuery)? How does EXEC (@lcQuery) execute? In a different space?
Does any one know how to pass variables to a stroed procedure within a trigger and does anyone know how to capture a value from a stored procedure and store that value in a variable all within a trigger.
If anybody could help me with this i would love to hear from you, at the moment this is driving me crazy?
now i have a stored procedure in SQL server 2000, which i need to pass some variables into (pretty straight forward so far!!) I am copying tables from one database to another and i what to be able to choose the spefic database i'm copying to, this is the basic's of my query!
Anyone,Is this possible?I am connecting to a TeraData server via MS SQL 8.0 using the OpenQuerystatement. I need to pass a list of ever-changing deal numbers Mylist of numbers are stored as a table on MS SQL.So what I want is thisSelect * from OpenQuery(TeraSrvr, "Select Col1, Col2, Col3[color=blue]>From Teradata_Table_1[/color]Where Deal_no in (Select Deal_no from SQLTable)")Now I know that wont work, but How can I pass 184 Deal Numbers from mySQL server to this query before it is sent to the Teradata server to bedone? Do I have to keep re-doing an in statement each month?Anyone can help?Doug
I have an SSIS package. In my control flow I have an Execute SQL Task and a data flow task. In my Execute SQL Task in the SQL Statement I have (select dbo.to_date(getdate()) as process_date) its a direct input with a result set. It gets Getdate as (processed_date) is declared. I have set no parameters, but I have set a Result Set. Also, I have set a global variable in the scope where I passes the date (processed_date)
In my Data Flow, I have an OLE DB data source with the following SQL statement in my SQL Command. I am trying to pass down the variable as the ? .
It works when I pass it only to: lr.processed_date = ?
But I get an error when I pass it down to the : and ? between cav.begin_date and cav.end_date
SELECT distinct acc.account_num,
FROM cust_version_slow cvs
inner join cust_acco_version cav on cus.cust_id = cav.cust_id
and ? between cav.begin_date and cav.end_date
inner join bia.dbo.acct acc on cav.acco_id = acc.acco_id
inner join bia.dbo.account_version_slow avs on acc.account_id = avs.account_id and ? between avs.begin_date and avs.end_date
inner join bia_org ('02','2,3,4,5,6,7,9,10') boh on cvs.branch_code = boh.branch_code
inner join accou_version_profit accM4 on acc.acco_id = accM4.acco_id and ? between accM4.begin_date and accM4.end_date
where
lr.processed_date = ?
I need to pass down the same variable (processed_date) which is ? in all the ? in red
In our project users log in and are assigned a GUID. The GUID is stored as a session variable that is used for filtering what a user sees on a page/report etc.
We have a report in which there are 2 parameters (Drop Downs).
Drop Down 1 lists the Entities a user can see (this is filtered by the GUID that is passed to the backend) and this works fine. Drop Down 2 lists the products a user can see within Entity (this is filtered by the same GUID and also the selected value from DDL1.)
Here€™s the dilemma, how to we pass 2 variables into DDL2, when one of the variables comes from DDL1, and the other is passed by the URL?
hai <br> <p>i have a procedure where in which i want to access a specific table whose name will be passed into procedure as argument. Here is the peice of code i have made.</p> CREATE PROCEDURE outputtable @tablename nchar(10) AS<br> begin<br> .........................<br> insert into @tablename (classno) values (@cno)<br> .................................<br> END<br>
<p> I get the following error message :</p> Msg 1087, Level 15, State 2, Procedure................<br> Must declare the table variable "@tablename".<br> <p> Is there anyway i can get around this one. I will be grateful if anyone can help me</p>
i'm trying to create a stored procedure that takes 2 input parameters (taken from a querystring): a table name, and a number that needs to be checked whether it exists within that table. yes, i have different tables, one for each forum, so they will have the same structures -- i'm figuring this approach will result in faster page-load times if the tables have less in them. if everyone here tells me that having one table for all the forums will work fine, i'll just go that route, but if not, here's my procedure:
Create Procedure VerifySubjectNum (@forum_ID VARCHAR(10), @subject_num INT) As If Exists (SELECT subject_num FROM @forum_ID WHERE subject_num = @subject_num) Return 1 Else Return 0
when i try to create this, i get an error saying @forum_ID must be declared... why doesn't it work within the select for the EXISTS?
Hello. I need to store 2 values that i retrive from a databse into variables so i can check them towards what is typed into the textboxes for my login script iv built. How can i do this?1 string myConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersalhoDocumentsIntrapointWebApp_DataIntrapoint.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"; 2 3 SqlConnection myConnection = new SqlConnection(myConnectionString); 4 SqlCommand cmd = new SqlCommand("SELECT [username], [password] FROM [company] WHERE (([username] = @username) AND ([password] = @password))"); 5 cmd.Parameters.AddWithValue("username", TextBoxUsername.Text); 6 cmd.Parameters.AddWithValue("password", TextBoxPassword.Text); 7 8 myConnection.Open(); 9 10 //how do i pass the values retrived into variables, like string usrname and string password? 11 12 myConnection.Close();
Similar to a previous thread, but not exactly. I have a batch file that takes two date parameters. I have a SQL script that gets the values I want and assigns them to variables, but I don`t know how to pass these to the batch.
I`m thinking of something like:
declare @myvar1 datetime, @myvar2 datetime
select @myvar1 = <some date value> select @myvar2 = <some date value>
This just errors out when it reaches the variables in the last line. If I move the variables inside the quotes, it passes them as literal text strings, not as the assigned datetime values.
Hi!! If I have declared a variable, such as char variable_name[x], and have assigned it a value, how do I go about passing this value to an SQL query such as: SELECT * FROM variable_name where my value replaces variable_name. Cheers, Marc
passing variables (values) from a form (MS Access Project) to a stored procedures in order to select records shouldn't be all that difficult. However after searching for hours and hours in various forums and discussions i'm still nowhere. I really hope someone can give me the missing hint.
OK All I whant to do is select data from the sql server via an access interface. the value entered into the access form by the user (for example the client name) should then be transferred to the stored procedure which will then return all the records of the chosen client.
The simple task of transferring this form value to the stored procedure is driving me crazy. Does anyone have an idea.
I am familure with having a report run based on users inputting variables via DDL or multiselect using SRS. But now want to access my reports a little differently. I have an ASPX page and I want a user to click a button and to have the report use the Session Variable that is stored in ASP. As a result the report will be prefiltered on data the specific user can see.
I'm developing an SSIS package that will theoretically be run in two different ways. One is as a nightly job, called by a SQL job. The second is from a web application. When run as a job, it will be looking for a flat file in a known location. Run from the web, the file name and location are determined at runtime. I've tried to handle this by using a variable to hold the flatfile connection string, and setting the variable value to the known location. Then, when called to the web, I determine the file location/name at runtime, and pass it to the package. I'd assumed that if I pass the value into the package, it would override the variable's value that I'd set, but this doesn't seem to be happening. I realize this could very well be due to a coding error, but I thought I'd check to see if this is even a viable approach to be taking. If a variable's value is set in the package itself, will it be overwritten by a value passed to the package (assuming no stupid mistakes)? Or is this entirely the wrong approach to be taking for what I'm trying to do?
I would like to pass variables to the package before it is executed (e.g. I am calling the bcp utility and I need to pass a password to the command line), so that at runtime a variable is set and then used.
Does anyone has some hints for good approaches? Every idea is welcome.
FYI: I do not use the bulk insert task as I need an errorlog file and the command is buggy with the errorfile option, therefore I chose the bcp approach. My connections are dynamic as far as servername and userid is concerned, but I did not find a solution for the password issue.
When executing a package from Business Intelligence Studio variables are well passed between packages but when same is done using dtexec utility variables don't get passed between packages. Does anyone have any idea why this could be happening and what would be possible solutions? Thanks in advance!
