View Complete Post
I have an OLE DB source in a data flow task. The connection is pointing to a SQL Server 2008 instance. My SQL works fine when I click the preview button. Now I need to make it parameterized by adding a question mark in my where clause.
After doing so & clicking the Parameters button, I get the error described in
this post . When I try to do what's described therein to correct the problem (click the "build query" button), I get the error "The PIVOT SQL construct or statement
is not supported."
I can't make this query an expression; it's simply too complex to maintain that way (besides, just pasting it into an expression builder window and trying to evaluate
as is doesn't work - can't evaluate).
So the question is, how do I parameterize a query? And why does SQL here work so radically differently from SQL in an execute SQL task?
I have the following query which needs to run on a schedule and email a list of all disabled jobs for the SQL Server instance (I've changed the server and email names for privacy):
@recipients = Nfirstname.lastname@example.org'
,@body = N'The attachment shows the names of all SQL jobs that are currently disabled on SERVER1'
,@subject = N'Disabled Jobs on SERVER1'
,@query = N'SELECT [NAME] FROM [msdb].[dbo].[sysjobs]'
,@attach_query_result_as_file = 1
This query works fine when running from a query window. however, when I try to run it from a SQL Agent job, I get the following error on the step output (username changed for privacy):
Executed as user: domain\user. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.
If I try other queries in the @query parameter, everything works fine when running from the job. As soon as I try to query [msdb].[dbo].[sysjobs] though, the job fails. I ran a profiler trace, and I found a more detailed error
message that occurs during the execution of the job, unfortunately it involves an undocument SP:
exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status
I have this script to find open transactions, wanted to create it as a SP sp_GetOpenTransactions in the master db, but I get a syntax error. Works just fine in the other DB. Server is sql 2008. I noticed that the master db compatibility level is 80, the
userdbs are in level 100. Wondering if that is the problem here ? Why not the system dbs get their compatibility level changed after an upgrade ?
Hi, I am having trouble with a view I am writing which provides the available status of a new or existing hardware installed at an organisations site for a web application.
The view runs a number of queries which are unioned to provide the correct available statuses. The logic is as follows:
T_Site - lists all sites at an organisation which have none or more gateways, or bureau gateways. A site also has site configuration which says how many active gateways (bureau and gateway) a site can have. So a site with a particular site config
should only be allowed to have the maximum number of ACTIVE gateways (gateway + bureau) gateways which the site config allows.
The other parts of the query involve:
T_gateway - table holding gateway details
T_Bureau_Gateway - table holding bureau gateway details
T_Site_config - table holding site configuration details.
The view originally worked fine since it just took into account the number of gateways and their statuses and returned what gateway sites and gateways that could have an active status assigned to them.
But now I need to add in the bureau gateway table, but this is causing problems. The joins aren't working and bringing back lots of rows which aren't correct.
Logic should be:
Get site_id, gw_id, where the total number of active gw's is l
I am facing problems retrieving correct number of records when I am performing a people search on SP 2007.
Following is the query that I had originally written:
Select Title,Path,PreferredName,WorkPhone,MobilePhone,WorkEmail,JobTitle,OfficeNumber,PictureURL,Responsibilities,Skills,PastProjects,SipAddress,EmployeeLocation,Manager,Department,Assistant,EmployeeType,AccountName,UserProfile_GUID from Scope() WHERE ("Scope"='People') AND
( FirstName LIKE '%Keyword%' OR LastName LIKE '%Keyword%' OR OfficeNumber LIKE '%Keyword%' ) ORDER BY Title ASC
But, there is a problem here. Those records where the condition "FirstName LIKE '%Keyword%'" is satisfied, does not display if there is no value in OfficeNumber. This is giving incorrect number of records.
To correct this, I have changed the query and am using UNION in my query. See the following query:
(Select Title,Path,PreferredName,WorkPhone,MobilePhone,WorkEmail,JobTitle,OfficeNumber,PictureURL,Responsibilities,Skills,PastProjects,SipAddress,EmployeeLocation,Manager,Department,Assistant,EmployeeType,AccountName,UserProfile_GUID from Scope() WHERE ("Scope"='People')
and (EmployeeType = 'Contingent Worker' or EmployeeType = 'Employee') AND (FirstName LIKE '%Keyword%'))
I have three versions of an indexed view I'd like to create. I can create the view, but cannot create a clustered index on (CollectionID,
ElementID, TimeID) because the definitions use variously a sub-query, CTE, or derived table, which SQL will
Has anyone figured a workaround for this? Any suggestions much appreciated.
We have following three servers with MOSS 2007 STD deployed in SharePoint server farm,
App01- Query Server
App02 - Index Server (We are using this server as dedicated web front end computer for crawling)
SPDBServer - SQL 2005
Search was working fine till 01/05/2008 and it stopped working after this date only for new contents meaning search is still working fine for the old contents which were added before 01/05/2008 but not for new contents added after 01/05/2008.
We have been working to find out reasons why did that happen? We have found following event ids on Query server,
Event ID:10042 - A new query machine, 'App01', has been added to the query rotation based on changes to farm topology.
I have probably a pretty simple problem that i do not understand why i does not work.
My database questions is:
SELECT COMPAN, SUM(AMOUNT) AS SALES
WHERE (ACCOUNT = 3010)
ORDER BY COMPAN
If I remove the WHERE then everything works but I dont get in that case what I want. Why cant I do like this? Secondly in that case how shall I do it then?
Thanx in advance
I'm starting a small website that will be backed by a database, and will allow a small number of users to authenticate and select an entry in the database, and then view (and optionally update) a corresponding entry (think master-detail).
I have some flexibility in how I'll do this, but I'd really like to incorperate some newer AJAX style coding, eliminating some page postbacks. I think this is a great opportunity to learn the concepts well.
I'll probably go with an ASP.NET 4.0 site. From what I've seen there are MANY ways to go about this:
-WCF Data Services to expose the data, and consume in ASP.NET
-Page methods to expose the data, separate method to update the data
-I'd rather avoid the UpdatePanel because I'm really looking for a full hands-on approach, with learning client side scripting being an objective as well.
Beyond that, it seems that one can use jQuery to build clientside templates, but also one can use the ASP.NET AJAX template engine (in ASP.NET 4.0 I believe).
A concern I have: users should only be able to work with the data exposed via these endpoints if they are logged into the website. Hopefully I can somehow reuse the ASP.NET forms authentication cookie for this purpose.
Well, I hope I've posted enough to a
Please let me know is there anything else i can do i really would like use this effect.
P.S all the effect have the same render problem, and i am using ie8 under compatibility mode.
I try to run a treeview example from msdn:
I also want to use code from
Here is my code: