Lets say I have a User Defined Function. I need to call the UDF a number of times within the same View. However, the results are not as expected. The only way (I know of) is to have seperate instances on the same function saved under different names. Is there a way to call a single UDF multiple times within a View without the returned values being messed-up?
I have basic SQL query that returns a one column result set. For each row returned in this result set, I need to pass the value in the column to a stored procedure and get back a result set.
I have 2 solutions, neither of which are very elegant. I'm hoping someone can point me in a better direction.
Solution 1: Use a cursor. The cons here, are the SP returns a result set on each pass which generates multiple result sets overall. If there is a way to combine these result sets, I think this solution might work.
Solution 2: Use a temp table or table variable. The cons here are, if the schema of the result set returned from the stored procedure changes, the table variable will have to change to accommodate it. This is a dependency I'd rather not create.
I am trying to create a logon trigger. As I am testing this, I discovered that each time I do a connection, I get 19 rows, inserted into my audit table. I ran profiler, and I see it is going through the logon trigger multiple times, for a single connection. So, what am I doing wrong? The code is fairly simplistic, and the profiler doesn't give a clue, as to what is going on. When I look at the output, I see the spid for the first couple of connections are different, then a spid, that is different from those 2 is in the next 17 rows. But, when I do an sp_who2, that spid does not exist.
This issue was noticed on a 2012 version, that I was first testing on, then had the same issue on a 2008 R2. I am currently testing on a 2014 version, that is doing the same thing. Is the logon trigger itself, firing, and causing this?
I also tried using the After Logon option, and got the same issue.
Here is the code:
CREATE TRIGGER LogonAuditTrigger ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN DECLARE @Body NVARCHAR(2000),
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set tables multiple times
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me?
i have a stored procedure with one coming id parameter
Code BlockALTER PROCEDURE [dbo].[sp_1] @session_id int ...
and a view that holds these @session_id s to be sent to the stored procedure.
how could i execute this sp_1 in a select loop of the view. I mean i want to call the stored procedure as times as the view has records with different ids.
I have a situation where I run the same taks multiple times during the execution. I would like to have one task which runs every time, instead of duplicating the task over and over 14 times in my script.
Basically, it is an audit log, which I set variables and then insert into a SQL table the variables.
I would like to do this:
Task1 ------Success-----> Set Vars -----Success--> Log | Task2 ------Success-----> Set Vars -----Success-| (do the Log task again) | Task3 -------Success-----> Set Vars -----Success-| (do the Log task again) | etc
This works, however, I have to duplicate Log over and over and over. No OR does not work, because it still only executes the Log task once.
Another option I thought of, but cannot find a way to implement is: Make the Log task "disabled" with no dependencies, then in the Set Vars script, enable and execute Log and disable again.
After doing some research it seems like you can only push the same table once using rda.push -- is this correct? If yes, are there any other alternatives for saving changes to the table back to SQL Server aside from merge replication?
One idea I am toying with is to pull the tracked table with 0 records, save changes to the tracked table, push, drop table and pull, repeating this process everytime I push the data. Wondering is somebody has any advice?
In SSIS 2005 what component can I use inside my Data Flow task to call my View and use it as a datasource? I know I've used an Execute T-SQL Task but I don't know what component to use inside an actual task itself. What I'm trying to do is in my Data Flow task, instead of using 3 OLE DB Source components, I want to actually call 3 stored procs and use those 3 components as the data sources to merge on using the merge and sort components later on
Hey all, I am still pretty new to all of this and I am having problems accessing a the same DB twice in my page. I want to pull information once in one spot, but then pull different information in a different spot on the page. Anyway, in the first spot, I have the following code: Dim conString As String = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString Dim con As New SqlConnection(conString)Dim cmd As New SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con con.Open() Dim RS As SqlDataReader = cmd.ExecuteReader() While RS.Read() blah blah blah End While End Using
Then in my other spot on the page I have the following: Dim cmdresults As New SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname") Using con con.Open()Dim RD As SqlDataReader = cmdresults.ExecuteReader() While RD.Read()%> <tr> <td><%=RD%> </td> </tr> <%End While End Using When I try to execute I get this error, "ExecuteReader: Connection property has not been initialized." on the following line, " RD As SqlDataReader = cmdresults.ExecuteReader()" Any ideas? If possible a little explanation on how multiple connections to the same database work would be nice just for future reference. Thanks in advance!!, Chris
I need to create a linked server that can access more than one database. I assume, the only wat this can be done by creating two separate links from the local server using Microsoft OLE DB Provider for SQL server. But the problem is the Server Name. I cannot use the same servername twice and if I use a name that is not an exisiting SQL server name I get an error that "server not found".
So, how do I addlink the same server a mulitple times to access different databases in the server?
First off, I'm new to SQL Server and apologize if this is a trivial question.
What I'm trying to do is alias the same table with 2 different names so that I may join them on two different fields.
Table1 Emp_Number Emp_Code_1 Emp_Code_2
Table2 (Contains a list of codes and their related descriptions) Emp_Code Long_Desc Short_Desc
I'm trying to query Table1 for the Emp_Number but I want to get the Short_Desc from Table2 for both Emp_Code1 and Emp_Code2. I'm using Microsoft Access as the front end (using Pass Through Queries) and SQL Server on the back end.
I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?
E.g. I have a table with 2 cols - amount, date.
SELECT total_amount, closing_amount, FROM table1 GROUP BY month(date)
Total amount is the SUM(amount) for the month. Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.
As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?
This is only a basic example, what I'm trying to do will invovle a lot more calcultions.
I'm new to SQL Reporting Services but have made some decent headway. I'm stumped by one issue though: How do I repeat a field over and over on a single line?
To elaborate:
I have a table of backup job failures, with a reason and the time it failed. ID | Reason | Time 1 | No tape | 3/13/3008 2 | Bad drive | 3/14/2008
I'm trying to create a summary, and I want to list the information as follows:
There were 2 failed backup jobs on 3/13/2008, 3/14/2008
My SQL query is as follows: SELECT Time, COUNT(*) AS NumFailures FROM FailedBackups GROUP BY Time ORDER BY Time ASC
How do I "loop" the Time field on a single line in Reporting Services?
I am trying to update the same row of the table multiple times. in other words, i am trying to replace the two stings on same row with two different values.
Example: if the column has a string "b" then replace with "B" and if the column has a string "d" then replace with "D" . I can write multiple updates to update it but i was just wondering if it can be done with single UPDATE statement
column before the update : bcdxyz after the update: BcDxyz
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
I’m binding the distinct values from each of 9 columns to 9 drop-down-lists using a stored procedure. The SP accepts two parameters, one of which is the column name. I’m using the code below, which is opening and closing the database connection 9 times. Is there a more efficient way of doing this? newSqlCommand = New SqlCommand("getDistinctValues", newConn)newSqlCommand.CommandType = CommandType.StoredProcedure Dim ownrParam As New SqlParameter("@owner_id", SqlDbType.Int)Dim colParam As New SqlParameter("@column_name", SqlDbType.VarChar)newSqlCommand.Parameters.Add(ownrParam)newSqlCommand.Parameters.Add(colParam) ownrParam.Value = OwnerID colParam.Value = "Make"newConn.Open()ddlMake.DataSource = newSqlCommand.ExecuteReader()ddlMake.DataTextField = "distinct_result"ddlMake.DataBind()newConn.Close() colParam.Value = "Model"newConn.Open()ddlModel.DataSource = newSqlCommand.ExecuteReader()ddlModel.DataTextField = "distinct_result"ddlModel.DataBind()newConn.Close() and so on for 9 columns…
I am importing xml multiple times a day from a vendor. However when SSIS created the ID's for nested XML data it is not unique. So importing the first time and I get 3-4 records it looks fine. However subsequent imports all use the same ID's so it isn't unique, how do I go about changing this as I cant find anything about it.
we have a table in database , which has multiple triggers defined by Vendor. I have created our own custom trigger on this table also , which fires last. Goal of this custom trigger is to send an email, when ever update happens on table. But somehow trigger is firing multiple times for same update, so it is sending multiple email for same update also
Hi all, In my project, I have a website and through that, I run my reports. But the reports take a lot of time to render. When I checked the profiler, it showed that the SP for the report is run around 4-5 times. Due to this, the report rendering takes a lot of time.
When, I ran the SP with the same set of Parameters in Query Analyser, it ran in around 18 seconds. But when I ran the report from web interface, it took around 3 minutes to completely show the data. And the SP has been run 5 times.
I am having serious problems with Report's performance because of this. Many a times, report just times out. I have set the timeout as 10 minutes. And because the Sp is run 5 times, the report times out, if there is huge amount of data.
I have an SSIS package that contains a For Each Loop Container. I have three Data Flow tasks within the container. I have an OnError event handler associated with the encapsulating container. When one of the Data Flow tasks within the For Each Loop Container fails, the OnError for the Loop Container gets called 5 times. The OnError handler is just a script task that sends a notification email. I am not explicitly Dts.taskresult = failure, nor am I calling FireError.
I want the below query to run 24 hours ..once the insert is complete, run again , so on for 24 hours .
there is a way to run every second in as job but i want to run only after run complete ..is there a way to run the query after every complete run ? and keep in job
INSERT INTO [dbo].[Audit_Active] ([SPID],[LoginName],[HostName],[ProgramName],[Command],[LastQuery],[DBName],[ServerName]) SELECT --DISTINCT p.SPID, p.LogiName, p.HostName,
I am recently encountering proble with SSAS cube,In a day cube is going to offline for several time and unable to browse it and after some time automatically cube is getting online.I am unable to figure it out what is happening.
FYIP..For every 15 Min cube will be Proccessed Full.
Ok, now for my not so happy new year problem ... here's the scenario:
I have a simple task of exporting data retrieved by a parameterized stored procedure to a delimited text file ... task done in 30 mins max using dataflow task from OLE DB Source with SQL Command to Text File Destination ... thank you very much SSIS!
But the problem is the OLE DB Source executed the SP MULTIPLE TIMES (3 to be exact) Actually configuration is:
1. OLE DB Source 2. SQL Command access mode 3. The following command written in the command text box:
The stored procedure logs to a database table everytime it is executed to report how many rows were selected (please don't crucify me with this method, i'm re-using procs from old system). The log shows that during execution of this package the SP was executed on 3 times.
I have created a single FULLTEXT on col2 & col3. suppose i want to search col2='engine' and col3='toyota' i write query as
SELECT
TBL.col2,TBL.col3 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col2,'engine') TBL1 ON
TBL.col1=TBL1.[key] INNER JOIN
CONTAINSTABLE(TBL,col3,'toyota') TBL2 ON
TBL.col1=TBL2.[key]
Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.
I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information. for e.g.; i was thinking to concatinate both fields like col4= ABengineBA + ABBToyotaBBA and in search i use SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key] Result = 1 row
But it don't work in following scenario col4= ABengineBA + ABBCorola ToyotaBBA
SELECT
TBL.col4 FROM
TBL INNER JOIN
CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1 ON
TBL.col1=TBL1.[key]
Result=0 Row Any idea how i can write second query to get result?
I have a single update statement that updates the same column multipletimes in the same update statement. Basically i have a column thatlooks like .1.2.3.4. which are id references that need to be updatedwhen a group of items is copied. I can successfully do this withcursors, but am experimenting with a way to do it with a single updatestatement.I have verified that each row being returned to the Update statement(in an Update..From) is correct, but that after the first update to acolumn, the next row that does an update to that same row/column combois not using the updated data from the first update to that column.Does anybody know of a switch or setting that can make this work, or doI need to stick with the cursors?Schema detail:if exists( select * from sysobjects where id = object_id('dbo.ScheduleTask') and type = 'U')drop table dbo.ScheduleTaskgocreate table dbo.ScheduleTask (Id int not null identity(1,1),IdHierarchy varchar(200) not null,CopyTaskId int null,constraint PK_ScheduleTask primary key nonclustered (Id))goUpdate query:Update ScheduleTask SetScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.')FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0Query used to verify that data going into update is correct:selectScheduleTask.Id, TaskCopyData.Id, ScheduleTask.IdHierarchy, '.' +CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +CAST(TaskCopyData.Id as varchar) + '.'FromScheduleTaskINNER JOIN ScheduleTask as TaskCopyData ONScheduleTask.CopyTaskId IS NOT NULL ANDTaskCopyData.CopyTaskId IS NOT NULL ANDcharindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',ScheduleTask.IdHierarchy) > 0
Hi,I'm having problems constructing a nested join. It's quite complex, sohere's a simplfied example of the problem. Any thoughts on what I'mdoig wrong - or if I've got the whole approach wrong are welcome.I've two tables :-one is a contact table contacting name, addresses etc. Three of thefields represent users - 'created by', 'last modified by' and 'owner'.They contain usernames - eg. JDOE, BSMITH etc.The other table contants usernames and new ID codes.What I want to do is create a new dataset by joining the contacts tablewith the user table on all three fields - so the new dataset containsthe ids for the creator, last modifier and owner.I've tried things similar to:select c.*, u1.id, u2,id, u3.idfrom contact cleft outer join users u1left outer join users u2left outer join users u3on (u3.username = c.owner)on (u2.username = c.modified)on (u1.username = c.creator )But it compains that"The column prefix 'c' does not match with a table name or alias nameused in the query."The problem is referencing c (contact) through the whole set of joins.I would like to do this in some similar format as the query is within acursor and post-processing would be very long-winded.Thanks
Is it possible to run through a buffer multiple times in an asynchronous script?
Let say I have a data set and I want to get the max value and then compare/subtract each row in the data set to that max value and add that as a new column - is that possible in the asynchronous script?
Basically I would need to run through the buffer once and pull out the value for the max, and then go through the buffer again pushing to the output buffer the row with the new column "DiffFromMax".
I already tried adding an asynchronous script to pull the max and put into a variable and downstream add a derived column which subtract from the variable but it doesn't work as the variable cannot be assigned till the postexecute() so its always too late.
I've tried having an asynchronous script that has 2 output, one containing the max and the other the rest of the data, however there is no way to subtract without spoofing a cross join which is really slow becuase of the sort required (I still can't believe msft rejected my request for adding a cross join, it had lots of votes and it should be easy to add... I'd code it myself if they let me have a script transformation with multiple inputs)