Can somebosy help with this dilema in my where clause I've got the followingWHERE (Customers.Owner = ISNULL(@Contract,Customers.Owner)) AND (Workorders.DateReceived = ISNULL(@DateReceived,Workorders.DateReceived)) AND (Workorders.DateRequired = ISNULL(@DateRequired,Workorders.DateRequired)) AND
(Workorders.EngineerID = ISNULL(@EngineerID,Workorders.EngineerID)) AND (Workorders.DateFinished = ISNULL(@DateFinished,Workorders.DateFinished)) AND (Workorders.JobTypeID = ISNULL(@JobTypeID,Workorders.JobTypeID)) AND
(Workorders.JobStatus = ISNULL(@JobStatus,Workorders.JobStatus)) AND (Workorders.PriorityID = ISNULL(@PriorityID,Workorders.PriorityID))
This is so if one of the parameters passed is null you still get a result is trouble is if the substitute value of the ISNULL statement is null no result is shown how can I get round this.
For Example:SELECT Column1, Column2 FROM Table WHERE Column1 = ( SELECT CASE @Test4Nulls WHEN 1 THEN NULL ELSE Column1 END) Basically, I want to test for Nulls in a column if my variable @Test4Nulls is True, however, I never get any results back. I believe it is because I am Criteria = NULL instead of Criteria IS NULL, however, I cannot use IS in a case statement How do I test for nulls in a case statement
Since a week we have a strange problem. We are using SQL Server 2005 with SP2 on a Windows Server 2003 updated last time in November. We have 2 servers like this, one is a virtual machine and the second one, a physical one.
Since a week, sometimes, the reports in the web report viewer just don't show. They keep loading for hours without showing anything. Beside, everything's fine, the server has plenty of free resources (CPU, RAM...), Visual Studio works normally, SSMS too. We can even go in http://localhost/reports and http://localhost/reportserver and see the folders, files etc... but when trying to load a report (what ever it is, even a small one) nothing comes, not even the message "Report is loading".
It happens not every day, sometimes two or three times a day at different hours. In the Windows event viewer, nothing. In the SSRS logs, nothing (as far as I can understand these logs).
It looks as if a job or a process was running and was blocking the reports or a service which would stop automatically. To have it work again, a reboot of the server works or within 2 or 3 hours, it comes back to normal state.
If someone has an idea of what can be the problem or some tips, please feel free because we are running out of ideas.
After doing a database restore on my development server, It only shows DBO As the db user..When I query SYSUSER all other users are still there..But doesn't show up in enterprise manager...
I have a question that has a co-worker and myself confused. We are using the SQL Server Enterprise Manager. When I select return all rows in my tables, any content that is too long will appear as <Long Text> on my co-workers machine, but the column is blank on my machine. Is there a property or configuration that enables/disables <Long Text>? I am confused as to why it appears on other machines, but not mine.
SS2005 question. Why doesn't securables show when I add a user to have select permission on a table? Like after adding a user I click ok to close the window then double click on the user to open it up, go to securables but it doesn't show. It's empty.
I've designed a report and assigned it a datasource that binds with a SP with a parameter. In the XML code of the dataset I can see the input parameter has been recognised by the wizard. Then, I built a report with that Dataset as the source. Everything is fine till this time. Now through my aspx code I pass on the value of the parameter to the report. But the report doesn't show any values at all. The aspx code is like
Code Snippet Dim reportParameter As New ReportParameter("@UserID", Convert.ToInt32(TextBox1.Text), True) Dim test() As ReportParameter = {reportParameter} ReportViewer1.LocalReport.SetParameters(test)
Any ideas what I could be doing wrong? VS 2005 & SSRS 2005. TIA.
I'm trying to use the following code to examine table schema for SQL 2000. But when I do, the IsKey value is null or blank. I tried this on two different tables - a State table with a char(2) primary key containing the state abbreviation and a Trend table which has an identity column as the primary key. Public Shared Function GetSchema(ByVal sTable As String) As String Dim sb As New StringBuilder
Dim oConn As SqlConnection = OpenConn() Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM " & sTable, oConn) Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
Dim dt As DataTable = dr.GetSchemaTable()
dr.Close() oConn.Close()
For ic As Integer = 0 To dt.Columns.Count - 1 sb.Append(ic & ":" & CNull(dt.Columns(ic).ColumnName) & " ") Next sb.Append(vbCrLf)
For ir As Integer = 0 To dt.Rows.Count - 1 For ic As Integer = 0 To dt.Columns.Count - 1 sb.Append(ic & ":" & CNull(dt.Rows(ir).Item(ic).ToString) & " ") Next sb.Append(vbCrLf) Next
I've created a very simple custom connection manager, and I followed the deployment instructions at http://msdn2.microsoft.com/en-us/library/ms345276.aspx. Basically, that was just copying the assembly to %programfiles%Microsoft SQL Server90DTSConnections, and then registering the asssembly in the gac.
However, when I open VS, right-click on the connection manager pane, and then choose new connection, my custom manager does not appear. What am I missing?
I have created a custom connection manager and it was showing up fine as was the UI I created for it. For some reason now no matter what I do I cannot get it to show up in the list when right-clicking and choosing New connection...
I have also created two custom tasks. One of them works perfectly. The second was working but now causes an error when you open the package. Here is the error message:
TITLE: Microsoft Visual Studio ------------------------------
There were errors while the package was being loaded. The package might be corrupted. See the Error List for details. There are no additional errors in the list only this one. I have tried everything I can think of to fix these two issues. Both problems emerged at the same time so may be related but that would not explain why the second custom task I have created works fine and doesn't cause errors. Help! Thanks, Dave
Here's my problem. I have a subreport that shows data and labels if data is returned. However, if no data is returned, the subreport doesn't show at all. I have a message on the subreport I would like to show if no data is returned, but the subreport won't show at all. Any ideas??
When I create a Smart Device project and and try to add a reference to SQL Server CE only the old version (3.0.3600.0) shows up. How do I get the new one (3.1) to show up?
When I open Report Builder I do not see any of the models that I've created. The "Select Site or Server" is blank and I cannot get to any of the models. I can't change the dropdown from "Recent Sites and Servers."
I am not sure what I did, because it used to work. I did install SP2 but that did not cause a problem originally.
I can create new models (on both the BIDS and the ReportServer site) and I see that they were created just not in Report Builder. At this point Report Builder is non-functional for me.
The other thing that may be related is that I've never been able to run Report Builder from the website. When I click the button nothing happens. I made a shortcut to ReportBuilder.exe to get around this.
I've installed SQL Express SP1 on a Windows 2003 server and I'm trying to get a new MySQL datasource to be recognized in the DTSWizard. A MySQL database needs to be moved over to a MS SQL server due to software requirements (which weren't looked at before the site was created). I've installed the ODBC Connector/MySQL software and created the System DSN (named "MySQL DB Import) in the Administrative Tools/Data Sources (ODBC) tool. When I start up the DTS wizard for SQL Express however, I do not get my new data source in the selection options for data sources. I am following the instructions on this page: http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx#ERJAE What am I missing?
Hi, all, I found that the SQL2000 EM does not show database space allcoated information, as well as tables and indexes size while SQL 7.0 does. Someitmes these information are fairly handy. is there any other easy ways to find out the same info from SQL2000 through Em, or elsewhere ?? Thanks Anthony
I thought I'd give the SQL Server 2000's log shipping wizard a try (inthe past I've always done it with Perl scripts), so I attemted to setit up on one of my servers. But in the maintenance plan wizard, Idon't get the checkbox at the bottom of the window to "Ship thetransactions logs to other SQL Servers (log shipping)" as it shows inthis article:http://www.microsoft.com/technet/pr...n/logship1.mspxCan anyone tell me what's wrong here? Please post to the newsgroup.Thanks,Steve
Te first record of a Recordest obtained from a Command Object executing a Stored Procedure, doesn't show the first record when I asociate this to a data report.(VB6 - SQL7) (ADO 2.1)
If I execute the stored procedure directly from query analizer, I have obtained the right resultset.
We are running 2014 enterprise. I noticed recently that my spreadsheet's column A, while not being used by user, doesn't show up in excel source preview. F1 is column B and so on.
It's a very strange thing! I havea a table called invoices, and a table calle customer payments which has the invoiceID of the payment.
I have many invoices that haven't been paid (so they don't have a record on the customer payments). I know this, as i can for example do: select * from invoices where invoiceID = 302247 (and i'll get one result) select * from customer_payments where invoice = 302247 (and i'll get none results)
however, if i do the following: select * from invoices where invoice_id not in (select invoice_id from customer_payments) I get nothing!!!???
It doesn't make any sense, as I should get at least 300 (including the 302247) - both invoiceids fields are int... so i just don't understand what's wrong?
I have two tables, Employee and Calls. They are joined on an Employee field. In my where clause I have a value specified that only returns specific calls. What I would like to have happen is to have the query return all Employee records regardless if any records from the Calls table is present for that employee. I want something that looks like this:Employee # of Calls Employee A 5 Employee B 0 Employee C 10
When I apply a WHERE clause to the Calls table I get this:Employee # of Calls Employee A 5 Employee C 10
I tried a LEFT OUTER JOIN without success. Any suggestions?
I am trying to import an Excel file into SQL Server 2005 using the SSIS import/export wizard; however, Microsoft Excel doesn't show up in the list of the data sources. I am assuming that something else must be install from either Microsoft Office or SQL Server 2005. I am using Microsoft Office 2003 on a Window XP machine. Does anyone know what I need to do to correct this.
In my BM_Maps subform, there is a combo box called called Borders. I want the borders to be filtered based on product series, so if the user picks "City Guide", they only get city guide borders, so I have added a table called Product_Series_X_Border which has the product series and border id.
I have a query which joins two tables, and I've ordered it by the border name, which works fine, until I add a where clause for the product series from the other table. I have attached a screengrab which I hope works. I have tried joining other tables but it still doesn't work. Is there any other way I can order by border name with the where clause? I've tested with individual product series as the where clause with the same result.
Can someone tell me why SQL seems to ignore my order by clause?I tried to run through the debugger, but the debugger stops at theselect statement line and then returns the result set; so, I have noidea how it is evaluating the order by clause.THANK YOU!CREATE proc sprAllBooks@SortAscend varchar(4),@SortColumn varchar(10)asIf @SortAscend = 'DESC'Select titles.title_id, title, au_lname, au_fname,Convert(varchar(12), pubdate, 101) as PubDatefrom authorsinner jointitleauthoronauthors.au_id = titleauthor.au_idinner jointitlesontitleauthor.title_id = Titles.title_idORDER BY au_lnameCASE @SortColumn WHEN 'title' THEN title END,CASE @SortColumn WHEN 'au_lname' THEN au_lname END,CASE @SortColumn WHEN 'PubDate' THEN PubDate ENDDESCELSESelect titles.title_id, title, au_lname, au_fname,Convert(varchar(12), pubdate, 101) as PubDatefrom authorsinner jointitleauthoronauthors.au_id = titleauthor.au_idinner jointitlesontitleauthor.title_id = Titles.title_idORDER BYCASE @SortColumn WHEN 'title' THEN title END,CASE @SortColumn WHEN 'au_lname' THEN au_lname END,CASE @SortColumn WHEN 'PubDate' THEN PubDate ENDGO
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table - Unable to modify table. Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails. The statement has been terminated.
I have created view by jaoining two table and have order by clause.
The sql generated is as follows
SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.* FROM dbo.UWYearDetail INNER JOIN dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag, dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth
If I run sql the results are displayed in proper order but the view only order by first item in order by clause.
Has somebody experience same thing? How to fix this issue?
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
for the query i created i need zeros where ever the filed is blank. i have used count(acc) for selecting the count . can any one help me out with sample query. Thanks in advance
I have two tables, tblMTO and tblIMPORT_MTO. If I import an entire MTOinto the import table I want to create a delta from it (i.e. leave onlythe changed items). I have a view (simplified)SELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,dbo.tblIMPORT_MTO.Rev AS New_Rev, dbo.tblMTO.Rev AS Old_RevFROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONoNow to get all rows where old_rev = new_rev I also want all rows whereboth are null, is the best method to put ISNULL() in the view or toselect from the view using ISNULL in the criteria, e.g.select * from view1 where ISNULL(Old_Rev,0)=ISNULL(new_rev,0)or in the viewCREATE VIEW view1 asSELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,ISNULL(dbo.tblIMPORT_MTO.Rev,0) AS New_Rev, ISNULL(dbo.tblMTO.Rev ASOld_Rev,0)FROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONo;select * from view1 where Old_Rev=new_rev;
