Creating DB from scripts: query

Showing posts with label query. Show all posts

Tuesday, March 27, 2012

creating alerts

I want to create an alert that for each time a sql view is deleted from the server alerts an operator and runs a sql query to recreate

I have the code for recreating the view and this can be manually invoked and need some help in automating this other than create a job to run at scheduled intervals as this is only needs executed when treh views have been deleted

**views are deleted by a rogue application processWouldn't it be better to find the process that is deleteing the iew(s) and stop it?

Creating Access tables in SQL

I'm using Coldfusion and trying to create a Access table with a autonumber field (SID).

I'm using the following query but to be honest I haven't got a clue how to set the first field (SID) to Autonumber

<cfquery name="createtable_users" datasource="#attributes.dsn#">
Create Table #tbl.code#_Users
(
SID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)
</cfquery>

The code in my SQL book is designed purely for SQL DBs and is not having any of it.

The error is -

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

SQL = "Create Table test1_Users ( SID INT NOT NULL PRIMARY KEY DEFAULT 1, Firstname VARCHAR(50) NOT NULL, Surname VARCHAR(50) NOT NULL, Address VARCHAR(150) NOT NULL, Town VARCHAR(50) NOT NULL, County VARCHAR(50) NOT NULL, Postcode VARCHAR(50) NOT NULL, email VARCHAR(50), phone VARCHAR(50) NOT NULL, mobile VARCHAR(50) )"

Which is really helpful as you can see.

Thanks in advance for any help.Create Table #tbl.code#_Users
(
SID COUNTER PRIMARY KEY,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)|||Thanks for that works a treat.sql

Sunday, March 25, 2012

creating a udf

I have built a query to parse the first 4 chunks of data in a string for
natural alphanumeric sorting, and would be extremely and eternally grateful
if someone could show me how to turn this into a function that I could use
like above.
I would like to be able to feed in the name of the column to be sorted(it is
'fieldid' below) and the name of the database(it is 'tblsamples' below). I
am having trouble in figuring out how to implement/create a function which
would act like this
select * from tblsamples
order by naturalsort('fieldid', 'tblsamples')
Thanks a lot. Here is my query:
select TOP 100 fieldid as 'sortcolumn',
LEFT(fieldid, PATINDEX('%[0-9]%', fieldid) - 1) as 'a',
CASE
WHEN right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ) not
like '%[a-z,-]%'
then CAST(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )
AS INT)
else
cast (
left (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
case
when patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) = 0
then patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 ))
else patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) -1
end
)
as int)
end
as 'b',
LEFT (
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
,
CASE
WHEN PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) = 0
THEN
LEN( right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
)
ELSE PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) -1
END
)
as 'c',
CASE
WHEN right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) not like '%[a-z,-]%'
then CAST(right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) AS INT)
else
cast (
left (
right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ),
case
when patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) = 0
then patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ))
else patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) -1
end
)
as int)
end
as 'd'
from tblsamples
order by a,b,c,dHow about some DDL and sample data?
MLsql

creating a table with select into

Hi,
how can i create a new table from a select statement? i use select into, but
my select query has case columns and query analyzer says that i did not
specified a column for them. how do i do it?
thanks in advance for your helpGive the column a name. Make this:
SELECT somecolum, CASE when x then y else z END
INTO #table
FROM table
look like this:
SELECT somecolum, CASE when x then y else z END AS SomeNewColumn
INTO #table
FROM table
or this:
SELECT somecolum, 'SomeNewColumn' = CASE when x then y else z END
INTO #table
FROM table
Keith
"Jose" <norespondais@.almailpor.favor> wrote in message
news:uNmJgiTDFHA.1396@.tk2msftngp13.phx.gbl...
> Hi,
> how can i create a new table from a select statement? i use select into,
but
> my select query has case columns and query analyzer says that i did not
> specified a column for them. how do i do it?
> thanks in advance for your help
>sql

Thursday, March 22, 2012

Creating a table inside a stored procedure

I am trying to creating a table inside a stored procedure using SQL that works fine in Query Analyzer. However, when I check the syntax I get the following message:

Error 208: Invalid object name '##OPTIONSEX'

I am using the following SQL script:

CREATE PROCEDURE [dbo].[Test2] AS

CREATE TABLE ##OPTIONSEX
(
OPTION_PLAN VARCHAR(50),
TOT_OPTIONS_EXCHANGED FLOAT NULL
)

GO

INSERT ##OPTIONSEX

SELECT
B.COMPONENT,
TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A, TBLCOMPONENT B
WHERE B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT

GO

Any help getting this to run correctly would be appreciated.Is that like the type of sex you want...is this mail order?

Anyway, you need to take out the GO...that's a scope terminator

And are you sure you want a GLOBAL Temp table instead of a local one?

Try this

CREATE PROCEDURE [dbo].[Test2]
AS
BEGIN
CREATE TABLE ##OPTIONSEX (
OPTION_PLAN VARCHAR(50)
, TOT_OPTIONS_EXCHANGED FLOAT NULL
)

INSERT ##OPTIONSEX
SELECT B.COMPONENT
, TOT_OPTIONS_EXCHANGED = SUM(A.UNITS)
FROM TBLEXERCISEOPTIONS A
INNER JOIN TBLCOMPONENT B
ON B.COMPONENTID = A.COMPONENTID
GROUP BY B.COMPONENT

DROP TABLE ##OPTIONSEX
GO|||Thanks for the reply. Worked fine only after I removed the BEGIN line. For some reason using BEGIN returned a syntax error.

Thanks again Brett.|||dooooooh

I forgot the END

BEGIN
.....some code
END

And you'll need those constructs if you do an control of flow logic

IF some condition
BEGIN
.........some code line 1
.........some code line 2
END

WHILE some Cond
BEGIN
.........some code line 1
.........some code line 2
END

Good luck

Creating a table in Access from an SSIS package

I need to run a make-table query against an Access database out of an SSIS package. I tried to do this with an OLE DB Command Task but it fails to create the table even though the task execution comes back successful. Any thoughts?

