Negate RESULT SET, All Inactive Client

Oct 3, 2007

I' d like to list all inactive clients.
Inactive client is a client who hasn't had invoice for 2 months.

I use INNER JOIN to join invoice view (vwDok4FSFZGrid) and clients
addresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_Nazwa !
=''). I select only invoices with date after the interesting date.

So now I can list all active clients but i can't negate this result
set to get all inactive clients:

SELECT dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
FROM vwDok4FSFZGrid
INNER JOIN adr__Ewid
ON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiek tu
WHERE
adr__Ewid.adr_Nazwa !=''
AND
(dok_DataWyst >= convert(datetime,'10/03/2007'))
GROUP BY dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst

Is there any way to negate this set?

1. Sorry for my English
2. I would appreciate any help
:)

View 1 Replies


ADVERTISEMENT

Negate A SQL Bit

Nov 2, 2007

Seems an easy questions, but how do I negate a SQL bit variable in SQL Server 2000? i.e. If the Bit is 1, I want it to be set to 0. Not that this is not a select, e.g.

SET @b = 1
SET @b = NOT @b -- This line does not work.

View 1 Replies View Related

What Value Needs To Be Passed To Parameter In WHERE To Negate The Filtering

Apr 9, 2008

I am using two drop downs, like so:
<asp:DropDownList ID="ChurchStateDrop" runat="server"                              DataSourceID="ChurchStateDropData" DataTextField="State"                              DataValueField="State" AutoPostBack="True"                              onselectedindexchanged="ChurchStateDrop_SelectedIndexChanged"                             AppendDataBoundItems="True">        <asp:ListItem Value="?????" Selected="True" Text="All States" /></asp:DropDownList>
<asp:DropDownList ID="ChurchCityDrop" runat="server"                             DataSourceID="ChurchCityDropData" DataTextField="City"                              DataValueField="City" AutoPostBack="True"                             onselectedindexchanged="ChurchCityDrop_SelectedIndexChanged"                             AppendDataBoundItems="True">        <asp:ListItem Value="?????" Selected="True"  Text="All Cities" /></asp:DropDownList>
I have ????? in the value fields because I don't know what value needs to be passed to negate filtering (to choose all). The Dropdowns have the following SQLDatasources:<asp:SqlDataSource ID="ChurchStateDropData" runat="server"                               ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>"                                SelectCommand="SELECT DISTINCT                                                                        [State]                                                             FROM [ChurchView]"                               DataSourceMode="DataReader"></asp:SqlDataSource>
<asp:SqlDataSource ID="ChurchCityDropData" runat="server"                                ConnectionString="<%$ ConnectionStrings:tceDatabaseOnlineSQLConnection %>"                               SelectCommand="SELECT DISTINCT                                                                        [City]                                                             FROM [ChurchView]                                                          WHERE ([State] = @State)"                               DataSourceMode="DataReader">                               <SelectParameters>                                 <asp:ControlParameter ControlID="ChurchStateDrop"                                                                   Name="State" PropertyName="SelectedValue" Type="String" />                               </SelectParameters></asp:SqlDataSource>
Now, lets say I wanted to pass a value to the WHERE statement in ChurchCityDropData to coincide with 'All States', what would I replace value="??????" with? Now you may think I'm crazy to do such a thing, but this actually has to do with adding a Denomination Dropdown to show Denominations from all states or all cities. I will figure out the best logic for that later, I just want to know the wildcard to pass to the parameter to choose all states (negate filtering).

View 3 Replies View Related

Datamining Veiwer Client And Chang Ing Taht And Savning The Result Of Prediction Query In Adventure Works Dw Without Wizard

Jan 24, 2008


Hi
I have three questions about several topics.
In this code:
public string ConnectionString
{
get
{
return "Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW";
}
}

