I am having problems opening a table from within my CLR UDF. Can anyone provide the proper configuration so that I can query existing tables?
I had originally posted to another area of the forums, but have not been getting any responses.
The original thread is here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2363760&SiteID=1
I have a requirement to access a lookup table from within an SSIS Transform Script Component
The aim is to eliminate error characters from within the firstname, lastname, address etc. fields by doing a lookup of an ASCII code reference table and making an InStr() type comparison.
I cannot find a way of opening the reference data set from withing the transform.
Regards
Tim
Hi All,
In one of my ScriptTasks, I instantiate FileSystemWatcher class and set events for it. This has been done inside the Main() method. I can access all the varibles declared in the SSIS package inside the Main() method (by using Dts.Variables("").Value) but none can be accessed inside the event methods. One possible reason for this can be, events might be running in different threads.
Now my question is, How the varibles can be accessed inside the event methods.
Hope someone can give me a solution. Appricate all your solutions.
Thanks
I am writing a Sql Function in CLR. That function receives some data for processing. For processing the data, the function requires some additonal data to be fetched from the same database.
So, how does a CLR function execute SELECT or other Sql statements (or does it need to open up a SqlConnection for the purpose)???
Hi,
I have a variable in SSIS that I want to access inside the Script Task. I assigned the variable in the ReadOnlyVariables in the Script Task property. How do I access it?
cherriesh
Dear Technical Team,
I am facing an error while displaying a sql reporting service report on my asp.net page. The report it self is build using Report server project. It consists of a Main report which contains a bar graph. Upon clicking on the bar graph, it takes you to another report (i have given in the jump to report section), call it Report 2. Upon clicking on Report 2 it shows another report say Report 3. It is displayed properly as long as i run the preview of the report in my report project. When i deploy this report on the report server and trying it to access using my asp.net code behind. The main reports runs very fine. Upon clicking, it does not do anything and when i click again it display error, the report does not contain any thing. Sometime it does not show even the error. But just could not drill me in.
I presume on the click event of the report which is a char item, it calls the page load event, and it loses it parent report information or something like that. Please can any body guide me how to do this.
The following functionality i need to achieve with my web page.
My main report : Main report :upon click on any char item, it should take you to the second report.
My second report : and on clicking on charitem on the the second report, it should take me to the third report.
Please help
Regards,
Nazim
Hi
I´m a SysAdmin and I´m implementing ASSP (anti-spam) to use with an email server.
But the ASSP requires a txt file with all domains hosted in the server that are in a SQL Table of the server.
My questions is: Is there anything to make a real time TXT file with all the domains ?
maybe use trigger I dont know..
Hello all,my question might be trivial but since my background isn't DB i'll dareto ask it any way:how to create a real (not virtual) table from view1 & view2 ni sqlserver?what should i do specificly in the sql server application & what is thesyntax for that?i thank all of you,groupy.
View 2 Replies View RelatedI have a report where in I want to show each record on a separate page.
So, to achieve that I took a single cell from table control, expanded it and used all the controls in that single cell. This looks nice so far.
Now, I also have to show a sub grid on each record. So I took a table control and added on the same single cell and tried to add a parent group to the table row.
When I preview, it throws this error.
"The tablix has a detail member with inner members. Detail members can only contain static inner members."
What am I doing wrong? How can I achieve table grouping inside a table cell?
Hi,I use view to join difference table together for some function. However,when the "real" table fields changed (e.g. add/delete/change field). Theview table still use the "old fields".Therefore everytimes when I change the real table, I also needed open theview table and save it by SQL enterprise manager manually for update theview table field.Can we use a SQL command or other method to update it directly?Regards,Silas
View 4 Replies View Relateddear list can anyone figure out a workaround as to why OLEDB Provider MSDORA cannot store passwords
I have all the info stored in a table (Project Real Best Practise)
The user id I have stored in string ConfiguredValue gets transfered to OLEDB Provider MSDORA named (SQL_REAL_Source_myoradb) but not the password
To workaround this bug only with MSDORA can aynonne sugest a setting I should use in package security
ie the default is EncryptSensitiveWithUserKey?
thanks Dave
CREATE TABLE [admin].[Configuration](
[ConfigurationFilter] [nvarchar](255) NOT NULL,
[ConfiguredValue] [nvarchar](255) NULL,
[PackagePath] [nvarchar](255) NOT NULL,
[ConfiguredValueType] [nvarchar](20) NOT NULL
) ON [PRIMARY]
ConfiguredValue = Provider=MSDAORA.1;Password=abc;User ID=myuserid;Data Source=myoradb;Persist Security Info=True
PackagePath = Package.Connections[SQL_REAL_Source_myoradb].ConnectionS
For inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.
Hello, inside of my SP i want to execute another SP, something like:
EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID
this function Forum_DeleteBoard returs one row with 3 columns as a table, how do i get the first column of that table into a variable so i can check if it was ok or not(it returns just one row with 3 columns).
Columns it returns:QResult , Threads , Answers
SELECT @isok = QResult FROM EXEC [dbo].[Forum_DeleteBoard] @BoardID = @DelBoardID ?
or how do you get it?
Patrick
i ve got a database that has a table...that table has a relationship between its primary key,and another field,actuelly i did it for doing menus and sub menus,so each menu has an ID say menuID and it has DEPTH and parentID which is the menuID of the parent...the problem is that i can not use "Cascade update Related Fields" or "Cascade Delete Related Records" which are really necessary ...for example when deleting parent ,not to have a child lost :)i hope i ll have an answer soon,and thanks in advancedPS: i am using MSSQL 2000 evaluation
View 4 Replies View RelatedI need to find a way to query a value stored inside a table column. I have a piece of sample script that looks like this:
select convert(xml,[VALUE]).value('(/ROOT/app/@S_YEAR)[1]','numeric(4,0)') as S_Year from myTble
where [KEY] = 'StringA'
The myTble has 2 columns, Key, Value, and the Value is of data type text. The data stored in there is a large xml node or doc. The table has only three rows for StringA, StringB, and StringC.
I feel bad that I cannot post the xml here because it contains a lot of sensitive data. It will be very hard for me to replace those sensitive data without breaking the syntax integrity.I used to know XPath, but couldn't make out above Select.
I wanted to insert values in columns as explained in below ex.
I am having a table that contains Column1,Column2,Column3,......,Column10.
Inside my for loop, i am getting Column1 value then Column2 then Column3 values and so on till Column10.
My requirement is that on each iteration,I wanted to insert value of Column1 in field Column1, value of Column2 in field Column2 and so on.
Hi,
we have the senario we want to call oracle table inside the sql stored procedure, it is possible to call oracle table inside the sql stored procedures.
Regards
Kannan.D
Desikankannan
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)
INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Any suggestions?
hello All,
I am trying to now access the data (if any) in the current cell of a table. I want to know if this cell has been populated, and if so to move on...if not then I want to populate with a zero.
I am using the following:
ReportItems!textbox9.Value inside of an if statement but it tells me that I am unable to do so as, and I quote:
"The Value expression for the textbox 'textbox 9' contains a direct or indirect reference to itself. Loops in expressions are not allowed."
Any ideas guys?
Hi All,
I am using a trigger. I want to get the data of a row before updating inside this trigger and insert it into a backup table. Please anybody help me. Example with code is highly appreciated.
Thanks in advance.
I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:
Error 208: Invalid object name '##OPTIONSEX'
I am using the following SQL script:
CREATE PROCEDURE [dbo].[Test2] AS
CREATE TABLE ##OPTIONSEX
(
OPTION_PLAN VARCHAR(50),
TOT_OPTIONS_EXCHANGED FLOAT NULL
)
GO
INSERT ##OPTIONSEX
SELECT
B.COMPONENT,
TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B
WHERE B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT
GO
Any help getting this to run correctly would be appreciated.
I have a need to show a "header" row inside a table group. The row will look like a header but the table has no true Header or Footer. The table will group by the a person's name and inside each group will be a set of rows. If I put a row between the group row and the inside data row, it will show for each row inside the group. I only want this "header" row to show once for each group and before any of the actual data rows in the group.
View 3 Replies View RelatedHi ...I have successfully installed Db2 client configuration tools in sql server and then created a link server to db2 server . I am correctly selecting IBM db2 provider through provider name dropdown in create link server dialog box (general tab) . However , I am unable to fill the data for 3 requirements ...1)product name (Is this db2 database name ?) 2) Data source (What is this ) 3)provider string ...(I tried various combinations ....But didn't succeed)(The msdn has given example of creating linked server like <code>
This example creates a linked server named DB2 that uses the Microsoft OLE DB Provider for DB2.EXEC sp_addlinkedserver
@server='DB2',
@srvproduct='Microsoft OLE DB Provider for DB2',
@catalog='DB2',
@provider='DB2OLEDB',
@provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=XYZ;Network Port=50000;Package Collection=admin;Default Schema=admin;'
</code>
Then in Security tab I gave remote login and password However I am unable to get tables view for the db2 database in sql server . It is showing errors like initialisation .....Provider DB2OLEDB etc.... Can anyone pl help .Is there any other way ?Bhat
We are trying to use a ## table (global temporary table) in Access or Excel thru a ODBC connection to tempdb. We are able to see the system tables, but not any ## tables. We are able to perform selects on the same ## table with the same userid and password in Query Analyzer. If we use sa or make the userid dbo we are able to see the ## tables, but I don't want to give these type of permissions.
Any help would be appreciated. Thanks in advance.
Hi,I have a need to process a column of data and get information from SQL2000 and return it to the Excel Spread Sheet.The data will be a list of order numbers in column 1. I need to lookup each order number and return infomration relating to the order fromSQL.Can anyone point me to the right process?thanks in advance,
View 1 Replies View RelatedHi all,
Can anyone tell me how to access all the rows in a table using script task?
I want to access each row in a table get their values and put it in a global variable. Can anyone hwlp mw with this please.
Thanks in advance,
I have an application where the columns in a datatable stored in a variable (as a dataset with one table in it) are dynamic. This means that the number of columns and their data types will vary. Also, I would like to access the column names. For these reasons it is not very practical to map the columns to variables.
Is there a way to use a Script Task within a For Each Loop (with ADO enumeration) to read the data row by row (presumably as a datarow) getting the column names and coloumn values by numeric (column) index?
Thanks,
*** Campbell
For some reason whenever I look at the SQL statement of a particular table, the table name displays twice.For example,SELECT * FROM State StateEven when I execute this statement, it still returns the correct results. It does this for all tables in this particular database. I also check another database and thoses display the table names in the SQL statements correctly. Does anyone know why the table name would display twice in a table inside of a particular database?
View 4 Replies View RelatedI'm trying to create a query that searches an entire database for keywords inside of the columns for each table within the database. For instance my tables have 2 columns one named ID and the other Permission, I'd like it be able to return all the lines that are associated with that keyword. So if I search "Schedule" it returns all the lines containing that word in it within that database.
View 6 Replies View RelatedIf I create a memory table inside a while loop, I expect that every loop the memory table is (newly) created and thus empty.
But when I test this, I see that on the second, third, ... run the inserted data in the memory table is still present and not cleared.
This can be reproduces with the following code:
DECLARE @tbl TABLE
( [id] BIGINT )
DECLARE @tbl2 TABLE
( [value] BIGINT )
INSERT INTO @tbl
VALUES (1), (2)
[Code] ....
/*
Expectation: twice 11 and 12 in @tbl2
Actual result: three times 11 and 12
*/
SELECT *
FROM @tbl2
Is this a wrong assumption or is this a bug in SQL Server? (Tested on: SQL Server 2014 and 2008).
I am trying to access data from a database to print a report. The code I am using says I have a syntax error regarding the object person_info. Person_Info is a table value function on the SQL Server (2005). Does anybody see a problem with this line of code? Thanks for your help! Regards, Steve
"INNER JOIN (SELECT * Person_Info FROM (" & Session("current_project") & ")) ON pc.personID_fk=pe.personID_pk " & _
I passed .net datatable from a .net app to a store procedure. From this store procedure, how to code to bulk insert (or another way) to SQL table?
View 7 Replies View RelatedHello,
Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?
Sincerely,
James Simpson
Straightway Technologies Inc.