Search results for query: *

select cast(substring(PID,10,4)-right(PID,4) as int) returns an invalid operator for data type error. I'm going to try SQLDenis suggestion shortly. Thanks for the help.

Thanks ca8msm, I'm trying to construct the select statement first. Here is what I have so far: select CODE = case when len(PID) = 17 then left(PID,9) substring(PID,10,4) else PID end from mySourceTable My question is how do I proceed further with the code to be able to...

My table has the field (PID varchar 17) either 13 or 17 character records. I would like to use the logic below to construct a new table with two fields (CODE varchar 13) and (CODEKEY varchar 17) IF len(PID) = 13 Then insert into iTable ESLE IF len(PID) = 17 Then 1. insert the first 9 characters...

Question for James, t1 and t2 are those sql tables? are they temp tales then or what? Thanks

The SQL table is one that keeps all employees even those retire, terminated, etc.. The AS400 file that holds same data only has current employees. I want to do an update to the SQL table ... which means appending only the new employees from the AS400 file. Here is some code UPDATE sqlEmp SET...

I'm currently using OPENQUERY to move data form AS400 to SQL. INSERT is what I use and it is working just fine. However I'm just curious on how UPDATE query syntax is written for similar puropse. Any headsup is appreciated. Thanks

Thanks to all for their response. I end up with the following: IF (select * from openquery(MYLINKEDSERVER, 'select count(*) from myFile/myLib')) > 0 BEGIN DELETE mySQLtable INSERT INTO mySQLtable(R1, R2, R3) SELECT * from openquery(MYLINKEDSERVER, 'select W1, W2, W3 from...

Hi all, From SQL Server I linked to an iSeries AS400 and everything works just fine. I can query the AS400 file/table with the following count statement: select * from openquery(MYLINKEDSERVER, 'select count(*) from myFile/myLib') I would like to use an IF statement first to check whether the...

I suggest you write some code and let us know what you'r thinking and I'm sure someone will help you.

You right nigelrivett, Query Analyzer does the trick. What I found out was that when you run Enterprise Manager Wizards against objects owned by anyone other than yourself or dbo, you will receive such an error. The solution for me was to user either Query Analyzer or Edit server registration...

I'm geting the error message "Error 21776 [SQL-DMO]the name 'mytable' was not found in the Tables collection". I was trying to create a non clustered index on 'mytable' using the Wizard from Enterprise Manager. This is happening to all the talbes in that database. How can I correct...

Hello all, I have a decimal data type in my table that holds financial data. I setup the scale to 2, so it holds the decimal places. SQL for some reason kept rounding up. How do I display the data as is. Example, 125.89 display as 126.00 Should I use 'money' data type rather than the decimal...

Hi everyone, I'm trying to setup transactional replication. During the process of setting up the Distributor I'm getting the following message: "The snapshot folder path, "\\\MyServer\D$\MSSQL\...", uses a special share name which can usually be accessed only by a login with...

Hey AngelWPB could you please point me to a document or something about how to set up such a process. That would be great. I'm still open for more ideas. Thanks

Hell everyone, I currently have our AS400 linked to SQL Server and we are doing a nightly upload from AS40 to SQL. This process is working great and our intranet site is flying. We need to upload some what same data into our internet hosting company's SQL server. I'm just looking for ideas...

I solved my problem as well. Group by did for me. insert into t1(f1) select * from openquery(myAS400, 'select f400 from Lib1/File1') group by f400

I have the same question but a little bit different. I'm doing an insert into a SQL table from AS400 using openquery. I have a field f1 that must be unique - no duplicates - in my SQL table t1. I would like to group the records and keep the first one of each group and delete the rest. My field...

I would like to execute an UPDATE statement rather than the INSERT statement. That way I can update my SQL table quickly as changes occur to the AS400 file. Thanks in advance. I tried the DTS method, but I got to the point where I still have to build a query to transfer data. I rather just do...

Hello all, I found this forum to be very helpfull, so I'm back with more questions. I'm querying AS400 machine by linking it to SQL Server just fine. I would like however, rather than getting the data display in the results pane of Query Analyzer I would like to automatically insert the data...

Thanks to both Jay and lienyi. lienyi you are exactly right when I replaced the period with / it worked. You guys are good. Can you guys point me to a book or manual on how those queries work on the AS400. I appreciate that. Els2