MS SQL Server :: Inline Sql With An Array

How Would I Send A String Array As A Integer Array?
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type.
What would I need to do to convert it to an Integer array?
@OfficerIDs as varchar(200)
Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs)
Thanks

View Replies !

Array Of Array - IRR Function
Hi, I am using the IRR function in a report. I have created the following code so it creates an array:

Public GroupIRRArray(-1) As Double
Public Function addToIRRArray(ByVal BMV As Decimal, ByVal BAB As Decimal, ByVal EMV As Decimal, ByVal EAB As Decimal, ByVal CFB As Decimal)
Dim g As Integer
g = uBound(GroupIRRArray) + 1
ReDim Preserve GroupIRRArray(g)
if g=0 then
GroupIRRArray(g) = (CFB+EAB-BAB+BMV)*-1
else if g=1 then
GroupIRRArray(g) = (BAB-CFB-EAB+EMV)
else
GroupIRRArray(g-1)= GroupIRRArray(g-1)-(BMV)
GroupIRRArray(g) = (BAB-CFB-EAB+EMV)
end if
End Function

It works fine but now I want to create multiple groups within my report. How can I change the code so it loops on another parameter? What I had in mind was to create an initial array with the parameter value that I want to use for grouping and a dynamic array based on the name of each group. So I would end up with one array containing the group name plus x number of arrays with the raw data. Alternatively, is there a way to use the IRR function without creating a custom code? Like a conversion parameter that would make my floating field a one dimensional array?

Thanks,
Jam

View Replies !

Inline If
I want to execute something likeselect iif(type='credit',amount*-1,amount) from tablehow can i do that? help file says iif is used in MultidimensionalExpressions but that seems overly complicated for this task.any ideas?

View Replies !

Inline SQL
Hello,

I would like to know if there is any sort of improvement when using Inline SQL vs Stored Procedures as far as the execution plans are concerned when they are used in SSIS packages. I happened to create a SSIS package which calls Stored Procedures but internally they are using Linked Servers to get the data required. At this point they want to know what will be benefit that can be achieved when Inline SQL is used.

I would appreciate if anybody can give their thoughts or provide some informative articles like pros and cons.

Thanks in advance.

View Replies !

More Command Inline
Hi.

"isqlw" is for Query Analizer.

Which is for Enterprise Manager?

View Replies !

IF In Query (inline IF)
Hi, I'm trying to get MSSQL to choose which row to display from a result from a query, but it seems to need an 'IF' or something similar.

My query asks the database to pick out all rows that meet critera X, but sometimes (quite correctly) some of the rows created by query X are duplicates. (A data overlap nothing wrong with the query)

I want to be able to get the query to decide which one of these duplicates to display. There is a unique element.

Example Below

|ID | SYSNAME |DATE |ACTIVE
---------------------------------------------
1 | PC1 | 10/3/7 | 1
2 | PC1 | 10/3/7 | 0
3 | PC2 | 10/3/7 | 1

ID 1 + 2 is the duplicate.

In my example I want to remove ID 1 from the result, I can't use the 'active' column as this will remove ID3 fro the result.
I want the query to recognise the duplicate based on the sysname, then choose to display the result with 0 in it.

Does anyone have a clue what I'm talking about? I'm loosing the plot.

View Replies !

Inline Vs Multistatement Functions
I understand what constitutes an inline function (and multi-statement) but I'm not sure what SQL actually does with them. I'm assuming that its name gives it away, that when you use a inline function then its code is "copied" (at compile time) into your calling procedure and then the optimiser gets to work as if you'd written it as one batch? I.e. just like inline functions in other programming languages.

View Replies !

Inline Variable Assignment
I have to write a query for printing multiple barcodes, depending on the quantity of items that came in the store, based on the order number.

DECLARE @num INT
SELECT BarCodes.BarCode, BarCodes.ArticleID, ArticlesTrafic.DocumentID, ArticlesTrafic.TrafficQuantity
FROM BarCodes INNER JOIN
Articles ON BarCodes.ArticleID = Articles.ArticleID INNER JOIN
getAutoNumberTable(@num) ON @num=ArticlesTrafic.TrafficQuantity
WHERE (ArticlesTrafic.DocumentID = @Param2)

The thing i would like to do, is somehow assign a value to @num and pass it to the getAutoNumberTable stored procedure, which generates a table of consequtive numbers, so that each record is displayed multiple times. Is it even possible to do it without using temp tables and loops?

View Replies !

Inline Schema - XML Source
I have a serialized XML that I got from a dataset. In my 'Data Flow Task', I bind the 'XML Source' source to this XML file. Since the XML file is having the schema along with it, I check the 'Use Inline schema' option. However, when I put a dataviewer to see the rows getting sent to the destination, I see that no rows are getting transfered. As you will see from the XML file I am trying to use, I do have one row to transfer.

I tried kepping the schema file and the content file separate and that worked. I am not sure if there are any inherent issues I need to take care of, when using inline schemas to transfer data. I have the SP2 for SQL 2005 installed.

Here is the XML file content:

<?xml version="1.0"?>
<DataSet>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="NewDataSet">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="Table1">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" name="LastName" type="xs:string" />
              <xs:element minOccurs="0" name="FirstName" type="xs:string" />
              <xs:element minOccurs="0" name="Descr" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
</xs:element>
</xs:schema>
<NewDataSet>
<Table1>
<LastName>Agrawal</LastName>
<FirstName>Sumeet</FirstName>
<Descr>Consultant 1</Descr>
</Table1>
</NewDataSet>
</DataSet>

Thanks,

Sumeet

View Replies !

Bold Inline Textbox
This feels like a question that has been asked 1000 times...I'm just not having much luck finding an answer.

I want to bold a single word in a Textbox on a RS2005 report. Is there a way to do this? The text is always bold. Like this:

By signing this document you accept our Terms and Conditions.

Thanks.
Brian

View Replies !

Inline Images Appear As Attachments
I have a report subscription that sends the reports in MHTML format. On some email clients, Outlook 2003, it looks just fine. On other clients, Outlook Web Access 2003, the images are "broken with a red X" and come in as attachments instead. Now if someone with Outlook opens the message and forwards it to someone that opens it in Outlook Web Access, the problem gets fixed and the images appear!

It's like the MHTML format is not 100% up to the email standard. Some email clients can work with it and some can't.

Also, I tried to not embed the images and have them as links instead. But when the emails get rendered, the images still get embeded in the MHTML instead of going as links.

Help!

Thanks,
Stu

View Replies !

Formatting Inline Bar Charts
I have two questions regarding in-line bar charts...

First, I put the text as data labels and check 'Auto' for positioning, most of the time the text is written to the right of the bar (which is what I want). However, there is one case that if the space to the right of the bar is not enough for the text, it starts writting on the bar itself.... is there a way I could set it so that the text only appears when there is enough space to the right, so that text never appears on any bars?

Secondly, is there a way I could put hyperlinks on the text?

Thanks again,
Steven

View Replies !

Storedprocedure Or Inline SQL Statement?
I am developing ASP.NET 2.0 website. I need to know some about using stored procedure. I searched through google. But could now find a favourable repLy.
Here is ..

Which way is efficient, using SQL inside the code or as SRORED PROCEDRE, which one to use with ASP.NET?
Is the Stored procedure must be created withing the server or from my application?Can anyone please give some practicle explaination about this?
My advance thanks for all...

View Replies !

How To Do A Sqlcmd Inline Commands

hi,

i am not sure if this forum is right place to ask this question..
i am trying right a dos batch file to do setup of sql commands run by sqlcmd , run some dos commands etc

net start mssql$server
sqlcmd -E .....

net stop mssql$server...

sqlcmd -E ....

in unix you can run isql with the sql commands place inside..

isql -Uuser -S server <<EOF
select 1
select 2
go
EOF

you can put above in a shell and it will run.

i am trying to do similar stuff in windows for sqlcmd.. how can i do it

only option i have is to create lot of .sql files and
run with -i option on sqlcmd..

can some body let me know how to do a inline commands in dos?
thx
AK

View Replies !

Converting An Inline Sql To A Stored Procedure
Hi i have the following method in my page, it works fine and everything, but i am trying to modify it so that it can take a stored procedure;
protected void Button1_Click(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);String sql = "SELECT userID, userName FROM users WHERE userName LIKE " + "'" + userName.Text + "%' OR organisation= " + "'" + OrganisationList.SelectedValue + "'";
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
DataList1.DataSource = reader;
DataList1.DataBind();
conn.Close();
}

My question is what should my stored procedure be, and also how will i now structure the above method.

View Replies !

Changing A Stored Procedure To Inline Sql
Hi i have the following stored procedure, i am trying to convert it to inline sql, however i got stuck, because how would i output a value e.g "@Access_Right_ID", this is the stored procedure below
CREATE PROCEDURE dhoc_AccessRight_Insert @AccessLevel char(50), @Access_Right_ID int OUT,
@Tstamp timestamp out
ASSELECT * FROM tblAccess_Right
WHERE AccessLevel = @AccessLevel IF @@ROWCOUNT <>0   BEGIN         RAISERROR('This record is already in the database',16,1)         RETURN 14    END
ELSE   BEGIN SET NOCOUNT OFF;             INSERT INTO tblAccess_Right (   AccessLevel ) VALUES (@AccessLevel);            SET @Access_Right_ID = @@Identity            SET @Tstamp = (SELECT Tstamp FROM tblAccess_Right WHERE Access_Right_ID=@Access_Right_ID)
      --Make sure this has saved, if not return 10 as this is unexpected error     IF @@rowcount = 0 BEGIN                    RAISERROR('This record has not been inserted at this time, it might have been inserted by another user, please try again',16,1)        RETURN 10 END     ELSE          BEGIN  IF @@error <>0 BEGIN    RETURN @@error            END     ENDEND
GO

View Replies !

Stored Procedures Vs Inline Sql Commands
Hi,I have a simple question.Which is more efficient, using stored procedures in Sql Server or writing the sql commands in my C# code???

View Replies !

Left Joins And Inline Views
Hi,

I am working a DTS package and I need to Join to completely differnet tables in such a way that I need to do an inline view and an Outer Join. In this current form, it drops all columns for a day if one of the inline views returns null.

SELECT 01 as WHSE_ID
, A.On_Time
, B.Early
, C.Late
, (D.AVG_Duration / (A.On_Time + B.Early + C.Late))AS AVG_Duration
, E.DelDate
, F.*
FROM

(SELECT COUNT(SDD_Status) AS On_Time
, SDD_Date as On_Time_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%' AND SDD_Status = 'On Time'
AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) a,

(SELECT COUNT(SDD_Status) AS Early
,SDD_DATE As Early_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Early'
And SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) b,

(SELECT COUNT(SDD_Status) AS Late
, SDD_Date As Late_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%' AND SDD_Status = 'Late'
AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) c,

(SELECT SUM(CAST(SDD_Stay AS NUMERIC)) AS AVG_Duration
, SDD_Date As Stay_Date
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Route LIKE '01%'
AND SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date) d,

(SELECT DISTINCT(SDD_Date) AS DelDate
FROM SDD_Store_Delivery_Data_Table
WHERE SDD_Date < '12/19/2004' AND SDD_Date > '12/10/2004'
GROUP BY SDD_Date)e,

(SELECT *
FROM WAREHOUSE_METRICS
WHERE MTRC_DTE < '12/19/2004' AND MTRC_DTE > '12/10/2004'
AND WHSE_ID = 2
GROUP BY MTRC_DTE
, MTRC_ID
, WHSE_ID
, INVN_LVL_CS_CNT
, INVN_LVL_DLLR
, INVN_LVL_PLLT_CNT
, RCV_CS_CNT
, SHP_CS_CNT
, SRVC_LVL_PCT
, SCRTCH_CNT
, DROP_CNT
, RPLNSH_CNT
, DMG_CNT
, RET_CNT
, PICK_CYC_CNT
, PICK_ERR_CNT
, RSV_CHK_CNT
, ERR_CNT
, DLY_CS_VRNC
, DLY_DLLR_VRNC
, CMB_THRUPUT
, DELAY_MINS_CNT
, DELAY_PCT
, UNLOAD_AVG
, LABOR_AVG
, SELECT_HR_CNT
, CRT_USERID
, CRT_DTE_TME
, UPD_USERID
, UPD_DTE_TME) f

WHERE a.On_Time_Date = E.DelDate
AND B.Early_Date = E.DelDate
AND C.Late_Date = E.DelDate
AND D.Stay_Date = E.DelDate
AND F.MTRC_DTE = E.DelDate

GROUP BY E.DelDate
, A.On_Time
, B.Early
, C.Late
, AVG_Duration
, A.On_Time_Date
, B.Early_Date
, C.Late_Date
, D.Stay_Date
, F.WHSE_ID
, F.INVN_LVL_CS_CNT
, F.INVN_LVL_DLLR
, F.INVN_LVL_PLLT_CNT
, F.RCV_CS_CNT
, F.SHP_CS_CNT
, F.SRVC_LVL_PCT
, F.SCRTCH_CNT
, F.DROP_CNT
, F.RPLNSH_CNT
, F.DMG_CNT
, F.RET_CNT
, F.PICK_CYC_CNT
, F.PICK_ERR_CNT
, F.RSV_CHK_CNT
, F.ERR_CNT
, F.DLY_CS_VRNC
, F.DLY_DLLR_VRNC
, F.CMB_THRUPUT
, F.DELAY_MINS_CNT
, F.DELAY_PCT
, F.UNLOAD_AVG
, F.LABOR_AVG
, F.SELECT_HR_CNT
, F.CRT_USERID
, F.CRT_DTE_TME
, F.UPD_USERID
, F.UPD_DTE_TME
, F.MTRC_ID
, F.MTRC_DTE
Order By E.DelDate

Please excuse the length of the code but one of the tables has a lot of columns.

Can anyone tell me if it's possble to do a join on an inline view?

Any help would be greatly appreciated.

Thanks in advance,

John

View Replies !

Efficiency: Inline TSQL V. Subcalls
Hello,

I regularly create stored procedures and use them like functions within other stored procedures.

I've never had any difficulty but then I never ran any metrics on it.

Does anyone know if there is an efficiency difference between that approach and just doing an inline query? How much of a difference is it? minimal? impractically large?

For example, if I define an sp like:

create proc isValidUser @userID int , @result int OUTPUT as

if exists(select * from user where userid = @userID)
set @result = 1

View Replies !

Stored Procedures Vs Inline Queries

Hi,

We have our DWH built in SQL Server 2005. We are doing reporting against the DWH and most of query logic is complex. For this purpose we have proposed to write SPs to encapsulate the logic and which in turn can be called from the Report Data Tab. But our IT Architect suggest us to have inline queries instead of SPs as that will reduce the overhead of going to the DBAs everytime the logic changes. As I am new to DWH+Reporting, I was really pondering over which way to go. Can anyone suggest a way out on this.
Thanks,
S Suresh

View Replies !

Create View Of Inline Function
Hello. I'm a real newbie - using Access 2003 front end and connecting to SQL Server 2005 ODBC.
I'm having trouble accessing functions through access. I've built the following function:

CREATE FUNCTION fnSTR_LEASESTATUS(@TRS nvarchar(12))

RETURNS TABLE

AS

RETURN

(

SELECT dbo.tblTRACT.STR, dbo.tblTRACT.[TRACT_#], dbo.tblMIN_OWNERS.Min_Owner_Name AS [OWNER OF RECORD], dbo.tblLEASE_TRACTS.LOC_ID, dbo.tblLOCATION.LPR_No, dbo.tblLOCATION.Lease_ID, dbo.tblLEASE_LOG.Date_Mailed, dbo.tblLEASE_LOG.Scan_Lease_Received, dbo.tblLEASE_LOG.Orig_Lease_Recd, dbo.tblLPR_INVOICES.Invoice_No, dbo.tblLPR_PAY.CHECK_DRAFT_No, dbo.tblLESSORS.Name AS [Lease Name]

FROM dbo.tblTRACT LEFT JOIN ((dbo.tblMIN_OWNERS RIGHT JOIN dbo.tblTRACT_OWNER ON dbo.tblMIN_OWNERS.Min_Owner_ID = dbo.tblTRACT_OWNER.Owner_Lease) LEFT JOIN ((((((dbo.tblLPR RIGHT JOIN dbo.tblLOCATION ON dbo.tblLPR.LPR_No = dbo.tblLOCATION.LPR_No) LEFT JOIN dbo.tblLESSORS ON dbo.tblLPR.Lessor_Number = dbo.tblLESSORS.Lessor_Number) RIGHT JOIN dbo.tblLEASE_TRACTS ON dbo.tblLOCATION.LOC_ID = dbo.tblLEASE_TRACTS.LOC_ID) LEFT JOIN dbo.tblLEASE_LOG ON dbo.tblLPR.LPR_No = dbo.tblLEASE_LOG.LPR_No) LEFT JOIN dbo.tblLPR_INVOICES ON dbo.tblLPR.LPR_No = dbo.tblLPR_INVOICES.LPR_No) LEFT JOIN dbo.tblLPR_PAY ON dbo.tblLPR.LPR_No = dbo.tblLPR_PAY.LPR_No) ON dbo.tblTRACT_OWNER.TRACT__Owner_ID = dbo.tblLEASE_TRACTS.Tract_Owner_Id) ON (dbo.tblTRACT.[TRACT_#] = dbo.tblTRACT_OWNER.[TRACT_#]) AND (dbo.tblTRACT.STR = dbo.tblTRACT_OWNER.STR)

WHERE (((dbo.tblTRACT.STR)=@TRS))

)

GO

I understand now I can create a view of the function Simply by using the function name in my FROM statement. However I get an error that arguments provided do not match parameters required. However, I'm not getting the prompt to enter my criterion. Is my error in my function statement? I can't save the view. I also understand I could use a pass-through query. Is there some sort of guidance or tutorial on that to which you could point me?
Thanks for your time.

View Replies !

Resultset From Proc As Inline View?

Hi!
I have some stored procedures returning resultsets.

You can store this resultset in an table (table variable, permanent table or temp. table),
but this would require additional code, which has to be maintaned
when called code is changed (typical added columns in resultset).

I would like to avoid this, if it can be done with minimal overhead.

I looked at openrowset/openquery (I looked for something similar to the "THE"-operator), but couldn't find an solution.

Suggestions welcome.

Here some examples showing the idea:

CREATE PROC tmpA

AS

BEGIN

SELECT CAST(42 AS INT) b;

END;

-- 1. can't work

select b from (exec tmpA);

-- 2. cant't work

select *

into tmp

(exec tmpA);

-- 3. will work

declare @a table (a int);

insert into @a

exec tmpA;

select * from @a;

View Replies !

Inline-table-valued Functions
Help! Been doing the box step with BOL for several hours , Using tables in Adventureworks to create inline-table-valued function to provide a parameterized view of three JOINS - Have sucessfully created the function but can't figure out where to 'Declare' my variable "@SalesAgentID" need to be able to invoke the function with a particular ID - If you can help me cut this dance short I would REALLY Appreciate it.

View Replies !

Splitting Space Delimited String Inline
So we have a field called forenames, and it needs to be split into fields forename_1, forename_2, forename_3, forename_4 (don't ask).

Ok, I've come up with this so far, which works, but is pretty nacky in my opinion. Has any one got a better way of achieving this?

SELECT forenames
, Replace(forenames, ' ', '.')
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 1)) As [f1]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 2)) As [f2]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 3)) As [f3]
, Reverse(ParseName(Replace(Reverse(forenames), ' ', '.'), 4)) As [f4]
FROM ( SELECT 'John' As [forenames]
UNION SELECT 'John Paul'
UNION SELECT 'John Paul George'
UNION SELECT 'John Paul George Ringo'
) As [x]

Results

forenames (no column name) f1 f2 f3 f4
---------------------- ---------------------- ---- ---- ------ -----
John John John NULLNULL NULL
John Paul John.Paul John PaulNULL NULL
John Paul George John.Paul.George John PaulGeorgeNULL
John Paul George Ringo John.Paul.George.Ringo John PaulGeorgeRingo

View Replies !

XML Source:Inline Schema Option Not Working

Hello Folks...I have a problem that sounds simple but couldn't fix it.
I am trying to load data from an XML File to Sql Database for which I have added XML Source Component.
It has 2 options for providing XSD.
1. External Path of XSD File : This worked fine with hardcoded path. But I need to give relative path or set from a variable.
2. Inline Schema : I injected XSD Schema to input xml and tried this option. It is smoothly going green but not loading any data. I tried with sample Xml from MSDN as below. It is NEITHER throwing error NOR loading data.

<?xml version="1.0" encoding="UTF-8"?>
<root>
<xschema xmlns:xs='http://www.w3.org/2001/XMLSchema'
            xmlns='xsdHeadCount'
            targetNamespace='xsdHeadCount'>
    <xs:element name='HeadCount'>
        <xs:complexType>
            <xsequence>
                <xs:element name='Name' type='xstring' maxOccurs='unbounded'/>
            </xsequence>
            <xs:attribute name='division' type='xstring' use='optional' default='QA'/>
        </xs:complexType>
    </xs:element>
</xschema>
<hc:HeadCount xmlns:hc='xsdHeadCount'>
    <Name>Waldo Pepper</Name>
    <Name>Red Pepper</Name>
</hc:HeadCount>
</root>

Please kindly suggest. Thank you.

- Ravi Gopal

View Replies !

How To Create A Stored Procedure Of This Code (inline Sql In Aspx) ?
I have some SQL code as inline SQL (bad habit, I know). Now I want to convert this to an sproc, but I'm pretty much out of ideas here. The code looks like this:
string SQL = "SELECT * FROM MyDBTable WHERE 1=1";
if (txtMyField1.Text != "")
{
SQL = SQL + " AND MyField1 = @MyField";
}
if (txtMyField2.Text != "")
{
SQL = SQL + " AND MyField2 LIKE '%'+ @MyField2 + '%'";
}
if (txtMyField3.Text != "")
{
SQL = SQL + " AND MyField3 LIKE '%' + @MyField3 + '%'";
}
I have an search page built on ASP.NET 2.0. Based on what the user has entered to the form fields, the SQL in constructed on the fly. Since this is now inside codebehind file (aspx.cs), I want to get rid of it and move it to an sproc. But the question is how ? Some simple SQL clauses are easy to convert to an sproc but this is causing me lots of issues.

View Replies !

Inline Table-valued Function With Multi-value Parameter
Hello everybody,

I need to create a function which takes a multi-value parameter. When I select more than one item, I get the error that I have too many arguments. Does anybody have a solution?

Or can I create a view and then do a "SELECT * FROM viewName WHERE columnName IN (@param)"?

Thanks in advance for your answers.

View Replies !

Exception Error - Incorrect Syntax Near '('. Inline UPDATE Command
Hi,
Here's the code I've used to try and update a new user's IP Address to a Table called Customer who's key field in the UserId:
Getting the Exception Error "Incorrect Syntax near'('. " Any ideas?
protected void ContinueButton_Click(object sender, EventArgs e)
{
//Get the ip address and put it into the customer table - (the instance of this user now exists)

MembershipUser _membershipUser = Membership.GetUser(); //This gets the active user if there is someone logged in...
Guid UserId = (Guid)_membershipUser.ProviderUserKey; //This gets the userId for the currently logged in user
string IPAddress = Request.UserHostAddress.ToString();//This gets the IPAddress of the currently logged in user
string cs = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
using (System.Data.SqlClient.SqlConnection con =new System.Data.SqlClient.SqlConnection(cs))
{
con.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = con;
cmd.CommandType = System.Data.CommandType.Text;

cmd.CommandText = "UPDATE Customer SET(IP_Address = @IP_Address) WHERE (UserId = @UserId)";
cmd.Parameters.Add("@UserId", System.Data.SqlDbType.UniqueIdentifier).Value = UserId;
cmd.Parameters.Add("@IP_Address", System.Data.SqlDbType.Char, 15).Value = IPAddress;
cmd.ExecuteNonQuery();

con.Close();
}
Thanks.

View Replies !

How To Pull Data In The Inline Code Block Inside A Template?
The code block is embedded into a grid view item template. <ItemTemplate> <% if ((bool)Eval("isNew")) { %> New Post <% } %></ItemTemplate> but as per rule Eval and Bind can be only use bind the data. How would i pull the information from sqldatasource in the template like this?

View Replies !

Wild Search NText And NVarChar In Parameterized Inline Statement
I want to retrieve data from SQL containing non English character but fail, can anyone shed me some light?

What I use currently:

Dim strSQL As String
strSQL = "SELECT ArticleID, "
strSQL &= "ISNULL(Body, '') AS Body, "
strSQL &= "ISNULL(Subject, '') AS Subject "
strSQL &= "FROM Articles "
strSQL &= "WHERE (Subject LIKE N'%' + @Keyword + '%' OR [Body] LIKE N'%' + @Keyword + '%') "
Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As New SqlDataAdapter(strSQL, con)
cmd.SelectCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = keyword
...

I'm not so sure where should I place the letter "N", I use :

SELECT ArticleID,
ISNULL(Body, '') AS Body,
ISNULL(Subject, '') AS Subject,
FROM Articles
WHERE (Subject LIKE N'%SomeNonEnglishString%' OR [Body] LIKE N'%SomeNonEnglishString%')

in Query Analzyer, it works! But it failed in my program... oh my god...

Thanks a lot!

View Replies !

Can A Stored Procedure Called From An Inline Table-Valued Function
Hi,

I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks

View Replies !

Using OPTION Clause Within CREATE FUNCTION Statement For Inline Table Functions
Hi!

I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but it does not within CREATE FUNCTION statement - I'm getting error:

Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34

Incorrect syntax near the keyword 'option'.

Here is the function:

create FUNCTION [dbo].[ExpandedCTE]

(

@p_id int

)

RETURNS TABLE

AS

RETURN

(

with tbl_cte (id, tbl_id, lvl)

as

(

select

id, tbl_id, 0 lvl

from

tbl

where

id = @p_id

union all

select

t.id, t.tbl_id, lvl + 1
from

tbl_cte
inner join tbl t

on rnr.tbl_id = tbl_cte.id

)

select

id, tbl_id, lvl

from

tbl_cte

option (maxrecursion 0)

)

Please help!

Alexander.

P.S.
I'm really sorry if it is about syntax, but I could not find it in the documentation.

View Replies !

Stored Procedure Using A Declared Variable In Insert Query (inline Or Using EXEC)
Hello,

I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:

I want to be able to do this (with or without the EXEC) :
------------------------------------------------------------------------------------

DECLARE @NewTableNameOut as varchar(100)

Set @NewTableNameOut = 'TableToInsertInto'

EXEC(
                Insert Into @NewTableNameOut
                Select * From tableToSelectFrom
            )

------------------------------------------------------------------------------------

I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.

I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.

------------------------------------------------------------------------------------

DECLARE @NewTableNameOut as varchar(100)

Set @NewTableNameOut = 'TableToInsertInto'

EXEC(
                'Insert Into ' + @NewTableNameOut + ' ' +
                'Select * From tableToSelectFrom'
            )

------------------------------------------------------------------------------------

It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.

Is this possible at all?

If you need more info please let me know.

View Replies !

SSIS Undouble - Inline Double Quote And Column Delimeter Problem
We have problems with SSIS flat file adapter.

We try to import file which contains next sort of data:

"1";"ABC";5
"2";"A""BC""";5
"3";"A""B;C""";5

The resulting table must be:
f1 f2 f3
1 ABC 5
2 A"BC" 5
3 A"B;C" 5

How can we get this result?

p.s.
We tried to use "SQL Server SSIS Sample Component: UnDouble" but the result was unsuccesfull.

View Replies !

Array
I messed up my question so badly a few posts ago, that I'm going to lay out the problem carefully as follows:

On a spreadsheet:

--there are a few names down column A, one name per row (e.g., r3c1Mary, r4c1Scott, r5c1Jane, r6c1Ann, r7c1Cathy, r8c1Jim)

--there are a bunch of columnar animals heading up row 1 (e.g., r1c2cat, r1c4dog, r1c6horse, r1c8bird, r1c10snake, r1c12elephant, r1c14goat, r1c16giraffe, r1c18ox, r1c20rat, r1c22monkey, r1c24pig)

--(might be addressed in a later post to this or another thread): on row 2, there is a Budget field and a Cost field for each animal (e.g., r2c2Bud, r2c3Cost, r2c4Bud, r2c5Cost, r2c6Bud, r2c7Cost, r2c8Bud, r2c9Cost, r2c10Bud, r2c11Cost, r2c12Bud, r2c13Cost, r2c14Bud, r2c15Cost, r2c16Bud, r2c17Cost, r2c18Bud, r2c19Cost, r2c20Bud, r2c21Cost, r2c22Bud, r2c23Cost, r2c24Bud, r2c25Cost)

But for now, just the people names down the left side, and the animals across the top would suffice to pose my question.

The question is this: the number of animal types gets added to, or subtracted from, across the top of the page every month. On July, there might be 12 animal kinds listed; but on August, there might be 19--or there might be 11. (The number of people involved change as well.)

As a user, I don't know how many and of what kind of animals (aka fields) will be on the page on any given month. The underlying query to the table doesn't know whether a Giraffe is going to be a category, among others at the top of the sheet, from one month to the next. If a garden variety query was written, it would hard code: SELECT r1c2cat, r1c4dog, r1c6horse, r1c8bird, r1c10snake, r1c12elephant, r1c14goat, r1c16giraffe, r1c18ox, r1c20rat, r1c22monkey, r1c24pig.

But that rots. The query would have to be re-written for every new list of applicable animals each month. Or even more ugly, I'd have a predetermined number of variables to hold each type of animal; some would go unused while a number exceeding the variables available wouldn't make it into the query, or whatever.

I could on the other hand slowly loop through a SELECT sAnimalType for each sPerson. Slowly.

That's where my question comes in: Can a query be written that allocates a SELECT Array(sAnimalType)?

(And if so, I hope I can extract the sequential info accordingly.)

Simplified, I currently have an Access table with two columns (four if we go whole hog): Person, Animal (, Budget, Cost). (But I'm perfectly happy to leave Budget and Cost for a separate posting. Maybe that's a sub query or something.)

Thanks for any assistance that you can provide.

View Replies !

Array
how can i send array from asp.net to sql?

View Replies !

Parameterized Or Array With This SQL?
I have two CheckBoxList controls. One CheckBoxList is a group of area codes as they apply to our customers, and the second CheckBoxList is a group of categories of those customers. The code below works fine with either CheckBoxList as a standalone (this code applies to the Area Codes selection), but what I need is the VB code to combine the choices a user makes in both CheckBoxLists. Is this where parameterized SQL comes into play? Or can I/should I use an array statement to combine both CheckBoxList choices? Sometimes a user will select nothing in one CBL and a few choices in the other, or vice versa, or a handful of choices in both CBLs, so that they might want only customers in, say two area codes and then only the selected categories of those area codes. Need help on this one, thanks...Protected Sub btn_CustomerSearchCombine_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombine.Click Dim ACSelections As Boolean = False Dim ACItem As ListItem For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSelections = True End If Next If ACSelections = True Then Dim ACSqlString As String ACSqlString = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Categories.Category FROM Customers INNER JOIN Categories ON Customers.CategoryID = Categories.CategoryID WHERE " For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR " End If Next ACSqlString = Left(ACSqlString, Len(ACSqlString) - 4) ACSqlString &= "ORDER By Customers.CustomerName" sql_CustomerSearchGrid.SelectCommand = ACSqlString End IfEnd Sub

View Replies !

DataRow Array
Hi,
i m pretty new to this forum and c#.net
i m doin a project in c#.net
I have four values in my datarow array
for example
DataRow[] cmb;
cmb=dsResult.Tables[0].Select("Controls Like 'cmb%'");// Here i m getting four Rows

for(i=0;i<cmb.Length;i++)
{
cmb[i]=Session["cmb'+i].ToString().Trim()//Here i m getting error;Cannot implicitly convert type 'string' to 'System.Data.DataRow'
}

How to assign my session values to them.
I want to assign my value stored in the session variable to that array.Is there any way i can do it.Can i convert datarow array to string array! Please can any one help
me.

View Replies !

SqlCommand Array Help
I want to do something like the following but I get an error: Object reference not set to an instance of an object. SqlConnection sqlConnection = new SqlConnection("server=xxxxx"); SqlCommand [] cmd = new SqlCommand[3]; Object returnValue; cmd[0].CommandText = "DO QUERY"; cmd[1].CommandText = "DO QUERY"; cmd[2].CommandText = "DO QUERY"; cmd[3].CommandText = "DO QUERY"; } sqlConnection.Open();int i = 0;while(i<4){ cmd[i].CommandType = CommandType.Text; cmd[i].Connection = sqlConnection; cmd[i].ExecuteNonQuery(); returnValue[i] = cmd[i].ExecuteScalar();i++} sqlConnection.Close();How should I do the followingThanks

View Replies !

Array In WHERE Clause
Hello, how can I use an array in a WHERE clause? If I had an array saystring[] NamesArray = new string[] {"Tom", "***", "Harry"} How would I do this:string myquery = "SELET name, city, country FROM myTable WHERE name in NamesArray" Thanks in advance,Louis

View Replies !

Array Problem
Hi All,
I have this code below:Dim a As Integer = 0 While a <= myArray.Length() Conn.Open() Dim UpdateCmd As New SqlClient.SqlCommand("Update email_addr SET category = 'Deleted' where HP = @hp", Conn) UpdateCmd.Parameters.Add("@hp", SqlDbType.VarChar, 50).Value = myArray(a) UpdateCmd.ExecuteNonQuery() a = a + 1 Conn.Close() End While
BUt, When I run it, I receive error "Prepared statement '(@hp varchar(50))Update email_addr SET category = 'Deleted' wher' expects parameter @hp, which was not supplied. "
DOes anyone have the solution???
Thanks....

View Replies !

SQL Query In A VB.net Array
I want to do a query on an SQL Server 2005 db and have the results returned into aarray or collection in vb.net... how do I do this? I know the basicconnection and stuff.. just not how to get the result to an array thanks!

View Replies !

Get Array Value From A Loop?
Hi, I am trying to do a loop while a list of array is assigned ('CHP,CNH,COW') ... I am using comma seperator to get each list value ... but, it donest really do what I am trying to do ... pls help!!! How do I loop through each value and do the rest ...??
=====================================
DECLARE @ABBR AS NVARCHAR(50)SET @ABBR = 'CHP,CNH,COW'
DECLARE @SEP AS NVARCHAR(5)SET @SEP = ','
WHILE patindex('%,' + @ABBR + ',%', @ABBR ) > 0 BEGIN
-- do the rest
END

View Replies !

Result Set Into Array
Hi All,

How can I read a query result set (which are of type VARCHAR) into an array? i.e the result set comprises of just one column and 5 rows and i want to save these into an array to easily extract each row whenever i want to. Hope I have conveyed my idea clearly.

Gayathri

View Replies !

How To Identify An Array
I have seen several examples explaining the fact that a tablecontaining a field for each day of the week is for the most part anarray. An specific example is where data representing worked hours isstored in a table.CREATE TABLE [hoursWorked] ([id] [int] NOT NULL ,[location_id] [tinyint] NOT NULL,[sunday] [int] NULL ,[monday] [int] NULL ,[tuesday] [int] NULL ,[wednesday] [int] NULL ,[thursday] [int] NULL ,[friday] [int] NULL ,[saturday] [int] NULL)I had to work with a table with a similar structure about 7 years agoand I remember that writing code against the table was pretty close toHell on earth.I am now looking at a table that is similar in nature - but different.CREATE TABLE [blah] ([concat_1_id] [int] NOT NULL ,[concat_2_id] [int] NOT NULL ,[code_1] [varchar] (30) NOT NULL ,[code_2] [varchar] (20) NULL ,[code_3] [varchar] (20) NULL ,[some_flg] [char] (1) NOT NULL) ON [PRIMARY]The value for code_2 and code_3 will be dependently null and they willrepresent similar data in both records (i.e. the value "abc" can existin both fields) . For example if code_2 contains data then code_3 willprobably not contain data.I do not think that this is an array. But with so many rows wherecode_2 and code_3 will be NULL something just does not feel right.I will appreciate your input.

View Replies !