What is data source and initial catalog and what does this code do? And if I want to use other database how can change this code? (This code is for data mining viewer client project)
And in this code:
SqlConnection cn = new SqlConnection("Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=True");
SqlCommand cm = new SqlCommand("Select AddressID,AddressLine1 from Person.Address", cn);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cm;
DataTable dt = new DataTable();
da.Fill(dt);
this.comboBox1.DisplayMember = "AddressLine1";
this.comboBox1.ValueMember = "AddressID";
this.comboBox1.DataSource = dt;

what is comboBox1.DisplayMember and comboBox1.ValueMember ,and what is difference between those ?
and other question:
in adventure works dw project for data mining predicting ,in forecasting model ,if I want to show the result of this query in the combobox in c# how can I show that?
SELECT
PredictTimeSeries(amount)
From
[Forecasting]
And again in this code ,it has a result which has two culomns ,on of them is for amount and other column is for time ,in sql I can save this result in exsiting table or neew table with wizard,but I want to
Do this work in c#,that€™s mean with a adomdconnection I connect to forecasting model and write this query then in a datagridviwe ,Iwant to see the values of prediction in adventure works dw database.
Other question:
In €œdataminingviwerclient€? project I change this code and you can see it,for this code I have a form that give servername and catlogname in that and then with clcking on a button I want to show the chart of model in a child form ,but I can€™t.

public Form1 form1 = new Form1();
public string m_ServerName;
public string m_CatalogName;
public Form3()
{
m_ServerName = "";
m_CatalogName = "";
InitializeComponent();
}


public string ConnectionString
{
get
{
return "Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW";
}
}


public void button1_Click(object sender, EventArgs e)
{
connectingtoserver();
AdomdConnection conn = new AdomdConnection();
try
{
this.Cursor = Cursors.WaitCursor;
conn.ConnectionString = this.ConnectionString;
conn.Open();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "Connection Failed");
}
this.Cursor = Cursors.Default;
conn.Close();
ShowModel(panel1, form1.comboBox1.Text);
}


public void connectingtoserver()
{
LoadCatalog();

}
private void LoadCatalog()
{
AdomdConnection conn = null;
try
{
conn = new AdomdConnection(this.ConnectionString);
this.Cursor = Cursors.WaitCursor;
conn.Open();
DataSet ds = conn.GetSchemaDataSet(AdomdSchemaGuid.Catalogs, null);
foreach (DataRow row in ds.Tables[0].Rows)
form1.comboBox1.Items.Add(row[0].ToString());
}
catch (System.Exception)
{
/**/
}
this.Cursor = Cursors.Default;
if (conn != null)
conn.Close();
}

private void ShowModel(Panel panel, string modelName)
{
AdomdConnection conn = new AdomdConnection();
try
{
MiningModelViewerControl viewer = null;
MiningModel model = null;
MiningService service = null;

// Clear any existing controls from the panel
if (panel.HasChildren)
panel.Controls.Clear();

// Connect to server
conn.ConnectionString = ConnectionString;
conn.Open();

// Determine the viewer type based on the model service and
// instantiate the correct viewer
model = conn.MiningModels[modelName];
service = conn.MiningServices[model.Algorithm];
if (service.ViewerType == "Microsoft_TimeSeries_Viewer")
viewer = new TimeSeriesViewer();
else throw new System.Exception("Custom Viewers not supported");

// Set up and load the viewer
viewer.ConnectionString = ConnectionString;
viewer.MiningModelName = modelName;
viewer.Dock = DockStyle.Fill;
panel.Controls.Add(viewer);
viewer.LoadViewerData(null);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "Model Load");
}
conn.Close();
when I run this code ,I have one error that say: the €œ object not found parametr name:index
Please see this code and answer my question.
If you just can answer one of my qestions ,please say.
Thanks a lot for your answers.With best wishes for you

View 3 Replies View Related

Inactive Tables

Apr 24, 2008

We have 350 tables in our database, I want to drop/delete those table(s) that are inactive. Is there a way to check which tables are not active, so I could drop/delete that table(s).

View 1 Replies View Related