Did you tried with a script task ? Use the excel connection from the Connection Manager to connect to your Access DB and execute your create table query from a OleDbCommand object.

I've not tried that method, it just a thought.

sql

Creating a sum for the year

Hi all I have to create a query that would give the users a a total amount of each field for the month or year.

I just need it to give me a total amount of each item for the month. So basically what happens is that the user enters in a beginning date and an ending date for the month or year and this report gives them the [INVESTIGATOR], [VIOLATION TYP], [DATE], [TOTAL LOSS].

I just want to show how many vilolationtypes per investigator so if Smith had 40 in one month I would need to reflect that in the report. does that make sense?

[VIOLATION TYPE](NVARCHAR)
[DATE] (DATETIME0
[TOTAL LOSS](MONEY)
[INVESTIGATOR](Nvarchar)Hi all I have to create a query that would give the users a a total amount of each field for the month or year.

I just need it to give me a total amount of each item for the month. So basically what happens is that the user enters in a beginning date and an ending date for the month or year and this report gives them the [INVESTIGATOR], [VIOLATION TYP], [DATE], [TOTAL LOSS].

I just want to show how many vilolationtypes per investigator so if Smith had 40 in one month I would need to reflect that in the report. does that make sense?

[VIOLATION TYPE](NVARCHAR)
[DATE] (DATETIME0
[TOTAL LOSS](MONEY)
[INVESTIGATOR](Nvarchar)

1. Create a column for YYYYMM using DatePart.
link here: http://msdn2.microsoft.com/en-us/library/aa258265(SQL.80).aspx
Be sure to CONVERT the results into VARCHAR so you can string them together (YYYY + MM).

2. Return that and the columns you want.

3. Put that query into a subquery.

4. On the outer query;
Group by:
Investigator, YYYYMM, ViolationType

5. Do an aggregate on the fields you want to tally
SUM(Money), ...

If you want actual code, would you be so kind as to do the typing for "create table" and "insert" statements to create a little test data. The concept seems pretty universally basic however. I'll see if I can find a table with dates to make an example.

EDIT: Here's a (meaningless) sample ... This returns a monthly tally of "Balance" by "Customer ID" from our Invoices table:
Note: It uses the "DateName" function for readability of the "Month" result ... not sure if that's ANSI Standard.

SELECT
CustomerID,
MM_YYYY,
SUM(Balance) AS Balance
FROM
( SELECT
CustomerID,
Balance,
CONVERT(varchar(10), DATENAME(MM, InvoiceDate)) + ' ' +
CONVERT(varchar(4), DATEPART(YYYY, InvoiceDate)) AS MM_YYYY
FROM tbInvoice
WHERE (InvoiceDate >= CONVERT(DATETIME, '2006-01-01 00:00:00', 102))
) InnerSelect
GROUP BY CustomerID, MM_YYYY|||I'm sure it would help if I showed you all the DDL

CREATE TABLE [IncidentReports] (
[Notes] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IncidentReports_Notes] DEFAULT (N'Scanned Report'),
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_IncidentReports_I/RDocument] DEFAULT (N'Scanned Reports'),
[Action Type] [int] NULL ,
[Action] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss Type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss] [money] NULL CONSTRAINT [DF_IncidentReports_Loss] DEFAULT (0.00),
[Violation Type] [int] NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Inspector] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_IncidentReports] PRIMARY KEY CLUSTERED
(
[IR Number]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO|||SELECT MONTH([DATE]) AS MNTH, COUNT([VIOLATION] AS VLT
FROM INCIDENTREPORTS
WHERE [DATE] BETWEEN @.BEGDATE AND @.ENDDATE
GROUP BY MONTH([DATE])

I'll let you fill in the restsql

Creating a Sum for the Year

Hi all I have to create a query that would give the users a a total amount of each field for the month or year.

I just need it to give me a total amount of each item for the month. So basically what happens is that the user enters in a beginning date and an ending date for the month or year and this report gives them the [INVESTIGATOR], [VIOLATION TYP], [DATE], [TOTAL LOSS].

I just want to show how many vilolationtypes per investigator so if Smith had 40 in one month I would need to reflect that in the report. does that make sense?

Code Snippet

[VIOLATION TYPE](NVARCHAR)
[DATE] (DATETIME0
[TOTAL LOSS](MONEY)
[INVESTIGATOR](Nvarchar)

Does this look right

Code Snippet

SELECT Month([Date]) AS Mnth, COUNT([Violation]) AS VlT
FROM Revised_MainTable
WHERE [Date] BETWEEN @.BeginDate AND @.EndDate
GROUP BY Month([Date])
GO

Try: GROUP BY Year([Date]), Month([Date])

If you only group on the month, then, for example, May 2006 would be grouped with May 2005.
|||

this is the error message I get when I exectute that

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'BETWEEN'.

Code Snippet

SELECT Month([Date]) AS Mnth, COUNT([Violation]) AS VlT
FROM Revised_MainTable
WHERE BETWEEN @.StartDate AND @.EndDate
GROUP BY Year([Date]), Month([Date])
GO

|||Looks like you're missing the [Date] column name before the BETWEEN keyword in that second excerpt.
|||

WHERE {what} BETWEEN @.StartDate AND @.EndDate

|||

Ok I did that now the error message is

Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@.StartDate'.

|||

In your first post, you used @.BeginDate, and then in a later post, you used @.StartDate.

Perhaps you need to change this to @.BeginDate...

|||

Now I'm getting

Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@.StartDate'.

Here's the DDL

Code Snippet

CREATE TABLE [Revised_MainTable] (
[I/RDocument] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Revised_MainTable_I/RDocument] DEFAULT (N'Scanned Report'),
[IR Number] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Date] [datetime] NULL ,
[Inspector] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Area] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Violation Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Loss] [money] NULL CONSTRAINT [DF_Revised_MainTable_Loss] DEFAULT (0.0000),
[Loss Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employee] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Guest] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Security/GC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Revised_MainTable_Security/GC] DEFAULT (N'GC'),
CONSTRAINT [PK_Revised_MainTable] PRIMARY KEY CLUSTERED
(
[IR Number]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

Is this a stored procedure?

Do you have the parameters @.StartDate, @.BeginDate, or @.EndDate defined?

Do you provide those parameters with values anywhere?

|||

thank you so much it works no thank you

One more question please, how would I get it to calculate each violation. lets say if theres 6 Thefts how would I get it to tell me that plus the total amount that was lost?

Code Snippet

SELECT MONTH(Date) AS Mnth, COUNT([Violation Type]) AS [Violation Type], SUM(Loss) AS [Sum Of Loss], [Violation Type] AS Violation
FROM dbo.Revised_MainTable
WHERE (Date BETWEEN @.StartDate AND @.EndDate)
GROUP BY YEAR(Date), MONTH(Date), [Violation Type]

|||

Assuming that [Violation] and [Violation Type] are not the same thing, you could use the same query, substituting one for the other -and it already had the sum of loss included.

Otherwise, if [Violation] is a sub-set of [Violation Type], then add [Violation] to the GROUP BY.

If my assumption are incorrect, send more information.

|||

I would just like to mention one thing about your use of @.StartDate and @.EndDate.

Be aware that as these are datetimes they can mark a point in time. If you are using these as date only then you should make sure that they all indicate midnight at the start of the day (the default). The reason for this is that if the Date field ever manages to get time information in it as well then checking against an @.EndDate that is midnight at the start of the day will not catch any offences which are for times later in the day.

|||

See this is the query the guy built and based his reports off of, I inherited this database and I need to make it into an ADP where sQL server is the engine not access.

But the thing is that Jet SQL and SQL Server are a little different. this is the query he uses in access this the sql view of it. and I'm trying to turn it into SQL server but I'm not getting the same results he's getting.

Code Snippet

SELECT DISTINCTROW Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Violation Type], Sum([Main Table].Loss) AS [Sum Of Loss], Count([Main Table].[Violation Type]) AS [CountOfViolation Type]
FROM [Main Table]
GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Violation Type], Year([Main Table].Date)*12+DatePart('m',[Main Table].Date)-1
HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the month and year]));

