Crazy Problem - Two Part Query

May 29, 2008

I need some serious help with this query...

SELECT *
FROM dbo.wce_contact
WHERE (Mail1Date IS NOT NULL)
AND (Mail2Date IS NULL)
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)

or(IDStatus LIKE '')
or (NOT (Task LIKE '%x%'))
or (ExpressEmail LIKE '%@%')
or (WebSite NOT LIKE '')
or (Unsubscribe = 0)
or (Artwork LIKE '')

Basically the parts in red work best when they use the 'or' criteria.

My problem.

I run the query together black+red and get a different result than expected. Is there a way of saying 'do the black part first and its an imperative that the red part follows those rules on the black part of the query?' eg:

IDStatus must be LIKE '' when running the black part of the query
and expressemail must be like '%@%' on the black part of the query but not on red part of the query. So it doesnt matter that the expressemail must have idstatus like '' whilst having an @

Hope it makes sense??

View 11 Replies


ADVERTISEMENT

Parameterized Query Driving Me Crazy

Apr 16, 2008

I'm trying to do a basic update query which is working on other pages but not on this page.  Dim uid As Integer = CInt(Session("uid"))
Dim cmd As New SqlCommand("UPDATE [cvdata] SET [jobCompanyName] = @inputJobCompanyName WHERE [user_id] = @uid", strConn)
With cmd.Parameters
cmd.Parameters.AddWithValue("@inputJobCompanyName", inputJobCompanyName.Text)
cmd.Parameters.AddWithValue("@uid", uid)
End With
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
The funny thing is that if i remove inputJobCompanyName.Text and add a custom value (for example "test") it works.So it doesn't seem to read my updated textfield or something im clueless.Kind regards,
Mark

View 2 Replies View Related

Web Part Deserialization Error When Trying To Change Report Viewer Web Part Programmatically.

Oct 29, 2007



I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site.
I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part.
While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as
"Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"

If someone has a solution, please respond at your earlist.

Thanks

Shankar

View 1 Replies View Related

Retreive Only Part Of The Query?

Sep 16, 2004

Is there a chance to retreive let's say rows positioned from 30-70 if a query produced result that contains 100 rows?

View 3 Replies View Related

4 Part Name Query Very Slow

Nov 5, 2007

exec sp_tables_ex 'linked server'


The table_cat returned a null value. I ended up writing

select * from linked_server..schema.table and it did work. However, it was really slow. 56 seconds vs. less than a second for the pass through openquery.


My guess is that the problem is that the table_cat was null and this is why it is so slow. Is this correct? How do I resolve this speed issue?

View 4 Replies View Related

Split A Decimal Number Into The Integer Part And The Fraction Part

Dec 7, 2007

I have a table with a column named measurement decimal(18,1).  If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return  2.5.  So if the value after the decimal point is 0, I only want the stored proc to return the integer portion.  Is there a sql function that I can use to determine what the fraction part of the decimal value is?  In c#, I can use
dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.

View 3 Replies View Related

Show The Decimal Part In Query

Jun 19, 2008

Hi,In the following query the calculator gives value 3.3  but the query returns 3.0 I need to get the decimal part also. i.e I need to get 3.3 as answer from the query. select cast(66/20 as decimal(6,2)) Need help.Thanks 

View 3 Replies View Related

Four - Part Distributed Query Is Not Working

Aug 28, 2006

I have a huge problem as mentioned in my previous queries some of my applications is using Link Server Query as "select * from sm-matrix.matrix.dbo.stage_orders" this doesn't work it gives following error:-

ODBC: Msg 0, Level 18, State 1
SqlDumpExceptionHandler: Process 62 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Unspecified error]


I understand best method to use is "select * from openquery(sm-matrix,"select * from stage_orders")" but i can't do away with above mentioned query as lot of places in application it has been using.

This was working fine till i moved to Windows 2003 from Windows 2000.