What Is INACTIVE Delete?

Oct 10, 2005

Here are two tables. How do i create an SP for INACTIVE delete and how do i use the UNIQUE constraint to avoid duplication of the NAME columns(logingroup_name and login_name) in these tables?

--Creating Table ref_LoginGroup:-
create table ref_LoginGroup
(
logingroupid tinyint identity(1,1) constraint pklogingroupid primary key clustered,
logingroup_name varchar(25) constraint chkname check(logingroup_name NOT LIKE '% %'),
changed_login_id int,
is_active bit,
lastupdr smalldatetime,
)
--Creating Unique nonclustered Index for ref_LoginGroup:-
create unique nonclustered index idxlgn on ref_LoginGroup(logingroup_name)


--Creating Table ref_Login:-
create table ref_Login
(
login_id int identity(1,1) constraint pkloginid primary key clustered,
login_name varchar(50) constraint chklogname check(login_name NOT LIKE '% %'),
firstname varchar(30),
lastname varchar(30),
logingroupid tinyint constraint fklogingroupid foreign key references ref_LoginGroup(logingroupid),
changed_login_id int,
is_active bit,
lastupdr smalldatetime
)
--Creating Unique nonclustered Index for ref_Login:-
create unique nonclustered index idxl on ref_Login(login_name)

View 20 Replies View Related

Why I Can't Remove The Inactive Log?

May 16, 2007

My db is working in simple recovery mode, when I run "dbcc sqlperf(logspace)", I find the log file is very large and the log usage percentage is 98%. In most case, when I run "backup log db_name with no_log", the log usage percentage will be reduced. but in fact it is no change. I make sure there are no active logs in it. Who can tell me the reason ? thx.

View 1 Replies View Related

Replication - Subscription Inactive, Why ?

Jul 23, 2003

I have a snapshot & a transcation publication on Server A, and a pull subscription for each on Server B.

The distribution agent on ServerA has the error:

"The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated."

Any idea what caused this ..... Everything was fine for a week or so.

Does anyone know of any good books or publications on Replication ? I've got some DBA books that cover the basics, but I'd like something that helps explain the oddball situations.

SQL 2K

View 2 Replies View Related

Lock On Inactive Thread Won't Go Away

Feb 29, 2008

Hello,

I am having a problem with the SQL Server 2005 database of our ERP system. We have a scheduled batch that gets stuck because of a lock on a table. The lock remains overnight and halts all other processes (it seems most of them are waiting for this lock to get solved)
I know which table the lock is on and if I kill the lock manually, the batch continues and finishes without a problem

my question is: can I change this behaviour somewhere?

The piece of code that causes this lock is protected, but in a debug log I was able to see that it happens during following actions:

first a select is performed to fetch a certain record in the file that gets locked
next an update is performed with following syntax:

update ... set.... where current of 'pointername'

this update is waiting for a lock to be released before the batch continues.

My guess is that somehow the pointer that is set with the select statements, locks the row and that's why the update cannot be performed.
The strange part is that this lock does NOT occur everytime that this table needs to be updated. And that's why I am stuck and asking help of the real database people :)

If someone can give me a hint on a setup,property, or just a pointer to where I can find more info on the 'why'-part of the lock, I would be very grateful

database is SQL Server 2005 SP1 on win2k3

View 3 Replies View Related

How To Find Inactive Objects..

Jun 5, 2007

I am trying to find any stored procedures or tables that have not been used in the last 2 months or so. Can anyone recommend me a good way to do this?



SQL 2005 Enterprise Edition SP 2



Thanks in advance.



Kay

View 1 Replies View Related

DTS To SSIS ExecutionStatus Inactive

Sep 5, 2007

Hi ,

I have code in SQL Server 2000 (DTS). what is same code to inactive execution status in SSIS
If returnValue <> "" Then

DTSPackage.Steps("DTSStep_DTSDataPumpTask_2").ExecutionStatus = DTSStepExecStat_Inactive