Wednesday, March 21, 2012

Creating a SQLExecute function in c#

Hi im trying to create a function called SQLExecute that takes an SQL query, executes it and returns the resultant dataset in dsetResponse and if an error in strError, however i am unsure if whether im on the right track or not, where would i put the sql query and what else needs to be done, my code is as follows;

publicstaticDataSet SQLExecute(string strSQL,string strError)

{

DataSet dsetResponse =newDataSet();

try

{

using (SqlConnection conn =newSqlConnection(DHOC.clsDHOC.GetConnString()))

{

SqlCommand cmd =newSqlCommand();cmd.CommandType =CommandType.Text;

}

catch (ThreadAbortException thEx)

{

throw;

}

catch (Exception ex)

{

string strError

}

return dsetResponse;

}

At least I can point out two things:

1). You don't need to pass string error into your function because the error should be caught inside your function;

2). In your two catch blocks, you should throw the error like throw thEx and ex. In my practice, I usually just do error throw in development phase. Before I move to product, I will LOG the error, and return null for the DataSet if error is caught, so that client will not be able to see ugly error.

|||

Hi thanks for responding, i have adjusted it but i dont think what i have done so far is correct;

publicstaticDataSet SQLExecute(string strSQL)

{

string strData ="";string strTableName ="";

DataSet dsetResponse =newDataSet();

try

{

using (SqlConnection conn =newSqlConnection(DHOC.clsDHOC.GetConnString()))

{

SqlCommand cmd =newSqlCommand("SELECT ColumnName FROM TableName WHERE ColumnName = 'ColumnValue'", conn);

cmd.CommandType =CommandType.Text;

cmd.Parameters["@.ColumnName"].Value = strData;

cmd.Parameters["@.TableName"].Value = strTableName;

cmd.Parameters["@.ColumnValue"].Value = strData;

cmd.ExecuteNonQuery;

}

catch (ThreadAbortException thEx)

{

throw;

}

catch (Exception ex)

{

clsDHOC objDHOC =newclsDHOC();objDHOC.Write2ErrorLogTable(ex1.Message,"GetDataSetByID","clsDHOC", System.Web.HttpContext.Current.Session["UserFullName"].ToString());

}

return dsetResponse;

}

|||

Hi,

SqlCommand cmd = new SqlCommand("SELECT ColumnName FROM TableName WHERE ColumnName = 'ColumnValue'", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters["@.ColumnName"].Value = strData;
cmd.Parameters["@.TableName"].Value = strTableName;
cmd.Parameters["@.ColumnValue"].Value = strData;
cmd.ExecuteNonQuery;

From the code you provided, I think there should be something wrong while you are using Parameters. As you have modify your select command, "SELECT ColumnName FROM TableName WHERE ColumnName = 'ColumnValue'"".

There's no parameter holders for ColumnName,TableName and ColumnValue. All the parameter you want to bind should be declared with a "@." prefix which indicates that it's a parameter in your select command.

Besides, only values in condition part can be the parameter. Such value like TableName cannot be the parameter, if you want to select dynamical tables, you may create your select command manually by string.Format() method.

Thanks.

Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
.. ... ... 4
Howto achieve it?
TIA
Ana
That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>
|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>
|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>
|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en el
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>

Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
. ... ... 4
Howto achieve it?
TIA
Anahi
set the num column as IDENTITY. see bol for more on IDENTITY
Regards
--
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribió en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribió en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>> Hi,
>> I'd like to generate a column in a query which shows the row number
>> chronologically (Num) as:
>>
>> Cust_ID Sales Date Num
>> 526 12.350 12/5/2007 1
>> 632 11.520 5/5/2007 2
>> 123 10.899 6/6/2007 3
>> . ... ... 4
>>
>> Howto achieve it?
>> TIA
>> Ana
>|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribió en el
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Sorry, I didn't explain myself well. The result of a query is a ranking
>> based on customers' sales.
>> The fields from a table are:
>> Cust_ID
>> Sales
>> Date (European format)
>> The query generates the following results:
>> Cust_ID Sales Date
>> 526 12.350 12/5/2007
>> 632 11.520 5/5/2007
>> 123 10.899 6/6/2007
>>
>> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
>> Customer ID 632 generated 11.520 euros so should be 2.
>> And Cust. ID 123 should be 3. and etc.
>> So I was wondering if a column can be generated in a query which would
>> label the ranking from 1 to wherever ends the query. Meaning, if I have
>> 10 rows so will be till 10.
>> Hope I have been a bit clearer.
>> Thank you much for your prompt response.
>> Ana
>>
>> "Ana" <ananospam@.yahoo.es> escribió en el mensaje de noticias
>> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>> Hi,
>> I'd like to generate a column in a query which shows the row number
>> chronologically (Num) as:
>>
>> Cust_ID Sales Date Num
>> 526 12.350 12/5/2007 1
>> 632 11.520 5/5/2007 2
>> 123 10.899 6/6/2007 3
>> . ... ... 4
>>
>> Howto achieve it?
>> TIA
>> Ana
>>
>sql

Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
. ... ... 4
Howto achieve it?
TIA
Anahi
set the num column as IDENTITY. see bol for more on IDENTITY
Regards
Vt
Knowledge is power;Share it
http://oneplace4sql.blogspot.com
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en e
l
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>

Monday, March 19, 2012

Creating a report

Hi,
Im new to dot net and CR.
I would like to create a a report based on an SQL query at run time.
How do i do it.
Tnx
PapsHave you tried doing a search on this forum or on Google or Crystal Report's website? There's tons of information on the many different ways to do Crystal Reports, you just have to a bit of digging!

Crystal Reports:
http://support.businessobjects.com/search/advsearch.asp

Google:
http://www.google.com

Crystal Reports Forum:
http://support.businessobjects.com/forums/default.asp

creating a query sql

My query in MSAccess is:
SELECT ArticoliMagazzino1.CodiceArticolo,
ArticoliMagazzino1.DescrizioneArticolo,
ArticoliMagazzino1.UmDiMagazzino, Query2.Prezzo
FROM ArticoliMagazzino1 LEFT JOIN Query2 ON
ArticoliMagazzino1.CodiceArticolo=Query2.CodiceArticolo
WHERE (((ArticoliMagazzino1.StatoRecord)=0));
Where Query2 is:
SELECT TabellaListiniArt.CodiceArticolo, TabellaListiniArt.Prezzo
FROM TabellaListiniArt
WHERE (((TabellaListiniArt.CodiceListino)="1") AND
((TabellaListiniArt.Stato)=0));
The problem is that I have to create a unique query for use with SQL.
Thanks in advance.(giminera@.libero.it) writes:
> My query in MSAccess is:
> SELECT ArticoliMagazzino1.CodiceArticolo,
> ArticoliMagazzino1.DescrizioneArticolo,
> ArticoliMagazzino1.UmDiMagazzino, Query2.Prezzo
> FROM ArticoliMagazzino1 LEFT JOIN Query2 ON
> ArticoliMagazzino1.CodiceArticolo=Query2.CodiceArticolo
> WHERE (((ArticoliMagazzino1.StatoRecord)=0));
> Where Query2 is:
> SELECT TabellaListiniArt.CodiceArticolo, TabellaListiniArt.Prezzo
> FROM TabellaListiniArt
> WHERE (((TabellaListiniArt.CodiceListino)="1") AND
> ((TabellaListiniArt.Stato)=0));
> The problem is that I have to create a unique query for use with SQL.
> Thanks in advance.
I'm not sure what your question is. But since this is a newsgroup about
SQL Server, I will produce a query that works on SQL Server, without
considering whether it would work on Access. (Since I don't know Access,
I can't consider it anyay.)
SELECT AM.CodiceArticolo, AM.DescrizioneArticolo,
AM.UmDiMagazzino, TLA.Prezzo
FROM ArticoliMagazzino1 AM
LEFT JOIN TabellaListiniArt TLA ON AM.CodiceArticolo = TLA.CodiceArticolo
AND TLA.CodiceListino = "1"
AND TLA.Stato = 0
WHERE AM.StatoRecord = 0;
I've taken the liberty to introduce aliases, and to remove the exesses
of parentheses.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Creating a Query for Notification

We want to be able to cache the data for our propertydetails web page (a real estate web site)

Right now the stored procedure for that page is not a good candidate for query notification.

Is it possible to have a trigger on a Property table and when the lastupdated field is changed then go and update the web cache data for that propertydetails page?

(i believe that this is Broker Services).

You'll probably receive more responses from another newsgroup or forum. This one is dedicated to SQL Server Notification Services - not the same technology as Query Notifications.

Cheers!

Joe

Creating a Query for Notification

We want to be able to cache the data for our propertydetails web page (a real estate web site)

Right now the stored procedure for that page is not a good candidate for query notification.

Is it possible to have a trigger on a Property table and when the lastupdated field is changed then go and update the web cache data for that propertydetails page?

(i believe that this is Broker Services).

You'll probably receive more responses from another newsgroup or forum. This one is dedicated to SQL Server Notification Services - not the same technology as Query Notifications.

Cheers!

Joe

Sunday, March 11, 2012

Creating a new database

quote:

> I am trying to create a new database, say name YYYData. I designed it in

Visio and created a .DDL file. When I opened the file in Query Analyzer and
ran it, I was informed there was a file already present named, YYYData. I
tried DROP database YYYData and sp_dbremove YYYData and both times I was
informed that YYYData does not exist.

quote:

> What is wrong? How do it fix it?
> Thanks.

|||This illustrates the dilemma:
-- IN SQL ANALYZER --
use master
go
create database [YYYData]
ON PRIMARY
( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Da
ta\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB )
LOG ON
( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Da
ta\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB )
go
use [YYYData]
go
--THE RESPONSE IN SQL ANALYZER --
Server: Msg 1828, Level 16, State 5, Line 4
The file named 'YYYData' is already in use. Choose another name.
Server: Msg 911, Level 16, State 1, Line 2
Could not locate entry in sysdatabases for database 'YYYData'. No entry foun
d with that name. Make sure that the name is entered correctly.
-- QUESTION --
How can YYYData be already in use in one place and not found immediately aft
er in another?
Thanks,
EagleRed
-- oj wrote: --
You will have to follow the identifier rule.
http://msdn.microsoft.com/library/d... />
3_6e9e.asp
e.g.
create database [YYYData]
-oj
http://www.rac4sql.net
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com...

quote:

> I am trying to create a new database, say name YYYData. I designed it in

quote:

> How can YYYData be already in use in one place and not found immediately

after in another?
Logical file names must be unique within a database. The CREATE DATABASE
statement should be something like:
CREATE DATABASE [YYYData]
ON PRIMARY
( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
LOG ON
( NAME = YYYLog, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
Hope this helps.
Dan Guzman
SQL Server MVP
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:2A01B39B-8C77-4CFD-B95D-0C0155824361@.microsoft.com...

quote:

> This illustrates the dilemma:
> -- IN SQL ANALYZER --
> use master
> go
>
> create database [YYYData]
> ON PRIMARY
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL

Server\MSSQL\Data\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )

quote:

> LOG ON
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL

Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )

quote:

> go
> use [YYYData]
> go
> --THE RESPONSE IN SQL ANALYZER --
> Server: Msg 1828, Level 16, State 5, Line 4
> The file named 'YYYData' is already in use. Choose another name.
> Server: Msg 911, Level 16, State 1, Line 2
> Could not locate entry in sysdatabases for database 'YYYData'. No entry

found with that name. Make sure that the name is entered correctly.

quote:

>
> -- QUESTION --
> How can YYYData be already in use in one place and not found immediately

after in another?

quote:

> Thanks,
> EagleRed
> -- oj wrote: --
> You will have to follow the identifier rule.
>

http://msdn.microsoft.com/library/d...con_03_6e9e.asp[QUOTE
]
> e.g.
> create database [YYYData]
>
> --
> -oj
> http://www.rac4sql.net
>
> "EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com>[/QUOTE]
wrote

quote:

> in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com...
it in[QUOTE]
> visio and created a .DDL file. When I opened the file in Query

Analyzer and

quote:

> ran it, I was informed there was a file already present named,

YYYData. I

quote:

> tried DROP database YYYData and sp_dbremove YYYData and both times I

was

quote:

> informed that YYYData does not exist.
>
>

|||The logical names for the data and log are the same - "NAME = YYYYData" is
used for both (but they must be unique)
Incidentally, the online help and BOL (Books On Line) have a complete
description of the syntax. Usually, this is the first place to start when
verifying syntax. In the description of create table, you will find:
logical_file_name
Is the name used to reference the file in any Transact-SQL statements
executed after the database is created. logical_file_name must be unique in
the database and conform to the rules for identifiers. The name can be a
character or Unicode constant, or a regular or delimited identifier.
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:2A01B39B-8C77-4CFD-B95D-0C0155824361@.microsoft.com...

quote:

> This illustrates the dilemma:
> -- IN SQL ANALYZER --
> use master
> go
>
> create database [YYYData]
> ON PRIMARY
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL

Server\MSSQL\Data\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )

quote:

> LOG ON
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL

Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )

quote:

> go
> use [YYYData]
> go
> --THE RESPONSE IN SQL ANALYZER --
> Server: Msg 1828, Level 16, State 5, Line 4
> The file named 'YYYData' is already in use. Choose another name.
> Server: Msg 911, Level 16, State 1, Line 2
> Could not locate entry in sysdatabases for database 'YYYData'. No entry

found with that name. Make sure that the name is entered correctly.

quote:

>
> -- QUESTION --
> How can YYYData be already in use in one place and not found immediately

after in another?

quote:

> Thanks,
> EagleRed
> -- oj wrote: --
> You will have to follow the identifier rule.
>

quote:

> in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com...
it in[QUOTE]
> visio and created a .DDL file. When I opened the file in Query

Analyzer and

quote:

> ran it, I was informed there was a file already present named,

YYYData. I

quote:

> tried DROP database YYYData and sp_dbremove YYYData and both times I

was

quote:

> informed that YYYData does not exist.
>
>

|||THIS IS THE CRUX OF MY PROBLEM!!!!!!! This what I am experiencing. I canno
t create a database named YYYData because SQL Server says the name is in use
(not necessarily that a DB exists by that name). However, I cannot find it
on the list of databases o
n the server nor can I drop it or delete it with sp_dbremove. It seems I ha
ve a "zombie" DB that is dead but not gone. I would like to use the name YY
YData, since I have published the name in some documentation but I will chan
ge if I must. I would, how
ever, like to understand what is going on so I can at least avoid this probl
em in the future. I may have created a DB with this name in the past and de
leted it. I literally don't remember.
Thanks.
EagleRed|||I have consulted the docs and tried what was recommended there. But it isn't
working with a DB named YYYData. See my response to Dan Guzman for a bette
r description of my problem.
I appreciate your help!|||Re-read Dan's post. Your problem has NOTHING to do with an existing
database named YYYData - it is a problem with the logical names given to the
primary data file and the log file. Dan has chosen to use the logical name
of YYYData for the primary data file and the name of YYYLog for the log
file.
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:0D2C87AB-2B9D-49A0-A7BA-85E5EE6481F5@.microsoft.com...