Following is the error we are getting:-
SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process..
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 08/28/06 15:52:06 spid 58
*
* Exception Address = 00404743 (RecBase::Resize + 00000005 Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
* [ M A T R I X ] 1b 00 5b 00 4d 00 41 00 54 00 52 00 49 00 58 00 5d 00
* . . s p _ i n d e 2e 00 2e 00 73 00 70 00 5f 00 69 00 6e 00 64 00 65 00
* x e s _ r o w s e 78 00 65 00 73 00 5f 00 72 00 6f 00 77 00 73 00 65 00
* t ç S 74 00 00 00 00 00 e7 18 00 00 00 00 00 00 18 00 53 00
* T A G E _ O R D E 54 00 41 00 47 00 45 00 5f 00 4f 00 52 00 44 00 45 00
* R S ç 52 00 53 00 00 00 e7 00 00 00 00 00 00 00 ff ff 00 00
* ç d b o e7 06 00 00 00 00 00 00 06 00 64 00 62 00 6f 00
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00B32FFF 00733000
* ntdll 7C800000 7C8BFFFF 000c0000
* kernel32 77E40000 77F41FFF 00102000
* ADVAPI32 77F50000 77FEBFFF 0009c000
* RPCRT4 77C50000 77CEEFFF 0009f000
* USER32 77380000 77411FFF 00092000
* GDI32 77C00000 77C47FFF 00048000
* OPENDS60 41060000 41065FFF 00006000
* MSVCRT 77BA0000 77BF9FFF 0005a000
* UMS 41070000 4107BFFF 0000c000
* SQLSORT 42AE0000 42B6FFFF 00090000
* MSVCIRT 60020000 6002FFFF 00010000
* sqlevn70 10000000 10006FFF 00007000
* Secur32 76F50000 76F62FFF 00013000
* NETAPI32 110B0000 11107FFF 00058000
* ole32 113A0000 114D3FFF 00134000
* XOLEHLP 11660000 11665FFF 00006000
* MSDTCPRX 11670000 116E7FFF 00078000
* msvcp60 116F0000 11750FFF 00061000
* MTXCLU 11760000 11778FFF 00019000
* VERSION 11780000 11787FFF 00008000
* WSOCK32 11790000 11798FFF 00009000
* WS2_32 117A0000 117B6FFF 00017000
* WS2HELP 117C0000 117C7FFF 00008000
* OLEAUT32 117D0000 1185BFFF 0008c000
* CLUSAPI 118A0000 118B1FFF 00012000
* RESUTILS 118C0000 118D2FFF 00013000
* USERENV 118E0000 119A3FFF 000c4000
* mswsock 119C0000 11A00FFF 00041000
* DNSAPI 11A10000 11A38FFF 00029000
* winrnr 11A80000 11A86FFF 00007000
* WLDAP32 11A90000 11ABDFFF 0002e000
* rasadhlp 11AE0000 11AE4FFF 00005000
* SSNETLIB 00C70000 00C84FFF 00015000
* NTMARTA 00C90000 00CB1FFF 00022000
* SAMLIB 00CC0000 00CCEFFF 0000f000
* security 125D0000 125D3FFF 00004000
* hnetcfg 125E0000 12638FFF 00059000
* wshtcpip 12800000 12807FFF 00008000
* SSmsLPCn 12810000 12817FFF 00008000
* SSnmPN70 12A20000 12A25FFF 00006000
* ntdsapi 12AE0000 12AF4FFF 00015000
* kerberos 12B10000 12B67FFF 00058000
* cryptdll 12B70000 12B7BFFF 0000c000
* MSASN1 12B80000 12B91FFF 00012000
* SQLFTQRY 12920000 12951FFF 00032000
* xpsp2res 12EB0000 13174FFF 002c5000
* CLBCatQ 13180000 13202FFF 00083000
* COMRes 13210000 132D5FFF 000c6000
* sqloledb 132E0000 13360FFF 00081000
* MSDART 12960000 12979FFF 0001a000
* MSDATL3 12980000 12994FFF 00015000
* oledb32 136F0000 13768FFF 00079000
* OLEDB32R 13770000 13780FFF 00011000
* msv1_0 13810000 13836FFF 00027000
* iphlpapi 13840000 13859FFF 0001a000
* PSAPI 13860000 1386AFFF 0000b000
* xpsqlbot 13910000 13915FFF 00006000
* rsaenh 13A50000 13A7EFFF 0002f000
* xpstar 13BB0000 13BF6FFF 00047000
* SQLRESLD 13C00000 13C06FFF 00007000
* SQLSVC 13C10000 13C26FFF 00017000
* ODBC32 13C60000 13C9CFFF 0003d000
* COMCTL32 13CA0000 13D36FFF 00097000
* comdlg32 13D40000 13D89FFF 0004a000
* SHELL32 14110000 14912FFF 00803000
* SHLWAPI 13D90000 13DE1FFF 00052000
* odbcbcp 13C30000 13C35FFF 00006000
* W95SCM 13C40000 13C4BFFF 0000c000
* SQLUNIRL 13DF0000 13E1CFFF 0002d000
* WINSPOOL 13E20000 13E46FFF 00027000
* SHFOLDER 13E50000 13E58FFF 00009000
* comctl32 14920000 14A22FFF 00103000
* odbcint 13EE0000 13EF6FFF 00017000
* NDDEAPI 13F00000 13F06FFF 00007000
* SQLSVC 14CB0000 14CB5FFF 00006000
* xpstar 14CC0000 14CC8FFF 00009000
* ACTIVEDS 14CD0000 14D02FFF 00033000
* adsldpc 14D10000 14D36FFF 00027000
* credui 14D40000 14D6DFFF 0002e000
* ATL 14D70000 14D87FFF 00018000
* adsldp 14DF0000 14E1DFFF 0002e000
* SXS 14FA0000 1505BFFF 000bc000
* xplog70 15060000 15071FFF 00012000
* xplog70 13C50000 13C53FFF 00004000
* DBNETLIB 11630000 1164BFFF 0001c000
* crypt32 15180000 15212FFF 00093000
* SQLOLEDB 11650000 1165EFFF 0000f000
* dbghelp 15620000 156D4FFF 000b5000
*
* Edi: 00000005:
* Esi: 6BDE4924: 00000000 00000052 00000000 00000000 00000003 0000001B
* Eax: 00000000:
* Ebx: 3B6AFFFD: 013FF000 20000001 CA000000 01000014 CC004200 01000014
* Ecx: 6BDE4924: 00000000 00000052 00000000 00000000 00000003 0000001B
* Edx: 00000E00:
* Eip: 00404743: E183088A 04E9830E 00C9840F 4949001B 4E8B5275 08668304
* Ebp: 1289D77C: 1289D790 005BD328 00A5EA38 1289D78C 1289E8B8 1289EC74
* SegCs: 0000001B:
* EFlags: 00010246: 0057004F 003B0053 003A0043 0057005C 004E0049 004F0044
* Esp: 1289D768: 6BDE4924 00446C52 00000000 00000002 3B6AB940 1289D790
* SegSs: 00000023:
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
* 00404743 Module(sqlservr+00004743) (RecBase::Resize+00000005)
* 00446C52 Module(sqlservr+00046C52) (CSysScan::GetVaried+0000002A)
* 005BD328 Module(sqlservr+001BD328) (CUserScan::CbGroupBitmap+00000016)
* 006315F7 Module(sqlservr+002315F7) (SecCache::FGetFromDiskScedb+00000317)
* 0040C694 Module(sqlservr+0000C694) (checkdbperm+00000114)
* 0040C3BC Module(sqlservr+0000C3BC) (usedb+000000DA)
* 0040C2DF Module(sqlservr+0000C2DF) (CAutoDb::FUse+00000031)
* 004B2BE3 Module(sqlservr+000B2BE3) (CreateFakeTableRowset+00000038)
* 00424175 Module(sqlservr+00024175) (OpenRowsetSS::OpenRowset+000000EC)
* 0050D873 Module(sqlservr+0010D873) (GetTableCursor+00000056)
* 0050D7FE Module(sqlservr+0010D7FE) (CQScanRowset::StandardGetRowset+0000009D)
* 00539A82 Module(sqlservr+00139A82) (CQScanTableScan::CQScanTableScan+0000008E)
* 005399EA Module(sqlservr+001399EA) (CXteTableScan::QScanGet+00000089)
* 004332E2 Module(sqlservr+000332E2) (CQScanHashMatch::CQScanHashMatch+0000051A)
* 00432E02 Module(sqlservr+00032E02) (CXteHashMatch::QScanGet+0000008C)
* 00427368 Module(sqlservr+00027368) (CXteProject::QScanGet+00000092)
* 0053D884 Module(sqlservr+0013D884) (CQScanSort::CQScanSort+000000BC)
* 0053D7A7 Module(sqlservr+0013D7A7) (CXteSort::QScanGet+0000012C)
* 0042306F Module(sqlservr+0002306F) (CQueryScan::CQueryScan+0000028E)
* 00422E59 Module(sqlservr+00022E59) (CQuery::Execute+0000006A)
* 0041D456 Module(sqlservr+0001D456) (CStmtQuery::ErsqExecuteQuery+0000022C)
* 0042C4AF Module(sqlservr+0002C4AF) (CStmtSelect::XretExecute+00000229)
* 0041C3CB Module(sqlservr+0001C3CB) (CMsqlExecContext::ExecuteStmts+000003B9)
* 0041BA11 Module(sqlservr+0001BA11) (CMsqlExecContext::Execute+000001B6)
* 0041B02D Module(sqlservr+0001B02D) (CSQLSource::Execute+00000357)
* 00437EC6 Module(sqlservr+00037EC6) (execrpc+00000507)
* 00437128 Module(sqlservr+00037128) (execute_rpc+00000019)
* 0042921A Module(sqlservr+0002921A) (process_commands+00000232)
* 41072838 Module(UMS+00002838) (ProcessWorkRequests+00000272)
* 410725B3 Module(UMS+000025B3) (ThreadStartRoutine+00000098)
* 77BCB3CA Module(MSVCRT+0002B3CA) (endthread+000000AB)
* 77E66063 Module(kernel32+00026063) (GetModuleFileNameA+000000EB)
* -------------------------------------------------------------------------------




View 1 Replies View Related

Question About Query Part Of A String

Aug 7, 2007

I'm passing a variable to SQL and I want it to query a column (IP_user), but query any part of what is given. For example if I given Chris it would bring up Chris, Christian, Christine, etc. What is the syntax to do this?? thanks

View 7 Replies View Related

SQL 2012 :: Function With 2nd Part Working On Results 1st Part

Jan 28, 2015

I have made the following Scalar-valued function:

CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)

