Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. eselhassan

    For Loop, While Loop to Manipulate Data

    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.
  2. eselhassan

    For Loop, While Loop to Manipulate Data

    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...
  3. eselhassan

    For Loop, While Loop to Manipulate Data

    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...
  4. eselhassan

    Update Query in Linked Server

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

    Update Query in Linked Server

    ...the AS400 file. Here is some code UPDATE sqlEmp SET EmpID = SEEN, Fname = SEFNM, Lname = SELNM, Add = SESTR1, Status = SESTAT FROM (SELECT * FROM OPENQUERY(AS400Svr, 'select SEEN, SEFNM, SELNM, SESTR1, SESTAT from my400Lib/my400File')) Is there a better way than this? thanks
  6. eselhassan

    Update Query in Linked Server

    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
  7. eselhassan

    Count Before Delete & Insert data

    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...
  8. eselhassan

    Count Before Delete & Insert data

    ...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 AS400...
  9. eselhassan

    SQL JOIN Query

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

    Error 21776 [SQL-DMO]

    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...
  11. eselhassan

    Error 21776 [SQL-DMO]

    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...
  12. eselhassan

    Decimal Data Type

    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...
  13. eselhassan

    Snapshot Folder Path Message in Replication

    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...
  14. eselhassan

    Upload Data on our Internet Hosting SQL Server

    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
  15. eselhassan

    Upload Data on our Internet Hosting SQL Server

    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...
  16. eselhassan

    Remove duplicates, leaving only first

    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
  17. eselhassan

    Remove duplicates, leaving only first

    ...records and keep the first one of each group and delete the rest. My field f1 is varchar type. If I write my query this way - see below - I get everything including the duplicates: insert into t1(f1) select * from openquery(myAS400, 'select f400 from Lib1/File1') Any help is greatly...
  18. eselhassan

    Hello all, I found this forum to

    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...
  19. eselhassan

    Hello all, I found this forum to

    ...Can someone give me a hand on where to start? I'm thinking to create a stored procedure that query the AS400 and update my sql table nightly. My current query is a simple one: SELECT * FROM OpenQuery(MyServer, 'SELECT field1, field2 FROM LIBRARY1/FILE1'). Thanks for all the help I can get.
  20. eselhassan

    Linked to AS400 from SQL Server

    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

Part and Inventory Search

Back
Top