I have a question. I have a standard query that retrieves the list of users from a database. The query uses "exist" statement and inside it I have another smaller query. The smaller query which used withing exist statement is called numerous times during the query execution. I am wondering if I can speed things up by storing result of a smaller query in a table rather than asking DB to run it again and again. The query takes over a minute to run and I am trying to find a way of speeding it up.
I having a strange problem, my code is as below: cmd.connection = cnncmd.commandtext = "select count(*) from member" dim i as integeri = cInt(cmd.executescalar) However, what the result tat i get is "&H0" ! When I use the same query in sqlquery, it did show out the result as "11".I have no idea abt wat is goin on, can anyone gv me some guide ? Thanks.
I am transferring data from msaccess to sql2005 using foreach loop. I have a table called Students and a column in it called State.
I want to store the value of the State in a variable in the foreach loop. I am using Dataflowtask to transfer rows from access to sql. what component can i use inside dft to store the value of State in a variable.
Can anyone tell me or point me in the direction of how I can store select query results to a variable in VB.NET? Im using the SqlDataSource control with dropdowns and textboxes for searching. I want to store the search results in a variable on the button click event.
As part of auditing of our application, I want to write the values of all column in one row out to an audit table both before and after an update to see what the changes were.
I know I can SELECT * FROM tableName FOR XML AUTO to generate a single XML 'column', but I cannot figure out how to capture that output and store it.
According to MS, you cannot use FOR XML in a subselect, or fill a TEXT variable with the results of such (which, IMHO really diminishes the usefulness).
I want to stream out the results of one entire row and store that in ONE column of an audit table. XML seems like the easiest way if not for this limitation.
I have image type col.I'm trying to do the following,DECLARE @Data varbinary(16)SET @Data = (select imageCol from Table1 where id=3)As image datatype returns varbinary value, so I want to store image col value to a varbinary variable(or any other type variable, eg., varchar). But getting following error,========================================Server: Msg 279, Level 16, State 3, Line 2The text, ntext, and image data types are invalid in this subquery or aggregate expression.========================================Is there anyway to store image datatype value to a variable?Cheers.
I want to declare a variable that will serve as my where clause. The variable will be passed in from our website. Does anyone have any information or can guide me in the right direction to do this?
example:
@variable varchar(1000)
SELECT * FROM table WHERE @variable
(The @variable would reflect the where clause string)
Hi there, I should prefix this message by saying, I am brand new to SQL so please be gentle
What I am doing seems pretty standard and I'd like some advice on the best way to proceed.
I am simply trying to add some customer information to my database. For this I have set up two tables. One for the Customer name, title, etc and then a related table for the address which is referenced in the previous table by a AddressID.
In the address table the AddressID uses an Identity. So what I'd like to do (which may not be the best way, if so tell me) is write a stored procedure to accept all of the customer information then INSERT a new entry into the Addresses table, then use DBCC to retrieve the current IDENTITY value and then add the remaining customer info into the Customers table using the IDENTITY value retrieved for the AddressID.
Now is that a total bass-ackwards way of doing this?
I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.
I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.
I am building a simple package that looks at the results of a sql query, prior to running the next step.
Basically Outline and settings
SQL task
Result Set: Full result set
SQL Source: Direct Input
SQL Statement: Select Count (*) FROM X
Parameter Mapping
Variable Name: User::C
Direction: Return Value
Data Type: Numeric
Parameter Name: NewParameterName
Result Set = NewResultName: User::C
Precedence Constraint
Evaluation Operation: Expression and Constraint
Value: Success
Expression: @C > 0 ( I originally tried User::C and received an error message)
Execute SQL Server Agent Job
If step one results greater than 1, then execute the SQL agent job (SRS Report) Using this setup I receive an error message stating "
TITLE: Package Validation Error ------------------------------ Package Validation Error ------------------------------ ADDITIONAL INFORMATION: Error at Package: The expression "@C > 0" must evaluate to True or False. Change the expression to evaluate to a Boolean value. Error at Package: There was an error in the precedence constraint between "Execute SQL Task" and "Execute SQL Server Agent Job Task". (Microsoft.DataTransformationServices.VsIntegration)
Any thought or suggestions would be greatly appreciated.
Does anyone know a way to store the ConnectionString of an OLEDB connection in a variable? I want to see exactly what values are in there because the step is failing with an error at that point. Here is what I'm looking for:
Data Source=;Initial Catalog=;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
I have duplicate records in table.I need to count duplicate records based upon Account number and count will be stored in a variable. I need to check whether count > 0 or not in stored procedure.I have used below query.
SELECT @_Stat_Count= count(*),L1.AcctNo,L1.ReceivedFileID from Legacy L1,Legacy L2,ReceivedFiles where L1.ReceivedFileID = ReceivedFiles.ReceivedFileID and L1.AcctNo=L2.AcctNo group by L1.AcctNo,L1.ReceivedFileID having Count(*)> 0 IF (@_Stat_Count >0) BEGIN SELECT @Status = status_cd from status-table where status_id = 10 END
I wrote a stored procedure that finds a number. I want to store the number it finds into a variable so i can use it within another procedure. I hope i'm being clear. Any help will be appreciated. Here is an example of how i am finding my number Employee is the name of my table and Number is the name of my column.
I have a dynamic query which returns me a result and I want to capture that value to make further use of it in the same code. Is that possible?? exec ('select col_nm from table_name'). i want the result of this query to be captured. DP
I have a few flat files that will be retrieved from some SFTP server. One of the flat file will act as a terminal file where it will specify the total number of records expected in each other the flat file.
Data in the terminal.txt FileName TotalRecords File1 1000 File2 1500 File3 2000
So, before transforming the data from the flat file sources into the target destination, i wish to do a row count checking for each of the flat file source to make sure that the number of records in the flat file source is tally with the number of records specify in the terminal.txt file. I'm able to get the number of records in each of the flat file by using the RowCount component but don't know how to get the data out from the terminal.txt file in order to make a rowcount comparison.
Can any1 help me on this? Or is there any other way we can do to make sure that the flat file source is alright before proceeding with the data transformation task?
name age weightaaa 23 50bbb 23 60ccc 22 70ddd 24 20 eee 22 30i need the output that calculate the sum of weight group by name input : age limit ex: 22 - 23 output : age total weight 23 11022 100 this output must stored in a sql declared variable for some other further process .
I have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.
Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1
I declared a nvarchar(max) variable and trying to saved the result of StoredProc1
I have one package through for each loop container I am loading all flat files into target table and its working fine.Now I my requirement is to capture the .txt file and loading into sqlserver table.Please check below file name.
CCCLOC_DDDLOC_LOC_20151203_240000_trigger.txt
from above file name I have to derived below detail.I have variable @File_Name which is used to stored the file name at rune time.Now I want to derived 5 values from the file name as below
I cannot find the data type for parameter mapping from Execute SQL Task Editor to make this works.
1. Execute SQL Task 1 - select max(columnA) from tableA. ColumnA is varbinary(8); set result to variable which data type is Object.
2. Execute SQL Task 2 - update tableB set columnB = ? What data type should I use to map the parameter? I tried different data types, none working except GUI but it returned wrong result.
Does SSIS variable support varbinary data type? I know there's a bug issue with bigint data type and there's a work-around. Is it same situation with varbinary?
Im trying to determine the best way to store data gathered from a form that a user will fill out online. The form is dynamic and is customized at run time based on group-specific criteria. The end result is a form that might have 3 extra text boxes, 2 extra sets of radio buttons and a freeform textbox, whereas for another group, there might be a slightly different set of input fields. Now comes the issue of storing this data. Since the fields can be somewhat dynamic, it could get tricky to define table columns for each possible input field. So Im considering storing the data as xml. Has anyone else had to build custom forms and ended up storing the data as xml ?
insert into #t(branchnumber) values (005) insert into #t(branchnumber) values (090) insert into #t(branchnumber) values (115) insert into #t(branchnumber) values (210) insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
In return I need to check column Status for whenever the value has changed and need to store that in my table. If there are two records for which value in column Status is same, I need to pick only one of the records and that being the earliest of them and therefore the Date field is mentioned in my table . My output should be something like below.
Simple example: declare @tTable(col1 int) insert into @tTable(col1) values (1) select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