Else

DTSPackage.Steps("DTSStep_DTSDataPumpTask_3").ExecutionStatus = DTSStepExecStat_Inactive

End If



Thanks,
Raja

View 3 Replies View Related

The Subscription Has Been Marked Inactive

Jul 17, 2006

hi all,

i have a snapshot replication running on multiple publisher singel subscriber running on sql2k. i'm frequently encountering these error.

the subscription has been marked inactive and must be reinitialized.
no synch



i've set the subcription to never expire in the publications property tab.

thanks

View 5 Replies View Related

Check For Inactive Database

Sep 21, 2007

Hi

I am working at a site that has no dedicated DBA, and our Sql Server 2000 environment has recently been playing up. There are several databases on the Production server that should be inactive (for applications no longer used).

What is the best way for me to see if there has been any recent activity on these databases (short of taking them offline and waiting for screams). Please keep in mind that I am a Sql Server novice.

Cheers

View 5 Replies View Related

Tool For Disconnecting Inactive Logins??

Aug 15, 2000

Does anyone know if there is a 3rd party tool available to disconnect user logins after being inactive for a period of time? Is this something that can be custom written?

Thanks for any assistance, Jim

View 3 Replies View Related

Problem On Login: User Is Inactive

Aug 28, 2006

hi,i've just restored my db and when i try to login with a user (even ifthe user is "sa"), an error occours.The error is "User sa is currently inactive"How can i solve this problem?Thanks,Massimo

View 1 Replies View Related

SSRS Inactive Menu Issue

Jul 20, 2007

Is anyone else having an issue with SSRS menus not being able to be clicked on?



When I roll the mouse over the menu item, it highlights, but when I click, nothing happens.

It affects the top menu items, and any right click menu.



This is an intermittent problem, but it does happen daily. I have to shut down VS to fix.



BobP

View 5 Replies View Related

Report Builder: How To Access Last Inactive Date

Apr 7, 2008

A Contractor has a list of Contracting Companies that he has worked for in his history.

For each row in the "Contracting Company" table, there is an Active Date and an Inactive Date. Each Contracting Company cannot have overlapping dates.

The last row in the table for that Contractor may have a null Inactive Date. That just means that he doesn't know when his contract will end.

In Report Builder, I can access the "Last Active Date", because there is a pre-built formula for "Last Active Date". However, whenever I access the prebuilt formula "Last Inactive Date" - I get the inactive date for the second-last row, because the last row has a null value for the Inactive Date! I want to get the Inactive Date for the last row - not the second last row!

How to do this easily? Thanks!

View 3 Replies View Related

INSERT, UPDATE, And DELETE Statement Checkbox Inactive

Mar 26, 2008

Hi  AllgI have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advancehttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPGhttp://img139.a.com/img.php?image=28285_2_122_937lo.JPG   

View 1 Replies View Related

INSERT, UPDATE And DELETE Statements Checkbox Inactive

Mar 26, 2008

I have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advance

the screenshot links:
http://img139.imagevenue.com/img.php?image=28285_2_122_937lo.JPGhttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPG 

View 7 Replies View Related

SQL Server Admin 2014 :: Find All The Inactive Windows Logins?

Jan 30, 2014

How to query in SQL to retrieve all the disabled Windows Users which are available as records(only) in the SQL Server ?

View 9 Replies View Related

Report Toolbar Inactive - Suspect RsClientPrint ActiveX Problem

Mar 15, 2007

Hi,

I€™m hoping someone might be able to help me.

I have a Development Machine running Windows XP Professional SP2, with SQL Server 2005 including Reporting Services. I am developing an ASP.NET application using VS 2005, and am including Server Side Reports from SQL Server. Everything is working well, except when viewing the Reports.

The Reports when viewed from Reporting Services€™ Report Manager or from within the ASP.NET application only display the first page of the report, displaying the total number of pages as 0. As well, most of the functions on the report toolbar appear to be disabled. This occurs for any report viewed, even sample reports such as the Adventure Works Samples.

