ReplMerge.exe Works But RMO Doesn't -

May 14, 2007

I have been successful in getting a Merge Replication to happen via the web using ReplMerge.exe, but unsuccessful in doing the same using RMO.



The following code is what I am using for merge replication via the web using the ReplMerge.exe and this is working fine.



replString = "C:Program FilesMicrosoft SQL Server90COMREPLMERG.EXE"

Dim procID As Integer

Dim newProc As Diagnostics.Process

Dim si As Diagnostics.ProcessStartInfo

si.WindowStyle = ProcessWindowStyle.Hidden

newProc.StartInfo = si

newProc = Diagnostics.Process.Start(replString, "-Publication pub_mergetest

-Publisher publishername

-Subscriber subscribername

-Distributor distributorname

-DistributorLogin sa

-DistributorPassword pwd

-DistributorSecurityMode 0

-PublisherDB MergeTest-Publisher

-SubscriberDB MergeTest-Subscriber

-PublisherLogin sa

-PublisherPassword pwd

-SubscriberLogin sa

-SubscriberPassword pwd

-SubscriptionType 1

-SubscriberSecurityMode 0

-LoginTimeOut 30

-ParallelUploadDownload 1

-QueryTimeOut 300")



procID = newProc.Id

newProc.WaitForExit()

Dim procEC As Integer = -1

If newProc.HasExited = -1 Then

procEC = newProc.ExitCode

End If



However, I cannot get the same to work via RMO. The error i get back is "The process could not connect to subscriber "subscribername". What am I missing? Any help and feedback is greatly appreciated.



Dim _mergeAgent As MergeSynchronizationAgent = New MergeSynchronizationAgent()

_mergeAgent.Publication = "pub_mergetest"

_mergeAgent.Publisher = "publishername "

_mergeAgent.Subscriber = "subscribername"

_mergeAgent.Distributor = "distributorname"

_mergeAgent.DistributorLogin = "sa"

_mergeAgent.DistributorPassword = "pwd"

_mergeAgent.DistributorSecurityMode = SecurityMode.Standard

_mergeAgent.PublisherDatabase = "MergeTest-Publisher"

_mergeAgent.SubscriberDatabase = "MergeTest-Subscriber"

_mergeAgent.SubscriberLogin = "sa"

_mergeAgent.SubscriberPassword = "pwd"

_mergeAgent.SubscriberSecurityMode = SecurityMode.Standard

_mergeAgent.SubscriptionType = SubscriptionOption.Pull

_mergeAgent.PublisherLogin = "sa"

_mergeAgent.PublisherPassword = "pwd"

_mergeAgent.PublisherSecurityMode = SecurityMode.Standard

_mergeAgent.LoginTimeout = 30

_mergeAgent.QueryTimeout = 300

_mergeAgent.UseWebSynchronization = True

_mergeAgent.InternetUrl = "https://###.###.###.###/virtualdirectory/replisapi.dll"

_mergeAgent.InternetLogin = "domainusername"

_mergeAgent.InternetPassword = "pwd"

_mergeAgent.InternetTimeout = 5000

_mergeAgent.Synchronize()

View 3 Replies


ADVERTISEMENT

Transact SQL AND Works, OR Doesn't

Mar 31, 2006

I have a stored procedure with a where clause like this:
WHERE
  Q.EffectiveDate >= @FromEffectiveDate  AND   Q.EffectiveDate <= @ToEffectiveDate AND   I.InsuredName LIKE '%' +  isnull(@PreQuoteDesc,I.InsuredName) + '%'  AND     isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'   AND   rsu.FirstName LIKE '%' + isnull(@OwnerFirstName, rsu.FirstName) + '%' AND  rsu.LastName LIKE '%' + isnull(@OwnerLastName, rsu.LastName) + '%' AND   Q.quoteID  = isnull(@quoteID,Q.QuoteID) AND   Q.QuoteStatusID = isnull(@quoteStatusID, Q.QuoteStatusID) AND  rsu.AspNetUserID = isnull(@ASPNetUserID, rsu.AspNetUserID)
-------------------------------------------------------------------
All is working well except for the line of business:
------------------------------------------------------------
AND     isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%'   AND   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
---------------------------------------------------------------------------------
If the user checks just 'Property' results look like:
Property
Property
Property, General Liability
If the user checks just 'General Liability' the resultes look like:
Genral Liablility
General Liability
General Liability, Inland Marine
If the user checks both Property and General Liability all they get back is:
Property, General Liability
They should get back everything including just Property or just General Liability or both.
So I tried to change the ANDs to ORs and it doesn't work.
-----------------------------------------
AND  (   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%'   OR   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%'   OR   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%'   OR   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%'   OR   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%'   OR   isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
)
I know this is incredibly hard to follow because its incredibly hard to write out.
Is there anyone smart out there who can figure this out?
Thanks

View 2 Replies View Related

CLI Connection To SQL Works, C++ Doesn't

May 7, 2008

I'm trying to connect to my SQL, and both ways I tried with CLI work. However, I have no luck with native C++, with the same string. Any ideas why? Here's the error message I get, and the source.

IM008
[Microsoft][ODBC Driver Manager] Dialog failed







Code Snippet

#include<iostream>
#include<windows.h>
#include<sql.h>
#include <sqlext.h>
using namespace std;
#using <mscorlib.dll>
#using <System.dll>
#using <System.Data.dll>
using namespace System::Data;

void main()
{
System::Data::SqlClient::SqlConnection^ cSql=gcnew System::Data::SqlClient::SqlConnection("Data Source=II.II.II.II;Initial Catalog=XXXXX;Integrated Security=SSPI;uid=YYYYY;pwd=ZZZZZ");
cSql->Open();
cSql->Close();
System::Data::OleDb::OleDbConnection^ cOledb=gcnew System::Data::OleDb::OleDbConnection("Data Source=II.II.II.II;Initial Catalog=XXXXX;Integrated Security=SSPI;uid=YYYYY;pwd=ZZZZZ;PROVIDER=SQLOLEDB");
cOledb->Open();
cOledb->Close();

SQLRETURN iRet;
SQLHANDLE EnvHndl;
SQLHANDLE ConnHndl;
iRet = SQLAllocHandle(1, 0, &EnvHndl);
iRet = SQLSetEnvAttr(EnvHndl, 200, (SQLPOINTER)3, 0);
iRet = SQLAllocHandle(2,EnvHndl, &ConnHndl);

wchar_t ConnStr[255];
wcscpy(ConnStr,L"Data Source=II.II.II.II;Initial Catalog=XXXXX;Integrated Security=SSPI;uid=YYYYY;pwd=ZZZZZ;PROVIDER=SQLOLEDB");
short ConnStrLength=wcslen(ConnStr);
iRet = SQLDriverConnect(ConnHndl, 0, (wchar_t*)ConnStr, ConnStrLength, (wchar_t*)ConnStr, 255, &ConnStrLength, 1);

long NativeError;
wchar_t*Msg=new wchar_t[10000];
wchar_t*SqlState=new wchar_t[10000];
int iParDiag=1;
short MsgLen=10000;
iRet = SQLGetDiagRec(2, ConnHndl, iParDiag, SqlState, &NativeError, Msg, 512, &MsgLen);
wcout<<ConnStr<<L'';
wcout<<SqlState<<L'';
wcout<<Msg<<L'';
}

View 1 Replies View Related

DataSet Works... DataTable Doesn't... (ODBC)

Jun 21, 2007

I have a longstanding problem where Stored Procedures or complex T-SQL called from VB.NET will not populate a DataTable object, but will work fine with a DataSet.  For example:    'oConn is defined elsewhere...        Dim sErr as String = ""        Dim dt As New DataTable        If Not oConn Is Nothing Then            Try                Dim sSQL as String = "select 1"                Dim oCommand As New OdbcDataAdapter(sSQL, oConn)                oCommand.Fill(dt)            Catch ex As Exception                sErr = "Database Error: " & ex.Message            Finally                sqlCloseConnection(oConn)            End Try        End Ifthis works fine and my dt DataTable object gets one row.  However using this as the SQL:                Dim sSQL as String = "declare @foo table(mycol integer);insert @foo select 1;select mycol from @foo;"does not work.   It executes with no errors, but the DataTable has no rows.  Finally, if I replace the DataTable with:                Dim ds as DataSetI can then get the data in ds.Tables(0) no problem.So, if the results of the sql are a single result table being put at index 0 of a DataSet, why are they not being put in a single DataTable?When a sql is a simple select statement it always works directly to a DataTable.  Only when it's a SP or sql with some logic does it require the DataSet approach.  This is a reporting utility so I need to standardize the code though the sql will be dynamic. Any ideas?  

View 1 Replies View Related

Scheduled Job Doesn't Work, But Works Manually

Apr 17, 2007

I have scheduled a job in Management Studio, but it doesn't work. However, when I run it maually in Visual Studio it works. I have connected an outside server by mapping it to mine. Maybe this is the problem?

I have also tried to configure a linked server, but I cannot find out how to connect my SSIS package to the linked server.

Can anybody help me?

Thank's!

View 8 Replies View Related

Job Doesn't Work But Package Works Fine

Jun 26, 2007

hi,

I have many jobs on sql 05 and all work but one. This one writes to an Access DB on the same server as SQL. The package works fine. But when executed in the context of the SQL Agent job, it fails.

Jobs that write to a text file work fine. The Access DB has no password required. By the way, that job in sql 2000 worked fine.

Any ideas?

View 4 Replies View Related

'Run Package' Works On Server, But Doesn't Complete As Job.

Mar 10, 2008



I have an SSIS package is made up of SQL tasks and dataflows. The dataflows connect to an Oracle database using Native OLE DBOracle Provider for OLE DB (10g). This is the first package dealing with oracle that runs on the server.
I can execute the package manually by right clicking and going to 'Run Package' while logged in remotely from the server, but it gets hung up and does nothing if I run it as a job. I always have to quit the job. I can disable everything but the dataflows in the package and the job completes and runs fine.

Anyone have any ideas or similiar situations?

Thanks.

View 5 Replies View Related

Query Works Fine Outside Union, But Doesn't Work .. .

Mar 31, 2004

hi all

I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.

any one know why that might be the case??


select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc




i've included the union query here for completeness of the question



begin
declare @current_date datetime
set @current_date = GETDATE()


select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc

end

View 1 Replies View Related

My SP Works On 2005 Bit Not On 2000 It Doesn't Step Through My Code.

Aug 23, 2007



Hi

I have a SP that works on SQL 2000 but not on 2005

It is just suppose to step through my code and insert values into tables where it finds the "ticked" values

here is apiece of my code. I hope it Helps.







Code Snippet
INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent)
SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent]
FROM StageMemberUploading
WHERE ID = @numValues