quote:

> THIS IS THE CRUX OF MY PROBLEM!!!!!!! This what I am experiencing. I

cannot create a database named YYYData because SQL Server says the name is
in use (not necessarily that a DB exists by that name). However, I cannot
find it on the list of databases on the server nor can I drop it or delete
it with sp_dbremove. It seems I have a "zombie" DB that is dead but not
gone. I would like to use the name YYYData, since I have published the name
in some documentation but I will change if I must. I would, however, like
to understand what is going on so I can at least avoid this problem in the
future. I may have created a DB with this name in the past and deleted it.
I literally don't remember.

quote:

> Thanks.
> EagleRed

|||create database [YYYData]
ON PRIMARY
( NAME = YYYData, /*first logical name*/
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\YYYData.mdf',
SIZE = 3 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 256 KB )
LOG ON
( NAME = YYYData, /*second logical name*/
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\YYYData.ldf',
SIZE = 1 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 256 KB )
The second logical name is where the error actually is. As stated, you
cannot have a duplicate logical name for a given database. You should change
the second one to something like YYYLog. Note: this has nothing to do with
the database name. It's just a logical name for the data or log file.
-oj
http://www.rac4sql.net
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:0D2C87AB-2B9D-49A0-A7BA-85E5EE6481F5@.microsoft.com...

quote:

> THIS IS THE CRUX OF MY PROBLEM!!!!!!! This what I am experiencing. I

quote:

> Thanks.
> EagleRed

|||Thanks, guys. I knew I was "holding my mouth wrong on something"! ENJOY!!!
!

Creating a new database

I am trying to create a new database, say name YYYData. I designed it in Visio and created a .DDL file. When I opened the file in Query Analyzer and ran it, I was informed there was a file already present named, YYYData. I tried DROP database YYYData and sp_dbremove YYYData and both times I was informed that YYYData does not exist.
What is wrong? How do it fix it?
Thanks.You will have to follow the identifier rule.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6e9e.asp
e.g.
create database [YYYData]
-oj
http://www.rac4sql.net
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com...
> I am trying to create a new database, say name YYYData. I designed it in
Visio and created a .DDL file. When I opened the file in Query Analyzer and
ran it, I was informed there was a file already present named, YYYData. I
tried DROP database YYYData and sp_dbremove YYYData and both times I was
informed that YYYData does not exist.
> What is wrong? How do it fix it?
> Thanks.|||This illustrates the dilemma
-- IN SQL ANALYZER -
use maste
g
create database [YYYData
ON PRIMAR
( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB
LOG O
( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256 KB )
g
use [YYYData
g
--THE RESPONSE IN SQL ANALYZER -
Server: Msg 1828, Level 16, State 5, Line
The file named 'YYYData' is already in use. Choose another name
Server: Msg 911, Level 16, State 1, Line
Could not locate entry in sysdatabases for database 'YYYData'. No entry found with that name. Make sure that the name is entered correctly
-- QUESTION -
How can YYYData be already in use in one place and not found immediately after in another
Thanks
EagleRed
-- oj wrote: --
You will have to follow the identifier rule
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6e9e.as
e.g
create database [YYYData
--
-o
http://www.rac4sql.ne
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrot
in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com..
> I am trying to create a new database, say name YYYData. I designed it i
Visio and created a .DDL file. When I opened the file in Query Analyzer an
ran it, I was informed there was a file already present named, YYYData.
tried DROP database YYYData and sp_dbremove YYYData and both times I wa
informed that YYYData does not exist
>> What is wrong? How do it fix it
>> Thanks|||> -- QUESTION --
> How can YYYData be already in use in one place and not found immediately
after in another?
Logical file names must be unique within a database. The CREATE DATABASE
statement should be something like:
CREATE DATABASE [YYYData]
ON PRIMARY
( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
LOG ON
( NAME = YYYLog, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
--
Hope this helps.
Dan Guzman
SQL Server MVP
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:2A01B39B-8C77-4CFD-B95D-0C0155824361@.microsoft.com...
> This illustrates the dilemma:
> -- IN SQL ANALYZER --
> use master
> go
>
> create database [YYYData]
> ON PRIMARY
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
> LOG ON
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
> go
> use [YYYData]
> go
> --THE RESPONSE IN SQL ANALYZER --
> Server: Msg 1828, Level 16, State 5, Line 4
> The file named 'YYYData' is already in use. Choose another name.
> Server: Msg 911, Level 16, State 1, Line 2
> Could not locate entry in sysdatabases for database 'YYYData'. No entry
found with that name. Make sure that the name is entered correctly.
>
> -- QUESTION --
> How can YYYData be already in use in one place and not found immediately
after in another?
> Thanks,
> EagleRed
> -- oj wrote: --
> You will have to follow the identifier rule.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6e9e.asp
> e.g.
> create database [YYYData]
>
> --
> -oj
> http://www.rac4sql.net
>
> "EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com>
wrote
> in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com...
> > I am trying to create a new database, say name YYYData. I designed
it in
> Visio and created a .DDL file. When I opened the file in Query
Analyzer and
> ran it, I was informed there was a file already present named,
YYYData. I
> tried DROP database YYYData and sp_dbremove YYYData and both times I
was
> informed that YYYData does not exist.
> >> What is wrong? How do it fix it?
> >> Thanks.
>
>|||The logical names for the data and log are the same - "NAME = YYYYData" is
used for both (but they must be unique)
Incidentally, the online help and BOL (Books On Line) have a complete
description of the syntax. Usually, this is the first place to start when
verifying syntax. In the description of create table, you will find:
logical_file_name
Is the name used to reference the file in any Transact-SQL statements
executed after the database is created. logical_file_name must be unique in
the database and conform to the rules for identifiers. The name can be a
character or Unicode constant, or a regular or delimited identifier.
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:2A01B39B-8C77-4CFD-B95D-0C0155824361@.microsoft.com...
> This illustrates the dilemma:
> -- IN SQL ANALYZER --
> use master
> go
>
> create database [YYYData]
> ON PRIMARY
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.mdf', SIZE = 3 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
> LOG ON
> ( NAME = YYYData, FILENAME = 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\YYYData.ldf', SIZE = 1 MB, MAXSIZE = UNLIMITED, FILEGROWTH
= 256 KB )
> go
> use [YYYData]
> go
> --THE RESPONSE IN SQL ANALYZER --
> Server: Msg 1828, Level 16, State 5, Line 4
> The file named 'YYYData' is already in use. Choose another name.
> Server: Msg 911, Level 16, State 1, Line 2
> Could not locate entry in sysdatabases for database 'YYYData'. No entry
found with that name. Make sure that the name is entered correctly.
>
> -- QUESTION --
> How can YYYData be already in use in one place and not found immediately
after in another?
> Thanks,
> EagleRed
> -- oj wrote: --
> You will have to follow the identifier rule.
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6e9e.asp
> e.g.
> create database [YYYData]
>
> --
> -oj
> http://www.rac4sql.net
>
> "EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com>
wrote
> in message news:C24DB8F9-BF60-4039-AFA9-E2A764B4E01D@.microsoft.com...
> > I am trying to create a new database, say name YYYData. I designed
it in
> Visio and created a .DDL file. When I opened the file in Query
Analyzer and
> ran it, I was informed there was a file already present named,
YYYData. I
> tried DROP database YYYData and sp_dbremove YYYData and both times I
was
> informed that YYYData does not exist.
> >> What is wrong? How do it fix it?
> >> Thanks.
>
>|||THIS IS THE CRUX OF MY PROBLEM!!!!!!! This what I am experiencing. I cannot create a database named YYYData because SQL Server says the name is in use (not necessarily that a DB exists by that name). However, I cannot find it on the list of databases on the server nor can I drop it or delete it with sp_dbremove. It seems I have a "zombie" DB that is dead but not gone. I would like to use the name YYYData, since I have published the name in some documentation but I will change if I must. I would, however, like to understand what is going on so I can at least avoid this problem in the future. I may have created a DB with this name in the past and deleted it. I literally don't remember
Thanks
EagleRed|||Re-read Dan's post. Your problem has NOTHING to do with an existing
database named YYYData - it is a problem with the logical names given to the
primary data file and the log file. Dan has chosen to use the logical name
of YYYData for the primary data file and the name of YYYLog for the log
file.
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:0D2C87AB-2B9D-49A0-A7BA-85E5EE6481F5@.microsoft.com...
> THIS IS THE CRUX OF MY PROBLEM!!!!!!! This what I am experiencing. I
cannot create a database named YYYData because SQL Server says the name is
in use (not necessarily that a DB exists by that name). However, I cannot
find it on the list of databases on the server nor can I drop it or delete
it with sp_dbremove. It seems I have a "zombie" DB that is dead but not
gone. I would like to use the name YYYData, since I have published the name
in some documentation but I will change if I must. I would, however, like
to understand what is going on so I can at least avoid this problem in the
future. I may have created a DB with this name in the past and deleted it.
I literally don't remember.
> Thanks.
> EagleRed|||create database [YYYData]
ON PRIMARY
( NAME = YYYData, /*first logical name*/
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\YYYData.mdf',
SIZE = 3 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 256 KB )
LOG ON
( NAME = YYYData, /*second logical name*/
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\YYYData.ldf',
SIZE = 1 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 256 KB )
The second logical name is where the error actually is. As stated, you
cannot have a duplicate logical name for a given database. You should change
the second one to something like YYYLog. Note: this has nothing to do with
the database name. It's just a logical name for the data or log file.
--
-oj
http://www.rac4sql.net
"EagleRed@.highflyingbirds.com" <anonymous@.discussions.microsoft.com> wrote
in message news:0D2C87AB-2B9D-49A0-A7BA-85E5EE6481F5@.microsoft.com...
> THIS IS THE CRUX OF MY PROBLEM!!!!!!! This what I am experiencing. I
cannot create a database named YYYData because SQL Server says the name is
in use (not necessarily that a DB exists by that name). However, I cannot
find it on the list of databases on the server nor can I drop it or delete
it with sp_dbremove. It seems I have a "zombie" DB that is dead but not
gone. I would like to use the name YYYData, since I have published the name
in some documentation but I will change if I must. I would, however, like
to understand what is going on so I can at least avoid this problem in the
future. I may have created a DB with this name in the past and deleted it.
I literally don't remember.
> Thanks.
> EagleRed|||Thanks, guys. I knew I was "holding my mouth wrong on something"! ENJOY!!!!

Thursday, March 8, 2012

creating a function to be used in select query

I have this select query which returns a date. I would like to be able to
call this from a stored procedure and have the result appear in the result
set from the stored procedure.
i.e. SELECT *, CalcDate
FROM Table
WHERE somedate = @.dte
The CalcDate field would be the c.dt in the following select statement.
SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isW

day = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 25, @.dte)
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isW

day=1
AND c2.isHoliday=0
)
How can I do this - I think the above will be a UDF with a return statement
but I am not sure of the syntax.
THanksHi
I assume this subquery does not return more than 1 value.Yes you can write
an UDF to return the date as well , so please refer to the BOL for more info
See if this hepls , I could not tested it since you have not provided DDL+
sample data
SELECT *, (SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isW

day = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day, 25, @.dte)
AND 9 = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isW

day=1
AND c2.isHoliday=0
)
) as CalcDate
FROM Table
WHERE somedate = @.dte
in message news:uoSoNyhAGHA.3268@.TK2MSFTNGP10.phx.gbl...
>I have this select query which returns a date. I would like to be able to
>call this from a stored procedure and have the result appear in the result
>set from the stored procedure.
> i.e. SELECT *, CalcDate
> FROM Table
> WHERE somedate = @.dte
> The CalcDate field would be the c.dt in the following select statement.
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isW

day = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 25, @.dte)
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isW

day=1
> AND c2.isHoliday=0
> )
>
> How can I do this - I think the above will be a UDF with a return
> statement but I am not sure of the syntax.
> THanks
>|||I have tried the following but get the error msg:
The column prefix c does not match with a table name . ..
CREATE FUNCTION dbo.AddWorkDays
(
@.dte smalldatetime,
@.NoDays TINYINT
)
RETURNS SMALLDATETIME
AS
BEGIN
RETURN (SELECT c.dt
FROM dbo.Calendar c
WHERE
c.isW

day = 1
AND c.isHoliday =0
AND c.dt > @.dte
AND c.dt <= DATEADD(day,25, @.dte)
AND @.NoDays = (
SELECT COUNT(*)
FROM dbo.Calendar c2
WHERE c2.dt >= @.dte
AND c2.dt <= c.dt
AND c2.isW

day=1
AND c2.isHoliday=0
))
END
GO
"Newbie" <nospam@.noidea.com> wrote in message
news:uoSoNyhAGHA.3268@.TK2MSFTNGP10.phx.gbl...
>I have this select query which returns a date. I would like to be able to
>call this from a stored procedure and have the result appear in the result
>set from the stored procedure.
> i.e. SELECT *, CalcDate
> FROM Table
> WHERE somedate = @.dte
> The CalcDate field would be the c.dt in the following select statement.
> SELECT c.dt
> FROM dbo.Calendar c
> WHERE
> c.isW

