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 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.
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?
hi there, i m newbie in using the ms sql server.i wouldlike to learn how to write the store procudure.i hope u all can guide me on this. i have one table with data appid int (PK) leaveappid int (FK) firstapproval int firstapproval email varchar, 2nd approval int 2nd approval email varchar resutl firstapproval result secondapproval. i would like to do a automatic send mail function where: the first mail will send to the firstapproval email.if after 2 days, the firstapproval no response(the result firstapproval is empty) then the email will automatic send to the second approval. how can i write the SP for this case? i really have no idea to start since i duno how to write the SP syntax and dont have the concept too. hope can hear from u all soon. thanx
I have the following situation; I have one table (tblA) in which a new record just has been inserted. Once this insert is completed successfully, I want to insert a variable number of records into another table (tblB). The primary key of tblA is being used inside tblB as one of the columns in each insert. I’ve already been able to transfer the primary key, generated by the insert for tblA, pretty easy. But to make things a bit more complicated, the variable number of records to add is being decided by the outcome of a query based on an entry inside tblA (after the insert) and this is then being run on another table (tblC). The SELECT statement from tblC combined with the Select parameter from tblA will then decide how many records I have to insert. Sorry for the (perhaps) confusing way of writing this down, but I’ve been struggling with this for a couple of days now and I really need to get it working. Anybody who can help?Thanks in advance,Sunny Guam
I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.
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 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.
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.
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
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 have a table called dbo.test. This table is built with two columns, namely; col_id and col_name.. Now, I want col_id to be auto-populated when a new col_name is added. But the col_id - should be of formulae xyyyy
where x = 2
and yyyy = will be incremental
Therefore, the first 3 records will be like ;
col_id Col_name 20001 A 20002 B 20003 C ...... ...... and so on
Now, is there a way SQL can auto-populate teh col_id whenever a col_name is added?
I have a column that is not set to auto increment "IDX and Im inserting 800 part numbers but i want the IDX column to start at IDX 400 and increment 1 time per part number that is inserted. how can i accomplished that task.
EXAMPLE:
IDX PART#
400 abcde 401 fghi 402 jklm
etc. and so forth until the last part# will have IDX 1200...
Hello, I have a column that is not auto-incremented. I would like to make it auto-incremented. For the moment, the only solution I found is to delete my column and to add a new auto-incremented column. The problem is I cannot delete it without deleting dependencies on it... So is there a solution to make an existing column auto-incremented or is it necessary to a create a new column ?
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?
Hi There,I want to create a column that usto numbers upto a specified value, and the resets. I've tried using the identity column and then using DBCC CHECKIDENT, but this doesnt doesn't have the desired affect. Here's an example of what I'm after.AutoRow1234 -------- reset1234 --------resetAny help would be great, many thanksStuart
Hello, I have a table (publication) that has a primary key pubID which is an identity specification. This ID already has relationships to other tables. I am having problems trying to make one of the other columns in the same table to auto generate. This column (bibNumber) is not related to any other table. All i want to do is when i create a new record in the table, auto generate a number for this column bibNumber. How can i do this? I am using sql server express 2005 Regards