SET @CurrentValue = (SELECT SCOPE_IDENTITY())
IF @ClientID IS NOT NULL BEGIN
INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID)
VALUES (@CurrentValue, @ClientID, @UsergroupID)
END
IF @DateOfBirth IS NOT NULL BEGIN
INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue)
VALUES (@CurrentValue, 1, @DateOfBirth)
END
-------------------My Code Stops here ------------------------------
IF @Male = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@CurrentValue, 2, 1)
END
IF @Female = 'x' BEGIN
INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID)
VALUES (@CurrentValue, 2, 3)
END


Any help would be greatly appreciated

Kind Regards
Carel greaves

View 5 Replies View Related

Help: Why IN-Operator With Select-Statement It Doesn't Work? But With Given Values It Works

Jun 4, 2007

Hello to all,
i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me.
I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418:
declare @IDM int;
declare @IDO varchar(8000);
set @IDM = 3418;
set @IDO = '3430' 
select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ))
but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select *
from wtcomValidRelationships as A
where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM))
I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks
I use MS SQL 2005 Server Management Stadio Express
Thanks a million and Best regards
Sha

View 2 Replies View Related

SQL Query That Works In SQL Server Management Studio, But Doesn't On .NET 2.0 Page

Feb 21, 2008

SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasource and a gridview, no data is displayed.anyone know what my problem is? 

View 1 Replies View Related

Select Records Between Dates - Query Works In VS 2005 But It Doesn't In Asp 3

Nov 9, 2006

Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance

View 1 Replies View Related

DateAdd Expression Works In Tsql But Doesn't Work In Ssis

May 9, 2007

Hi There,

I am trying to set a variable with this default value using expression. This works in tsql but doesn't in ssis. Can anybody tell me what is wrong with this?



dateadd("dd", -1, datediff("dd", 0, getdate()))



Thanks.

View 8 Replies View Related

Works Fine In Designer But When I Load The Report It Doesn't Work

Oct 23, 2006

works fine in designer but when i load the report services
I get the following error
anybody know what to do
there is one subreport with this report
maybe the passing value but what could be wrong ????

Item has already been added. Key in dictionary: '9' Key being added: '9'

View 2 Replies View Related

Windows App Form Works: Datagrid Doesn't 'login Failed' ..tutors Out There?

Sep 11, 2005

Anyone live in seattle (meet on cap hill)? I have been stuck for weeks now and can't wait any longer wasting time - i will pay someone to set me up correctly quickly. I doubt this will be resolved online, but here goes... I need a datagrid to bring up simple database info (northwind) instead of showing the error 'login failed for mycomputeraspnet'. I am using windows authentication and my string is connectionstring = datasource=(local)\netsdk;initial catalog=Northwind;integrated security=sspi; persistent security=false;, I have seen online they say alter the authorization in the app virtual directory-- is that the C/inetpub/wwwroot or the web config of my project? I did so in the web config of my project, and also I added the impersonate=true to same already. I shared all the folders and made sure they are not read-only. I am flabbergasted.
Paul
XP pro 2004 sp 2
vis studio 2002 w updates
C# Asp web app project

View 2 Replies View Related

ReplMerge.exe Vs. RMO

May 10, 2007

What are the pros and cons using replMerge.exe vs RMO? I'm specifically interested in error handling issues. I have a test app that calls replmerge and it works great, but if there are errors in the replication, does it pass back specific error information?

View 3 Replies View Related

Why The Query Works In Query Analyser And Doesn't Work In Reporting Service ?

Apr 30, 2007



Hello everybody,



I'm developing a report using the following structure :



declare @sql as nvarchar(4000)

declare @where as nvarchar(2000)



set @sql = 'select ....'



If <conditional1>

begin

set @where = 'some where'

end



If <conditional2>

begin

set @where = 'some where'

end



set @sql = @sql + @where



exec(@sql)



I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.



I realize that when I cut off the if clauses, then it works at Reporting services.

Does anybody know what is happening?

Why the query works in query analyser and doesn't work in Reporting Service ?



Thanks,



Maurício

View 2 Replies View Related

Http://localhost/reportserver Works Http://&&<servername&&>/reportserver Doesn't

Aug 1, 2007

Hi All,

I have setup SSRS 2000 and gotten it to work but I am having trouble with SSRS 2005. I can't access to reportserver anywhere on the network. The only way to get to reportserver is termserv into the server and hit it with http://localhost/reportserver The server is Windows 2003 server Standard Ed. running SQL 2005 SP2 and Sharepoint Portal Server 2007. Can somebody please help? Thank you.

View 11 Replies View Related

SQL SERVER ACCESS DENIED!! BUT Everything Works On MSDE And Works SHOWING RECORDS ON SQL SERVER!! PLEASE HELP

Jul 26, 2004

I've got a popular problem so i get a message that server acces denied! ..

But that what is different in my error.... When i use same setting same database and connection string (on MSDE server) there is no problem...

On SQL server i have got windwos authentication but i added all accounts as ASPNET and SA.... and when i try to connect by

RETTO - name of my server

server=RETTO;uid=sa;pwd=password;database=db1;
or by
Integrated Security=SSPIserver=RETTO;uid=RETTOASPNET;database=db1;

I CAN BROWSE RECORDS THERE ARE NO PROBLEMS WITH CONNECTION!!! but when i try to update or iinsert or delete something in database there becomame this error that access denied or server does not exist!!!


PLEASE HELP I'm FIGHTING WITH THAT FOR OVER 5 DAYS!!!

I MADE FOR MY ACCOUNTS (SA, ASPNET) ALL THINGS ALLOWED AS EXECUTING stored procedures.. OR ACCESING datatables with insert delete and update query WHERE IS THE PROBLEM!!!??

View 3 Replies View Related

Telnet Connection Works, Sql Cmd Connection Works, SQL Server Managment Studio 2005 Does Not

Jun 20, 2007

I'm having a strange problem with this but I know (and admit) that the problem is on my PC and nowhere else. My firewall was causing a problem because I was unable to PING the database server, switching this off gets a successful PING immediately. The most useful utility to date is running netstat -an in the command window. This illustrates all the connections that are live and ports that are being listed to. I can establish a connection both by running



telnet sql5.hostinguk.net 1433 and

sqlcmd -S sql5.hostinguk.net -U username -P password



See below:



Active Connections

Proto Local Address Foreign Address State

TCP 0.0.0.0:25 0.0.0.0:0 LISTENING

TCP 0.0.0.0:80 0.0.0.0:0 LISTENING

TCP 0.0.0.0:135 0.0.0.0:0 LISTENING

TCP 0.0.0.0:443 0.0.0.0:0 LISTENING

TCP 0.0.0.0:445 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING

TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED

TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED

TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING

TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING

TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING

UDP 0.0.0.0:445 *:*

UDP 0.0.0.0:500 *:*

UDP 0.0.0.0:1025 *:*

UDP 0.0.0.0:1030 *:*

UDP 0.0.0.0:3456 *:*

UDP 0.0.0.0:4500 *:*

UDP 81.105.102.47:123 *:*

UDP 81.105.102.47:1900 *:*

UDP 81.105.102.47:5353 *:*

UDP 127.0.0.1:123 *:*

UDP 127.0.0.1:1086 *:*

UDP 127.0.0.1:1900 *:*

Both these utilities show as establishing a connection in netstat so I am able to connect the database server every time, this worked throughout yesterday and has continued this morning.

The problem is when I attempt to use SQL Server Management Studio. When I attempt to connect to tcp:sql5.hostinguk.net, 1433 nothing shows in netstat at all. There is an option to encrypt the connection in the connection properties tab in management studio, when I enable this I do get an entry in netstat -an, see below:



TCP 81.105.102.47:1138 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1139 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1140 217.194.210.169:1433 TIME_WAIT



Amost as if it's trying the different ports but you get this time_wait thing. The error message is more meaningful and hopefull because I get:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)

I would expect this as the DNS has not been advised to encrypt the conection.

This is much better than the : Login failed for user 'COX10289'. (.Net SqlClient Data Provider) that I get, irrespective of whether I enter a password or not.


This is on a XP machine trying to connect to the remote webhosting company via the internet.

I can ping the server

I have enabled shared memory and tcp/ip in protocols, named pipes and via are disabled

I do not have any aliases set up

No I do not force encryption

I wonder if you have any further suggestions to this problem?

View 7 Replies View Related

I Don't Understand How This Works, However It Works (sometimes)

Sep 26, 2006

I use the code below for updating data from a AS400 Liked server. I dont understend how the WHERE NOT EXISTS( sections work however usualy they do, in this case it does not andt I can't seem to find out why.

Does anyone see the error?

Thanks

--=========================================
--Create a local temporary table that hold
--all the data from the source table
--=========================================

SELECT * INTO #TEMP FROM dbo.LINK_LTTSTOC

--=========================================
--Remove table entries that are no longer
--needed or that have to be updated
--=========================================

DELETE FROM LTTSTOCK

WHERE NOT EXISTS( SELECT * FROM #TEMP

WHERE LTTSTOCK.WarehouseNo = LTWHLO

AND LTTSTOCK.Location = LTWHSL

AND LTTSTOCK.ItemNo = LTITNO

AND LTTSTOCK.NumberAvail = LTAVAL

)

--=========================================

--Insert data that is missing or that

--needed to be updated and was previously

--deleted

--=========================================

INSERT INTO dbo.LTTSTOCK(WarehouseNo,Location,ItemNo,NumberAvail,rowguid)

SELECT DISTINCT LTWHLO,LTWHSL,LTITNO,LTAVAL, NEWID()

FROM #TEMP

WHERE NOT EXISTS( SELECT * FROM LTTSTOCK

WHERE WarehouseNo = LTWHLO

AND Location = LTWHSL

AND ItemNo = LTITNO

AND NumberAvail = LTAVAL

)

--========================================

--Remove local temporary table.

--========================================

DROP TABLE #TEMP

View 2 Replies View Related

DTS. Works Sometimes, Not Others

Sep 17, 2001

I'm running a DTS package that works correctly when I do "Execute Package" directly, by right clicking the package.

But, if I schedule the job, it fails.

It's trying to write data to another server, but I can't see why it doesn't work as a scheduled job. I tried changing all the parameters I can think of. I'm no Windows expert, so if you have any ideas, please feel free to explain as if you're talking to a "newbie" (since I am one)

Win 2000, SQL 2000

View 1 Replies View Related

This Works, But...

Apr 8, 2008

I am going to tangle with our ERP system for the rest of my natural life, and in so doing, will need to create new SQL queries on an almost-daily basis. Based on the handful of queries that I have created against it so far, I recognize that I need to get better at authoring them.

Towards that end, I was wondering if anyone with a little firmer grasp on the subject could take a look at this query (which works & does exactly what I need it to do) and make suggestions as to what I might have done better/differently.

I've changed the names of everything to make it more comprehensible (I hope).

The query returns one row for each unique record in DetailTable, with data from the SummaryTable and a PartDescription from ThirdTable.

SELECT SummaryTable.RecordID,
SummaryTable.Status,
SummaryTable.CustomerName,
SummaryTable.CustomerNumber,
SummaryTable.OrderNumber,
SummaryTable.AssignedTo,
SummaryTable.ResolvedBy,
SummaryTable.ResolveDate,
SummaryTable.PartNumber,
SummaryTable.PartRevision,
SummaryTable.OrderQuantity,
SummaryTable.IssueCategory,
SummaryTable.IncidentDate,
SummaryTable.InquiryDate,
SummaryTable.IssueClass,
SummaryTable.Severity,
SummaryTable.IssueNumber,
SummaryTable.AuthorizedBy,
SummaryTable.Facility,
DetailTable.AssignedTo,
DetailTable.ActionDate,
DetailTable.ActionBy,
DetailTable.JobNumber,
DetailTable.ActionTaken,
DetailTable.NextAction,
DetailTable.IncidentNotes,
ThirdTable.PartDescription

FROMtheDatabase.dbo.DetailTable
LEFT JOIN
theDatabase.dbo.SummaryTable ON DetailTable.RecordID=SummaryTable.RecordID
LEFT JOIN
theDatabase.dbo.ThirdTable on SummaryTable.PartNumber = ThirdTable.PartNumber
AND SummaryTable.PartRevision = ThirdTable.PartRevision

WHERE (SummaryTable.IssueCategory='1' OR SummaryTable.IssueCategory='2' OR SummaryTable.IssueCategory='3'
OR SummaryTable.IssueCategory='D' OR SummaryTable.IssueCategory='E' OR SummaryTable.IssueCategory='L'
OR SummaryTable.IssueCategory='O' OR SummaryTable.IssueCategory='R' OR SummaryTable.IssueCategory='S'
OR SummaryTable.IssueCategory='V' OR SummaryTable.IssueCategory='X' OR SummaryTable.IssueCategory='Z')
AND SummaryTable.Facility='Default' AND SummaryTable.Status='OPEN'

ORDER BY SummaryTable.RecordID

View 6 Replies View Related

Sometimes It Works, Sometimes Not...

Sep 12, 2007

I have a VB app that loads 9 text files for 8 different products, one product at a time. The user must select the product to import. For each product the application:

1. Copies the 9 text files from the source directory to a "process" directory.
2. Calls a stored procedure that sequentially calls a different SSIS package for each text file.
3. Performs some additional processing.

Here's the problem. For the 7th product in the sequence, at least 2 of the text files are not being loaded, and no error exceptions are being thrown. For the 8th product in the sequence at least one text file is not being loaded. If I comment out the call to the stored proc in VB and run the stored proc manually at that point in the program, all files are processed.

On the text file side, the files are being copied correctly and the read-only flag on the file is not being set. In addition, I can open the files in a text editor without any problem.

I'm totally stumped here, so any helpful advice would be appreciated.

TIA,

Mike

View 2 Replies View Related

Dts Works From SqlServer But Not From ASP.net

Apr 18, 2005

I have an asp.net page that executes a DTS. When I execute that DTS from enterprise manager it takes about 5000 rows from the as400 and insert into sql serverIt works right. but when I execute it from my asp.net page I have this error.Error al procesar DTS TransferirDatos(ExistMP) en el paso DTSStep_DTSActiveScriptTask_1System.Exception: Error al procesar DTS TransferirDatos(ExistMP) en el paso DTSStep_DTSActiveScriptTask_1 at LibreraLentos.exec.ejecuta_SP_EXISTENCIASMP() in C:Documents and SettingsluisvalenMis documentosVisual Studio ProjectsInventariosLentosLibreraLentosexec.vb:line 43 at InventariosLentos.generacionprocesomateriaprima.btnenviar_Click(Object sender, EventArgs e) in C:AplicacionesWebInventariosLentosgeneracionprocesomateriaprima.aspx.vb:line 60LibreraLentos I have this on my ASP page   Private Sub btnenviar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnenviar.Click        Try            objexecsp.ejecuta_SP_EXISTENCIASMP()            lblmensajes.Text = "Proceso generado satisfactoriamente"        Catch ex As Exception            lblmensajes.Text = ex.Message + ex.GetBaseException.ToString + ex.Source.ToString        End Try    End Subthis on my Data Classs   Public Function ejecuta_SP_EXISTENCIASMP()        ' call UpdatePrice using a parameter array of SqlParameter objects        Try            Dim ejecutardts As New cDTS            ejecutardts.EjecutarDTS("TransferirDatos(ExistMP)")        Catch ex As Exception            Throw ex        End Try    End FunctionThis is what executes the DTSImports System.Runtime.InteropServicesImports System.Configuration.ConfigurationSettingsImports DTSPublic Class cDTS    Public Sub EjecutarDTS(ByVal NombreDTS As String)        Dim pkg As New DTS.Package        Dim oStep As DTS.Step        Try            pkg = New DTS.Package            'pkg.LoadFromSQLServer(AppSettings("MED20NT"), AppSettings("user"), AppSettings("pwd"), DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "pruebaCdr1")            pkg.LoadFromSQLServer("MED20NT", "sa", "prueva", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", NombreDTS, "")            pkg.AutoCommitTransaction = True            pkg.Execute()            For Each oStep In pkg.Steps                If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then                    Throw New Exception("Error al procesar DTS " & pkg.Name & " en el paso " & oStep.Name)                End If            Next        Catch ex As System.Runtime.InteropServices.COMException            Throw ex        Catch ex As Exception            Throw ex        Finally            pkg.UnInitialize()            pkg = Nothing        End Try    End SubEnd Class

View 2 Replies View Related

SMO Backup Works On One Not The Other

Jun 9, 2006

Can anyone enlighten me on sqlexpress smo?
I have compiled an exe using vbc running the folloing code
Imports SystemImports Microsoft.SqlServer.Management.SmoImports Microsoft.SqlServer.Management.CommonImports System.IO
Module SMOtest
    Sub Main()      Try        Kill(System.Environment.GetFolderPath Environment.SpecialFolder.ProgramFiles) & " estSMO_BACKUP")      Catch      End Try      Try        System.Threading.Thread.Sleep(500)        Dim bkpfileName As String = System.Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & " estSMO_BACKUP"        Dim backDeviceItem As New BackupDeviceItem(bkpfileName, DeviceType.File)        Dim db As String = "test"  ' Define and set db        Dim bck As New Backup()  ' Instantiate a Backup object        bck.Action = BackupActionType.Database  ' Set Action
   bck.BackupSetName = db & "_BackupSet"  ' Set Backup
        bck.Database = db  ' Set Database name property        bck.Devices.Add(backDeviceItem)
        Dim srv As New Server()  ' Instantiate a Server object        bck.SqlBackup(srv)  ' Invoke Backup object's SqlBackup method      Catch      End Try    End SubEnd Module
This works fine on a win2k dev system though when moving to another win2k system the code will error on non system databases. If db= model,master,tempdb etc it executes fine. When I try to backup a created db (any) I get an error stating the name is not found in sysdatabases.
SELECT name FROM master..sysdatabases
where name not in ('master','tempdb')
shows all the created databases including "test" in this case
keep in mind many recompiles using different db's work fine on one system and not the other. Even restored "test" from a backup from the system with no problems only to have a 3041 error consistently on the other. System databases work fine for all attempts on the offending system.
Any ideas? (the offending system is a SAT raid which has been evil from the get go) corruption seems to be the only conclusion I can reach
 
 
 
 

View 11 Replies View Related

From Access To SQL And Now Nothing Works!

Apr 11, 2006

Help, I had my entire DB created and when i thought i was done, i upsized to SQL and now almost none of my queries work?

The below works when i remove Distinct, but then i have doubles?


Code:

SELECT DISTINCT
Equip_ProductName.ProductName, Equip_ProductName.ProductInfoID, Equip_ProductName.ProductDesc, Equip_ProductName.ProductSearchTerm,
Equip_ProductName.ProductMore, Equip_ProductName.Visible, Equip_Products.ProductID, Equip_Products.CategoryID
FROM Equip_Products INNER JOIN
Equip_ProductName ON Equip_Products.ProductInfoID = Equip_ProductName.ProductInfoID
WHERE (Equip_ProductName.Visible = 1) AND (Equip_Products.CategoryID = 1)
ORDER BY Equip_ProductName.ProductName

View 6 Replies View Related

Normalisation Vs If It Works Just Do It!

Apr 16, 2006

Hi All,

As an accomplished web devver of many years using ASP and ASP.NET in conjunction with Access and SQL Server, I am a bit pedantic on the rules of good data structures.

Specifically the two main rules of data redundancy and normalisation.

The latter dictates at the lowest level that a data table should NOT contain a field that can be gleaned from one or a combination of others.

I have a problem with this now, I am building a betting system which will take the odds given, plus the stake placed and calculate the winnings or losses accordingly.

There is an added complication in that not all profit is calculated the same way, as a horse can also be 'placed' which does the same calculation as for profit, but then quarters it, so one single select statement won't do.

I could calculate this at data entry stage on a per entry basis and simply store in a Profit/Loss field and keep the value for each bet, however I know this is not the correct thing to do!

My other alternative [and the correct method] is to do this calculation at data request time, but that would involve the use of a cursor or loop in the SP.

I am aware of the huge resources a cursor can consume and I am not sure which is worse, using a cursor or ignoring the normalisation procedures.

So the question is this, what would you do here?

Since I may not be the same SQL Server expert as I am a programmer, is there an alternative way of reading all the bets and doing these calcs on SQL server and bang them back to ASP as a self contained recordset with all the profit/losses calculated for each bet?

Each bet as a unique EntryID and there is a field called Result which stores 'Win, Place or Loss' accordingly.

Thanks in advance of any help/opinions.

:)

View 4 Replies View Related

Dts Works But Run Same Dts Via Sql Job Failed

Nov 14, 2007

hi all,
i posted this somewhere else but i think here is the right group.

i have a dts that shld write to a text file located at a mapped drive. i read somewhere that sql job does not recognise mapped drive, so i use UNC path in form \128.1.1.1dtsfile, which dtsfile folder is the shared folder name. i opened the folder permission to everyone.

thats abt the remote server. the sql server i am running is on windows NT, logon using Administrator to local. my sql is SQL 2000. i register the server using sa username. the server agent is start up using System account.

now the problem is when i run the dts interactively/manually, it runs succesfully. but when i run it thru SQL job it says "Access denied". Or full error is like this,

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

i really hope and appreciate if someone out there can help me out. thanks!

ps: pls let me know if more clues needed from my environment settings.

View 7 Replies View Related

DTS Works But Job Fails

Dec 18, 2007

Hi

I have a DTS to copy data from Oracle to SQL Server. When I logon to SQL Server box with a userID xxx, I can run the DTS from EM and it works perfectly fine but when I schedule the DTS as job, it fails.

SQL Server agent is running with same account "xxx"
DTS connects to SQL Server with sa authentication
Job owner is same account "xxx"

Job error log

Executed as user: DOMAINNAMExxx. ... Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnStart: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Drop table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO] Step DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[PART_CATALOG] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[INVENTORY_PART] Step DTSRun OnStart... Process Exit Code 6. The step failed.


I copied the DTS to another one and scheduled it

This time I got the error log
Executed as user: DOMAINNAMExxx. ...... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnFinish: Create Table [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnStart: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step DTSRun OnError: Copy Data from SUPPLIER_INFO_ADDRESS to [IFS_PROD].[dbo].[SUPPLIER_INFO_ADDRESS] Step, Error = -2147467259 (80004005) Error string: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed. Error source: Microsoft OLE DB Provider for Oracle Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (. The step failed.

Please help!
Thanks in advance

View 3 Replies View Related

The Code Works ... But Is It Right?

Dec 11, 2006

Hi,

Is there a better (more elegant) way to build the file name than the following code:


/* File name has format mmddhhmmDX.748 with all time/date values zero padded */

DECLARE @FileName char(14)
DECLARE @out_dte datetime
DECLARE @CharDate char(8)
DECLARE @CharTime char(8)

set @out_dte = '2006-08-03 04:05:12.670'
set @CharDate = convert(char(8),@out_dte,1)
set @CharTime = convert(char(8),@out_dte,8)


set @FileName = substring(@CharDAte,1,2) +
substring(@CharDate,4,2) +
substring(@CharTime,1,2) +
substring(@CharTime,4,2) +
'DX.748'
select @FileName

/* Expected Result: 08030405DX.748 */


My first attempt was this:

DECLARE @FileName char(14)
DECLARE @out_dte datetime

set @out_dte = '2006-08-03 04:05:12.670'
set @FileName = cast(DATEPART(mm,@out_dte) as char(2)) +
cast(DATEPART(dd,@out_dte) as char(2)) +
cast(DATEPART(HH,@out_dte) as char(2)) +
cast(DATEPART(mm,@out_dte) as char(2)) +
'DX.748'


select @FileName

… but then date and time values were left justified spaced filled.
Example output: 8 3 4 8 DX.748

I don't just want to get code that works. I want to learn how to write the best possible code.

Thanks,

Laurie

View 3 Replies View Related

How Clustering Works?

Mar 5, 2007

Hi,

I am having data like this

Studid Date Perf

001 01/01/2008 90

001 02/01/2008 89

001 03/02/2008 91

002 01/01/2008 75

002 02/01/2008 79

002 03/02/2008 69

I gave Perf as PREDICT. When I use the

"SELECT * FROM [Cluster_Model]"

Query I am getting

Perf

82.

Can anyone help me how clustering works? and how to write a Query to group the values here based on StudId?

View 1 Replies View Related

How Does Sp_start_job Works?

May 27, 2008

Hi:

I would like to use sp_start_job to execute a SSIS Package but I am not sure how it works. If there are two requests run the job two times simultaneously or sequentially?

View 4 Replies View Related







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