Professional Web Applications Themes

Stored procedure - Microsoft SQL / MS SQL Server

Wayde Please post DDL + sample data+ desired result Look at CASE expression on BOL "Wayde" <com> wrote in message news:026e01c34c3d$e25cbb70$gbl... ...

  1. #1

    Default Re: Stored procedure

    Wayde
    Please post DDL + sample data+ desired result
    Look at CASE expression on BOL

    "Wayde" <com> wrote in message
    news:026e01c34c3d$e25cbb70$gbl... 


    Uri Guest

  2. #2

    Default Re: Stored procedure

    Wayde
    Look at this works for you
    CREATE TABLE #w
    (
    [NAME] VARCHAR(10) NOT NULL,
    [DATE] DATETIME NOT NULL,
    COST REAL NOT NULL
    )
    INSERT INTO #w VALUES ('Computer1','20030225',1913.35)
    INSERT INTO #w VALUES ('Computer2','20030328',969.38)
    INSERT INTO #w VALUES ('Computer3','20030407',900.00)

    SELECT * FROM #W
    DECLARE YEAR DATETIME
    SET YEAR='20030630'
    SELECT *,CASE WHEN (SELECT DATEDIFF(MONTH,YEAR,GETDATE()))<12 THEN
    COST/12
    ELSE COST/3 END AS NEW_VALUE
    FROM #w



    "Wayde" <com> wrote in message
    news:07b801c34c49$e014d0d0$gbl... [/ref]
    > I [/ref]
    > year, 
    > >
    > >
    > >.
    > >[/ref][/ref]


    Uri Guest

  3. #3

    Default Re: stored procedure

    No I didnt try microsoft.public.dotnet.framework.adonet
    user group. I wasnt sure where the issue lies since the
    web service and the code works fine at least what it
    appears to use as doing.

    so I will post the requested information here and see what
    happens if no result I will try there.

    [WebMethod]
    public string SaveDevice(string Ori,string
    Device, string os, string osVrs, string diskSpace,
    string cpu, string memory, string resolution, string ip,
    string ieVrs, string ieServicePck)
    {
    string theDeviceReturnValue = "";
    try
    {
    SqlConnection conn = new SqlConnection
    (System.Configuration.ConfigurationSettings.AppSet tings
    ["connectionString"]);
    conn.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "st_SaveDevice";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add
    ("Agency_Ori",SqlDbType.Char,10,"Agency_Ori");
    cmd.Parameters.Add
    ("Device_ID",SqlDbType.Char,5,"Device_ID");
    cmd.Parameters.Add
    ("OS_System",SqlDbType.Char,250,"OS_System");
    cmd.Parameters.Add
    ("OS_ServicePack",SqlDbType.Char,75,"OS_ServicePa ck");
    cmd.Parameters.Add
    ("FreeDiskSpace",SqlDbType.Char,5,"FreeDiskSpace" );
    cmd.Parameters.Add("Cpu_MHZ",SqlDbType.Char,10,"C pu_MHZ");
    cmd.Parameters.Add
    ("Main_Memory",SqlDbType.Char,10,"Main_Memory" );
    cmd.Parameters.Add
    ("ScreenResolution",SqlDbType.Char,10,"ScreenReso lution");
    cmd.Parameters.Add
    ("IPAddress",SqlDbType.Char,15,"IPAddress");
    cmd.Parameters.Add("IE_Vrs",SqlDbType.Char,10,"IE _Vrs");
    cmd.Parameters.Add
    ("IE_ServicePackVrs",SqlDbType.Char,10,"IE_Servic ePackVrs"
    );
    cmd.Parameters["Agency_Ori"].Value = Ori;
    cmd.Parameters["Device_ID"].Value = Device;
    cmd.Parameters["OS_System"].Value = os;
    cmd.Parameters["OS_ServicePack"].Value = osVrs;
    cmd.Parameters["FreeDiskSpace"].Value = diskSpace;
    cmd.Parameters["Cpu_MHZ"].Value = cpu;
    cmd.Parameters["Main_Memory"].Value = memory;
    cmd.Parameters["ScreenResolution"].Value = resolution;
    cmd.Parameters["IPAddress"].Value = ip;
    cmd.Parameters["IE_Vrs"].Value = ieVrs;
    cmd.Parameters["IE_ServicePackVrs"].Value = ieServicePck;
    cmd.Connection = conn;
    int num = cmd.ExecuteNonQuery();
    if(num != 0)
    theDeviceReturnValue = "Device Data Save Successful, Exit
    the Application" ;
    }
    catch(Exception e)
    {
    theDeviceReturnValue = e.Message;
    }
    return theDeviceReturnValue;
    }

    CREATE PROCEDURE st_SaveDevice
    (
    Agency_Ori char(10),
    Device_ID char(6),
    OS_System char(250),
    OS_ServicePack char(5),
    FreeDiskSpace char(25),
    Cpu_MHZ char(10),
    Main_Memory char(10),
    ScreenResolution char(10),
    IPAddress char(15),
    IE_Vrs char(10),
    IE_ServicePackVrs char(250)
    )

    AS


    Begin
    INSERT Device
    (Agency_Ori,Device_ID,OS_System,OS_ServicePack,
    FreeDiskSpace,Cpu_MHZ,Main_Memory,ScreenResolution ,
    IPAddress,IE_Vrs,IE_ServicePackVrs)
    Values(Agency_Ori,Device_ID,OS_System,OS_Servi cePack,
    FreeDiskSpace,Cpu_MHZ,Main_Memory,ScreenResolu tion,
    IPAddress,IE_Vrs,IE_ServicePackVrs)
    END
    GO

    Thanks!

    Stokh

     
    enough 
    your web 
    be helpful. 
    someone in there 
    news:66a301c3581b$78952f30 [/ref][/ref]
    and [/ref][/ref]
    insert [/ref][/ref]
    which [/ref][/ref]
    from 
    >> it [/ref][/ref]
    would 
    >>[/ref]
    >
    >.
    >[/ref]
    Sandie Guest

  4. #4

    Default Re: stored procedure

    sandie:

    i didn't see a ddl (table definition) for the table Device, is it
    possible the table has a size of 10 chars? try sp_help Device from
    query yzer. you might find the column, OS_System, allows only 10
    chars.

    good luck,
    jeff clausius
    sourcegear corporation



    "Sandie" <mn.us> wrote in news:062601c35a88
    $19189780$gbl:
     
    > enough 
    > your web 
    > be helpful. 
    > someone in there 
    > news:66a301c3581b$78952f30 [/ref]
    > and [/ref]
    > insert [/ref]
    > which [/ref]
    > from [/ref]
    > would 
    >>
    >>.
    >>[/ref][/ref]

    Jeff Guest

  5. #5

    Default Re: stored procedure

    Now that would be to easy but its not it I checked that
    and its set to char 250 just to make sure it will all come
    through. I dont think I am missing something here but you
    can check. Any other ideas? The field used to be set to
    char 10 but was changed to the char 250, its like it is
    not recompiling or whatever you call it.

    Device
    dbo
    user table 2003-07-31 09:32:57.200
    Device_ID

    char

    no
    6
    no
    no
    no
    SQL_Latin1_General_CP1_CI_AS
    Agency_Ori


    char

    no
    10
    no
    no
    no
    SQL_Latin1_General_CP1_CI_AS
    OS_System


    char

    no
    250
    yes
    no
    yes
    SQL_Latin1_General_CP1_CI_AS
    OS_ServicePack


    char

    no
    5
    yes
    no
    yes
    SQL_Latin1_General_CP1_CI_AS
    FreeDiskSpace


    char

    no
    25
    yes
    no
    yes
    SQL_Latin1_General_CP1_CI_AS
    Cpu_MHZ


    char

    no
    10
    yes
    no
    yes
    SQL_Latin1_General_CP1_CI_AS
    Main_Memory


    char

    no
    10
    yes
    no
    yes
    SQL_Latin1_General_CP1_CI_AS
    ScreenResolution


    char

    no
    10
    yes
    no
    yes
    SQL_Latin1_General_CP1_CI_AS
    IPAddress


    char

    no
    15
    yes
    no
    yes
    SQL_Latin1_General_CP1_CI_AS
    IE_Vrs


    char

    no
    10
    yes
    no
    yes
    SQL_Latin1_General_CP1_CI_AS
    IE_ServicePackVrs


    char

    no
    250
    yes
    no
    yes
    SQL_Latin1_General_CP1_CI_AS
     
    Device, is it 
    Device from 
    allows only 10 
    news:062601c35a88 [/ref]
    the [/ref]
    what [/ref]
    ip, [/ref]
    ("Cpu_MHZ",SqlDbType.Char,10,"Cpu_MHZ"); [/ref]
    ("ScreenResolution",SqlDbType.Char,10,"ScreenReso lution"); [/ref]
    ("IE_Vrs",SqlDbType.Char,10,"IE_Vrs"); [/ref]
    ("IE_ServicePackVrs",SqlDbType.Char,10,"IE_Servic ePackVrs" [/ref]
    ieServicePck; [/ref]
    Exit [/ref]
    (Agency_Ori,Device_ID,OS_System,OS_ServicePack , 
    >> enough [/ref][/ref]
    in [/ref][/ref]
    would 
    >> someone in there 
    >> news:66a301c3581b$78952f30 
    >> and 
    >> insert 
    >> which 
    >> from [/ref][/ref]
    in [/ref][/ref]
    matter 
    >> would [/ref]
    >
    >.
    >[/ref]
    Sandie Guest

  6. #6

    Default Re: stored procedure

    Hi Sandie,

    Which field is changed for char(10) to char(250)? If you directly execute
    the st_SaveDevice stored procedure in Query yzer, will the data be
    inserted correctly?


    Bill Cheng
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.
    --------------------
    | Content-Class: urn:content-classes:message
    | From: "Sandie" <mn.us>
    | Sender: "Sandie" <mn.us>
    | References: <038f01c3578c$e57b0af0$gbl>
    <66a301c3581b$78952f30$gbl>
    <46.248.16>
    <062601c35a88$19189780$gbl>
    <46.248.16>
    | Subject: Re: stored procedure
    | Date: Mon, 4 Aug 2003 07:54:30 -0700
    | Lines: 328
    | Message-ID: <804e01c35a98$4f241740$gbl>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | cht="iso-8859-1"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    | Thread-Index: AcNamE8kBlfU9jAFRJa24CBB+TQb1A==
    | Newsgroups: microsoft.public.sqlserver.programming
    | Path: cpmsftngxa06.phx.gbl
    | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:378887
    | NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    | Now that would be to easy but its not it I checked that
    | and its set to char 250 just to make sure it will all come
    | through. I dont think I am missing something here but you
    | can check. Any other ideas? The field used to be set to
    | char 10 but was changed to the char 250, its like it is
    | not recompiling or whatever you call it.
    |
    | Device
    | dbo
    | user table 2003-07-31 09:32:57.200
    | Device_ID
    |
    | char
    |
    | no
    | 6
    | no
    | no
    | no
    | SQL_Latin1_General_CP1_CI_AS
    | Agency_Ori
    |
    |
    | char
    |
    | no
    | 10
    | no
    | no
    | no
    | SQL_Latin1_General_CP1_CI_AS
    | OS_System
    |
    |
    | char
    |
    | no
    | 250
    | yes
    | no
    | yes
    | SQL_Latin1_General_CP1_CI_AS
    | OS_ServicePack
    |
    |
    | char
    |
    | no
    | 5
    | yes
    | no
    | yes
    | SQL_Latin1_General_CP1_CI_AS
    | FreeDiskSpace
    |
    |
    | char
    |
    | no
    | 25
    | yes
    | no
    | yes
    | SQL_Latin1_General_CP1_CI_AS
    | Cpu_MHZ
    |
    |
    | char
    |
    | no
    | 10
    | yes
    | no
    | yes
    | SQL_Latin1_General_CP1_CI_AS
    | Main_Memory
    |
    |
    | char
    |
    | no
    | 10
    | yes
    | no
    | yes
    | SQL_Latin1_General_CP1_CI_AS
    | ScreenResolution
    |
    |
    | char
    |
    | no
    | 10
    | yes
    | no
    | yes
    | SQL_Latin1_General_CP1_CI_AS
    | IPAddress
    |
    |
    | char
    |
    | no
    | 15
    | yes
    | no
    | yes
    | SQL_Latin1_General_CP1_CI_AS
    | IE_Vrs
    |
    |
    | char
    |
    | no
    | 10
    | yes
    | no
    | yes
    | SQL_Latin1_General_CP1_CI_AS
    | IE_ServicePackVrs
    |
    |
    | char
    |
    | no
    | 250
    | yes
    | no
    | yes
    | SQL_Latin1_General_CP1_CI_AS
    |
    | >-----Original Message-----
    | >sandie:
    | >
    | >i didn't see a ddl (table definition) for the table
    | Device, is it
    | >possible the table has a size of 10 chars? try sp_help
    | Device from
    | >query yzer. you might find the column, OS_System,
    | allows only 10
    | >chars.
    | >
    | >good luck,
    | >jeff clausius
    | >sourcegear corporation
    | >
    | >
    | >
    | >"Sandie" <mn.us> wrote in
    | news:062601c35a88
    | >$19189780$gbl:
    | >
    | >> No I didnt try microsoft.public.dotnet.framework.adonet
    | >> user group. I wasnt sure where the issue lies since
    | the
    | >> web service and the code works fine at least what it
    | >> appears to use as doing.
    | >>
    | >> so I will post the requested information here and see
    | what
    | >> happens if no result I will try there.
    | >>
    | >> [WebMethod]
    | >> public string SaveDevice(string Ori,string
    | >> Device, string os, string osVrs, string diskSpace,
    | >> string cpu, string memory, string resolution, string
    | ip,
    | >> string ieVrs, string ieServicePck)
    | >> {
    | >> string theDeviceReturnValue = "";
    | >> try
    | >> {
    | >> SqlConnection conn = new SqlConnection
    | >> (System.Configuration.ConfigurationSettings.AppSet tings
    | >> ["connectionString"]);
    | >> conn.Open();
    | >> SqlCommand cmd = new SqlCommand();
    | >> cmd.CommandText = "st_SaveDevice";
    | >> cmd.CommandType = CommandType.StoredProcedure;
    | >> cmd.Parameters.Add
    | >> ("Agency_Ori",SqlDbType.Char,10,"Agency_Ori");
    | >> cmd.Parameters.Add
    | >> ("Device_ID",SqlDbType.Char,5,"Device_ID");
    | >> cmd.Parameters.Add
    | >> ("OS_System",SqlDbType.Char,250,"OS_System");
    | >> cmd.Parameters.Add
    | >> ("OS_ServicePack",SqlDbType.Char,75,"OS_ServicePa ck");
    | >> cmd.Parameters.Add
    | >> ("FreeDiskSpace",SqlDbType.Char,5,"FreeDiskSpace" );
    | >> cmd.Parameters.Add
    | ("Cpu_MHZ",SqlDbType.Char,10,"Cpu_MHZ");
    | >> cmd.Parameters.Add
    | >> ("Main_Memory",SqlDbType.Char,10,"Main_Memory" );
    | >> cmd.Parameters.Add
    | >>
    | ("ScreenResolution",SqlDbType.Char,10,"ScreenReso lution");
    | >> cmd.Parameters.Add
    | >> ("IPAddress",SqlDbType.Char,15,"IPAddress");
    | >> cmd.Parameters.Add
    | ("IE_Vrs",SqlDbType.Char,10,"IE_Vrs");
    | >> cmd.Parameters.Add
    | >>
    | ("IE_ServicePackVrs",SqlDbType.Char,10,"IE_Servic ePackVrs"
    | >> );
    | >> cmd.Parameters["Agency_Ori"].Value = Ori;
    | >> cmd.Parameters["Device_ID"].Value = Device;
    | >> cmd.Parameters["OS_System"].Value = os;
    | >> cmd.Parameters["OS_ServicePack"].Value = osVrs;
    | >> cmd.Parameters["FreeDiskSpace"].Value = diskSpace;
    | >> cmd.Parameters["Cpu_MHZ"].Value = cpu;
    | >> cmd.Parameters["Main_Memory"].Value = memory;
    | >> cmd.Parameters["ScreenResolution"].Value = resolution;
    | >> cmd.Parameters["IPAddress"].Value = ip;
    | >> cmd.Parameters["IE_Vrs"].Value = ieVrs;
    | >> cmd.Parameters["IE_ServicePackVrs"].Value =
    | ieServicePck;
    | >> cmd.Connection = conn;
    | >> int num = cmd.ExecuteNonQuery();
    | >> if(num != 0)
    | >> theDeviceReturnValue = "Device Data Save Successful,
    | Exit
    | >> the Application" ;
    | >> }
    | >> catch(Exception e)
    | >> {
    | >> theDeviceReturnValue = e.Message;
    | >> }
    | >> return theDeviceReturnValue;
    | >> }
    | >>
    | >> CREATE PROCEDURE st_SaveDevice
    | >> (
    | >> Agency_Ori char(10),
    | >> Device_ID char(6),
    | >> OS_System char(250),
    | >> OS_ServicePack char(5),
    | >> FreeDiskSpace char(25),
    | >> Cpu_MHZ char(10),
    | >> Main_Memory char(10),
    | >> ScreenResolution char(10),
    | >> IPAddress char(15),
    | >> IE_Vrs char(10),
    | >> IE_ServicePackVrs char(250)
    | >> )
    | >>
    | >> AS
    | >>
    | >>
    | >> Begin
    | >> INSERT Device
    | >> (Agency_Ori,Device_ID,OS_System,OS_ServicePack,
    | >> FreeDiskSpace,Cpu_MHZ,Main_Memory,ScreenResolution ,
    | >> IPAddress,IE_Vrs,IE_ServicePackVrs)
    | >> Values
    | (Agency_Ori,Device_ID,OS_System,OS_ServicePack ,
    | >> FreeDiskSpace,Cpu_MHZ,Main_Memory,ScreenResolu tion,
    | >> IPAddress,IE_Vrs,IE_ServicePackVrs)
    | >> END
    | >> GO
    | >>
    | >> Thanks!
    | >>
    | >> Stokh
    | >>
    | >>
    | >>>-----Original Message-----
    | >>>sandie:
    | >>>
    | >>>we would like to help, but you haven't really provided
    | >> enough
    | >>>information to post an answer. for example, the code
    | in
    | >> your web
    | >>>method, the actual stored proc, the table's ddl all
    | would
    | >> be helpful.
    | >>>
    | >>>
    | >>>additionally, have you tried the
    | >>>microsoft.public.dotnet.framework.adonet user group?
    | >> someone in there
    | >>>might be able to help as well.
    | >>>
    | >>>
    | >>>jeff clausius
    | >>>sourcegear corporation
    | >>>
    | >>>
    | >>>
    | >>>"Stokh" <mn.us> wrote in
    | >> news:66a301c3581b$78952f30
    | >>>$gbl:
    | >>>
    | >>>> No one in here knows the answer to this?
    | >>>>>-----Original Message-----
    | >>>>>I'm using a web service for a windows application and
    | >> and
    | >>>>>in my webmethod I use a stored procedure to do the
    | >> insert
    | >>>>>to a certain table. The field was set at char(10)
    | >> which
    | >>>>>was to small for all the data I am collecting in the
    | >>>>>windows application the field collects the OS system
    | >> from
    | >>>>>the clients computer so it reads in the application
    | >>>>>as "Microsoft Windows XP Professional 5.1.2600" but
    | >>>>>inserts as "Microsoft" I changed the field size both
    | in
    | >>>>>the db and in the stored procedure and it doesnt
    | matter
    | >>>> it
    | >>>>>still doesnt work, if I do it from the asmx page and
    | >>>>>invoke it from there it works just fine. Any ideas
    | >> would
    | >>>>>be great because we are stumped!.
    | >>>>>
    | >>>>>Thanks in advance for your help!
    | >>>>>
    | >>>>>
    | >>>>>.
    | >>>>>
    | >>>>
    | >>>
    | >>>.
    | >>>
    | >
    | >.
    | >
    |

    Bill Guest

  7. #7

    Default Re: stored procedure

    It's OS_System field that was changed. And yes if one of
    the staff use the web page to insert data it works fine,
    she is hand typing the data into it. If you hand type it
    in the .asmx page it goes in that way to I ran ethereal on
    the server while I connected and ran the windows
    application and the data comes across that way as well,
    but on the insert to the db is where it does not go in,
    what does get written in the field is Microsoft which with
    the word Microsoft and a space is exactly 10 characters,
    so like I said earlier its like it isnt updating its set
    to 250 now, but it works every where else. We have
    checked the code a 100 times over and we know it will be
    something really simple but can not seem to find it
    anywhere.

    thanks

    Sandie 
    directly execute 
    will the data be 
    confers no rights. 
    microsoft.public.sqlserver.programming:378887 
    come 
    you 
    to 
    dbo 
    Device_ID 
    char 
     
    Agency_Ori 
     
    char 
     
    OS_System 
     
    char 
     
    OS_ServicePack 
     
    char 
     
    FreeDiskSpace 
     
    char 
     
    Cpu_MHZ 
     
    char 
     
    Main_Memory 
     
    char 
     
    ScreenResolution 
     
    char 
     
    IPAddress 
     
    char 
     
    IE_Vrs 
     
    char 
     
    IE_ServicePackVrs 
     
    char 
     
    sp_help 
    microsoft.public.dotnet.framework.adonet 
    see 
    (System.Configuration.ConfigurationSettings.AppSet tings 
    ("OS_ServicePack",SqlDbType.Char,75,"OS_ServicePa ck"); 
    ("ScreenResolution",SqlDbType.Char,10,"ScreenReso lution"); 
    ("IE_ServicePackVrs",SqlDbType.Char,10,"IE_Servic ePackVrs" 
    resolution; 
    FreeDiskSpace,Cpu_MHZ,Main_Memory,ScreenResolu tion, 
    provided 
    code 
    group? 
    and 
    the 
    system 
    application 
    but 
    both 
    and 
    ideas 
    Sandie Guest

  8. #8

    Default Re: stored procedure

    Hi Sandie,

    I would like to make sure that I understand you correctly. OS_System field
    is changed from char(10) to char(250), and, if you
    directly execute the st_SaveDevice stored procedure in Query yzer, data
    can be inserted correctly (data is not truncated to char(10)).

    If so, I would suggest that you check if the .asmx page has been
    re-compiled after you change the code for OS_System field to char(250).
    Also, since stored procedure does not have problems, it is related to the
    ADO.NET code, you may check with microsoft.public.dotnet.framework.adonet
    newsgroup.

    You may also use SQL Profiler to capture the TSQL statement sent to SQL
    Server to check if data is sent correctly, e.g. whether the TSQL is
    incorrect when arriving at SQL Server.


    Bill Cheng
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.
    --------------------
    | Content-Class: urn:content-classes:message
    | From: "Sandie" <mn.us>
    | Sender: "Sandie" <mn.us>
    | References: <038f01c3578c$e57b0af0$gbl>
    <66a301c3581b$78952f30$gbl>
    <46.248.16>
    <062601c35a88$19189780$gbl>
    <46.248.16>
    <804e01c35a98$4f241740$gbl>
    <phx.gbl>
    | Subject: Re: stored procedure
    | Date: Mon, 4 Aug 2003 10:43:32 -0700
    | Lines: 460
    | Message-ID: <009a01c35aaf$ec76a140$gbl>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | cht="iso-8859-1"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    | Thread-Index: AcNar+x0K4swyuqpSJC1ntfYB4+idA==
    | Newsgroups: microsoft.public.sqlserver.programming
    | Path: cpmsftngxa06.phx.gbl
    | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:378941
    | NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    | It's OS_System field that was changed. And yes if one of
    | the staff use the web page to insert data it works fine,
    | she is hand typing the data into it. If you hand type it
    | in the .asmx page it goes in that way to I ran ethereal on
    | the server while I connected and ran the windows
    | application and the data comes across that way as well,
    | but on the insert to the db is where it does not go in,
    | what does get written in the field is Microsoft which with
    | the word Microsoft and a space is exactly 10 characters,
    | so like I said earlier its like it isnt updating its set
    | to 250 now, but it works every where else. We have
    | checked the code a 100 times over and we know it will be
    | something really simple but can not seem to find it
    | anywhere.
    |
    | thanks
    |
    | Sandie
    | >-----Original Message-----
    | >Hi Sandie,
    | >
    | >Which field is changed for char(10) to char(250)? If you
    | directly execute
    | >the st_SaveDevice stored procedure in Query yzer,
    | will the data be
    | >inserted correctly?
    | >
    | >
    | >Bill Cheng
    | >Microsoft Online Partner Support
    | >
    | >Get Secure! - www.microsoft.com/security
    | >This posting is provided "as is" with no warranties and
    | confers no rights.
    | >--------------------
    | >| Content-Class: urn:content-classes:message
    | >| From: "Sandie" <mn.us>
    | >| Sender: "Sandie" <mn.us>
    | >| References: <038f01c3578c$e57b0af0$gbl>
    | ><66a301c3581b$78952f30$gbl>
    | ><46.248.16>
    | ><062601c35a88$19189780$gbl>
    | ><46.248.16>
    | >| Subject: Re: stored procedure
    | >| Date: Mon, 4 Aug 2003 07:54:30 -0700
    | >| Lines: 328
    | >| Message-ID: <804e01c35a98$4f241740$gbl>
    | >| MIME-Version: 1.0
    | >| Content-Type: text/plain;
    | >| cht="iso-8859-1"
    | >| Content-Transfer-Encoding: 7bit
    | >| X-Newsreader: Microsoft CDO for Windows 2000
    | >| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    | >| Thread-Index: AcNamE8kBlfU9jAFRJa24CBB+TQb1A==
    | >| Newsgroups: microsoft.public.sqlserver.programming
    | >| Path: cpmsftngxa06.phx.gbl
    | >| Xref: cpmsftngxa06.phx.gbl
    | microsoft.public.sqlserver.programming:378887
    | >| NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
    | >| X-Tomcat-NG: microsoft.public.sqlserver.programming
    | >|
    | >| Now that would be to easy but its not it I checked that
    | >| and its set to char 250 just to make sure it will all
    | come
    | >| through. I dont think I am missing something here but
    | you
    | >| can check. Any other ideas? The field used to be set
    | to
    | >| char 10 but was changed to the char 250, its like it is
    | >| not recompiling or whatever you call it.
    | >|
    | >| Device
    | >|
    | dbo
    | >| user table 2003-07-31 09:32:57.200
    | >|
    | Device_ID
    | >|
    | >|
    | char
    | >|
    |
    | >| no
    | >| 6
    | >| no
    | >| no
    | >| no
    | >| SQL_Latin1_General_CP1_CI_AS
    | >|
    | Agency_Ori
    | >|
    |
    | >|
    | >|
    | char
    | >|
    |
    | >| no
    | >| 10
    | >| no
    | >| no
    | >| no
    | >| SQL_Latin1_General_CP1_CI_AS
    | >|
    | OS_System
    | >|
    |
    | >|
    | >|
    | char
    | >|
    |
    | >| no
    | >| 250
    | >| yes
    | >| no
    | >| yes
    | >| SQL_Latin1_General_CP1_CI_AS
    | >|
    | OS_ServicePack
    | >|
    |
    | >|
    | >|
    | char
    | >|
    |
    | >| no
    | >| 5
    | >| yes
    | >| no
    | >| yes
    | >| SQL_Latin1_General_CP1_CI_AS
    | >|
    | FreeDiskSpace
    | >|
    |
    | >|
    | >|
    | char
    | >|
    |
    | >| no
    | >| 25
    | >| yes
    | >| no
    | >| yes
    | >| SQL_Latin1_General_CP1_CI_AS
    | >|
    | Cpu_MHZ
    | >|
    |
    | >|
    | >|
    | char
    | >|
    |
    | >| no
    | >| 10
    | >| yes
    | >| no
    | >| yes
    | >| SQL_Latin1_General_CP1_CI_AS
    | >|
    | Main_Memory
    | >|
    |
    | >|
    | >|
    | char
    | >|
    |
    | >| no
    | >| 10
    | >| yes
    | >| no
    | >| yes
    | >| SQL_Latin1_General_CP1_CI_AS
    | >|
    | ScreenResolution
    | >|
    |
    | >|
    | >|
    | char
    | >|
    |
    | >| no
    | >| 10
    | >| yes
    | >| no
    | >| yes
    | >| SQL_Latin1_General_CP1_CI_AS
    | >|
    | IPAddress
    | >|
    |
    | >|
    | >|
    | char
    | >|
    |
    | >| no
    | >| 15
    | >| yes
    | >| no
    | >| yes
    | >| SQL_Latin1_General_CP1_CI_AS
    | >|
    | IE_Vrs
    | >|
    |
    | >|
    | >|
    | char
    | >|
    |
    | >| no
    | >| 10
    | >| yes
    | >| no
    | >| yes
    | >| SQL_Latin1_General_CP1_CI_AS
    | >|
    | IE_ServicePackVrs
    | >|
    |
    | >|
    | >|
    | char
    | >|
    |
    | >| no
    | >| 250
    | >| yes
    | >| no
    | >| yes
    | >| SQL_Latin1_General_CP1_CI_AS
    | >|
    | >| >-----Original Message-----
    | >| >sandie:
    | >| >
    | >| >i didn't see a ddl (table definition) for the table
    | >| Device, is it
    | >| >possible the table has a size of 10 chars? try
    | sp_help
    | >| Device from
    | >| >query yzer. you might find the column, OS_System,
    | >| allows only 10
    | >| >chars.
    | >| >
    | >| >good luck,
    | >| >jeff clausius
    | >| >sourcegear corporation
    | >| >
    | >| >
    | >| >
    | >| >"Sandie" <mn.us> wrote in
    | >| news:062601c35a88
    | >| >$19189780$gbl:
    | >| >
    | >| >> No I didnt try
    | microsoft.public.dotnet.framework.adonet
    | >| >> user group. I wasnt sure where the issue lies since
    | >| the
    | >| >> web service and the code works fine at least what it
    | >| >> appears to use as doing.
    | >| >>
    | >| >> so I will post the requested information here and
    | see
    | >| what
    | >| >> happens if no result I will try there.
    | >| >>
    | >| >> [WebMethod]
    | >| >> public string SaveDevice(string Ori,string
    | >| >> Device, string os, string osVrs, string diskSpace,
    | >| >> string cpu, string memory, string resolution, string
    | >| ip,
    | >| >> string ieVrs, string ieServicePck)
    | >| >> {
    | >| >> string theDeviceReturnValue = "";
    | >| >> try
    | >| >> {
    | >| >> SqlConnection conn = new SqlConnection
    | >| >>
    | (System.Configuration.ConfigurationSettings.AppSet tings
    | >| >> ["connectionString"]);
    | >| >> conn.Open();
    | >| >> SqlCommand cmd = new SqlCommand();
    | >| >> cmd.CommandText = "st_SaveDevice";
    | >| >> cmd.CommandType = CommandType.StoredProcedure;
    | >| >> cmd.Parameters.Add
    | >| >> ("Agency_Ori",SqlDbType.Char,10,"Agency_Ori");
    | >| >> cmd.Parameters.Add
    | >| >> ("Device_ID",SqlDbType.Char,5,"Device_ID");
    | >| >> cmd.Parameters.Add
    | >| >> ("OS_System",SqlDbType.Char,250,"OS_System");
    | >| >> cmd.Parameters.Add
    | >| >>
    | ("OS_ServicePack",SqlDbType.Char,75,"OS_ServicePa ck");
    | >| >> cmd.Parameters.Add
    | >| >> ("FreeDiskSpace",SqlDbType.Char,5,"FreeDiskSpace" );
    | >| >> cmd.Parameters.Add
    | >| ("Cpu_MHZ",SqlDbType.Char,10,"Cpu_MHZ");
    | >| >> cmd.Parameters.Add
    | >| >> ("Main_Memory",SqlDbType.Char,10,"Main_Memory" );
    | >| >> cmd.Parameters.Add
    | >| >>
    | >|
    | ("ScreenResolution",SqlDbType.Char,10,"ScreenReso lution");
    | >| >> cmd.Parameters.Add
    | >| >> ("IPAddress",SqlDbType.Char,15,"IPAddress");
    | >| >> cmd.Parameters.Add
    | >| ("IE_Vrs",SqlDbType.Char,10,"IE_Vrs");
    | >| >> cmd.Parameters.Add
    | >| >>
    | >|
    | ("IE_ServicePackVrs",SqlDbType.Char,10,"IE_Servic ePackVrs"
    | >| >> );
    | >| >> cmd.Parameters["Agency_Ori"].Value = Ori;
    | >| >> cmd.Parameters["Device_ID"].Value = Device;
    | >| >> cmd.Parameters["OS_System"].Value = os;
    | >| >> cmd.Parameters["OS_ServicePack"].Value = osVrs;
    | >| >> cmd.Parameters["FreeDiskSpace"].Value = diskSpace;
    | >| >> cmd.Parameters["Cpu_MHZ"].Value = cpu;
    | >| >> cmd.Parameters["Main_Memory"].Value = memory;
    | >| >> cmd.Parameters["ScreenResolution"].Value =
    | resolution;
    | >| >> cmd.Parameters["IPAddress"].Value = ip;
    | >| >> cmd.Parameters["IE_Vrs"].Value = ieVrs;
    | >| >> cmd.Parameters["IE_ServicePackVrs"].Value =
    | >| ieServicePck;
    | >| >> cmd.Connection = conn;
    | >| >> int num = cmd.ExecuteNonQuery();
    | >| >> if(num != 0)
    | >| >> theDeviceReturnValue = "Device Data Save Successful,
    | >| Exit
    | >| >> the Application" ;
    | >| >> }
    | >| >> catch(Exception e)
    | >| >> {
    | >| >> theDeviceReturnValue = e.Message;
    | >| >> }
    | >| >> return theDeviceReturnValue;
    | >| >> }
    | >| >>
    | >| >> CREATE PROCEDURE st_SaveDevice
    | >| >> (
    | >| >> Agency_Ori char(10),
    | >| >> Device_ID char(6),
    | >| >> OS_System char(250),
    | >| >> OS_ServicePack char(5),
    | >| >> FreeDiskSpace char(25),
    | >| >> Cpu_MHZ char(10),
    | >| >> Main_Memory char(10),
    | >| >> ScreenResolution char(10),
    | >| >> IPAddress char(15),
    | >| >> IE_Vrs char(10),
    | >| >> IE_ServicePackVrs char(250)
    | >| >> )
    | >| >>
    | >| >> AS
    | >| >>
    | >| >>
    | >| >> Begin
    | >| >> INSERT Device
    | >| >> (Agency_Ori,Device_ID,OS_System,OS_ServicePack,
    | >| >> FreeDiskSpace,Cpu_MHZ,Main_Memory,ScreenResolution ,
    | >| >> IPAddress,IE_Vrs,IE_ServicePackVrs)
    | >| >> Values
    | >| (Agency_Ori,Device_ID,OS_System,OS_ServicePack ,
    | >| >>
    | FreeDiskSpace,Cpu_MHZ,Main_Memory,ScreenResolu tion,
    | >| >> IPAddress,IE_Vrs,IE_ServicePackVrs)
    | >| >> END
    | >| >> GO
    | >| >>
    | >| >> Thanks!
    | >| >>
    | >| >> Stokh
    | >| >>
    | >| >>
    | >| >>>-----Original Message-----
    | >| >>>sandie:
    | >| >>>
    | >| >>>we would like to help, but you haven't really
    | provided
    | >| >> enough
    | >| >>>information to post an answer. for example, the
    | code
    | >| in
    | >| >> your web
    | >| >>>method, the actual stored proc, the table's ddl all
    | >| would
    | >| >> be helpful.
    | >| >>>
    | >| >>>
    | >| >>>additionally, have you tried the
    | >| >>>microsoft.public.dotnet.framework.adonet user
    | group?
    | >| >> someone in there
    | >| >>>might be able to help as well.
    | >| >>>
    | >| >>>
    | >| >>>jeff clausius
    | >| >>>sourcegear corporation
    | >| >>>
    | >| >>>
    | >| >>>
    | >| >>>"Stokh" <mn.us> wrote in
    | >| >> news:66a301c3581b$78952f30
    | >| >>>$gbl:
    | >| >>>
    | >| >>>> No one in here knows the answer to this?
    | >| >>>>>-----Original Message-----
    | >| >>>>>I'm using a web service for a windows application
    | and
    | >| >> and
    | >| >>>>>in my webmethod I use a stored procedure to do the
    | >| >> insert
    | >| >>>>>to a certain table. The field was set at char(10)
    | >| >> which
    | >| >>>>>was to small for all the data I am collecting in
    | the
    | >| >>>>>windows application the field collects the OS
    | system
    | >| >> from
    | >| >>>>>the clients computer so it reads in the
    | application
    | >| >>>>>as "Microsoft Windows XP Professional 5.1.2600"
    | but
    | >| >>>>>inserts as "Microsoft" I changed the field size
    | both
    | >| in
    | >| >>>>>the db and in the stored procedure and it doesnt
    | >| matter
    | >| >>>> it
    | >| >>>>>still doesnt work, if I do it from the asmx page
    | and
    | >| >>>>>invoke it from there it works just fine. Any
    | ideas
    | >| >> would
    | >| >>>>>be great because we are stumped!.
    | >| >>>>>
    | >| >>>>>Thanks in advance for your help!
    | >| >>>>>
    | >| >>>>>
    | >| >>>>>.
    | >| >>>>>
    | >| >>>>
    | >| >>>
    | >| >>>.
    | >| >>>
    | >| >
    | >| >.
    | >| >
    | >|
    | >
    | >.
    | >
    |

    Bill Guest

  9. #9

    Default Stored Procedure

    Hello

    I would like to write a stored procedure that writes to
    two different tables. The parent table only has one
    record, and the child could have one or more records. Does
    any one know how to go about this? The reason I need to
    write to both tables is that i would like it done in one
    transaction.

    Thanks in Advance





    Lynn Guest

  10. #10

    Default Re: Stored Procedure

    Look at this:
    http://www.algonet.se/~sommar/arrays-in-sql.html
    it has some good tips and techniques for using arrays in SQL.

    Keep this site and:
    http://www.algonet.se/~sommar/
    handy.
    Good stuff.

    "Lynn Owens" <com> wrote in message
    news:0a2b01c35b55$d93765a0$gbl... 
    > procedure 
    > insert into table 2 
    > procedure [/ref]
    > to [/ref]
    > Does 
    > >
    > >
    > >.
    > >[/ref][/ref]


    raydan Guest

  11. #11

    Default Re: stored procedure

    Hi Sandie,

    I see that you have posted an issue in dotnet.framework.adonet. Since the
    code is related to ADO.NET and the problem is with the executable program,
    I think that newsgroup would provide more detailed information.


    Bill Cheng
    Microsoft Online Partner Support

    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.
    --------------------
    | Content-Class: urn:content-classes:message
    | From: "Sandie" <mn.us>
    | Sender: "Sandie" <mn.us>
    | References: <038f01c3578c$e57b0af0$gbl>
    <66a301c3581b$78952f30$gbl>
    <46.248.16>
    <062601c35a88$19189780$gbl>
    <46.248.16>
    <804e01c35a98$4f241740$gbl>
    <phx.gbl>
    <009a01c35aaf$ec76a140$gbl>
    <phx.gbl>
    <0b5801c35b57$8c4ee950$gbl>
    <phx.gbl>
    | Subject: Re: stored procedure
    | Date: Thu, 7 Aug 2003 03:36:56 -0700
    | Lines: 774
    | Message-ID: <099001c35ccf$d3003e20$gbl>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | cht="iso-8859-1"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    | Thread-Index: AcNcz9L9p6EnYvWDRbWBb7GJfgkqqg==
    | Newsgroups: microsoft.public.sqlserver.programming
    | Path: cpmsftngxa06.phx.gbl
    | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:379774
    | NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
    | X-Tomcat-NG: microsoft.public.sqlserver.programming
    |
    | This is from the .cs page (this is only the code
    | pertaining to this section of the exe.
    |
    | ManagementObjectSearcher query1 = new
    | ManagementObjectSearcher
    | ("SELECT * FROM Win32_OperatingSystem") ;
    | ManagementObjectCollection
    | queryCollection1 = query1.Get();
    | foreach( ManagementObject mo in
    | queryCollection1 )
    | { //Operating system
    | TxtOs.Text = ("" + mo
    | ["Caption"]+ " " + mo["Version"].ToString());
    | //service pack version and
    | build
    | TxtOsSPVrs.Text = ("" + mo
    | ["ServicePackMajorVersion"].ToString()) ;
    | >-----Original Message-----
    | >Hi Sandie,
    | >
    | >It seems that the problem is with the executable. Could
    | you post the code
    | >of the executable?
    | >
    | >
    | >Bill Cheng
    | >Microsoft Online Partner Support
    | >
    | >Get Secure! - www.microsoft.com/security
    | >This posting is provided "as is" with no warranties and
    | confers no rights.
    | >--------------------
    | >| Content-Class: urn:content-classes:message
    | >| From: "Sandie" <mn.us>
    | >| Sender: "Sandie" <mn.us>
    | >| References: <038f01c3578c$e57b0af0$gbl>
    | ><66a301c3581b$78952f30$gbl>
    | ><46.248.16>
    | ><062601c35a88$19189780$gbl>
    | ><46.248.16>
    | ><804e01c35a98$4f241740$gbl>
    | ><phx.gbl>
    | ><009a01c35aaf$ec76a140$gbl>
    | ><phx.gbl>
    | >| Subject: Re: stored procedure
    | >| Date: Tue, 5 Aug 2003 06:43:27 -0700
    | >| Lines: 619
    | >| Message-ID: <0b5801c35b57$8c4ee950$gbl>
    | >| MIME-Version: 1.0
    | >| Content-Type: text/plain;
    | >| cht="iso-8859-1"
    | >| Content-Transfer-Encoding: 7bit
    | >| X-Newsreader: Microsoft CDO for Windows 2000
    | >| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
    | >| Thread-Index: AcNbV4xOqKpAEtA5Tj+eI6qufBvixQ==
    | >| Newsgroups: microsoft.public.sqlserver.programming
    | >| Path: cpmsftngxa06.phx.gbl
    | >| Xref: cpmsftngxa06.phx.gbl
    | microsoft.public.sqlserver.programming:379168
    | >| NNTP-Posting-Host: TK2MSFTNGXA14 10.40.1.166
    | >| X-Tomcat-NG: microsoft.public.sqlserver.programming
    | >|
    | >| Yes it can be inserted correctly with the yzer,and
    | if
    | >| I envoke it from the webservice page and the free
    | standing
    | >| web page, just not the executable. I used ethereal to
    | >| capture the TSql and it comes in fine through the
    | >| executable that way as well. When I run it through the
    | >| profiler I dont get all the data just the first part
    | which
    | >| is Mircosoft. I will check with the
    | >| microsoft.public.dotnet.framework.adonet.
    | >|
    | >| Thanks
    | >|
    | >| Sandie
    | >| >-----Original Message-----
    | >| >Hi Sandie,
    | >| >
    | >| >I would like to make sure that I understand you
    | >| correctly. OS_System field
    | >| >is changed from char(10) to char(250), and, if you
    | >| >directly execute the st_SaveDevice stored procedure in
    | >| Query yzer, data
    | >| >can be inserted correctly (data is not truncated to
    | char
    | >| (10)).
    | >| >
    | >| >If so, I would suggest that you check if the .asmx
    | page
    | >| has been
    | >| >re-compiled after you change the code for OS_System
    | field
    | >| to char(250).
    | >| >Also, since stored procedure does not have problems,
    | it
    | >| is related to the
    | >| >ADO.NET code, you may check with
    | >| microsoft.public.dotnet.framework.adonet
    | >| >newsgroup.
    | >| >
    | >| >You may also use SQL Profiler to capture the TSQL
    | >| statement sent to SQL
    | >| >Server to check if data is sent correctly, e.g.
    | whether
    | >| the TSQL is
    | >| >incorrect when arriving at SQL Server.
    | >| >
    | >| >
    | >| >Bill Cheng
    | >| >Microsoft Online Partner Support
    | >| >
    | >| >Get Secure! - www.microsoft.com/security
    | >| >This posting is provided "as is" with no warranties
    | and
    | >| confers no rights.
    | >| >--------------------
    | >| >| Content-Class: urn:content-classes:message
    | >| >| From: "Sandie" <mn.us>
    | >| >| Sender: "Sandie" <mn.us>
    | >| >| References: <038f01c3578c$e57b0af0$gbl>
    | >| ><66a301c3581b$78952f30$gbl>
    | >| ><46.248.16>
    | >| ><062601c35a88$19189780$gbl>
    | >| ><46.248.16>
    | >| ><804e01c35a98$4f241740$gbl>
    | >| ><phx.gbl>
    | >| >| Subject: Re: stored procedure
    | >| >| Date: Mon, 4 Aug 2003 10:43:32 -0700
    | >| >| Lines: 460
    | >| >| Message-ID: <009a01c35aaf$ec76a140$gbl>
    | >| >| MIME-Version: 1.0
    | >| >| Content-Type: text/plain;
    | >| >| cht="iso-8859-1"
    | >| >| Content-Transfer-Encoding: 7bit
    | >| >| X-Newsreader: Microsoft CDO for Windows 2000
    | >| >| X-MIMEOLE: Produced By Microsoft MimeOLE
    | V5.50.4910.0300
    | >| >| Thread-Index: AcNar+x0K4swyuqpSJC1ntfYB4+idA==
    | >| >| Newsgroups: microsoft.public.sqlserver.programming
    | >| >| Path: cpmsftngxa06.phx.gbl
    | >| >| Xref: cpmsftngxa06.phx.gbl
    | >| microsoft.public.sqlserver.programming:378941
    | >| >| NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
    | >| >| X-Tomcat-NG: microsoft.public.sqlserver.programming
    | >| >|
    | >| >| It's OS_System field that was changed. And yes if
    | one
    | >| of
    | >| >| the staff use the web page to insert data it works
    | >| fine,
    | >| >| she is hand typing the data into it. If you hand
    | type
    | >| it
    | >| >| in the .asmx page it goes in that way to I ran
    | ethereal
    | >| on
    | >| >| the server while I connected and ran the windows
    | >| >| application and the data comes across that way as
    | well,
    | >| >| but on the insert to the db is where it does not go
    | in,
    | >| >| what does get written in the field is Microsoft
    | which
    | >| with
    | >| >| the word Microsoft and a space is exactly 10
    | >| characters,
    | >| >| so like I said earlier its like it isnt updating its
    | >| set
    | >| >| to 250 now, but it works every where else. We have
    | >| >| checked the code a 100 times over and we know it
    | will
    | >| be
    | >| >| something really simple but can not seem to find it
    | >| >| anywhere.
    | >| >|
    | >| >| thanks
    | >| >|
    | >| >| Sandie
    | >| >| >-----Original Message-----
    | >| >| >Hi Sandie,
    | >| >| >
    | >| >| >Which field is changed for char(10) to char(250)?
    | If
    | >| you
    | >| >| directly execute
    | >| >| >the st_SaveDevice stored procedure in Query
    | yzer,
    | >| >| will the data be
    | >| >| >inserted correctly?
    | >| >| >
    | >| >| >
    | >| >| >Bill Cheng
    | >| >| >Microsoft Online Partner Support
    | >| >| >
    | >| >| >Get Secure! - www.microsoft.com/security
    | >| >| >This posting is provided "as is" with no warranties
    | >| and
    | >| >| confers no rights.
    | >| >| >--------------------
    | >| >| >| Content-Class: urn:content-classes:message
    | >| >| >| From: "Sandie" <mn.us>
    | >| >| >| Sender: "Sandie" <mn.us>
    | >| >| >| References: <038f01c3578c$e57b0af0
    | $gbl>
    | >| >| ><66a301c3581b$78952f30$gbl>
    | >| >| ><46.248.16>
    | >| >| ><062601c35a88$19189780$gbl>
    | >| >| ><46.248.16>
    | >| >| >| Subject: Re: stored procedure
    | >| >| >| Date: Mon, 4 Aug 2003 07:54:30 -0700
    | >| >| >| Lines: 328
    | >| >| >| Message-ID: <804e01c35a98$4f241740
    | $gbl>
    | >| >| >| MIME-Version: 1.0
    | >| >| >| Content-Type: text/plain;
    | >| >| >| cht="iso-8859-1"
    | >| >| >| Content-Transfer-Encoding: 7bit
    | >| >| >| X-Newsreader: Microsoft CDO for Windows 2000
    | >| >| >| X-MimeOLE: Produced By Microsoft MimeOLE
    | >| V5.50.4910.0300
    | >| >| >| Thread-Index: AcNamE8kBlfU9jAFRJa24CBB+TQb1A==
    | >| >| >| Newsgroups: microsoft.public.sqlserver.programming
    | >| >| >| Path: cpmsftngxa06.phx.gbl
    | >| >| >| Xref: cpmsftngxa06.phx.gbl
    | >| >| microsoft.public.sqlserver.programming:378887
    | >| >| >| NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
    | >| >| >| X-Tomcat-NG:
    | microsoft.public.sqlserver.programming
    | >| >| >|
    | >| >| >| Now that would be to easy but its not it I
    | checked
    | >| that
    | >| >| >| and its set to char 250 just to make sure it will
    | >| all
    | >| >| come
    | >| >| >| through. I dont think I am missing something here
    | >| but
    | >| >| you
    | >| >| >| can check. Any other ideas? The field used to
    | be
    | >| set
    | >| >| to
    | >| >| >| char 10 but was changed to the char 250, its like
    | it
    | >| is
    | >| >| >| not recompiling or whatever you call it.
    | >| >| >|
    | >| >| >| Device
    | >| >| >|
    | >| >|
    | >|
    | dbo
    | >| >| >| user table 2003-07-31
    | >| 09:32:57.200
    | >| >| >|
    | >| >|
    | >|
    | Device_ID
    | >| >| >|
    | >| >| >|
    | >| >|
    | >|
    | char
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >| no
    | >| >| >| 6
    | >| >| >| no
    | >| >| >| no
    | >| >| >| no
    | >| >| >| SQL_Latin1_General_CP1_CI_AS
    | >| >| >|
    | >| >|
    | >|
    | Agency_Ori
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >|
    | >| >| >|
    | >| >|
    | >|
    | char
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >| no
    | >| >| >| 10
    | >| >| >| no
    | >| >| >| no
    | >| >| >| no
    | >| >| >| SQL_Latin1_General_CP1_CI_AS
    | >| >| >|
    | >| >|
    | >|
    | OS_System
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >|
    | >| >| >|
    | >| >|
    | >|
    | char
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >| no
    | >| >| >| 250
    | >| >| >| yes
    | >| >| >| no
    | >| >| >| yes
    | >| >| >| SQL_Latin1_General_CP1_CI_AS
    | >| >| >|
    | >| >|
    | >|
    | OS_ServicePack
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >|
    | >| >| >|
    | >| >|
    | >|
    | char
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >| no
    | >| >| >| 5
    | >| >| >| yes
    | >| >| >| no
    | >| >| >| yes
    | >| >| >| SQL_Latin1_General_CP1_CI_AS
    | >| >| >|
    | >| >|
    | >|
    | FreeDiskSpace
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >|
    | >| >| >|
    | >| >|
    | >|
    | char
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >| no
    | >| >| >| 25
    | >| >| >| yes
    | >| >| >| no
    | >| >| >| yes
    | >| >| >| SQL_Latin1_General_CP1_CI_AS
    | >| >| >|
    | >| >|
    | >|
    | Cpu_MHZ
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >|
    | >| >| >|
    | >| >|
    | >|
    | char
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >| no
    | >| >| >| 10
    | >| >| >| yes
    | >| >| >| no
    | >| >| >| yes
    | >| >| >| SQL_Latin1_General_CP1_CI_AS
    | >| >| >|
    | >| >|
    | >|
    | Main_Memory
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >|
    | >| >| >|
    | >| >|
    | >|
    | char
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >| no
    | >| >| >| 10
    | >| >| >| yes
    | >| >| >| no
    | >| >| >| yes
    | >| >| >| SQL_Latin1_General_CP1_CI_AS
    | >| >| >|
    | >| >|
    | >|
    | ScreenResolution
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >|
    | >| >| >|
    | >| >|
    | >|
    | char
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >| no
    | >| >| >| 10
    | >| >| >| yes
    | >| >| >| no
    | >| >| >| yes
    | >| >| >| SQL_Latin1_General_CP1_CI_AS
    | >| >| >|
    | >| >|
    | >|
    | IPAddress
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >|
    | >| >| >|
    | >| >|
    | >|
    | char
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >| no
    | >| >| >| 15
    | >| >| >| yes
    | >| >| >| no
    | >| >| >| yes
    | >| >| >| SQL_Latin1_General_CP1_CI_AS
    | >| >| >|
    | >| >|
    | >|
    | IE_Vrs
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >|
    | >| >| >|
    | >| >|
    | >|
    | char
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >| no
    | >| >| >| 10
    | >| >| >| yes
    | >| >| >| no
    | >| >| >| yes
    | >| >| >| SQL_Latin1_General_CP1_CI_AS
    | >| >| >|
    | >| >|
    | >|
    | IE_ServicePackVrs
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >|
    | >| >| >|
    | >| >|
    | >|
    | char
    | >| >|
    | >|
    | >|
    | >| >|
    | >| >| >| no
    | >| >| >| 250
    | >| >| >| yes
    | >| >| >| no
    | >| >| >| yes
    | >| >| >| SQL_Latin1_General_CP1_CI_AS
    | >| >| >|
    | >| >| >| >-----Original Message-----
    | >| >| >| >sandie:
    | >| >| >| >
    | >| >| >| >i didn't see a ddl (table definition) for the
    | table
    | >| >| >| Device, is it
    | >| >| >| >possible the table has a size of 10 chars? try
    | >| >| sp_help
    | >| >| >| Device from
    | >| >| >| >query yzer. you might find the column,
    | >| OS_System,
    | >| >| >| allows only 10
    | >| >| >| >chars.
    | >| >| >| >
    | >| >| >| >good luck,
    | >| >| >| >jeff clausius
    | >| >| >| >sourcegear corporation
    | >| >| >| >
    | >| >| >| >
    | >| >| >| >
    | >| >| >| >"Sandie" <mn.us> wrote in
    | >| >| >| news:062601c35a88
    | >| >| >| >$19189780$gbl:
    | >| >| >| >
    | >| >| >| >> No I didnt try
    | >| >| microsoft.public.dotnet.framework.adonet
    | >| >| >| >> user group. I wasnt sure where the issue lies
    | >| since
    | >| >| >| the
    | >| >| >| >> web service and the code works fine at least
    | what
    | >| it
    | >| >| >| >> appears to use as doing.
    | >| >| >| >>
    | >| >| >| >> so I will post the requested information here
    | and
    | >| >| see
    | >| >| >| what
    | >| >| >| >> happens if no result I will try there.
    | >| >| >| >>
    | >| >| >| >> [WebMethod]
    | >| >| >| >> public string SaveDevice(string
    | >| Ori,string
    | >| >| >| >> Device, string os, string osVrs, string
    | >| diskSpace,
    | >| >| >| >> string cpu, string memory, string resolution,
    | >| string
    | >| >| >| ip,
    | >| >| >| >> string ieVrs, string ieServicePck)
    | >| >| >| >> {
    | >| >| >| >> string theDeviceReturnValue = "";
    | >| >| >| >> try
    | >| >| >| >> {
    | >| >| >| >> SqlConnection conn = new SqlConnection
    | >| >| >| >>
    | >| >|
    | (System.Configuration.ConfigurationSettings.AppSet tings
    | >| >| >| >> ["connectionString"]);
    | >| >| >| >> conn.Open();
    | >| >| >| >> SqlCommand cmd = new SqlCommand();
    | >| >| >| >> cmd.CommandText = "st_SaveDevice";
    | >| >| >| >> cmd.CommandType = CommandType.StoredProcedure;
    | >| >| >| >> cmd.Parameters.Add
    | >| >| >| >> ("Agency_Ori",SqlDbType.Char,10,"Agency_Ori");
    | >| >| >| >> cmd.Parameters.Add
    | >| >| >| >> ("Device_ID",SqlDbType.Char,5,"Device_ID");
    | >| >| >| >> cmd.Parameters.Add
    | >| >| >| >> ("OS_System",SqlDbType.Char,250,"OS_System");
    | >| >| >| >> cmd.Parameters.Add
    | >| >| >| >>
    | >| >|
    | ("OS_ServicePack",SqlDbType.Char,75,"OS_ServicePa ck");
    | >| >| >| >> cmd.Parameters.Add
    | >| >| >| >>
    | >| ("FreeDiskSpace",SqlDbType.Char,5,"FreeDiskSpace" );
    | >| >| >| >> cmd.Parameters.Add
    | >| >| >| ("Cpu_MHZ",SqlDbType.Char,10,"Cpu_MHZ");
    | >| >| >| >> cmd.Parameters.Add
    | >| >| >| >>
    | ("Main_Memory",SqlDbType.Char,10,"Main_Memory" );
    | >| >| >| >> cmd.Parameters.Add
    | >| >| >| >>
    | >| >| >|
    | >| >|
    | >|
    | ("ScreenResolution",SqlDbType.Char,10,"ScreenReso lution");
    | >| >| >| >> cmd.Parameters.Add
    | >| >| >| >> ("IPAddress",SqlDbType.Char,15,"IPAddress");
    | >| >| >| >> cmd.Parameters.Add
    | >| >| >| ("IE_Vrs",SqlDbType.Char,10,"IE_Vrs");
    | >| >| >| >> cmd.Parameters.Add
    | >| >| >| >>
    | >| >| >|
    | >| >|
    | >|
    | ("IE_ServicePackVrs",SqlDbType.Char,10,"IE_Servic ePackVrs"
    | >| >| >| >> );
    | >| >| >| >> cmd.Parameters["Agency_Ori"].Value = Ori;
    | >| >| >| >> cmd.Parameters["Device_ID"].Value = Device;
    | >| >| >| >> cmd.Parameters["OS_System"].Value = os;
    | >| >| >| >> cmd.Parameters["OS_ServicePack"].Value =
    | osVrs;
    | >| >| >| >> cmd.Parameters["FreeDiskSpace"].Value =
    | >| diskSpace;
    | >| >| >| >> cmd.Parameters["Cpu_MHZ"].Value = cpu;
    | >| >| >| >> cmd.Parameters["Main_Memory"].Value = memory;
    | >| >| >| >> cmd.Parameters["ScreenResolution"].Value =
    | >| >| resolution;
    | >| >| >| >> cmd.Parameters["IPAddress"].Value = ip;
    | >| >| >| >> cmd.Parameters["IE_Vrs"].Value = ieVrs;
    | >| >| >| >> cmd.Parameters["IE_ServicePackVrs"].Value =
    | >| >| >| ieServicePck;
    | >| >| >| >> cmd.Connection = conn;
    | >| >| >| >> int num = cmd.ExecuteNonQuery();
    | >| >| >| >> if(num != 0)
    | >| >| >| >> theDeviceReturnValue = "Device Data Save
    | >| Successful,
    | >| >| >| Exit
    | >| >| >| >> the Application" ;
    | >| >| >| >> }
    | >| >| >| >> catch(Exception e)
    | >| >| >| >> {
    | >| >| >| >> theDeviceReturnValue = e.Message;
    | >| >| >| >> }
    | >| >| >| >> return theDeviceReturnValue;
    | >| >| >| >> }
    | >| >| >| >>
    | >| >| >| >> CREATE PROCEDURE st_SaveDevice
    | >| >| >| >> (
    | >| >| >| >> Agency_Ori char(10),
    | >| >| >| >> Device_ID char(6),
    | >| >| >| >> OS_System char(250),
    | >| >| >| >> OS_ServicePack char(5),
    | >| >| >| >> FreeDiskSpace char(25),
    | >| >| >| >> Cpu_MHZ char(10),
    | >| >| >| >> Main_Memory char(10),
    | >| >| >| >> ScreenResolution char(10),
    | >| >| >| >> IPAddress char(15),
    | >| >| >| >> IE_Vrs char(10),
    | >| >| >| >> IE_ServicePackVrs char(250)
    | >| >| >| >> )
    | >| >| >| >>
    | >| >| >| >> AS
    | >| >| >| >>
    | >| >| >| >>
    | >| >| >| >> Begin
    | >| >| >| >> INSERT Device
    | >| >| >| >> (Agency_Ori,Device_ID,OS_System,OS_ServicePack,
    | >| >| >| >>
    | >| FreeDiskSpace,Cpu_MHZ,Main_Memory,ScreenResolution ,
    | >| >| >| >> IPAddress,IE_Vrs,IE_ServicePackVrs)
    | >| >| >| >> Values
    | >| >| >|
    | (Agency_Ori,Device_ID,OS_System,OS_ServicePack ,
    | >| >| >| >>
    | >| >|
    | FreeDiskSpace,Cpu_MHZ,Main_Memory,ScreenResolu tion,
    | >| >| >| >> IPAddress,IE_Vrs,IE_ServicePackVrs)
    | >| >| >| >> END
    | >| >| >| >> GO
    | >| >| >| >>
    | >| >| >| >> Thanks!
    | >| >| >| >>
    | >| >| >| >> Stokh
    | >| >| >| >>
    | >| >| >| >>
    | >| >| >| >>>-----Original Message-----
    | >| >| >| >>>sandie:
    | >| >| >| >>>
    | >| >| >| >>>we would like to help, but you haven't really
    | >| >| provided
    | >| >| >| >> enough
    | >| >| >| >>>information to post an answer. for example,
    | the
    | >| >| code
    | >| >| >| in
    | >| >| >| >> your web
    | >| >| >| >>>method, the actual stored proc, the table's
    | ddl
    | >| all
    | >| >| >| would
    | >| >| >| >> be helpful.
    | >| >| >| >>>
    | >| >| >| >>>
    | >| >| >| >>>additionally, have you tried the
    | >| >| >| >>>microsoft.public.dotnet.framework.adonet user
    | >| >| group?
    | >| >| >| >> someone in there
    | >| >| >| >>>might be able to help as well.
    | >| >| >| >>>
    | >| >| >| >>>
    | >| >| >| >>>jeff clausius
    | >| >| >| >>>sourcegear corporation
    | >| >| >| >>>
    | >| >| >| >>>
    | >| >| >| >>>
    | >| >| >| >>>"Stokh" <mn.us> wrote in
    | >| >| >| >> news:66a301c3581b$78952f30
    | >| >| >| >>>$gbl:
    | >| >| >| >>>
    | >| >| >| >>>> No one in here knows the answer to this?
    | >| >| >| >>>>>-----Original Message-----
    | >| >| >| >>>>>I'm using a web service for a windows
    | >| application
    | >| >| and
    | >| >| >| >> and
    | >| >| >| >>>>>in my webmethod I use a stored procedure to
    | do
    | >| the
    | >| >| >| >> insert
    | >| >| >| >>>>>to a certain table. The field was set at
    | char
    | >| (10)
    | >| >| >| >> which
    | >| >| >| >>>>>was to small for all the data I am
    | collecting
    | >| in
    | >| >| the
    | >| >| >| >>>>>windows application the field collects the
    | OS
    | >| >| system
    | >| >| >| >> from
    | >| >| >| >>>>>the clients computer so it reads in the
    | >| >| application
    | >| >| >| >>>>>as "Microsoft Windows XP Professional
    | 5.1.2600"
    | >| >| but
    | >| >| >| >>>>>inserts as "Microsoft" I changed the field
    | size
    | >| >| both
    | >| >| >| in
    | >| >| >| >>>>>the db and in the stored procedure and it
    | >| doesnt
    | >| >| >| matter
    | >| >| >| >>>> it
    | >| >| >| >>>>>still doesnt work, if I do it from the asmx
    | >| page
    | >| >| and
    | >| >| >| >>>>>invoke it from there it works just fine.
    | Any
    | >| >| ideas
    | >| >| >| >> would
    | >| >| >| >>>>>be great because we are stumped!.
    | >| >| >| >>>>>
    | >| >| >| >>>>>Thanks in advance for your help!
    | >| >| >| >>>>>
    | >| >| >| >>>>>
    | >| >| >| >>>>>.
    | >| >| >| >>>>>
    | >| >| >| >>>>
    | >| >| >| >>>
    | >| >| >| >>>.
    | >| >| >| >>>
    | >| >| >| >
    | >| >| >| >.
    | >| >| >| >
    | >| >| >|
    | >| >| >
    | >| >| >.
    | >| >| >
    | >| >|
    | >| >
    | >| >.
    | >| >
    | >|
    | >
    | >.
    | >
    |

    Bill Guest

  12. #12

    Default Re: stored procedure

    Well that would be nice. 
    dotnet.framework.adonet. Since the 
    executable program, 
    information. 
    confers no rights. 
    microsoft.public.sqlserver.programming:379774 
    Could 
    and 
    V5.50.4910.0300 
    yzer,and 
    to 
    the 
    in 
    problems, 
    $gbl> 
    $gbl> 
    microsoft.public.sqlserver.programming 
    if 
    works 
    go 
    its 
    have 
    it 
    (250)? 
    warranties 
    microsoft.public.sqlserver.programming 
    will 
    here 
    to 
    like 
    Device 
    dbo 
    Device_ID 
    char 
    Agency_Ori 
    char 
    OS_System 
    char 
    OS_ServicePack 
    char 
    FreeDiskSpace 
    char 
    Cpu_MHZ 
    char 
    Main_Memory 
    char 
    ScreenResolution 
    char 
    IPAddress 
    char 
    IE_Vrs 
    char 
    IE_ServicePackVrs 
    char 
    try 
    in 
    lies 
    least 
    here 
    resolution, 
    CommandType.StoredProcedure; 
    ("Agency_Ori",SqlDbType.Char,10,"Agency_Ori"); 
    ("OS_System",SqlDbType.Char,250,"OS_System"); 
    ("ScreenResolution",SqlDbType.Char,10,"ScreenReso lution"); 
    ("IPAddress",SqlDbType.Char,15,"IPAddress"); 
    ("IE_ServicePackVrs",SqlDbType.Char,10,"IE_Servic ePackVrs" 
    memory; 

    (Agency_Ori,Device_ID,OS_System,OS_ServicePack, 
    really 
    example, 
    user 
    in 
    to 
    the 
    field 
    asmx 
    Sandie Guest

  13. #13

    Default stored procedure

    Hello All,
    Hoping for some input...new to sp's.

    I need to update a table based on results of the sp below.
    If Returned + Invoiced + Voided = > 0 then update a
    specific field in a record.
    Any ideas on what the next step would be?

    Thanks, R

    CREATE PROCEDURE dbo.X_UPDATE
    AS
    SELECT RETURNED = (CASE WHEN TABLEA.TYPE = 4 THEN '-1'
    ELSE '0' END),
    INVOICED = (CASE WHEN TABLEA.TYPE = 3 THEN '1' ELSE '0'
    END),
    VOIDED = (CASE WHEN TABLEA.VSTTS = 1 THEN '-1' ELSE '0'
    END),
    TABLEA.CUSTNMBR, TABLEB.ITEMNMBR, TABLEB.INVNUMB
    FROM TABLEA INNER Join
    TABLEB ON TABLEA.NUMB = TABLEB.INVNUMB

    GO
    robert Guest

  14. #14

    Default Re: stored procedure

    Try this:

    Add the values Returned + Invoiced + Voided. Return the SUM as a INT.
    If the returned sum from the SP is 0, the insert will proceed.

    Jens Süßmeyer.


    "robert" <com> schrieb im Newsbeitrag
    news:07dd01c36665$70bbc5f0$gbl... 


    Jens Guest

Similar Threads

  1. Stored Procedure
    By Aaron Bertrand - MVP in forum ASP.NET
    Replies: 13
    Last Post: July 5th, 05:43 AM
  2. stored procedure help
    By Maria in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 20th, 06:55 PM
  3. stored procedure value
    By -D- in forum Dreamweaver AppDev
    Replies: 1
    Last Post: March 28th, 07:48 PM
  4. Stored Procedure Help
    By Jack in forum Microsoft SQL / MS SQL Server
    Replies: 7
    Last Post: July 14th, 06:33 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139