I don't understand how this works, however it works (sometimes)

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

Walter:

You have the delete statement:

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
)

Does the the combination of (1) ItemNo, (2) WarehouseNo, and (3) Location guarantee uniqueness in your table?

Dave

|||

Hi Dave

Yes this is the case

Walter

I dont know How can i upload my sqlserver file to the server

I don't know How can i upload my sqlserver file to the server
could you help me?
could you write an example code in query analyser for
uploading sql server file to databaseDo you have access to the SQL Server on the server? That is, can you connect to the server from the machine with your SQL Server? If so, you can use DTS (in internet explorer, right click on the database and select Import (or export, depending upon the database you right click on).

i cant start page to perform

this is my code:

1myconnection =new SqlConnection(kiemtraloi,chuoiketnoi);2mycommand.Parameters.Add(new SqlParameter("@.mancc",SqlDbType.VarChar,10));3mycommand.Parameters["@.mancc"].Value = TextBox1.Text;4myconnection.Open();5myreader = mycommand.ExecuteReader();6if(myreader.HasRows)

And when I click to start button, a error appear:

NO overload for method 'sqlconnection' takes '2' arguments

I don't know what i must do :(, please help me

thx

Is that all you have or do you have more code? Where is the SQL statement? and the command object?|||

string chuoiketnoi = System.Configuration.ConfigurationSettings.AppSettings["myconn"];

System.Data.SqlClient.SqlConnection myconnection;

System.Data.SqlClient.SqlCommand mycommand;

System.Data.SqlClient.SqlDataAdapter myadapter;

System.Data.SqlClient.SqlDataReader myreader;

System.Data.DataSet mydataset;

// khai bao chuoi cho maytinh

string selectquery = "Select * from maytinh";

string insertquery="Insert into maytinh(mamay) values(@.mamay)";

string updatequery="Update maytinh set mamay=@.mamoi where mamay=@.mamay";

string deletequery = "Delete from maytinh where(mamay=@.mamay)";

// khai bao chuoi cho nhacungcap

string selectnhacungcap="Select * from nhacungcap";

string insertnhacungcap="Insert into nhacungcap(mancc,tenncc,diachi,dienthoai) values(@.mancc,@.tenncc,@.diachi,@.dienthoai)";

string updatenhacungcap="Update nhacungcap set mancc=@.manccmoi,tenncc=@.tennccmoi,diachi=@.diachimoi,dienthoai=@.dienthoaimoi where mancc=@.mancc";

string deletenhacungcap="Delete from nhacungcap where(mancc=@.mancc)";

string kiemtraloi = "Select * from nhacungcap where mancc=@.mancc";

|||

That's because the constructor of SqlConnection class only takes 0 or 1 argument, but you pass 2 arguments to it:

myconnection =new SqlConnection(kiemtraloi,chuoiketnoi);

Why did you pass 2 strings to the constructor?

|||

I think he wants:

myconnection=new SqlConnection(chuoiketnoi);

mycommand=new SqlCommand(kiemtraloi,myconnection);

sql

I cannot get Enterprise Manager to remotely connect!

I have an ASP page that is pulling data from an SQL server 2000 database. The code connects just fine using the specified Username and Password which was set up by the admin.

However, using that same username and password, I cannot get connected using Enterprise Manager SQL registration method so I can change database settings.

The error message I get says: SQL server does not exist or access denied (Conection Open(connect)).

The SQL server does exist (because it finds it in the server list). For some reason I am getting access denied. The server is running a mixed authentication mode.if sql2000 is running on win2003,it must be fix by sql service pack .
then you can settle the problem.|||I am trying to connec to a machine running win2000.|||Are you registering by machine name or ip address ? Can you ping the server ? When you say remotely - please provide more details on this ?|||Remotely means outside the firewall. I have also tried to connect inside the firewall. Inside the firewall, the server is listed by name in the available servers list when registering a new server. It just won't connect. Outside the firewall I am typing the IP in and nothing. SQL server is running on the webserver I am working off of.|||Can you ping the sql server (from inside or outside) ? Did you try using the ip address when registering from inside/outside ? What does your client network utility show as far as enabled protocols ? What are the errors you receive inside/outside ? Are you using the same machine to connect in both example (inside/outside) ?|||Also, since a firewall is involved - validate that the sql port is open. The default/normal port is 1433, but might have been changed by the sa/dba for security.

I cannot connect an application using OLE DB

When I try to make the connection, the following message appears

"Cannot open database requested in login 'DBName'. Login fails."
Err Code = -2147467259

We are using SQL Server 2000 on NT Server 4.0 SP 6.
The client machine has MDAC 2.6 SP1.

All ideas are welcome.

ThanksSounds like the connection to the server has been made but the login cannot open the database. Could be because the database does not exist or because the login does not map to a valid user in the database.
Try just connecting to master.

I am using the reportingservice web service, everything runs fine but does'nt display report

Please am i doing something wrong, i debug the code no where it is giving me an error:

I am using the reportservice.asmx web service to render the report, but it is not displaying the report at all. it just shows me on IE saying Done with out any error, please can you tell me am i doing something wrong, after a very hard time i reached upto this point, on my form i just have a button on its click event i wrote the following code. and i have no other control on the form. except this code. just referencing the webservice.

thank you.

Dim rs As New ReportingService

Dim LogonCredentials As System.Net.NetworkCredential

Dim Items As CatalogItem

LogonCredentials = New System.Net.NetworkCredential("username", "password", "mydomainname")

rs.Credentials = LogonCredentials

'rs.Credentials = System.Net.CredentialCache.DefaultCredentials

rs.PreAuthenticate = True

' Render arguments

Dim result As Byte() = Nothing

Dim reportPath As String = "/PCSnetReports/StatusReport"

Dim format As String = "MHTML"

Dim historyID As String = Nothing

Dim devInfo As String = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>"

' Prepare report parameter.

Dim parameters(1) As ParameterValue

parameters(0) = New ParameterValue

parameters(0).Name = "eStatus"

parameters(0).Value = "All"

parameters(1) = New ParameterValue

parameters(1).Name = "UserName"

parameters(1).Value = "johnsmith" ' June

Dim credentials As DataSourceCredentials() = Nothing

Dim showHideToggle As String = Nothing

Dim encoding As String

Dim mimeType As String

Dim warnings As Warning() = Nothing

Dim reportHistoryParameters As ParameterValue() = Nothing

Dim streamIDs As String() = Nothing

Dim sh As New SessionHeader

rs.SessionHeaderValue = sh

Try

result = rs.Render(reportPath, format, historyID, devInfo, parameters, _

credentials, showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)

sh.SessionId = rs.SessionHeaderValue.SessionId

Console.WriteLine("SessionID after call to Render: {0}", rs.SessionHeaderValue.SessionId)

Console.WriteLine("Execution date and time: {0}", rs.SessionHeaderValue.ExecutionDateTime)

Console.WriteLine("Is new execution: {0}", rs.SessionHeaderValue.IsNewExecution)

Catch e1 As SoapException

Console.WriteLine(e1.Detail.OuterXml)

End Try

' Write the contents of the report to an MHTML file.

Try

Dim stream As FileStream = File.Create("report.mhtml", result.Length)

Console.WriteLine("File created.")

stream.Write(result, 0, result.Length)

Console.WriteLine("Result written to the file.")

stream.Close()

Catch e2 As Exception

Console.WriteLine(e2.Message)

End Try

Hi,

You need to call the LoadReport method on the RS object before you call the Render method. For example:

rsExecService.ExecutionInfo execInfo = new rsExecService.ExecutionInfo();

rsExecService.ExecutionHeader execHeader = new rsExecService.ExecutionHeader();

rsExec.ExecutionHeaderValue = execHeader;

execInfo = rsExec.LoadReport(reportPath, historyID);

rsExec.SetExecutionParameters(execParameterValues, "en-gb");

String SessionId = rsExec.ExecutionHeaderValue.ExecutionID;

try

{

// Cache the results within the report definition.

report.Data = rsExec.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

}

catch (SoapException e)

{

Console.WriteLine(e.Detail.OuterXml);

}

Regards

Sanjay

I am new for SQL Server please help me to write the code

Hi Dear all

I am new for SQL Server I know VB Programming and Access also but I don't know about SQL Server pls help me or guide me the web site.

Thank U in advance.http://www.w3schools.com/sql/default.asp

i am having an issue with my trigger code

i am having an issue with my trigger code
i believe that i should be able to use the multiple if else statements
and i just threw in an update in each begin end statement
but when i check the syntax it fails

any reason why that is
CREATE TRIGGER [tr_update_conveyor_interface] ON [dbo].[t_conveyor_interface]
FOR UPDATE
AS

declare
@.id integer,
@.barcode varchar(25),
@.epc_tag varchar(24),
@.reject_flag char(1),
@.hold_flag char(1),
@.pe_3_flag char(1),
@.old_id integer,
@.old_barcode varchar(25),
@.old_epc_tag varchar(24),
@.old_reject_flag char(1),
@.old_hold_flag char(1),
@.old_pe_3_flag char(1)

select @.barcode = c.barcode,
@.id = c.id,
@.epc_tag = c.epc_tag,
@.reject_flag = c.reject_flag,
@.hold_flag = c.hold_flag,
@.pe_3_flag = c.pe_3_flag
from t_conveyor_interface c
inner join deleted d
on c.barcode = d.barcode

select @.old_barcode = c.barcode,
@.old_id = c.id,
@.old_epc_tag = c.epc_tag,
@.old_reject_flag = c.reject_flag,
@.old_hold_flag = c.hold_flag,
@.old_pe_3_flag = c.pe_3_flag
from t_conveyor_interface c
inner join inserted i
on c.barcode = i.barcode

if(@.old_epc_tag <> @.epc_tag)
begin
update t_load_audit
set id = 1
end

else
if(@.old_reject_flag <> @.reject_flag)
begin
update t_load_audit
set id = 1
end

else
if(@.old_hold_flag <> @.hold_flag)
begin
update t_load_audit
set id = 1
end

else
if(@.old_pe_3_flag <> @.pe_3_flag
begin
update t_load_audit
set id = 1
endNevermind, I got it

I am getting this message "System.Data.SqlClient.SqlException: xp_sendmail: Procedure expe

I have looked all over my code and can not find anywhere that I am referencing the xp_sendmail procedure!
Here is all the code
<code>
With sqlCmdUpdateParticipants

.Parameters("@.ClassID").Value = ddlClass.SelectedItem.Value

.Parameters("@.Person").Value = tbName.Text()

EndWith

cnCapMaster.Open()

sqlCmdUpdateParticipants.ExecuteNonQuery()

cnCapMaster.Close()
</code>
I am just getting a couple values and and inserting them into the database. the insert works then I get darn error message. This code worked at one time but it has been about 2 years sense I worked on it so who knows what might have happened sense then.
Thanks,
Bryan

Whats in your stored proc?

Nick

|||

How dumb of me not to include this

here is the code

<code>
INSERT INTO dbo.ClassPatricians
(ClassID, Person)
VALUES (@.classID, @.person)

</code>

|||Would you happen to have any triggers or anything on that table that is trying to send email?
Nick|||None that I can find. The only stored procedure has to do with dropping tables! This is just the strangest thing. Would I find the code somewhere other then under stored procedures in enterprise manager?
Thanks,
Bryan|||

Not that it would be causing the issue here, but it could be under DTS packages or SQL Jobs as well. I'd check my functions as well to make sure there is no call in them either. The best way to look for this is to go to your database in enterprise manager > right click on your database > All Tasks > Generate SQL Script > Show All > Click Script All Objects > Under Options, Script Triggers. Back under General click Preview. When it completes, Click copy, and then go to notepad or something similar, and look for xp_sendmail.

Nick

|||

nick-w wrote:

Not that it would be causing the issue here, but it could be under DTS packages or SQL Jobs as well. I'd check my functions as well to make sure there is no call in them either. The best way to look for this is to go to your database in enterprise manager > right click on your database > All Tasks > Generate SQL Script > Show All > Click Script All Objects > Under Options, Script Triggers. Back under General click Preview. When it completes, Click copy, and then go to notepad or something similar, and look for xp_sendmail.

Nick

Thank you oh Jedi Master... you solved the problem. I will so use that trick again!!!
Thanks,
Bryan

I added a column to a table, but, I can't see it.

I ran the code below that adds a column to an SQL table.

I refreshed Data Sources, but, the new column does not appear.

I refreshed Server Explorer and the new column does not appear.

I tried running the code again and I got:

"Column names in each table must be unique. Column name 'CaseNbr' in table 'Patient' is specified more than once."

so the column did actually get added, but, I can't see it.

Imports System.Data.SqlClient

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'Try

Dim sqlConnection As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Patient.mdf;Integrated Security=True;User Instance=True")

Dim str As String = "ALTER TABLE Patient ADD CaseNbr Varchar(50)"

Dim command As New System.Data.SqlClient.SqlCommand(str, sqlConnection)

sqlConnection.Open()

command.ExecuteNonQuery()

sqlConnection.Close()

'Catch

'End Try

Close()

End Sub

End Class

here it runs in query analyzer ...

u do try it..

it must work ..

|||YOu are using an user instance. Data Files of user instances in during development time will be overwritten everytime you start the application from Visual Studio if the setting of "Copy Always" is set to true. Disable this option in order to leave the change in the file.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||I had everything set to Do Not Copy.|||If not error tis thrown, the column was created, try to select the information from the INFORMATION_SCHEMA.COLUMNS View after executing your code to create a new column, you will see that the column was added to the table. There has to be a misconfiguration of the deployment where the debug process always copy a new datafile to the directory.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

hyperlink open new window

I have a link on my report which open new window. The code i have in advance properties of that field is :

="javascriptt:void(window.open('http://www.msn.com','_blank'))" (note: the extra "t" is intentionally added in the code, coz website block the keyword "java.....t")

It works great on testing server but it doesn't work on production server. Both testing and production servers run windows 2003 server and service pack 2 for reporting services.

Reports accessing form production server, when i click on report it opens up new window with address in address bar :

javascript:void(window.open('http://www.msn.com','_blank')) with "The page cannot be displayed" message. But if i open a new internet explorer window and copy past the same address in address bar then its open up www.msn.com. To test the production server behavior I created a test web page test.htm and added the java script to open new window, it works fine.

Any help would be much appreciated.

Ive had problems with this too!
I found it only works when you access the report via a URL such as
http://reportserver/reportserver?report
Rather than through the Report Manager:
http://reportserver/reports
Through the Report Manager I saw the same behaiviour "The page cannot be displayed" message but copying and pasting worked :S
Although Im not surew this is your problem as it works on your tst server, but not prod
Hope this helps
Al