The problem only occurs when viewed from within Internet Explorer on the Development Machine. To overcome the problem, I have upgraded SQLServer to SP2, and removed and reinstalled Internet Explorer, but it made no difference. It appears to be related to the rsClientPrint ActiveX addon which should have been installed when the print icon was first selected. It appears not to have installed properly, but up to that point everything worked OK. Selecting the Print icon (or any other icon) since nothing happens. I€™m guessing here, but I suspect that the rsClientPrint addon needs to be reinstalled, but I don€™t know how to go about it. It is not listed within IE addon (where it is on other machines connecting to the Development Machine), nor is it listed within the €œC:WindowsDownloaded Program Files€?. But it is not being downloaded when the Print Icon is selected. I have checked all the Security settings for the environment to ensure that ActiveX programs are downloaded, and everything appears to be in order.

Would anyone be familiar with this problem or have any suggestions on how to go about resolving it? Any help would be appreciated as it is causing significant problems. Strange though, the reports appear fine when viewed from within the Server Manager.

View 1 Replies View Related

Transact SQL :: Set Child Records To Inactive When Parent Record Deleted From Table

Oct 16, 2015

I need to create a trigger to meet following conditions.

When parent record is deleted from UI record becomes inactive in table. i need to create a trigger when this happens.

When parent record is deleted child records needs to be inactivated in table.

View 12 Replies View Related

Transact SQL :: Conversion Failed When Converting Varchar Value (inactive) To Data Type Int

Jul 20, 2015

I am getting this error when I run the case statement below. I think I have to convert at some point, but not sure where or how. 

Case when m.dispo = 2 then 'Inactive' else m.value end 'Score'

View 9 Replies View Related

Client With Tcp/ip Produce &#34;time Out Error&#34; While Same Client While Switced To Named Pipes

May 28, 2001

We have 15 clients running our applicaton
14 of then conected to SQL server using TCP/IP and it runs fine

1 of 15 when connected using TCP/IP produce "..Time out error "
but runs fine when swiched from TCP/IP to Named pipes

1.What area should we look to correct problem with Time out using TCP/IP ?
2. Where to get information about using TCP/IP via Named pipes ?

View 1 Replies View Related

Install SQL Server2005 Error:[Native Client]Encryption Not Supported On The Client

May 10, 2006

Product: Microsoft SQL Server 2005 -- Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Encryption not supported on the client. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.

View 78 Replies View Related

Client Unable To Establish Connection Encryption Not Supported On SQL Server. (Microsoft SQL Native Client)

May 2, 2006

On Windows XP systems I get the following issue when trying to browse the MSDB folder in SSIS

Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)

I have noticed another post where several others have noticed the same issue. It appears to only occur on Windows XP installations. Is there a workaround or fix for this?

View 2 Replies View Related

Setup Error : [SQL Native Client] Encryption Not Supported On The Client

Nov 1, 2006

Hi,

I have SQL2000 installed as the default instance, and now I'm trying to install SQL 2005 standard edition as a named instance.

I receive this error :
SQL Server could not connect to database service for server configuration.. [SQL Native client] Encryption not supported on the client. However I'm able to install client tools

The setup works fine on other box with the same config : SQL 2000/Windows XP, is there any work around for this issue ?

In my SQL 2000 client network utilty "Force proctocol encryption " is desabled and did not find the setting for SQL 2005 !

Thank you

View 1 Replies View Related

Upgrade Client Connectiviity Tools On Client Machines

Jul 23, 2005

Hello,Is it necessary to upgrade the Client Connectivity Tools on all clientmachines after the SQL Server database server is upgraded from Version7.0 to 2000?Thank you in advance!Eddy

View 1 Replies View Related

Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result

Jul 20, 2005

I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg

View 4 Replies View Related

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007



I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".

Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.

Thanks!

View 5 Replies View Related

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?

Dec 11, 2007

Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved