Max Query: example

Showing posts with label example. Show all posts

Wednesday, March 28, 2012

Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
Chuck
Concatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:

> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>
|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck

Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
ChuckConcatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:

> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Division
s"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Division
s"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck

Help With SQL Command

I have the following Table
Table1
ID = Int
Division = VarChar
FirstName = VarChar
LastName = VarChar
This is an example of the Data (Select * From Table1)
ID Division FirstName LastName
1 E Chuck Martin
2 E Frank Smith
3 F Chuck Martin
4 G Chuck Martin
5 A Mark James
6 E Mark James
I would like the query to return the following (Example)
First + Last Divisions
Chuck Martin EFG
Frank Smith E
Mark James AE
I can do the First and Last Column, but don't know how to do the "Divisions"
Column.
Any assistance will be greatly appreciated.
Unfortunately, I cannot change the table.
ChuckConcatenating row values in T-SQL
http://www.projectdmx.com/tsql/rowconcatenate.aspx
AMB
"Charles A. Lackman" wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck
>
>|||Here's how I did it, using a UDF to concatenate the strings.
CREATE TABLE [dbo].[Table1](
[ID] [smallint] IDENTITY(1,1) NOT NULL,
[Division] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
go
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Frank','Smith')
INSERT INTO Table1 (Division, FirstName, LastName) values
('F','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('G','Chuck','Martin')
INSERT INTO Table1 (Division, FirstName, LastName) values
('A','Mark','James')
INSERT INTO Table1 (Division, FirstName, LastName) values
('E','Mark','James')
go
CREATE FUNCTION [dbo].[ConcatDiv](@.fn varchar(50), @.ln varchar(50))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Output VARCHAR(8000)
SET @.Output = ''
SELECT
@.Output = @.Output + division
FROM
table1
WHERE
firstname = @.fn and lastname = @.ln
ORDER BY
division
RETURN @.Output
END
go
-- query to select the output
SELECT DISTINCT
firstname as [First Name],
lastname as [Last Name],
dbo.ConcatDiv(firstname, lastname) as Division
FROM
table1
go
On Apr 30, 4:17 pm, "Charles A. Lackman"
<Char...@.CreateItSoftware.net> wrote:
> I have the following Table
> Table1
> ID = Int
> Division = VarChar
> FirstName = VarChar
> LastName = VarChar
> This is an example of the Data (Select * From Table1)
> ID Division FirstName LastName
> 1 E Chuck Martin
> 2 E Frank Smith
> 3 F Chuck Martin
> 4 G Chuck Martin
> 5 A Mark James
> 6 E Mark James
> I would like the query to return the following (Example)
> First + Last Divisions
> Chuck Martin EFG
> Frank Smith E
> Mark James AE
> I can do the First and Last Column, but don't know how to do the "Divisions"
> Column.
> Any assistance will be greatly appreciated.
> Unfortunately, I cannot change the table.
> Chuck

Help with Sproc and multi parameter

I'm trying to build a sproc that will return rows even if some of the parameters are blank. For example; if a user does not enter a priority, a status, or a caller the sproce should still return rows based on the other parameters.

Can anyone help me find a way to modify my sproc bellow to allow this? I think the way I have it bellow will only return those rows where the user has entered a parameter or the record has a null in the field.

ALTER PROCEDURE dbo.ContactManagementAction(@.ClientIDint,@.Priorityint,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar,@.ConTypeIDint,@.Callernvarchar,@.Keywordnvarchar)ASSELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROM tblTasksWHERE (ClientID = @.ClientID)AND (Priority = @.Priority)OR (PriorityISNULL)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.Status)OR (StatusISNULL)AND (ContactTypeID = @.ConTypeID)OR (ContactTypeIDISNULL)AND (Caller = @.Caller)OR (CallerISNULL)AND (SubjectLIKE @.Keyword)OR (SubjectISNULL)RETURN

You have the query correct. Your OR's and AND's are misplaced around the brackets.

ALTER PROCEDURE dbo.ContactManagementAction(@.ClientIDint,@.Priorityint,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar,@.ConTypeIDint,@.Callernvarchar,@.Keywordnvarchar)ASBEGINSET NOCOUNT ONSELECTTask_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROMtblTasksWHERE(ClientID = @.ClientID)AND (Priority = @.PriorityOR @.PriorityISNULL)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.StatusOR @.StatusISNULL)AND (ContactTypeID = @.ConTypeIDOR @.ContactTypeIDISNULL)AND (Caller = @.CallerOR @.CallerISNULL)AND (SubjectLIKE @.KeywordOR @.SubjectISNULL)SET NOCOUNT OFFEND

|||

I tried it and I'm still not getting any rows returned. I have even tried it with all parameters having a good entry.

Just to be sure;

I should be able to enter a clientID, an ActionDate range, a priority, and the other fields of the table could have any entry or null for the other parameters and get returned, YES?

|||I did not notice it but try setting the length for your parameters.|||

Besize the size, could the default values be the reason:

Like:

...

@.Statusnvarchar(50)=NULL,
@.ConTypeIDint=NULL,
@.Callernvarchar(50)=NULL,
@.Keywordnvarchar(50)=NULL

|||I set the size and still I must have an entry in every parameter. Am I missing something? I should be able to do this, right?|||

I really am stuck on this one. Can anyone offer any suggestions? Does anyone understand my problem with this?

|||

You could add default value to parameter as limno suggested.

If that doesn't work. You could try adding following code to your stored procedure and test in Sql Server Management Studio to trace each parameters.:

if (@.PriorityISNULL)begin print N'Priority IS NULL'endelsebegin print @.Priorityend--print other parameters in similar wayHope this helps.

|||

So here is what I have, listed below: I should be able to pass in a clientID and a valid date range and it should not matter what is in the other fields because I'm passing in NULL, right?

ALTER PROCEDURE dbo.eP_BindContactManagementAction(@.ClientIDint,@.Prioritysmallint=NULL,@.TStartdatetime,@.TEnddatetime,@.Statusnvarchar(50)=NULL,@.ConTypeIDint=NULL,@.Callernvarchar(50)=NULL,@.Keywordnvarchar(50)=NULL)ASSELECT Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument, ReminderDate, Reminder, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDateFROM tblTasksWHERE (ClientID = @.ClientID)AND (Priority = @.Priority)AND (ActionDateBETWEEN @.TStartAND @.TEnd)AND (Status = @.Status)AND (ContactTypeID = @.ConTypeID)AND (Caller = @.Caller)AND (SubjectLIKE @.Keyword)RETURN

I appreciate any help,|||

You WHERE clause has problem to handle NULL values: Please try this one fromndinakar

SELECTTask_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument,
ReminderDate, Reminder, ReminderTime, Sol_ID,
DateEntered, EnteredBy, Caller, ContactTypeID, DueDate
FROMtblTasks
WHERE(ClientID = @.ClientID)
AND (Priority = @.PriorityOR @.PriorityISNULL)
AND (ActionDateBETWEEN @.TStartAND @.TEnd)
AND (Status = @.StatusOR @.StatusISNULL)
AND (ContactTypeID = @.ConTypeIDOR @.ContactTypeIDISNULL)
AND (Caller = @.CallerOR @.CallerISNULL)
AND (SubjectLIKE @.KeywordOR @.SubjectISNULL)

|||

Another question: could you show us your datasource control code too? if you are using SQLDatasource control, you may need to set this CancelSelectOnNullParameter="false".

Just another shot in dark.

|||

Here is the code, it will return a record if I make an entry for all parameters. But if I leave one blank no records are returned.

Sub BindData() Session("TaskStart") =Me.BasicDatePickerHStart.SelectedValue Session("TaskEnd") =Me.BasicDatePickerHEnd.SelectedValueDim TStartAs String = Session("TaskStart")Dim TEndAs String = Session("TaskEnd")Dim ConnectStrAs String = _ ConfigurationManager.ConnectionStrings("SQL2ConnectionString").ConnectionString'If user unchecks use due date sproc.Dim strSprocAs String strSproc ="BindContactManagementAction" MyConnection =New SqlConnection(ConnectStr) MyCommand =New SqlCommand(strSproc, MyConnection) MyCommand.CommandType = CommandType.StoredProcedureDim ClientIDParamAs New SqlParameter("@.ClientID", SqlDbType.Int, 4) MyCommand.Parameters.Add(ClientIDParam) ClientIDParam.Value = Session("lgClientID")Dim TaskStartParamAs New SqlParameter("@.TStart",Me.BasicDatePickerHStart.SelectedValue) MyCommand.Parameters.Add(TaskStartParam) TaskStartParam.Value =Me.BasicDatePickerHStart.SelectedValueDim TaskEndParamAs New SqlParameter("@.TEnd",Me.BasicDatePickerHEnd.SelectedValue) MyCommand.Parameters.Add(TaskEndParam) TaskEndParam.Value =Me.BasicDatePickerHEnd.SelectedValueIf String.IsNullOrEmpty(Me.KeyWordText.Text)Then Dim KeywordParamAs New SqlParameter("@.Keyword", DBNull.Value) MyCommand.Parameters.Add(KeywordParam) KeywordParam.Value =Me.KeyWordText.Text MsgBox(KeywordParam.Value)Else Dim KeywordParamAs New SqlParameter("@.Keyword",Me.KeyWordText.Text) MyCommand.Parameters.Add(KeywordParam) KeywordParam.Value =Me.KeyWordText.Text MsgBox(KeywordParam.Value)End If If String.IsNullOrEmpty(Me.StatusSearchDrop.SelectedItem.Text)Then Dim StatusParamAs New SqlParameter("@.Status", DBNull.Value) MyCommand.Parameters.Add(StatusParam) StatusParam.Value =Me.StatusSearchDrop.SelectedItem.Text MsgBox(StatusParam.Value)Else Dim StatusParamAs New SqlParameter("@.Status",Me.StatusSearchDrop.SelectedItem.Text) MyCommand.Parameters.Add(StatusParam) StatusParam.Value =Me.StatusSearchDrop.SelectedItem.Text MsgBox(StatusParam.Value)End If If String.IsNullOrEmpty(Me.PrioritySearchDrop.SelectedItem.Text)Then Dim PriorityParamAs New SqlParameter("@.Priority", DBNull.Value) MyCommand.Parameters.Add(PriorityParam) PriorityParam.Value =Me.PrioritySearchDrop.SelectedItem.Text MsgBox(PriorityParam.Value)Else Dim PriorityParamAs New SqlParameter("@.Priority",Me.PrioritySearchDrop.SelectedItem.Text) MyCommand.Parameters.Add(PriorityParam) PriorityParam.Value =Me.PrioritySearchDrop.SelectedItem.Text MsgBox(PriorityParam.Value)End If If String.IsNullOrEmpty(Me.CallerTextSearch.Text)Then Dim CallerParamAs New SqlParameter("@.Caller", DBNull.Value) MyCommand.Parameters.Add(CallerParam) CallerParam.Value =Me.CallerTextSearch.Text MsgBox(CallerParam.Value)Else Dim CallerParamAs New SqlParameter("@.Caller",Me.CallerTextSearch.Text) MyCommand.Parameters.Add(CallerParam) CallerParam.Value =Me.CallerTextSearch.Text MsgBox(CallerParam.Value)End If If String.IsNullOrEmpty(Me.ContactTypeSearchDrop.SelectedValue)Then Dim ConTypeIDParamAs New SqlParameter("@.ConTypeID", DBNull.Value) MyCommand.Parameters.Add(ConTypeIDParam) ConTypeIDParam.Value =Me.ContactTypeSearchDrop.SelectedValue MsgBox(ConTypeIDParam.Value)Else Dim ConTypeIDParamAs New SqlParameter("@.ConTypeID",Me.ContactTypeSearchDrop.SelectedValue) MyCommand.Parameters.Add(ConTypeIDParam) ConTypeIDParam.Value =Me.ContactTypeSearchDrop.SelectedValue MsgBox(ConTypeIDParam.Value)End If MyConnection.Open()Dim DSAs SqlDataReader = MyCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) MyEditDataGrid.DataSource = DS MyEditDataGrid.DataBind()End Sub

|||

We have to find where the problem is:

1. Whether the Stored Procedure is running OK or not in your database?

2. If the sp can return records with limited parameters, we can focus on the code part.

PS:(this is a version I tested on my db which works)( By the way, I would use field name to Name parameters to avoid confusion, but this is not the problem here)

ALTER

PROCEDURE [dbo].[eP_BindContactManagementAction]

(

@.ClientID

int,

@.Priority

smallint=NULL,

@.TStart

datetime,

@.TEnd

datetime,

@.Status

nvarchar(50)=NULL,

@.ConTypeID

int=NULL,

@.Caller

nvarchar(50)=NULL,

@.Keyword

nvarchar(50)=NULL)

SELECT

Task_ID, ClientID, Priority, ActionDate, Subject, Note, Status, CompletionDate, TaskDocument,ReminderDate,

Reminder

, ReminderTime, Sol_ID, DateEntered, EnteredBy, Caller, ContactTypeID, DueDate

FROM

tblTasks

WHERE

(ClientID= @.ClientID)

AND

(Priority= @.PriorityOR @.PriorityISNULL)

AND

(ActionDateBETWEEN @.TStartAND @.TEnd)

AND

(Status= @.StatusOR @.StatusISNULL)

AND

(ContactTypeID= @.ConTypeIDOR @.ConTypeIDISNULL)

AND

(Caller= @.CallerOR @.CallerISNULL)

AND

(SubjectLIKE @.KeywordOR @.KeywordISNULL)

END

|||

Ok, progress the sp is working in the database, I tested it in VS 2005 and it worked, so it must be in my code.

Any thoughts from my earlier post?

|||

I changed all my parameters to the code type below and it is working now:

I very much appreciate your help!

If String.IsNullOrEmpty(Me.KeyWordText.Text)Then MyCommand.Parameters.AddWithValue("@.Keyword", DBNull.Value)Else MyCommand.Parameters.AddWithValue("@.Keyword",Me.KeyWordText.Text)End If If String.IsNullOrEmpty(Me.StatusSearchDrop.SelectedItem.Text)Then MyCommand.Parameters.AddWithValue("@.Status", DBNull.Value)Else MyCommand.Parameters.AddWithValue("@.Status",Me.StatusSearchDrop.SelectedItem.Text)End If

sql

Monday, March 26, 2012

Help with setting Algorithm Paramteres

I was walking through the Text Mining example - which at one step required me to set Algorithm Parameters - MAXIMUM_OUTPUT_ATTRIBUTES=0. When I tried that the project would not build giving an error -
Error (Data mining): The 'MAXIMUM_INPUT_ATTRIBUTES' data mining parameter is not valid for the 'XYZ' model.

I was getting the same error when I tried to set it for Microsoft_neural_netowrk - Hidden_Node_ratio. When I do a properties from "set Algorithm Properties" from Mining Model, I do not see these properties set as default.

I have installed SQLServer 2005 Standard Edition Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00

Any help would be much appreciated.

Thanks
Rajeev Gupta

These parameters are only available in the Enterprise Edition.|||

Do you plan to add these features in future versions of standard edition.

Neural Network modeling suffers greatly as we cannot add more layers/neurons to the model. Are there any hacks...

Thanks
Rajeev Gupta

|||Then are we to assume the Text Mining tutorials are only useful for the Enterprise Edition?

Help with setting Algorithm Paramteres

I have installed SQLServer 2005 Standard Edition Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00

Any help would be much appreciated.

Thanks
Rajeev Gupta

These parameters are only available in the Enterprise Edition.|||

Do you plan to add these features in future versions of standard edition.

Neural Network modeling suffers greatly as we cannot add more layers/neurons to the model. Are there any hacks...

Thanks
Rajeev Gupta

|||Then are we to assume the Text Mining tutorials are only useful for the Enterprise Edition?

Friday, March 23, 2012

Help with sample code for ssis surrogate key transform

I am trying to write a ssis surrogate key data transform, my problem is I can't find an example how to add a column to the incoming columns and add some data to it. If anyone has a sample, can you please post it. I found a script option that works but I would like an actual transform.

Thanks

Basically - here is a surrogate key Transform Script - to generate image numbers for products
Input is ProdNum column - output ImgNo colum.
The idea is to get the result like this:
ProdNum ImgNo
1 1
1 2
2 1
3 1
3 2

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Dim imgno As Short, incr As Short, prevProdNum As String

Public Sub New()
imgno = 0
incr = 1
prevProdNum = ""
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.ProdNum <> prevProdNum Then
imgno = incr
Else
imgno += incr
End If
Row.ImgNo = imgno
prevProdNum = Row.ProdNum
End Sub
End Class|||Also - you can check out this article "SSIS Generating Surrogate Keys"

Wednesday, March 21, 2012

help with report

Hi everybody!

Need to create report according to example below

User will input start & end date

From Date:______ ToDate:________

Then the report should create output like this:

CustomerID: Number of orders:

John 5

Jim 12

Aileen 21

I tried to accomplish that with Northwind / Orders, had no success.

Thank you in advance.

Alex

Is your question about how to write the SELECT statement to retrive the data, or how to use Reporting Services to create a formatted report?

It would help to tell us what you've tried; that might give us a clue why you've not been successful.

Don|||

Hi Don,

my question is about how to write the SELECT statement to retrive the data

Thanks for the reply

|||You're going to keep what you've tried a close secret, eh? Heh.

This does what you want, except that it doesn't include the join to get the customer name:

SELECT [CustomerID]
 ,COUNT(*)AS [Numberof orders]
FROM [Orders]
WHERE OrderDateBETWEEN'6/1/1997'AND'6/30/1997'GROUP BY CustomerID

Don|||

Thanks again Don,

I did tried to use that Select string, I have no errors but also no results.

I am using datagrid to display the result of the query.

This is the code that I use as ASP.NET, please take a pick.

I must to admit that I'm very green in SQL area.

<%@. Page Language="VB" %><%@. import Namespace="System.Data.SqlClient" %><%@. import Namespace="System.Data.SqlTypes" %><script runat="server"> shared public dim startDate as date shared public dim endDate as date sub load_page() startDate = "6/1/1997" endDate = "6/30/1997" dgOrders.DataSource = orders(startDate, endDate ) dgOrders.DataBind() end sub Function orders(ByVal fromDate As Date, ByVal toDate As Date) As System.Data.IDataReader Dim connectionString As String = "server='(local)'; trusted_connection=true; database='Northwind'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim orderNumb as integer Dim queryString As String queryString = "SELECT [Orders].[CustomerID] ,COUNT(*) AS [orderNumb] FROM [Orders] WHERE OrderDate BETWEEN startDate AND endDate GROUP BY CustomerID" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dbParam_fromDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_fromDate.ParameterName = "@.fromDate" dbParam_fromDate.Value = fromDate dbParam_fromDate.DbType = System.Data.DbType.DateTime dbCommand.Parameters.Add(dbParam_fromDate) Dim dbParam_toDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_toDate.ParameterName = "@.toDate" dbParam_toDate.Value = toDate dbParam_toDate.DbType = System.Data.DbType.DateTime dbCommand.Parameters.Add(dbParam_toDate) dbConnection.Open Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) Return dataReader End Function</script><html><head></head><body> <form runat="server"> <p> </p> <p> <asp:DataGrid id="dgOrders" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundColumn DataField="orderNumb" HeaderText="CustomerID"></asp:BoundColumn> <asp:BoundColumn DataField="Orders" HeaderText="Number of Orders"></asp:BoundColumn> </Columns> </asp:DataGrid> </p> </form></body></html>

|||

try this:

BETWEEN @.startDate AND @.endDate

|||Actually, it would be

BETWEEN @.fromDateAND @.toDate

based on what are apparently the parameter names.

Don|||

Thank you guys for helping me here,

i tried that:BETWEEN @.fromDateAND @.toDate

still get empti web page, no errors though.

I probably missing somthing here, but i don't where to look cause there is no compilation error of any kind.

Thanks again

|||

Can you try this:

<asp:DataGrid id="dgOrders" runat="server" AutoGenerateColumns="True"> </asp:DataGrid>

You don't have the orders column in your data source.

|||

Thank Limno

did tried that - get the same result - blank web page.

may be using the datagrid isn't such a good idea for this case?

just don't know how to display the query result in any other way.

Alex

|||

The datareader for the datagrid looks fine to me. Can you confirm that you can run the sql query in your database with the results you are expecting? (hard code your dates in the query).

If you can get something from your database, we will be close to get something in the code.

|||

Hi Limno,

I did try this Select statement in SQL Query Analiser:

SELECT [CustomerID]
     ,COUNT(*)AS [Numberof orders]
 FROM [Orders]
WHERE OrderDateBETWEEN'6/1/1997'AND'6/30/1997'

GROUP BY CustomerID

and did get the following result:

ANTON 1
AROUT 1
BERGS 1
BLAUS 1
BLONP 3
BSBEV 1
ERNSH 1
FAMIA 1
FOLKO 1
...

I went through my code again and again but dont find the problem.

Please advice

Alex

|||

Here you go:

<%@. Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Public Shared startDate As Date
Public Shared endDate As Date

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

startDate = "6/1/1997"
endDate = "6/30/1997"
dgOrders.DataSource = orders(startDate, endDate)
dgOrders.DataBind()
End Sub

Function orders(ByVal fromDate As Date, ByVal toDate As Date) As System.Data.IDataReader
Dim connectionString As String = System.Configuration.ConfigurationManager.ConnectionStrings("Personal").ConnectionString
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String
queryString = "SELECT [Orders].[CustomerID] ,COUNT(*) AS [orderNumb] FROM [Orders] WHERE OrderDate BETWEEN @.startDate AND @.endDate GROUP BY CustomerID"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_fromDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_fromDate.ParameterName = "@.startDate"
dbParam_fromDate.Value = fromDate
dbParam_fromDate.DbType = System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_fromDate)

Dim dbParam_toDate As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_toDate.ParameterName = "@.endDate"
dbParam_toDate.Value = toDate
dbParam_toDate.DbType = System.Data.DbType.DateTime
dbCommand.Parameters.Add(dbParam_toDate)

dbConnection.Open()
Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Return dataReader
End Function

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid id="dgOrders" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundColumn DataField="CustomerID" HeaderText="CustomerID"></asp:BoundColumn>
<asp:BoundColumn DataField="orderNumb" HeaderText="Number of Orders"></asp:BoundColumn>
</Columns>
</asp:DataGrid>

</div>
</form>
</body>
</html>

|||

You are great Limno!

That works right from the beginning.

Didn't had the chance to examine the code yet, I'm curies how did you find the problem?

Oh…, is this two lines Must have in my code?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

Thank a lotLimno

Alex

|||

Hi Alex:

Actually, your page load event syntax is not right. It would save you a lot of headaches to use the IDE's intellisence. Pick the Page from left dropdown list , then find the event you want from right dropdown list.

Not the two lines you think. you can test them out.

Help with reformatting xml

This is a simplistic example of what I’m trying to achieve;
I have some XML stored in a table (in a column of type xml):
<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>
And I want to generate new xml as follows:
<report>
<somevalue>13</somevalue>
</report>
Where 13 is the total value of all somevalue attributes.
I’ve been looking at the query() and nodes() methods and cannot quite do
what I want to do although I feel it might be possible. Currently I’m simply
selecting the xml and letting my client application do the formatting,
however I feel that this might cause some performance issues on large blobs
of xml data.
Is there a better way to do this?
Many thanks in advance for any help with this.
Julia Beresford.
Something like this?
DECLARE @.doc XML;
SELECT @.doc = N'<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>';
SELECT @.doc.query('<report>
<somevalue>
{ fn:sum(/root/node/@.somevalue) }
</somevalue>
</report>');
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:5A5E83A9-F098-454D-80FA-FA212A3B15E8@.microsoft.com...
> This is a simplistic example of what I'm trying to achieve;
> I have some XML stored in a table (in a column of type xml):
> <root>
> <node id="1" somevalue="0" />
> <node id="2" somevalue="1" />
> <node id="3" somevalue="5" />
> <node id="4" somevalue="7" />
> </root>
> And I want to generate new xml as follows:
> <report>
> <somevalue>13</somevalue>
> </report>
> Where 13 is the total value of all somevalue attributes.
> I've been looking at the query() and nodes() methods and cannot quite do
> what I want to do although I feel it might be possible. Currently I'm
> simply
> selecting the xml and letting my client application do the formatting,
> however I feel that this might cause some performance issues on large
> blobs
> of xml data.
> Is there a better way to do this?
> Many thanks in advance for any help with this.
> Julia Beresford.
>

Help with reformatting xml

This is a simplistic example of what I’m trying to achieve;
I have some XML stored in a table (in a column of type xml):
<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>
And I want to generate new xml as follows:
<report>
<somevalue>13</somevalue>
</report>
Where 13 is the total value of all somevalue attributes.
I’ve been looking at the query() and nodes() methods and cannot quite do
what I want to do although I feel it might be possible. Currently I’m sim
ply
selecting the xml and letting my client application do the formatting,
however I feel that this might cause some performance issues on large blobs
of xml data.
Is there a better way to do this?
Many thanks in advance for any help with this.
Julia Beresford.Something like this?
DECLARE @.doc XML;
SELECT @.doc = N'<root>
<node id="1" somevalue="0" />
<node id="2" somevalue="1" />
<node id="3" somevalue="5" />
<node id="4" somevalue="7" />
</root>';
SELECT @.doc.query('<report>
<somevalue>
{ fn:sum(/root/node/@.somevalue) }
</somevalue>
</report>');
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:5A5E83A9-F098-454D-80FA-FA212A3B15E8@.microsoft.com...
> This is a simplistic example of what I'm trying to achieve;
> I have some XML stored in a table (in a column of type xml):
> <root>
> <node id="1" somevalue="0" />
> <node id="2" somevalue="1" />
> <node id="3" somevalue="5" />
> <node id="4" somevalue="7" />
> </root>
> And I want to generate new xml as follows:
> <report>
> <somevalue>13</somevalue>
> </report>
> Where 13 is the total value of all somevalue attributes.
> I've been looking at the query() and nodes() methods and cannot quite do
> what I want to do although I feel it might be possible. Currently I'm
> simply
> selecting the xml and letting my client application do the formatting,
> however I feel that this might cause some performance issues on large
> blobs
> of xml data.
> Is there a better way to do this?
> Many thanks in advance for any help with this.
> Julia Beresford.
>

Help with reading database.

I have this code that I hacked together from someone else's example. I kind of understand how it works. I just don't know if it will and i am not in a location right now to check. I was wondering if I did this correctly first, second how can it improve and should i do something different. Basically i just want to check the password in a database. I am passing the username and password to this function from another functio

private bool authUser(string UserName, string Password)
{
string connectionString = ConfigurationSettings.AppSettings["DBConnectionString"];

SqlConnection DBConnection = new SqlConnection(connectionString);

bool result = false;

DBConnection.open()
SqlCommand checkCommand = new SqlCommand("SELECT password FROM Users WHERE userName='" + Password + "', DBConnection)
SqlDataReader checkDataReader = checkCommand.ExecuteReader();

if(checkDataReader.GetString(0) == Password)
{
result = true;
}
else
{
result = false;
}
checkDataReader.Close();
DBConnection.Close();

return result;
}

Thank you
Buddy LindseyNo. It won't work.
You're using the Password in your SQL statement, not the username. So unless the users are really silly and use their usernames as their passwords, it's going to fail.
More seriously, using string concatenation to build query strings represents one of the biggest security disasters on the planet. Never, ever do it. Just search the web for "SQL Injection attack" to see what I mean.
You should also ponder whether == with string comparisons is case sensitive or not.
You can also leak connection resources because you're not using try {} finally {} blocks or the C# using(){} wrapper.
And I'm really hoping that the password is protected in some way (by hashing or encryption), so that the password that is passed into this method is not the plain text password as entered by a user.
|||Yep that definetly won't work,
I agree with DMW, people who write code like that should be shot! :-)
At it's most basic that function may work , apart from the obvious mistake of querying the Password as the Username,
I also convert my string comparisons to .toupper or .tolower , just get rid of casing issues.
on Passwords I always enter them into the DB encrypted, and basically compare the encrypted text.
I would never do this in Code, as I would rather do this kind of operation in the Stored Proc, and let the SP deliver the result.
Inline SQL is so Passe, in a field where we must be moving with the times , this is a no no.
|||That is a little harsh of a response don't you think. Did youtake the time to consider that i may be a new developer and not reallyknow what i am doing.
Anyway, that is why I posted I wanted to dknow if i was doing it rightor not. And when i pass the password to the function it willalready be hashed.
Thanks for the help.
|||

appologies if I came across a little, harsh I did not certainly mean in it in that way,

I was trying to be funny.

I'll stick to code, because I suck at language :-)

|||I agree.
I don't recall advocating that ANY developer ever be shot.
And the reason that I spend my time on this forum is that I'm passionate about passing on what little experience I have to other developers so that they can learn from my mistakes.
So keep posting, and you'll get lot's of support and advice.
Speaking of which, I'll offer one other piece of advice: never, ever use someone else's code (especially not sample code or book code) unless you're really happy with how it works. Most samples, including book samples, don't follow best practice. If they did, the code would be harder to follow and generally much too long to fit in a book. A lot of "book code" (by which I mean conference samples, MSDN samples, my samples) is written to try and explain a learning point, not for commercial use. I know that this sounds daft, but that's the way it is.
The most common thing that is omitted is error handling code (which is critical), and the normal security reviews that would accompany real application development.|||Apology accpeted no hard feelings at all.
yeah one reason I posted was to make sure that I was doing it right. Thank for taking the time to answer.
I have the book code complete so that i can learn better ways of codingI guess i need to pull that sucker out and try to read it again.
Thanks for all the help.
sql

Monday, March 12, 2012

Help with query

I want to take this code and insert data on a monthly basis. For example all the data for month of August.

INSERT INTO IISLOG
( ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department) SELECT
ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department
FROM IISLOG.DBO.IISLOG
WHERE LogTime BETWEEN '2004-01-01' AND GetDate()-1
GO

ThanksChange your where clause

WHERE LogTime BETWEEN '2004-01-01' AND GetDate()-1

To this

WHERE MONTH(LogTime)=8|||If you can ride an index, I'd use:INSERT INTO IISLOG
( ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department) SELECT
ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department
FROM IISLOG.DBO.IISLOG
WHERE '2004-08-01' <= LogTime
AND LogTime < '2004-09-01'
GOThis lets you ride an index if one exists on LogTime, which can improve your performance by literally orders of magnitude (things can take much less than one tenth as long as not riding the index).

-PatP

Wednesday, March 28, 2012

Help With SQL Command

Help With SQL Command

Help With SQL Command

Help With SQL Command

Help with Sproc and multi parameter

Monday, March 26, 2012

Help with setting Algorithm Paramteres

Help with setting Algorithm Paramteres

Friday, March 23, 2012

Help with sample code for ssis surrogate key transform

Wednesday, March 21, 2012

help with report

Help with reformatting xml

Help with reformatting xml

Help with reading database.

Monday, March 12, 2012

Help with query

Max Query

Blog Archive

About Me