[Code] ....

What it does is convert numbers to times

E.g.: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)

So far so good (function works perfectly)

My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.

This means that, e.g. with a time like 3.23.40 the last zero must be deleted.

My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,

is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))

There are 5 codes: 0 1 2 3 and 4

View 9 Replies View Related

Stored Procedure Query With A Conditional Part?

Apr 23, 2005

Hi,

I'm converting some direct SQL queries into stored procedures. The
original queries have conditional parts appended depending on certain
parameters like this:

Sql = "First part"
If certain condition then
   Sql &= "Second part"
Endif

How could I do this in a stored procedure? So far I have made them like this:

IF @condition > 0
Fist part Second part
ELSE
First part

You can see that I have to maintain 'First part' in two locations. What is a better way of dealing with this?

Thanks!

  Noc

View 3 Replies View Related

Query Problem (lack Of Knowledge On My Part)

Jan 17, 2006

Hello,
I've been trying for a couple of days now with the following problem in SQL
Ive a table called tblRresults that has the following
Date - just a regular date
OfficeLocation - just a unique id such as a zip code
SampleType - contains things like wall, floor, beam
SampleResult - "Detected" or "NotDetected"
When populated the table can look like
12/12/05   99505   Wall   Detected
12/12/05   99505   Wall   NotDetected
10/04/05   99211   Beam   Detected
10/04/05   99211   Beam   Detected
10/04/05   99111   Floor   NotDetected
10/04/05   99111   Floor    NotDetected
What I want is (I guess) a crosstab query that produces the following
Date         Location   SampleType CountDetected   CountNotDetected   
12/12/05   99505      Wall                  1                        1
10/04/05   99211      Beam                2                        0 (or null would be ok)
10/04/05   99111      Floor                0                        2
Any help gratefully accepted, this has been driving me mental!
thanks
 
 
 

View 2 Replies View Related

Query To Replace Part Of Field In PHPMyAdmin

Nov 27, 2011

I have a table called 'wp_postmeta' (Wordpress) which contains a column called 'meta_value'. A typical field in this column looks like this:

Code:
a:22:{s:12:"productimage";s:104:"http://www.disobeyclothing.com/wp-content/themes/eCommerce3/images/tshirts/SurveillanceSociety-Black.png";s:13:"productimage1";s:104:"http://www.disobeyclothing.com/wp-content/themes/eCommerce3/images/tshirts/SurveillanceSociety-White.png";s:13:"productimage2";s:0:"";s:13:"productimage3";s:0:"";s:13:"productimage4";s:0:"";
s:13:"productimage5";s:0:"";s:13:"productimage6";s:0:"";s:5:"pr

[code]...

I've tried to run the following query but each time I run it, it wipes all data in the field:

Code:
UPDATE wp_postmeta set meta_value=replace(meta_value, 'URL...')

I've also tried this query, but it has the same effect:

Code:
UPDATE wp_postmeta
SET meta_value = replace(LTRIM(RTRIM(meta_value)), URL...

View 5 Replies View Related

Distributed Query To Oracle Table With Four-part Name

Jan 17, 2007

Hi all,

I found an article which described about Distributed query to Oracle table with four-part name.

http://support.microsoft.com/kb/294459/en-us

However, we still got the same error with MSSQL2000 sp4 and SQL2005. Any fix to this?

View 1 Replies View Related

Dynamic Query: Using Parameter As A Part Of Table Name (possible?)

Mar 12, 2008


I have 2 tables (table1KKK, table2KKK), and want to run the same query on them by using parameter with the value "1" or "2".
Is it possible to use that parameter as a part of the queried table name?
Something like (only for demonstration €“ doesn€™t work):
Select *
From table +myParameter + kkk
Thanks in advance!

View 3 Replies View Related

Transact SQL :: Query To Return As Part Of The Fields

Oct 16, 2015

I am working on a query that is quite complex. I need the query to return as part of the fields a field that will contain the total percentage of tickets in a version.The query is below

select cat.name as name,count(distinct bug.id) as numberOfBugs,cast(count(bug.id) * 1000.0 / sum(count(bug.id) * 10.0) over() as decimal(10,2))/100 AS qnt_pct, vers.version, dateadd(s,vers.date_order,'1/1/1970') as "Release_Date"
from mantis_bug_table bug
INNER JOIN mantis_category_table cat on cat.id = bug.category_id
LEFT OUTER JOIN mantis_project_version_table vers on vers.project_id = vers.project_id and vers.version = bug.version

[code]....

View 12 Replies View Related

Please Someone Out There Has To Know... I Am Going Crazy

Oct 17, 2007

ok I will post no code this time. I need help I have tried a half of dozen methods and they all only work half way. I need to create a search page that searches on all keywords in a textbox. I have tried split functions, like commands, freetext catalogs.
I want to go with the free text search.
How?
Has anyone used a freetext search in asp or c#? please post some code I re arrange it or let me know how to pass the freetext search param to the sql server.....
 Please.....

View 3 Replies View Related

DTS Is Crazy! Or Maybe It's Me?

Oct 5, 2005

Hey all,I need to import an excel file into a SQL table. Users are able to modify and save the excel file. I have a stored procedure that calls a structured storage file which does the import. Both the excel file and the structured storage file reside in the same location. The first time that the stored procedure fires the DTS it works fine, but if the data in the excel file is overwirtten by a user it appears that the import does not function.Am I missing something here?Here is the SPROC:CREATE PROCEDURE [dbo].[importEmployees] AS
DECLARE @object intDECLARE @hr int
--create a package objectEXEC @hr = sp_OACreate 'DTS.Package', @object OUTPUTif @hr <> 0BEGIN    print 'error create DTS.Package'    RETURNEND
EXEC @hr = sp_OAMethod @object, 'LoadFromStorageFile',     NULL, 'C:importEmployees.dts', ''IF @hr <> 0BEGIN    print 'error LoadFromStorageFile'    RETURNEND
EXEC @hr = sp_OAMethod @object, 'Execute'IF @hr <> 0BEGIN  print 'Execute failed'  RETURNEND
GO

View 1 Replies View Related

Transact SQL :: Returning Different Data When Part Of Query Is Separate?

Aug 3, 2015

I am writing a stored procedure to prepare some reports.

The issue is that I am summing up the combined bid, 'QuoteTotal' + Sum(InvoiceItemAmount) (eg, quote add ons).

When part of the larger query it returns a different, and incorrect amount. The query listed after the main query is just that line and it's appropriate parts and it returns the correct amount. What can I do to correct this and where lies the problem so I can learn from this situation?

alter PROCEDURE [dbo].[Select_Quote_Info_By_Salesmen_By_Status]
@Salesmen nvarchar(50),
@QuoteStatus nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;

[code]...

View 5 Replies View Related

Problem Running SP_EXECUTESQL With OpenRowSet (with Variable) As Part Of The Query

Nov 27, 2007

Hi,

I had problem when combining OpenRowSet and SP_EXECUTESQL, when i tried to run the following query, it complaints that RESID is not declared. any idea how should i put the query so i will pass @RESID as 1 of the parameter? BTW, i know that the SP_EXECUTESQL is able to run query which length up to 8000, but how about the parameter?


DECLARE @SqlToRun NVARCHAR(MAX)
DECLARE @RESOURCEIDS NVARCHAR(MAX)

SET @RESOURCEIDS = REPLICATE(CAST('ABC' AS NVARCHAR(MAX)), 1000)

SET @SqlToRun = N'SELECT * FROM
OPENROWSET(''SQLNCLI'',
''server=;database=;uid=;pwd='',
''SELECT * FROM WHERE COL=@RESID'')'

DECLARE @PARAMS NVARCHAR(MAX)
SET @PARAMS = ''@RESID NVARCHAR(MAX)'

EXECUTE SP_EXECUTESQL @SqlToRun, @PARAMS, @RESID = @RESOURCEIDS

Regards,
Derek

View 5 Replies View Related

SQL 2007 Query Designer Using 4 Part Naming Linked Server - Bug ????

Jul 6, 2007

I have a linked server in SQL 2007 form SQL to DB2.(IBM)

If I write a distributed query from SQL to DB2 I get mt data returned.
ie:
select *
from openquery(Movex_Extranet,'Select * From mvxadta.ooline')

If I write a Query from SQL to DB2 using the 4-part naming convention linkserver.catalog.schema.object
then I also get my data returned.


ie:
select *
from MOVEX_EXTRANET.RCHASE5C.MVXADTA.OOLINE

My problem is with the Query Designer in SQL2007.

When I right click the first lot of code and select "Design query in editor"
The Designer GUI opens up and displays my table in graphical form with all columns selected - Great.



But When I repeat this for the code that uses the 4 part naming
The Designer opens up and displays the table but with no columns viewable or available for me to select ??

Anyone know what might be causing this to happen.
Is it a bug in SQL 2007 ??

I have tried linking the server using 2 types of providers ( One from Microsoft & one from IBM) but each time I still cannot see or choose columns in the designer using the 4part naming convention.


The 2 providers I tried where.
Microsoft OLE DB for ODBC Drivers.
IBM DB2 UDB for Iseries IBMDA400 OLE DB Provider



Any help greatly appretiated as I would really love to use the designer using 4part naming convention.

Apparently if you read this the it should work ?
http://msdn2.microsoft.com/en-us/library/aa225987(SQL.80).aspx#dvmscworkingwithtablesfromdifferentdatasources


Ray

View 1 Replies View Related

Crazy ADO Error

Aug 7, 2001

Hiya!
I have a VB6 app using ADO to connect to SQL Server 7. On one form I am doing two consecutive inserts and I want them to be wrapped in a transaction for integrity. My code looks like this:
connection.errors.clear
connection.begintrans
connection.execute statement 1
connection.execut statement 2
if connection.errors.count > 0 then connection.rollbacktrans else: connection.committrans.

On the BeginTrans line I get the following error:
-2147168227
"Transaction cannot start because more than one ODBC connection is in use". My NativeError is 0, I searched BOL and couldn't find anything informative so I think this is an ADO error only. Also, running sp_who in SQL showed everything was OK. So what's causing this error?

Thanks,
Sarah

View 1 Replies View Related

Old Crazy Database

Jul 14, 2004

Hi! Have you ever wanted to make life easier? Well, so I am :-)

I have to understand old database ported to ms sql server 2000. Well, the problem is that there are no primary keys at all, no constraints. Database logic is thrown onto triggers and Delphi applications. All fields that should be primary key are in people heads...just mentally :(

Well, you'll say try to find equal fields in different databases... well i was trying to do this. It's impossible...

Any ideas except social engineering?

Thanx

View 3 Replies View Related

Crazy Selection Help

Aug 21, 2007

Hi everyone -

I have two tables, one table (A) that holds a field called location that
has the partial path information of the file

and the second table (B) holds the full path including the filename

I only want to display the records from the partial path (A)
table that do not reside in the second (B) table


Table A =
imagefilename, description, directory, companyname, location
"96.jpg","test","Prog2006_02","Marey, John","Prog2006_0296.jpg"
"274.JPG","disney","Prog2006_07","Amy Gross","Prog2006_07274.JPG"
"570.jpg","sdfsdf","Prog2007_06","Lof3,"Test3","Prog2007_06570.jpg"
"850.JPG","222","Prog2007_08","Malis, Mark","Prog2007_08850.JPG"

Table B =
imagefilename
"d:webdatawebuploadsqfimagesProg2006_0296.jp g"
"d:webdatawebuploadsqfimagesProg2006_03112.p df"
"d:webdatawebuploadsqfimagesProg2006_03127.j pg"

I was thinking about using a cross join with a like condition,
but it fails (go figure)

could someone offer a little help to the query that i need to
perform??

thanks
tony

View 6 Replies View Related

I Have A Crazy Question!!! (Possible Or Un-Possible?)

Feb 6, 2008

Let me please start off by saying if your response to this question is:

"That's retarded! Why are you doing it that way! Do it this way!"

Please refrain from answering my query. With that out of the way, here I go.

I have attached a Sql Server 7.0 db (.mdf and .ldf file) to an instance of MSSQL 2000. When this attach happens, MSSQL2000 automatically upgrades it.

I then detach the db, and place the modified files back into the directory the Server 7.0 instance looks for. Obviously the format of the db files (.mdf and .ldf) have changed following the upgrade. I need to know if there is anything I can do (script or programmatically) to modify the files so that my Sql Server 7.0 instance can read them properly when the engine goes online.

Thank you in advance for any tidbits you can provide

View 7 Replies View Related

Crazy Question

May 9, 2008

Hi,

Is this at all possible to do. I have been stuck for a couple of hours trying.

SELECT DISTINCT ItemTag,
ItemBinLocation
FROM VInventory
WHERE
(
SELECT
CASE x
WHEN 'x'
THEN (LEFT(ItemBinLocation, 1) = 'abc'
WHEN 'y'
THEN LEFT(ItemBinLocation, 3) = 'efg'
END
FROM dbo.OtherTable
)

???
I need some Yak Milk please!

View 1 Replies View Related

Getting Crazy Error!

Sep 18, 2006

I keep getting this stupid error, if
any of you have time to kill and could look at this I would really appreciate
it.
This checks for a login in the
database and if it already exists it makes it unique.




In the error message, €˜ssewell504€™
was the value returned after the proc ran, the last 3 digits will be different
each time it€™s ran.

Thanks guys and take
care!Here's the error:

"Syntax error converting the varchar value 'ssewell504' to a column of datatype int"


ALTER proc
[dbo].[spx_CONS_CheckLoginUnique]

(
@SourceLogin varchar(25) = 'ssewell',
@TargetLogin varchar(25) = 'ssewell'
)
as
begin
declare @ReturnedValue int
declare @AlreadyInUse int
declare @AppendedValue varchar(3)
declare @Range int

set @Range = 999
set @AlreadyInUse = 1 -- set flag


--if target login and source login are
the same...we need to change target
login
if @SourceLogin =
@TargetLogin
begin
while @AlreadyInUse = 1 -- this stays eq to 1 until target login
is unique in DB
begin
Set @ReturnedValue = CONVERT(int, (@Range+1)*Rand()) -- get random
num
Set @AppendedValue = CAST(@ReturnedValue as varchar(3)) -- cast value to
varchar
set @TargetLogin =
@TargetLogin + @AppendedValue --
append to target login
--if by chance this login is already in
there(taken), keep going until it's
not
if EXISTS(select Login_Name from dbo.SECR_Login where Login_Name =
@TargetLogin)

begin

set @AlreadyInUse = 1
--set flag to keep
looping

end
else

begin

set
@AlreadyInUse = 0 --set flag to exit out of loop, our login is now
unique

end
end
end

return
@TargetLogin
end

View 3 Replies View Related

RESOLVED - Help With SQL Query - The Multi-part Identifier Alias Field Name Could Not Be Bound.

Nov 20, 2006

Hi Everyone
This is the query and I am getting follwoing error message

"The multi-part identifier "InvDate.Account Reference" could not be bound."

SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,
(SELECT *
FROM CUSTOMER) AS CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])
FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE]
ORDER BY CC.COMPANY ASC


By the way its SQL Server 2005 Environment.
Mitesh

View 4 Replies View Related

Select Records. Going Crazy.

Apr 29, 2007

Hello,I have two tables, Articles and Comments, with the following columns:Articles - [ArticleId] (PK), [ArticleTitle], [ArticleText], [ArticlePubDate]Comments - [CommentId] (PK), [ArticleId] (FK), [CommentTitle], [CommentText]I need to display on a web page the articles published during the last week and their comments.What I need is:1. Get Articles (DONE)  2. Get Comments for each ArticleThe solution I see are:1. Create a Stored procedure that somehow outputs 2 tables:     Articles and Comments associated with those articles2. Create 2 Stored procedures:    The first one outputs the articles.    The second output all comments given an article ID    In this case, while the data is being displayed on the page it will load the comments for each article.    The problem is that I will have many round trips to the server.I know how to use (2) but this would give me many round trips to the database.Could someone help me out with this?Thanks,Miguel  

View 4 Replies View Related

Inserting Blanks:I'm Almost Going Crazy-II

Mar 5, 2004

I posted this scenario earlier and ndinakar said my code worked on (I guess) his own machine but has not worked on mine! Does that not sound funny? I need to ge through with this simple but frustrating stuff in time. I've implemented more complex snippets before this but I just can't figure the problem out.

