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+']..SAVESET
SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentity
WHERE [IdPartition] = @IdPartition AND StoreIdentifier IS NULL
GO
Declare @PartitionItems int
EXECUTE 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 7
Invalid object name ''+@BaseName+'..SAVESET'
In this case the value is not passed into the @baseName-variable. What do I do wrong?
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 INTSet @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.SavesetIdentityWHERE [IdPartition] = ['+@idpartition+'] AND StoreIdentifier IS NULL') Server: Msg 207, Level 16, State 3, Line 2Invalid 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. Best regards, Tim
I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :
create procedure SelectDetails @Id string as Select * from DtTable where itemid in(@Id)
Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it. How can i solve this problem?
How do I pass system variables to a stored procedure? Is it possible to have an OLE DB transformation with the following sql command: exec InsertIntoLog @MachineName, @TaskName...? Do I have to use a Derived Transformation first to 'convert' variables into columns and then use exec InsertIntoLog ?, ? ...
I have written an XP for SQL Server 2000 SP2. It performs as expected if I call the XP with literal values for the parameters, however when I wrap the XP call into a regular stored procedure, only the first character of each input string is seen by the XP! Here are the relevant code snippets:
C++ Extended Stored Procedure:
(Basically all this code is doing is retrieving the parameters and printing them back out)
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.
Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SqlServerProject1.StoredProcedures.insertlogin(SqlString log, SqlString pas).No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[insertlogin]. ***************************************************************all i am trying to do is : creating a SP in VS using managed code and then trying to execute it. But every time i get the above error. If you can tell me how to edit connection string in this that would be very helpful. At present i am using : Using conn As New SqlConnection("context connection=true") I tried to do "" ALTER ASSEMBLY SqlServerProject1 WITH PERMISSION_SET=EXTERNAL_ACCESS""but i get this error "" Msg 10327, Level 14, State 1, Line 1ALTER ASSEMBLY for assembly 'SqlServerProject1' failed because assembly 'SqlServerProject1' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.""" *********************************************************************Plz help
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 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?
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!
We have a package with a package variable. This variable is of data-type 'DateTime'. However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'
/SET Package.Variables[MyDate].Value;"NULL"
What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...
I was wondering if it's possible to pass object properties to variables? For example, if I have a ConnectionString property for a SQL Server connection, would it be possible to pass this value to a User-scoped variable?
I have created lastUpdatedDate variable on package level. I have run a sql task and store a date in that variable.
now i am trying to pass that variable as parameter to oledb source connection (using command). it seems that we cant pass parameter in any sub query or derived table in query. its only working in outer query as soon as we place ? in WHERE clause of inner query it start throwing an 'Syntax Error' error saying that connection provider might not support that.
any idea ?????
I dont want to use command variables as my query is going to be quite big.
Note : I have tried Sql Server Native and OLEDB provider for sql server and this behaviour is seems to be constant in both.
im trying to display all of the results from a SQL database where the username is the same as the current user.currently, i have a long way around way of implementing this. i have a dropdownbox with a static entry (set at the page load) of the users name. i then tried to databind the gridview to the database and use the dropbox as the variable. however, for some reason, it doesnt acknowledge the username field and it doesnt return anything. i think this is because the dropbox is a static entry, but why does that matter? here is my current code: <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource3" EmptyDataText="There are no data records to display."> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:pas_db_connectionstring %>" ProviderName="<%$ ConnectionStrings:pas_db_connectionstring.ProviderName %>" SelectCommand="SELECT * FROM [files] WHERE ([submitter] = @submitter)"> <SelectParameters> <asp:ControlParameter ControlID="usernameDropBox" DefaultValue="none" Name="submitter" PropertyName="SelectedValue" Type="String" /> </SelectParameters> </asp:SqlDataSource> please understand that i am trying to set the @submitter variable equal to the current user's username. thx
I have an Execute SQL Task1 that executes an extraction stored proc (say spe). spe returns a rowset that has 25 columns. For each row in the rowset, a load stored proc (say spl) has to be executed (spl is executed using Execute SQL Task2). spl has 25 input parameters that match the 25 columns returned by spe (the column names returned by spe and input parameter names of spl are exactly same). To achieve this, in Execute SQL Task1, I had to specify a variable in the Result Set (say User::resultset). After declaring 25 variables, in the foreach loop editor, I had to specify the Variable Mappings of these 25 variables to the column indices of the rowset returned by spe. After this, in Execute SQL Task 2 I had to specify in the Parameter Mapping the mapping between the 25 variable names and 25 parameter names of spl. You can understand that it is cumbersome to define all these mappings manually, especially when there are a lot of variables involved. Is there some way of telling SSIS that it has to automatically map the columns returned by spe and the input parameters of spl (given that the column names and parameter names match exactly)? Or is there a totally different and simple way of achieving the above scenario? One more problem that I am facing :- In this package we are having a dataflow control which returns a recordset destination which has a column named ID(in the Input/output tab it is showing its datatype as DT_I8).This recordset is passed to a foreach container control through User::ID variable which is defined as Int64. While running the package we are getting an error like this: Error: ForEach Variable Mapping number 4 to variable "User::ID" cannot be applied. Error: The type of the value being assigned to variable "User::ID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. Actually both are related to mapping. I tried the second issue with just 1 variable as well to make sure there is no mismatch. Still, i face the same. Can anyone please provide a solution to this ?
I have a report that I'm trying to automate. Basically I want to create a cursor of containing authors, I want to run my report for that particular author, save the report, and move to the next author in the cursor. I want to continue this process until there's no more authors left in the table. Is this possible with reporting services 2005? If so how do I communicate the value from my cursor, to my report?
I've read the various posts and articles regarding this matter, but I seem to have problems getting to work:
In my control flow, I start by declaring a variable named "LastJobLedgerEntryID", to identify the records I need to add to the stage. From there I would like to use this variable in the source component in my dataflow, i.e.:
"SELECT [Entry No_],[Job No_],[Posting Date],[Document No_],[Type],[No_],[Description],[Quantity],[Direct Unit Cost],[Unit Cost],[Unit Price],[Chargeable],[Job Posting Group],[Global Dimension 1 Code],[Global Dimension 2 Code],[Work Type Code] FROM mytable WHERE [Entry No_] > " + @[User::LastJobLedgerEntryID]
But this fails? I should note that the variable LastJobLedgerEntryID is stored as a int32, and with the default value of 0