day = 1
> AND c.isHoliday =0
> AND c.dt > @.dte
> AND c.dt <= DATEADD(day, 25, @.dte)
> AND 9 = (
> SELECT COUNT(*)
> FROM dbo.Calendar c2
> WHERE c2.dt >= @.dte
> AND c2.dt <= c.dt
> AND c2.isW

day=1
> AND c2.isHoliday=0
> )
>
> How can I do this - I think the above will be a UDF with a return
> statement but I am not sure of the syntax.
> THanks
>

creating a function for natural alphanumeric sorting

I have built a query to parse the first 4 chunks of data in a string for
natural alphanumeric sorting, and would be extremely and eternally grateful
if someone could show me how to turn this into a function that I could use
like above. Here is the ddl and some sample data:
CREATE TABLE [tblSamples] ([FIELDID] [varchar] (50))
INSERT INTO tblsamples (fieldid) VALUES ('B101D-050214')
INSERT INTO tblsamples (fieldid) VALUES ('B102D-050215')
INSERT INTO tblsamples (fieldid) VALUES ('B104D-050216')
INSERT INTO tblsamples (fieldid) VALUES ('B105D-050525')
INSERT INTO tblsamples (fieldid) VALUES ('B201D-050523')
INSERT INTO tblsamples (fieldid) VALUES ('B401D-050216')
INSERT INTO tblsamples (fieldid) VALUES ('B101D-050523D')
INSERT INTO tblsamples (fieldid) VALUES ('B103DR-050213')
INSERT INTO tblsamples (fieldid) VALUES ('B101M-050214')
INSERT INTO tblsamples (fieldid) VALUES ('B102M-050215')
I would like to be able to feed in the name of the column to be sorted(it is
'fieldid' below) and the name of the database(it is 'tblsamples' below). I
am having trouble in figuring out how to implement/create a function which
would act like this
select * from tblsamples
order by naturalsort('fieldid', 'tblsamples')
Thanks a lot. Here is my query:
select TOP 100 fieldid as 'sortcolumn',
LEFT(fieldid, PATINDEX('%[0-9]%', fieldid) - 1) as 'a',
CASE
WHEN right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ) not
like '%[a-z,-]%'
then CAST(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )
AS INT)
else
cast (
left (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
case
when patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) = 0
then patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 ))
else patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) -1
end
)
as int)
end
as 'b',
LEFT (
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
,
CASE
WHEN PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) = 0
THEN
LEN( right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
)
ELSE PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) -1
END
)
as 'c',
CASE
WHEN right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) not like '%[a-z,-]%'
then CAST(right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) AS INT)
else
cast (
left (
right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ),
case
when patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) = 0
then patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ))
else patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) -1
end
)
as int)
end
as 'd'
from tblsamples
order by a,b,c,d
create table tblsamples>> I would like to be able to feed in the name of the column to be sorted(it
Such generic sorting attempts seems to offer nothing beneficial and
introduce unwanted complexity. Write clean queries with simple ORDER BY
clauses. If you have to come up with extra-ordinary string parsing routines
for an ORDER BY clause, perhaps you should reconsider your schema design --
it is quite possible that during logical design, multiple values are jammed
into a single column, due to faulty analysis of the business model.
What exactly are you trying to do? Do you have similarly structured tables?
If so, what is the sorting logic? Your script is too complex to understand
what you are trying to accomplish here.
Anith|||I cannot redesign the scema and I have no control over the naming of the
values in the fieldid column. I work in Environmental Consulting, and all o
f
my data is geochemical and/or hydrogeological data from water and/or soil
sampling. Whatever the field person writes down for a fieldid on the chain
of custody MUST be the value in the database. So, usually I get a set of
wells like this:
mw-1
mw-10
mw-2
mw-3
but, I want a natural order on these sampling locations, i.e.
mw-1
mw-2
mw-3
mw-10
To further complicate things, sometimes they use a dash, sometimes they
dont, sometimes these use emw, not mw, etc. etc.
What I am trying to do is sort text string with numbers and characters the
way a human. So, I am parsing the string into chunks, text chunk, number
chunk, text chunk, number chunk, to achieve a natural alphanumeric order.
Thanks for the help.
Archer
"Anith Sen" wrote:

>
> Such generic sorting attempts seems to offer nothing beneficial and
> introduce unwanted complexity. Write clean queries with simple ORDER BY
> clauses. If you have to come up with extra-ordinary string parsing routine
s
> for an ORDER BY clause, perhaps you should reconsider your schema design -
-
> it is quite possible that during logical design, multiple values are jamme
d
> into a single column, due to faulty analysis of the business model.
> What exactly are you trying to do? Do you have similarly structured tables
?
> If so, what is the sorting logic? Your script is too complex to understand
> what you are trying to accomplish here.
> --
> Anith
>
>|||If the numeric portion is always at the end of the string, then you can use
the following expression to extract them.
CAST( RIGHT( c, PATINDEX( '%[^0-9]%', REVERSE( c ) ) - 1 ) AS INT )
Anith

Tuesday, March 27, 2012

creating alerts

Creating Access tables in SQL

Sunday, March 25, 2012

creating a udf

creating a table with select into

Thursday, March 22, 2012

Creating a table inside a stored procedure

Creating a table in Access from an SSIS package

Creating a sum for the year

Creating a Sum for the Year

Wednesday, March 21, 2012

Creating a SQLExecute function in c#

Creating a sequential number in a column.

Creating a sequential number in a column.

Creating a sequential number in a column.

Monday, March 19, 2012

Creating a report

creating a query sql

Creating a Query for Notification

Creating a Query for Notification

Sunday, March 11, 2012

Creating a new database

Creating a new database

Thursday, March 8, 2012

creating a function to be used in select query

creating a function for natural alphanumeric sorting

Creating DB from scripts

Blog Archive

About Me