Can anyone out there tell me what I could be doing wrongly? I wrote a simple stored procedure called 'proc_insert_webuser' in a database that has been moved to a remote server called LAGOS-NTS3. I executed it from SQL Query Analyser and it worked. When, however, I executed the same stored procedure from ASP.Net it gives an impression that a new record has been inserted and even displays the newly generated ID column to me. On checking the base table I found only blank columns with only the ID column having the generated IDs!

I'm perplexed. I checked the code and can't possibly spot the error. Can anyone help. Found below are snippets used.



CREATE PROCEDURE proc_insert_webuser
(
@applicantidnumeric(18) output,
@firstnamevarchar(18),
@midname varchar(18),
@lastname varchar(50),
@secretcodevarchar(18),
@my_errnumeric(18) output
)
AS
IF (SELECT COUNT(ApplicantID) FROM tbl_webuser WHERE Firstname = @firstname AND Midname = @midname AND Lastname = @lastname) = 0
BEGIN
begin transaction
INSERT INTO tbl_webuser (Firstname, Midname, Lastname, SecretCode)
VALUES (@Firstname, @Midname, @Lastname, @SecretCode)
commit transaction
SET @applicantid = @@IDENTITY
END
ELSE
BEGIN
RAISERROR('Error! Execution aborted. A matching user profile exist.', 16, 1)
SELECT @my_err = @@ERROR
END
RETURN




Then the ASP.Net stuff:


Sub PostData()

'connection string production

Dim cstring As String = "User ID = sa; Password = ; database = E-Recruitment; Server = LAGOS-NTS3; Connect Timeout = 60"



'connection instantiation

Dim cnx As SqlConnection = New SqlConnection(cstring)



Try

'open connection

cnx.Open()



'instantiates and execute a command object

Dim cmd As SqlCommand = New SqlCommand



With cmd

.Connection = cnx

.CommandText = "proc_insert_webuser"

.CommandType = CommandType.StoredProcedure



Dim param1 As New SqlClient.SqlParameter("@applicantid", 0)

param1.DbType = DbType.Single

param1.Direction = ParameterDirection.Output

.Parameters.Add(param1)



Dim param2 As New SqlClient.SqlParameter("@firstname", txtFirstNM.Text)

param2.DbType = DbType.String

param2.Direction = ParameterDirection.Input

.Parameters.Add(param2)



Dim param3 As New SqlClient.SqlParameter("@midname", txtMidNM.Text)

param3.DbType = DbType.String

param3.Direction = ParameterDirection.Input

.Parameters.Add(param3)



Dim param4 As New SqlClient.SqlParameter("@lastname", txtLastNM.Text)

param4.DbType = DbType.String

param4.Direction = ParameterDirection.Input

.Parameters.Add(param4)



Dim param5 As New SqlClient.SqlParameter("@secretcode", txtPWD.Text)

param5.DbType = DbType.String

param5.Direction = ParameterDirection.Input

.Parameters.Add(param5)



Dim param6 As New SqlClient.SqlParameter("@my_err", 0)

param6.DbType = DbType.Single

param6.Direction = ParameterDirection.Output

.Parameters.Add(param6)



.ExecuteNonQuery()



'pick the auto-number

Dim strAppID = .Parameters("@applicantid").Value



'notifies the user of record success

lblMessage.Text = "Profile created. Your ApplicantID is : " & strAppID & " Kindly take note of your UserID. You'll need it for future logins."

End With

Catch ex As Exception

'notifies the user of any error(s)

lblMessage.Text = Err.Description & " originating from " & Err.Source

Finally

cnx.Close()

End Try

End Sub
==================

I'm calling this procedure from the click event of a command button. What could be happening to the code? I have done similar things and got positive results! Any hint from anyone out there?

View 6 Replies View Related

This Issue Drive Me Crazy

Aug 29, 2001

Hi

I have and issue with a query, I tried every thing but I couldn't find why. Here is the issue, I have one big DB1 with about 30 tables, the query based on a join of 5 tables, I have the right index in place, when I run the query it will take about a minute and 20 sec. But I have another DB2 on the same server and I imported just the 5 tables form DB1 with the same index, when I run the same query it will take 6 seconds.

I did drop and recreate the indexes and drop and recreate the statistics but still the same thing no difference.

I'm running SQL7 SP3.

Do you have any idea what might be the issue here?

Thanks for your help

View 4 Replies View Related

Parameter Values Going Crazy :(

Oct 5, 2006

Hi all,

I am having a serious problem developing my reports within VS2005. For no reason, on one of my report it seems the parameters have got a life of there own.

There is a collection of 4 parameters whose values change randomly (internal, hidden, multivalue). Regardless of what I set them to, once I click save some change, when I alter 1 parameter, the values of one of the other parameters change while the settings for the parameter I changed to not apply.

What is going on here, I have been developing reports without issue however this is becoming a major concern. Please help!

Kind regards
Taz

View 2 Replies View Related

Syntax Error - Driving Me Crazy

Nov 29, 2007

Hi Guys,    I have a syntax error in my SQL SELECT Query that is driving me crazy. All I am doing is adding a conditional with a literal value and that is breaking the query.This Query Works: SELECT
Quantity,
CAST(Gallons AS VARCHAR(10)) + ' ' + Style,
Colour
FROM
ORDER_TANK_LINK
LEFT JOIN TANKS
ON TANKS.TankID = ORDER_TANK_LINK.TankID
LEFT JOIN COLOURS
ON COLOURS.ColourID = ORDER_TANK_LINK.ColourID
 This One is Broken:SELECT
Quantity,
CAST(Gallons AS VARCHAR(10)) + ' ' + Style,
Colour
FROM
ORDER_TANK_LINK
ON ORDER_TANK_LINK.OrderID = 1
LEFT JOIN TANKS
ON TANKS.TankID = ORDER_TANK_LINK.TankID
LEFT JOIN COLOURS
ON COLOURS.ColourID = ORDER_TANK_LINK.ColourID
 Obviously I haven't explained the design of the tables, but because of the fact that I have another query that is so similar working perfectly, I didn't think you'd need it.
The exact error is "Incorrect Syntax Near the Keyword 'ON'". OrderID is definately a value of the ORDER_TANK_LINK Table and is of type int.
Thanks in advance!

View 4 Replies View Related







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