Hi,
I am getting an error message when I try to make an already existing,
unique index, clustered. I have other databases with the same core
design, some will allow the clustered index to be created, others
display the same error. Could anyone tell me what the error message is
refering to?
Details are:
SQL2000 SP3
The column is of data type varchar.
I am using Enterprise Manager and trying to change the index through the
design - properties window.
There are no nulls in this column.
There are relationships with six other tables, all bound to this column.
Enforce relationship for replication and Enforce relationship for
INSERTs and UPDATEs are checked.
Error Message:
'Tracks' table
- Unable to create index 'PK_Tracks'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
[Microsoft][ODBC SQL Server Driver][SQL Server]Location:
statisti.cpp:3222
Expression: cbStmtTxtLen/sizeof(WCHAR) <= cwchStmtTxtLen
SPID: 51
Process ID: 828
Many thanks
Gareth KingGareth King (QBVBKADUOIUJ@.spammotel.com) writes:
> I am getting an error message when I try to make an already existing,
> unique index, clustered. I have other databases with the same core
> design, some will allow the clustered index to be created, others
> display the same error. Could anyone tell me what the error message is
> refering to?
> Details are:
> SQL2000 SP3
> The column is of data type varchar.
> I am using Enterprise Manager and trying to change the index through the
> design - properties window.
> There are no nulls in this column.
> There are relationships with six other tables, all bound to this column.
> Enforce relationship for replication and Enforce relationship for
> INSERTs and UPDATEs are checked.
> Error Message:
> 'Tracks' table
> - Unable to create index 'PK_Tracks'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> [Microsoft][ODBC SQL Server Driver][SQL Server]Location:
> statisti.cpp:3222
> Expression: cbStmtTxtLen/sizeof(WCHAR) <= cwchStmtTxtLen
> SPID: 51
> Process ID: 828
That seems like an assertion error. An assertion error is a check
that the programmer adds to his code, to be sure that some condition
is true at this point. If they are not, he aborts, because if he
continued he could cause a big mess.
Or more concisely: this is a bug in SQL Server.
What you could try if you want to create this index as quick as
possible, is to configure SQL Server to only use one single processor
and then run the CREATE INDEX statement. These errors are often due
to problems with parallelism.
If that does not work out, I would suggest that you open a case with
Microsoft. Since this is a bug in SQL Server, any expenses you may
be charged initially, should be refunded.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Thursday, March 29, 2012
create app by VB.net 2003 and connect to SQL XE
Hi ,
Would it be available to connect from VB.net 2003 to SQL Server XE? VB.net 2003 works on .net framework1.1 while SQL XE works on .net framwork 2.0 so i think this couldn't be done!!
is this true?
yes this CAN/WILL work. I am assuming the real question here is having both sql & the VB03' app running on the same box. In which case the answer is YES it will work. You can have multiple versions of the Framework installed site-by-side, one of the great many benefits that .Net gives us :)sql
create another query
hi , I new in sql reporting services.
I have a report with a query . now for every record it printed I need to create another query to another table and to some calc and print the value . how do I do this .
I have a report with a query . now for every record it printed I need to create another query to another table and to some calc and print the value . how do I do this .
in other reporting program that I used before, they allow me to create multi body report. each body report allow me to defined a query .
thks
There are many options available in RS. You have the ability to have multiple datasets which can be used within the same report. You also have the ability to use sub-reports and linked reports. Could you be a little more specific with what you are trying to do?|||can you point me to example how to do multiple dataset in one report?
let me give example what I try to do with a report
AAA> mean result from default query
BBB> mean result from another query
-- header
Report Listing
--Body-
student ID : AAAAAAAAAAAAAAAA
student name : AAAAAAAAAAAAAA
Family
BBBBBBBBBB
BBBBBBBBBB
BBBBBBBBBB
Classs : AAAAAAAAAAA
group : AAAAAAAAAAAA
-footer-
thks
|||
http://msdn2.microsoft.com/en-us/library/ms156288.aspx
This might help.
Create and then USE a dynamically-named database?
I have a need to create a database, and then populate it. However, the
code below doesn't work as I hoped it might (it creates the table in
the "master" database, which is Not A Good Thing). I know already
(thanks Tony!) that if you use Dynamic SQL for the USE command, then
the subsequent operations need to be Dynamic SQL as well, which is a
pity since there are over 11,000 lines of it and I don't really fancy
debugging it!
Well, if you are creating databases on the fly then there is probably
some aspect of your design which needs to be relooked.
>
>
Well, if you are creating databases on the fly then there is probably
some aspect of your design which needs to be relooked.
>
Basically the USE clause is only in effect for as long as the EXEC
procedure is in scope, so you'll need to place all the statements in
the EXEC procedure, but I would rather recommend you pop this into a
stored procedure in a static database and simply pass the stored
procedure and the DBName as a parameter , e.g.: EXEC('sp_dostuff ' +
@.DBName). Anyway, here's how could've done it. Hope this helps:
>
USE MASTER
>
>
DECLARE @.DBName VARCHAR(123)
>
>
SET @.DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)
>
>
if not exists(select dbid from master.dbo.sysdatabases where name =
@.DBName)
>
>
exec('CREATE DATABASE ' + @.DBName)
>
>
else
>
>
raiserror('Database already exists.',3, 1)
>
>
EXEC ('USE ' + @.DBName +
>
>
' if exists (select * from dbo.sysobjects where id = ' +
'object_id(N''[dbo].[TestTable]'') and OBJECTPROPERTY(id,
N''IsUserTable'') ' +
'= 1) ' +
'drop table [dbo].[TestTable] ' +
>
>
>
'CREATE TABLE [dbo].[TestTable] ( ' +
' [TestID] [int] NOT NULL , ' +
' [Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
' +
') ON [PRIMARY] '
)
>
Thanks Louis. I know your solution will work, but my script has
11,000+ lines and trying to get that to parse and run in Dynamic SQL is
not on. In conclusion, I don't think this is a candidate for an
automated job so I'll tell the client that I'll create the archive
manually, or give them the tools to do it.
Well, if you are creating databases on the fly then there is probably
some aspect of your design which needs to be relooked.
>
Thanks Louis. I know your solution will work, but my script has
11,000+ lines and trying to get that to parse and run in Dynamic SQL is
not on. In conclusion, I don't think this is a candidate for an
automated job so I'll tell the client that I'll create the archive
manually, or give them the tools to do it.
>
Thanks
>
Edward
If this is always happening on a particular instance, could you break
your script down into 4 or 5 steps of an SQL job? Then, after creating
your new DB, you'd just sp_update_jobstep the steps to point to the new
database and then start it? (If multiple runs are possible, you'd also
need to have some way of locking until the job has finished. If always
started from the same DB, then an applock would work)
code below doesn't work as I hoped it might (it creates the table in
the "master" database, which is Not A Good Thing). I know already
(thanks Tony!) that if you use Dynamic SQL for the USE command, then
the subsequent operations need to be Dynamic SQL as well, which is a
pity since there are over 11,000 lines of it and I don't really fancy
debugging it!
Does anyone have a cunning plan? In a nutshell, I would like to be
able to:
1. Create a new database with a derived name - as in the case below a
name based on the year, though it might be month as well.
2. Create and populate tables in this new database.
These operations would ideally be running from a scheduled job.
Any thoughts?
TIA
Edward
====================================
USE MASTER
DECLARE @.DBName VARCHAR(123)
SET @.DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)
if not exists(select dbid from master.dbo.sysdatabases where name =
@.DBName)
exec('CREATE DATABASE ' + @.DBName)
else
raiserror('Database already exists.',3, 1)
EXEC ('USE ' + @.DBName)
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[TestTable]
GO
CREATE TABLE [dbo].[TestTable] (
[TestID] [int] NOT NULL ,
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Quote:
Originally Posted by
Does anyone have a cunning plan? In a nutshell, I would like to be
able to:
>
1. Create a new database with a derived name - as in the case below a
name based on the year, though it might be month as well.
>
2. Create and populate tables in this new database.
>
Well, if you are creating databases on the fly then there is probably
some aspect of your design which needs to be relooked.
Basically the USE clause is only in effect for as long as the EXEC
procedure is in scope, so you'll need to place all the statements in
the EXEC procedure, but I would rather recommend you pop this into a
stored procedure in a static database and simply pass the stored
procedure and the DBName as a parameter , e.g.: EXEC('sp_dostuff ' +
@.DBName). Anyway, here's how could've done it. Hope this helps:
USE MASTER
DECLARE @.DBName VARCHAR(123)
SET @.DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)
if not exists(select dbid from master.dbo.sysdatabases where name =
@.DBName)
exec('CREATE DATABASE ' + @.DBName)
else
raiserror('Database already exists.',3, 1)
EXEC ('USE ' + @.DBName +
' if exists (select * from dbo.sysobjects where id = ' +
'object_id(N''[dbo].[TestTable]'') and OBJECTPROPERTY(id,
N''IsUserTable'') ' +
'= 1) ' +
'drop table [dbo].[TestTable] ' +
'CREATE TABLE [dbo].[TestTable] ( ' +
' [TestID] [int] NOT NULL , ' +
' [Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
' +
') ON [PRIMARY] '
)
Regards,
Louis|||louisyoung187@.hotmail.com wrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
Does anyone have a cunning plan? In a nutshell, I would like to be
able to:
1. Create a new database with a derived name - as in the case below a
name based on the year, though it might be month as well.
2. Create and populate tables in this new database.
>
>
Well, if you are creating databases on the fly then there is probably
some aspect of your design which needs to be relooked.
>
Basically the USE clause is only in effect for as long as the EXEC
procedure is in scope, so you'll need to place all the statements in
the EXEC procedure, but I would rather recommend you pop this into a
stored procedure in a static database and simply pass the stored
procedure and the DBName as a parameter , e.g.: EXEC('sp_dostuff ' +
@.DBName). Anyway, here's how could've done it. Hope this helps:
>
USE MASTER
>
>
DECLARE @.DBName VARCHAR(123)
>
>
SET @.DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)
>
>
if not exists(select dbid from master.dbo.sysdatabases where name =
@.DBName)
>
>
exec('CREATE DATABASE ' + @.DBName)
>
>
else
>
>
raiserror('Database already exists.',3, 1)
>
>
EXEC ('USE ' + @.DBName +
>
>
' if exists (select * from dbo.sysobjects where id = ' +
'object_id(N''[dbo].[TestTable]'') and OBJECTPROPERTY(id,
N''IsUserTable'') ' +
'= 1) ' +
'drop table [dbo].[TestTable] ' +
>
>
>
'CREATE TABLE [dbo].[TestTable] ( ' +
' [TestID] [int] NOT NULL , ' +
' [Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
' +
') ON [PRIMARY] '
)
>
Thanks Louis. I know your solution will work, but my script has
11,000+ lines and trying to get that to parse and run in Dynamic SQL is
not on. In conclusion, I don't think this is a candidate for an
automated job so I'll tell the client that I'll create the archive
manually, or give them the tools to do it.
Thanks
Edward|||teddysnips@.hotmail.com wrote:
Quote:
Originally Posted by
louisyoung187@.hotmail.com wrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
Does anyone have a cunning plan? In a nutshell, I would like to be
able to:
>
1. Create a new database with a derived name - as in the case below a
name based on the year, though it might be month as well.
>
2. Create and populate tables in this new database.
>
Well, if you are creating databases on the fly then there is probably
some aspect of your design which needs to be relooked.
Basically the USE clause is only in effect for as long as the EXEC
procedure is in scope, so you'll need to place all the statements in
the EXEC procedure, but I would rather recommend you pop this into a
stored procedure in a static database and simply pass the stored
procedure and the DBName as a parameter , e.g.: EXEC('sp_dostuff ' +
@.DBName). Anyway, here's how could've done it. Hope this helps:
USE MASTER
DECLARE @.DBName VARCHAR(123)
SET @.DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)
if not exists(select dbid from master.dbo.sysdatabases where name =
@.DBName)
exec('CREATE DATABASE ' + @.DBName)
else
raiserror('Database already exists.',3, 1)
EXEC ('USE ' + @.DBName +
' if exists (select * from dbo.sysobjects where id = ' +
'object_id(N''[dbo].[TestTable]'') and OBJECTPROPERTY(id,
N''IsUserTable'') ' +
'= 1) ' +
'drop table [dbo].[TestTable] ' +
'CREATE TABLE [dbo].[TestTable] ( ' +
' [TestID] [int] NOT NULL , ' +
' [Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
' +
') ON [PRIMARY] '
)
>
Thanks Louis. I know your solution will work, but my script has
11,000+ lines and trying to get that to parse and run in Dynamic SQL is
not on. In conclusion, I don't think this is a candidate for an
automated job so I'll tell the client that I'll create the archive
manually, or give them the tools to do it.
>
Thanks
>
Edward
If this is always happening on a particular instance, could you break
your script down into 4 or 5 steps of an SQL job? Then, after creating
your new DB, you'd just sp_update_jobstep the steps to point to the new
database and then start it? (If multiple runs are possible, you'd also
need to have some way of locking until the job has finished. If always
started from the same DB, then an applock would work)
If the above doesn't help, then some more clues about whether we're
talking about 1, or n, or an unlimited number of instances and/or
databases (from which you kick this process off), or whether you're
kicking this process off through some other means - is it a stored
proc, and job, etc?
Damien
Create and manage Store Procedures from inside VS
With a local conn to SQL2000 I can edit sprocs no problem, but if I change the connection to a remote computer (to which I have all permissions) I cannot create or manage stored procedures from within VS IDE? Is there a work around?
I noticed that a procedure written for the localhost accepts CREATE PROCEDURE and changes it to ALTER PROCEDURE. When I script the sprocs from my development machine to the server, those scripts with ALTER in them do not work. I change them to CREATE and they work fine.
I haven't found anything yet on MSDN about this, but will continue to look.
Thanks in advance,
_EHi _E
You may have better luck with this question in the SQL Server Tools - General forum.
Allan|||Thanks, I'll try there. Didn't know if it was an IDE or SQL thing.
_E
Create and Edit flat file from Sqlserver (SP).
HI All,
I use sqlserver2000 server.
I have a requirement to create a flat file(.txt) and dump the data into the file with some formatting.
I tried to use DTS but,
1. it doesnt allow me to put one row information of table to multiple line in the flat file.
2. Dynamically we cannot create n number of files we need from DTS.
Now, i am trying to create a file from sql server Stored Procedure and write data into it.
Can any one help me..
How to create and write to file from sqlserver (sp) .
Regards
Abdul lateef.Originally posted by a_lateef
I tried to use DTS but,
1. it doesnt allow me to put one row information of table to multiple line in the flat file.
2. Dynamically we cannot create n number of files we need from DTS.
Maybe if you add a ActiveX script step in the DTS package. Hope someone else can explain further...|||Greetings!
I personally would just write a script (pick a language of choice) to pull the data from the server, format it and then write it to a textfile. Although there are ways to do it in SQL Server it is very cumbersome and I think you will find that just about any other language will offer a lot more flexibility in this case. If you use something like a Visual Basic Script file you could then just set it up as a job to run when it was supposed to.
Maybe someone else could offer something else on this, good luck!
HTH!|||I am unclear as to what you want in the text file - could you explain in detail (what are your requirements) - maybe with an example.
I use sqlserver2000 server.
I have a requirement to create a flat file(.txt) and dump the data into the file with some formatting.
I tried to use DTS but,
1. it doesnt allow me to put one row information of table to multiple line in the flat file.
2. Dynamically we cannot create n number of files we need from DTS.
Now, i am trying to create a file from sql server Stored Procedure and write data into it.
Can any one help me..
How to create and write to file from sqlserver (sp) .
Regards
Abdul lateef.Originally posted by a_lateef
I tried to use DTS but,
1. it doesnt allow me to put one row information of table to multiple line in the flat file.
2. Dynamically we cannot create n number of files we need from DTS.
Maybe if you add a ActiveX script step in the DTS package. Hope someone else can explain further...|||Greetings!
I personally would just write a script (pick a language of choice) to pull the data from the server, format it and then write it to a textfile. Although there are ways to do it in SQL Server it is very cumbersome and I think you will find that just about any other language will offer a lot more flexibility in this case. If you use something like a Visual Basic Script file you could then just set it up as a job to run when it was supposed to.
Maybe someone else could offer something else on this, good luck!
HTH!|||I am unclear as to what you want in the text file - could you explain in detail (what are your requirements) - maybe with an example.
create and drop table via vb
Hi,
How can i create and drop table in MS SQL Server 2000 via VB6? I think I should use ADOX object, but I don't know exactly how...
The following code uses ADO connection object and returns with runtime error "incorrect syntax near AS":
Dim db as ADODB.Connection
'... open connection to database
Dim strCmd As String
strCmd = "CREATE TABLE tmp_tbl AS SELECT * FROM tbl"
db.Execute strCmd
Thank you in advanceThe code you are using works on an Oracle database. The syntax for SQL Server is "SELECT...INTO...FROM".
SELECT * INTO newTable FROM oldTablesql
How can i create and drop table in MS SQL Server 2000 via VB6? I think I should use ADOX object, but I don't know exactly how...
The following code uses ADO connection object and returns with runtime error "incorrect syntax near AS":
Dim db as ADODB.Connection
'... open connection to database
Dim strCmd As String
strCmd = "CREATE TABLE tmp_tbl AS SELECT * FROM tbl"
db.Execute strCmd
Thank you in advanceThe code you are using works on an Oracle database. The syntax for SQL Server is "SELECT...INTO...FROM".
SELECT * INTO newTable FROM oldTablesql
create and attach data base
Hi, i want to create a database with out the LOG file I did not find a way to do that, I want to create database in 2 ways
1. With CREATE DATABASE command.
2.Attach an exsiting MDF file.
is there any way i can do that, my problem is that i'm creating the data base on my local computer the using sp_detach_db to detach the DB files, then trying to attach DB files from remote computer on the LAN that i dont have write premmsion on, but then i get an error from SQL server saying:
"[Microsoft][SQL Native Client][SQL Server]Unable to open the physical file "\\lab11\sqltest\pppSignaling_log.ldf". Operating system error 5: "5(Access is denied.)"."
what can i do to solve this problem?
thanks ishay
More about storage in network path in SQL Server can be found here:http://support.microsoft.com/kb/304261/en-us
Generally spoken, this is not supported.
HTH, Jens Suessmeyer.
http://www.slqserver2005.de
Create and Administer SQL Server Agent Jobs via Sprocs?
Hi,
Does anyone know if it is possible to set up stored procedures which will create, modify, and disable SQL Server Agent Jobs?
I have a web based application which I need to enable an administrator to change the frequency which a job runs and encapsulating the modifications in a stored procedure would be the easiest way for me to integrate this, if it's possbible.
Regards, MattHi,
You may use sp_update_jobschedule, sp_update_job etc or if you create your own sp and use tables such as sysjobschedules, sysjobs etc.
Tables and procs are located in MSDB.
Ex for updating scheduled time:
UPDATE msdb.dbo.sysjobschedules
SET active_start_time = 164000
WHERE (job_id = '8A0F1080-D22A-4F82-AE13-68F789989D1D')
AND (name = 'Once')
Try this and let us know how it work
Regards|||Thanks Tommy,
Going to give this a shot this afternoon. I'll let you know how it turns out.
Does anyone know if it is possible to set up stored procedures which will create, modify, and disable SQL Server Agent Jobs?
I have a web based application which I need to enable an administrator to change the frequency which a job runs and encapsulating the modifications in a stored procedure would be the easiest way for me to integrate this, if it's possbible.
Regards, MattHi,
You may use sp_update_jobschedule, sp_update_job etc or if you create your own sp and use tables such as sysjobschedules, sysjobs etc.
Tables and procs are located in MSDB.
Ex for updating scheduled time:
UPDATE msdb.dbo.sysjobschedules
SET active_start_time = 164000
WHERE (job_id = '8A0F1080-D22A-4F82-AE13-68F789989D1D')
AND (name = 'Once')
Try this and let us know how it work
Regards|||Thanks Tommy,
Going to give this a shot this afternoon. I'll let you know how it turns out.
Create and access database through ftp
I have to create a website and a database, but I 've only access for the server by ftp.
Is there a possibility to create and run some kind of sql script automaticly if I've only have access via ftp?!
(Using sqlserver 2000)
tia john
No, to the best of my knowledge you cannot run a SQL script solely through FTP. Surely your ISP must give you some sort of interface for creating tables?Create an XML Schema for a SQL Database structure
Hey,
I was wondering does anyone know a way to get the structure, with relationships, of a Sql Database and generate a XML Schema. I want to use the Schema to build a CrystalReport.
Thanks!
-KevinCheck this artilce;
Retrieving Objects from SQL Server Using SQLXML and Serialization
http://www.15seconds.com/issue/040713.htm
Create an XML file from a stored procedure
I'm trying to create a temp xml file to to convert it eventually into an excel file.
using C#
I am calling a stored procedure and from that data retrieved I want to write it in xml format.
I tried using the xmlTextWriter.. but I get an exception, it refers to a token..(?)
I am new to using xml, so any help would be great..
Can I write an xml file by using a streamWriter or writing all bytes?
Thanks,
kt
You haven't mentioned if you are using SQL Server 2000 or 2005.If its 2000, then check the FOR XML {AUTO, RAW, EXPLICIT} syntax in books online.
If its 2005 you might want to use the FOR XML PATH.
These will return for you the results in the XML format you want and you might not really need to format it manually using C#.
Hope that helps!
|||
I'm using SQL Server 2005...
You wouldn't possibly know any good resources on formatting.. for using "FOR XML PATH"..?
sqlCreate an XML file from a stored procedure
I'm trying to create a temp xml file to to convert it eventually into an excel file.
using C#
I am calling a stored procedure and from that data retrieved I want to write it in xml format.
I tried using the xmlTextWriter.. but I get an exception, it refers to a token..(?)
I am new to using xml, so any help would be great..
Can I write an xml file by using a streamWriter or writing all bytes?
Thanks,
kt
You haven't mentioned if you are using SQL Server 2000 or 2005.If its 2000, then check the FOR XML {AUTO, RAW, EXPLICIT} syntax in books online.
If its 2005 you might want to use the FOR XML PATH.
These will return for you the results in the XML format you want and you might not really need to format it manually using C#.
Hope that helps!
|||
I'm using SQL Server 2005...
You wouldn't possibly know any good resources on formatting.. for using "FOR XML PATH"..?
Create an SQL View from VBA
Hello All;
I'm new to SQL, and developing my first application using SQL 2000 as the back end for my data.
Can anyone tell me if I can create an SQL View from VBA?
I'm trying to create a data view that access various records from a single table, and it has to distribute that data 2 14 different subforms, representing different days in a 2 week period, which is distingiushed by a field called "Day", that is numbered from 1 to 14.
I also have a summary subform that has the weekly summary (days 1 to 7 and 8 to 14) on seperate subforms. In total I have 16 subforms, which actually source from a single table, just split depending on the day in the period.
As I see it now, creating views iis the best way for me to go, but I also have to be able to change the period id on the fly, so I'm think I have to use VBA to generate these views.
Does any of this make sense, and am I on the right track??You might want to consider a more dynamic solution. Do the 14 forms all hold the same data fields? If so, why not use one form and base the contents on the day of a week. Make the day of the week a field in your table and populate your form based on a stored procedure that uses the day of the week as a parameter in the query.
Fixing your schema now will pay you back many fold in the future.
Avoid creating database objects on the fly in end user applications.
To answer your question, yes this is possible. Is it a good idea? No.|||... populate your form based on a stored procedure that uses the day of the week as a parameter in the query.
Fixing your schema now will pay you back many fold in the future.
Avoid creating database objects on the fly in end user applications.
To answer your question, yes this is possible. Is it a good idea? No.
Agreed with all the points here. You might also consider a user defined function that returns a table. I don't generally use these for multi-column result sets, but it is permissible to do so.
Perhaps you could post some ddl and sample data and improve your chance for getting a useable answer...
Regards,
hmscott
I'm new to SQL, and developing my first application using SQL 2000 as the back end for my data.
Can anyone tell me if I can create an SQL View from VBA?
I'm trying to create a data view that access various records from a single table, and it has to distribute that data 2 14 different subforms, representing different days in a 2 week period, which is distingiushed by a field called "Day", that is numbered from 1 to 14.
I also have a summary subform that has the weekly summary (days 1 to 7 and 8 to 14) on seperate subforms. In total I have 16 subforms, which actually source from a single table, just split depending on the day in the period.
As I see it now, creating views iis the best way for me to go, but I also have to be able to change the period id on the fly, so I'm think I have to use VBA to generate these views.
Does any of this make sense, and am I on the right track??You might want to consider a more dynamic solution. Do the 14 forms all hold the same data fields? If so, why not use one form and base the contents on the day of a week. Make the day of the week a field in your table and populate your form based on a stored procedure that uses the day of the week as a parameter in the query.
Fixing your schema now will pay you back many fold in the future.
Avoid creating database objects on the fly in end user applications.
To answer your question, yes this is possible. Is it a good idea? No.|||... populate your form based on a stored procedure that uses the day of the week as a parameter in the query.
Fixing your schema now will pay you back many fold in the future.
Avoid creating database objects on the fly in end user applications.
To answer your question, yes this is possible. Is it a good idea? No.
Agreed with all the points here. You might also consider a user defined function that returns a table. I don't generally use these for multi-column result sets, but it is permissible to do so.
Perhaps you could post some ddl and sample data and improve your chance for getting a useable answer...
Regards,
hmscott
Create an instance of SQL 2005
Dear friends,
I deleted the instance of SQL 2005, and now I want to create and I dont know how! Colud you tell me?
Thanks.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
create an installer for sql server 2005 db
Can anyone tell how can I create an installer for sql server database.
What do you mean with an installer for sql databases?
|||The two popular methods to package a database are:
1. as empty database backup file
2. as a t-sql script
Microsoft actually employs the second method for their database deployment. You can take a look at "C:\Program Files\Microsoft SQL Server\MSSQL\Install\instnwnd.sql" file for an example of Northwind database delivery.
|||I am desigining a small database for my application. The users who use my application, I want to let them install the sql database too. Just like .msi packages u have for web applications, where you get the gui to install the application, I am wondering if you can do the same way or some other better approach to do this.
|||Take a look at sqlexpress redistribution.
http://www.microsoft.com/sql/editions/express/redistregister.mspx
create an installer for sql server 2005 db
Can anyone tell how can I create an installer for sql server database.
What do you mean with an installer for sql databases?
|||The two popular methods to package a database are:
1. as empty database backup file
2. as a t-sql script
Microsoft actually employs the second method for their database deployment. You can take a look at "C:\Program Files\Microsoft SQL Server\MSSQL\Install\instnwnd.sql" file for an example of Northwind database delivery.
|||I am desigining a small database for my application. The users who use my application, I want to let them install the sql database too. Just like .msi packages u have for web applications, where you get the gui to install the application, I am wondering if you can do the same way or some other better approach to do this.
|||Take a look at sqlexpress redistribution.
http://www.microsoft.com/sql/editions/express/redistregister.mspx
sqlcreate an Indexes/Keys Property with T-SQL
is there a function that i can use in a store procedure that allow me to create an Indexes/Keys Property
thanxYes. CREATE INDEX.
-PatP|||Yes. CREATE INDEX.
-PatP
excuse me while I climb back on my barstool...um office chair...
LNHockey...seriously though... alittle more background on what you're trying to do...
"create index"....why I outta.....|||Yes ok..excuse me.
i do this
<b>
ALTER TABLE TblSalle ADD [IdTypeTaxe] [int] NOT NULL default(0)<br>
CREATE INDEX PK_TblSalle ON TblSalle (IdTypeTaxe)</b>
and would like to assign is "Selected index value" to IX_IdTypeTaxe that i userly have in a combobox when i use the sql manager
but i want to do it in a store proc..
or how can i modify that value after i add the new column to my table|||Yup...I'm lost..
Huh?|||You know when you go to design mode of the table and go properties of the selected column and select the tab "Indexes/keys". under that you can switch the type "Primary key to Index" ?? !!!
just wondering if we can do the same thing in a Store proc using a function kind of thing
thanx !
thanxYes. CREATE INDEX.
-PatP|||Yes. CREATE INDEX.
-PatP
excuse me while I climb back on my barstool...um office chair...
LNHockey...seriously though... alittle more background on what you're trying to do...
"create index"....why I outta.....|||Yes ok..excuse me.
i do this
<b>
ALTER TABLE TblSalle ADD [IdTypeTaxe] [int] NOT NULL default(0)<br>
CREATE INDEX PK_TblSalle ON TblSalle (IdTypeTaxe)</b>
and would like to assign is "Selected index value" to IX_IdTypeTaxe that i userly have in a combobox when i use the sql manager
but i want to do it in a store proc..
or how can i modify that value after i add the new column to my table|||Yup...I'm lost..
Huh?|||You know when you go to design mode of the table and go properties of the selected column and select the tab "Indexes/keys". under that you can switch the type "Primary key to Index" ?? !!!
just wondering if we can do the same thing in a Store proc using a function kind of thing
thanx !
CREATE an INDEX when using CREATE TABLE?
Is there a way to create a index with create table when it is not a primary
key or unique?
Please see below, to see where I am getting lost.
I can create the index using the code below, but I can not see how to create
the index using CREATE TABLE.
drop table customer
-- Customer Table
CREATE TABLE customer
( personID int not null,
userID varchar(10) null
-- cust_userID_ind NONCLUSTERED (userid)
-- CONSTRAINT cust_userID_ind NONCLUSTERED (userid)
,
since smalldatetime not null,
notes varchar(1000) null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
CONSTRAINT PK_cust_personID PRIMARY KEY CLUSTERED(personID),
-- CONSTRAINT cust_userID_ind NONCLUSTERED (userid),
CONSTRAINT FK_cust_personID FOREIGN KEY (personID) REFERENCES
person(personID),
CONSTRAINT FK_cust_updateby FOREIGN KEY (updateby) REFERENCES
users(userID)
)
go
CREATE INDEX cust_userID_ind
ON customer (userID)
goHi,
Only the Indexes created based on the constraints (PRIMARY AND UNIQUE) will
be created using a
Create Table command.
Other Indexes needs to be created using CREATE INDEX command.
Thanks
Hari
SQL Server MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHs5M65uFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Is there a way to create a index with create table when it is not a
> primary key or unique?
> Please see below, to see where I am getting lost.
> I can create the index using the code below, but I can not see how to
> create the index using CREATE TABLE.
> drop table customer
> -- Customer Table
> CREATE TABLE customer
> ( personID int not null,
> userID varchar(10) null
> -- cust_userID_ind NONCLUSTERED (userid)
> -- CONSTRAINT cust_userID_ind NONCLUSTERED (userid)
> ,
> since smalldatetime not null,
> notes varchar(1000) null,
> lastupdate smalldatetime not null,
> updateby varchar(10) not null,
> CONSTRAINT PK_cust_personID PRIMARY KEY CLUSTERED(personID),
> -- CONSTRAINT cust_userID_ind NONCLUSTERED (userid),
> CONSTRAINT FK_cust_personID FOREIGN KEY (personID) REFERENCES
> person(personID),
> CONSTRAINT FK_cust_updateby FOREIGN KEY (updateby) REFERENCES
> users(userID)
> )
> go
> CREATE INDEX cust_userID_ind
> ON customer (userID)
> go
>|||Thank you
"Hari Pra
" <hari_pra_k@.hotmail.com> wrote in message
news:uWYaqA6uFHA.740@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Only the Indexes created based on the constraints (PRIMARY AND UNIQUE)
> will be created using a
> Create Table command.
> Other Indexes needs to be created using CREATE INDEX command.
> Thanks
> Hari
> SQL Server MVP
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uHs5M65uFHA.2312@.TK2MSFTNGP14.phx.gbl...
>
key or unique?
Please see below, to see where I am getting lost.
I can create the index using the code below, but I can not see how to create
the index using CREATE TABLE.
drop table customer
-- Customer Table
CREATE TABLE customer
( personID int not null,
userID varchar(10) null
-- cust_userID_ind NONCLUSTERED (userid)
-- CONSTRAINT cust_userID_ind NONCLUSTERED (userid)
,
since smalldatetime not null,
notes varchar(1000) null,
lastupdate smalldatetime not null,
updateby varchar(10) not null,
CONSTRAINT PK_cust_personID PRIMARY KEY CLUSTERED(personID),
-- CONSTRAINT cust_userID_ind NONCLUSTERED (userid),
CONSTRAINT FK_cust_personID FOREIGN KEY (personID) REFERENCES
person(personID),
CONSTRAINT FK_cust_updateby FOREIGN KEY (updateby) REFERENCES
users(userID)
)
go
CREATE INDEX cust_userID_ind
ON customer (userID)
goHi,
Only the Indexes created based on the constraints (PRIMARY AND UNIQUE) will
be created using a
Create Table command.
Other Indexes needs to be created using CREATE INDEX command.
Thanks
Hari
SQL Server MVP
"DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
news:uHs5M65uFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Is there a way to create a index with create table when it is not a
> primary key or unique?
> Please see below, to see where I am getting lost.
> I can create the index using the code below, but I can not see how to
> create the index using CREATE TABLE.
> drop table customer
> -- Customer Table
> CREATE TABLE customer
> ( personID int not null,
> userID varchar(10) null
> -- cust_userID_ind NONCLUSTERED (userid)
> -- CONSTRAINT cust_userID_ind NONCLUSTERED (userid)
> ,
> since smalldatetime not null,
> notes varchar(1000) null,
> lastupdate smalldatetime not null,
> updateby varchar(10) not null,
> CONSTRAINT PK_cust_personID PRIMARY KEY CLUSTERED(personID),
> -- CONSTRAINT cust_userID_ind NONCLUSTERED (userid),
> CONSTRAINT FK_cust_personID FOREIGN KEY (personID) REFERENCES
> person(personID),
> CONSTRAINT FK_cust_updateby FOREIGN KEY (updateby) REFERENCES
> users(userID)
> )
> go
> CREATE INDEX cust_userID_ind
> ON customer (userID)
> go
>|||Thank you
"Hari Pra
" <hari_pra_k@.hotmail.com> wrote in messagenews:uWYaqA6uFHA.740@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Only the Indexes created based on the constraints (PRIMARY AND UNIQUE)
> will be created using a
> Create Table command.
> Other Indexes needs to be created using CREATE INDEX command.
> Thanks
> Hari
> SQL Server MVP
> "DazedAndConfused" <AceMagoo61@.yahoo.com> wrote in message
> news:uHs5M65uFHA.2312@.TK2MSFTNGP14.phx.gbl...
>
Create an index in a PDF file
Hi!!
I have a report that is exported to a PDF file... Is possible to get an index in the beginning of the file? I have the page number at the bottom of each page but i would need an index...
Thx in advance!!
Any idea? Isn′t possible to do it? At the moment, I havent found anything linked with this...|||Hi Sergio,
Do you mean a document map? Check out this link: http://msdn2.microsoft.com/en-us/library/ms156383.aspx
|||
Hi Brad!!
not exactly... i would like to appear something similar but in a page in the beginning of the PDF document including the page number where each element is, i.e. the tables of the document... is there any option to do this? it′s very similar to the document map but with the page number...
I see two problems:
- I was thinking of using "=Globals.PageNumber" but i can′t, it says me that only can be placed in the header or the footer...
- and the other problem is setting the content of the document map in a page of the PDF... could I do this?
Perhaps, this is a bit difficult but I am always looking for challenges 
... any suggestions? thx in advance!!
Sergio
|||I know of no way to accomplish exactly what you are looking for sorry to say. The document map links to items as repeated on pages. But I am not sure what you mean by "setting the content of the document map in a page of the PDF". If you mean as a page, there is no way to control this.|||Hi Brad,
that was exactly i ment: i want to show it as a page, but if there is no way to do it... anyway, thank you for your help...
Sergio
create an incremental counter in the stored procedure
Hello, I have a following SP
I want to add an extra field "ranking" that just increments the row number.
Another feature would be: if several users have an equal totalvalue, they
should have an equal ranking number. the rankings following users would have
to be adjusted as well. thanks
I want to add an extra field "ranking" that just increments the row number.
Another feature would be: if several users have an equal totalvalue, they
should have an equal ranking number. the rankings following users would have
to be adjusted as well. thanks
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[Rankings]
@.iErrorCode int OUTPUT
AS
SELECT top 30
###COUNTER##,
[user],
[totalvalue], [cash], [stocksvalue]
FROM [dbo].[users]
ORDER BY totalvalue DESC
SELECT @.iErrorCode=@.@.ERROR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOThere is more than one way to rank a set with tied values.
CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2) NOT
NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)
INSERT INTO Users VALUES (101,1010,100,99)
INSERT INTO Users VALUES (102,2020,100,99)
INSERT INTO Users VALUES (103,3030,100,99)
INSERT INTO Users VALUES (104,3030,100,99)
INSERT INTO Users VALUES (105,1002,100,99)
INSERT INTO Users VALUES (106,1002,100,99)
INSERT INTO Users VALUES (107,1002,100,99)
INSERT INTO Users VALUES (108,1002,100,99)
INSERT INTO Users VALUES (109,1000,100,99)
See if this gives the result you expect:
SELECT COUNT(U2.totalvalue)+1 AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue < U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid
Result:
ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
3 102 2020.00 100.00 99.00
4 101 1010.00 100.00 99.00
5 105 1002.00 100.00 99.00
5 106 1002.00 100.00 99.00
5 107 1002.00 100.00 99.00
5 108 1002.00 100.00 99.00
9 109 1000.00 100.00 99.00
(9 row(s) affected)
Or maybe this:
SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue <= U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid
Result:
ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
2 102 2020.00 100.00 99.00
3 101 1010.00 100.00 99.00
4 105 1002.00 100.00 99.00
4 106 1002.00 100.00 99.00
4 107 1002.00 100.00 99.00
4 108 1002.00 100.00 99.00
5 109 1000.00 100.00 99.00
(9 row(s) affected)
--
David Portas
SQL Server MVP
--|||thank you! it works fine.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1pmdnW4hl-GFFt3dRVn-uA@.giganews.com...
> There is more than one way to rank a set with tied values.
> CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2)
NOT
> NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)
> INSERT INTO Users VALUES (101,1010,100,99)
> INSERT INTO Users VALUES (102,2020,100,99)
> INSERT INTO Users VALUES (103,3030,100,99)
> INSERT INTO Users VALUES (104,3030,100,99)
> INSERT INTO Users VALUES (105,1002,100,99)
> INSERT INTO Users VALUES (106,1002,100,99)
> INSERT INTO Users VALUES (107,1002,100,99)
> INSERT INTO Users VALUES (108,1002,100,99)
> INSERT INTO Users VALUES (109,1000,100,99)
> See if this gives the result you expect:
> SELECT COUNT(U2.totalvalue)+1 AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue < U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 3 102 2020.00 100.00 99.00
> 4 101 1010.00 100.00 99.00
> 5 105 1002.00 100.00 99.00
> 5 106 1002.00 100.00 99.00
> 5 107 1002.00 100.00 99.00
> 5 108 1002.00 100.00 99.00
> 9 109 1000.00 100.00 99.00
> (9 row(s) affected)
> Or maybe this:
> SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue <= U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 2 102 2020.00 100.00 99.00
> 3 101 1010.00 100.00 99.00
> 4 105 1002.00 100.00 99.00
> 4 106 1002.00 100.00 99.00
> 4 107 1002.00 100.00 99.00
> 4 108 1002.00 100.00 99.00
> 5 109 1000.00 100.00 99.00
> (9 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --
Create an Excel Sheet with parameters
On rendering Excel, i want to make a new sheet which contains the RS
parameters of the rendering.
So, the end user get an XLS data and the input parameters.
Is there any way to make this "parameters" sheet ?
Thanks
AntoineTry putting your parameters on a rectangle, in the body of the report, not
the header. Then go to the properties on the rectangle, indicate to have a
page break before or after the object. when the report display in your
browser, the first page will just be any headers and your parameters but,
when you export to excel, a separate tab will have been created for your
parameters. For the online view, you probably will want to indicate that the
rectangle should repeat based on your detailed data area. Hope this helps!
--
ChrisinPA
"tonio932" wrote:
> On rendering Excel, i want to make a new sheet which contains the RS
> parameters of the rendering.
> So, the end user get an XLS data and the input parameters.
> Is there any way to make this "parameters" sheet ?
> Thanks
> Antoine
>sql
parameters of the rendering.
So, the end user get an XLS data and the input parameters.
Is there any way to make this "parameters" sheet ?
Thanks
AntoineTry putting your parameters on a rectangle, in the body of the report, not
the header. Then go to the properties on the rectangle, indicate to have a
page break before or after the object. when the report display in your
browser, the first page will just be any headers and your parameters but,
when you export to excel, a separate tab will have been created for your
parameters. For the online view, you probably will want to indicate that the
rectangle should repeat based on your detailed data area. Hope this helps!
--
ChrisinPA
"tonio932" wrote:
> On rendering Excel, i want to make a new sheet which contains the RS
> parameters of the rendering.
> So, the end user get an XLS data and the input parameters.
> Is there any way to make this "parameters" sheet ?
> Thanks
> Antoine
>sql
Create an email link within the report...
Hello...
I have a column of data on my report that contains an email address. Is it possible to format this so that the person running the report can just click on the link and have that action launch their email program...like Outlook?
thanks for any help
- will
Hi Will - Yes, you can use expressions to build "mailto:" URLs, and set these for the "jump to" value in the navigation tab for the field properties.
|||
Thanks for the reply and info...this worked great.
- will
Create an data type with identity(1, 1) property
Hello!
But, an error occurs: don't possible create an data type with identity property....... Correct?
Code Snippet
--I'm trying this command:
create type int8 from tinyint identity(1,1) not null
But, an error occurs: don't possible create an data type with identity property....... Correct?
How to create an data type with identity property? The 'sys.sp_addextendedproperty' SP is the solution? An example, please?
Bye!
The IDENTITY property is linked to a table -as well as a datatype.
CREATE TABLE MyTable
( RowID int IDENTITY,
MyDate varchar(20)
)
You cannot simply CREATE TYPE or DECLARE a variable and also have an IDENTITY property for the type/variable.
|||another example?execute('
select
identity(int,1,1) as id,
sum(trunk.count) as count,
sum(trunk.duration) as duration,
sum(trunk.charge1) as charge1,
trunk.trunk,
trunk.currency
into
#utilisation
from
(
select
count(local.callid) as count,
sum(local.duration) as duration,
sum(isnull(local.charge1,''0'')) as charge1,
local.trunk,
local.currency
from
tt32.dbo.trunk t,
database.dbo.local01 local
where
local.trunk = t.trunk
group by
local.trunk,
local.currency
)trunk
group by
trunk.trunk,
trunk.currency
--showing the result
select
id,
isnull(count,''0'') as count,
duration,
isnull(charge1,''0'') as charge1,
trunk,
currency
from #utilisation
|||: (
Ok. Thanks!
Bye!
Create an automatic SUM ROW
Hello.
In a previous program i used to create reports, I had the option to enter a "SUM ROW".
In a previous program i used to create reports, I had the option to enter a "SUM ROW".
I saw that in RS2005 I can check the "add total" when using the report wizard and it will create for me a row with SUM for my column.
Is there a way to create a row like this automatically with out the wizard (a sum row for all my columns instead of creating a textbox with "sum" for each column)?
Thanks in advance,
Roy.
Hi,
nom this behaviour is influenced by the normal summing / grouping operations. A sum in a row has nothing to do with this. I don′t think that the standard behaviour of the wizrad cab be changed in Reporting Services.
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
I don't want the "standard behaviour" of the wizard to change.
I'll like the "SUM" je is makeing in the entire row.
what i whanted to know is:
if i'm making my own report, not with the wizard, is there a simple way to create a sum row like the wizard with out creating a textbox for every column manually?
Thanks in advance,
Roy.
|||
After making the report with the wizard you can simply use the Expression =SUM(Fields!SomeValueinQuery1.Value + Fields!SomeValueinQuery2.Value + Fields!SomeValueinQuery3.Value +Fields!SomeValueinQuery4.Value) OR use the appropiate ReportItems!Textbox1.Value, but there is no function like in Excel which automatically tries to gues what you want to summarize (even this function in Excel sometime guesses wrong what you want to achieve :-) )
HTH, Jens SUessmeyer.
http://www.sqlserver2005.de
Create an automated routine to check database definitions
Is there any utility, command, or query in SQL Server, that I can execute
from a batch mode to script out all of my database definitions (tables,
columns, sp, functions, indexes, constraints, triggers, etc.) so I can creat
e
an automated routine to check database definitions?
--
Jason"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> Is there any utility, command, or query in SQL Server, that I can execute
> from a batch mode to script out all of my database definitions (tables,
> columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> create
> an automated routine to check database definitions?
I wrote one some time ago, using DMO. I can provide compiled exe or sources
if you want. AFAIR works only with integrated security (kind of bug I never
really needed to fix). Works fine if you need to monitor database (or all
databases) schema.
Cheers,
Wojtek|||Yes, that would be great. Can I get both? I will need to modify to work
with sql authentication.
Thanks,
--
Jason
"Wojtek Garwol" wrote:
> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> I wrote one some time ago, using DMO. I can provide compiled exe or source
s
> if you want. AFAIR works only with integrated security (kind of bug I neve
r
> really needed to fix). Works fine if you need to monitor database (or all
> databases) schema.
> Cheers,
> Wojtek
>
>|||Sure:
http://www.garwol.net/DbCompare.src.zip
http://www.garwol.net/DbCompare.exe.zip
Enjoy
Cheers,
Wojtek
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45287186-E6D8-47E8-8194-DB2523FDF881@.microsoft.com...[vbcol=seagreen]
> Yes, that would be great. Can I get both? I will need to modify to work
> with sql authentication.
> Thanks,
> --
> Jason
>
> "Wojtek Garwol" wrote:
>
from a batch mode to script out all of my database definitions (tables,
columns, sp, functions, indexes, constraints, triggers, etc.) so I can creat
e
an automated routine to check database definitions?
--
Jason"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> Is there any utility, command, or query in SQL Server, that I can execute
> from a batch mode to script out all of my database definitions (tables,
> columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> create
> an automated routine to check database definitions?
I wrote one some time ago, using DMO. I can provide compiled exe or sources
if you want. AFAIR works only with integrated security (kind of bug I never
really needed to fix). Works fine if you need to monitor database (or all
databases) schema.
Cheers,
Wojtek|||Yes, that would be great. Can I get both? I will need to modify to work
with sql authentication.
Thanks,
--
Jason
"Wojtek Garwol" wrote:
> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> I wrote one some time ago, using DMO. I can provide compiled exe or source
s
> if you want. AFAIR works only with integrated security (kind of bug I neve
r
> really needed to fix). Works fine if you need to monitor database (or all
> databases) schema.
> Cheers,
> Wojtek
>
>|||Sure:
http://www.garwol.net/DbCompare.src.zip
http://www.garwol.net/DbCompare.exe.zip
Enjoy
Cheers,
Wojtek
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45287186-E6D8-47E8-8194-DB2523FDF881@.microsoft.com...[vbcol=seagreen]
> Yes, that would be great. Can I get both? I will need to modify to work
> with sql authentication.
> Thanks,
> --
> Jason
>
> "Wojtek Garwol" wrote:
>
Create an automated routine to check database definitions
Is there any utility, command, or query in SQL Server, that I can execute
from a batch mode to script out all of my database definitions (tables,
columns, sp, functions, indexes, constraints, triggers, etc.) so I can create
an automated routine to check database definitions?
Jason
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> Is there any utility, command, or query in SQL Server, that I can execute
> from a batch mode to script out all of my database definitions (tables,
> columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> create
> an automated routine to check database definitions?
I wrote one some time ago, using DMO. I can provide compiled exe or sources
if you want. AFAIR works only with integrated security (kind of bug I never
really needed to fix). Works fine if you need to monitor database (or all
databases) schema.
Cheers,
Wojtek
|||Yes, that would be great. Can I get both? I will need to modify to work
with sql authentication.
Thanks,
Jason
"Wojtek Garwol" wrote:
> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> I wrote one some time ago, using DMO. I can provide compiled exe or sources
> if you want. AFAIR works only with integrated security (kind of bug I never
> really needed to fix). Works fine if you need to monitor database (or all
> databases) schema.
> Cheers,
> Wojtek
>
>
|||Sure:
http://www.garwol.net/DbCompare.src.zip
http://www.garwol.net/DbCompare.exe.zip
Enjoy
Cheers,
Wojtek
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45287186-E6D8-47E8-8194-DB2523FDF881@.microsoft.com...[vbcol=seagreen]
> Yes, that would be great. Can I get both? I will need to modify to work
> with sql authentication.
> Thanks,
> --
> Jason
>
> "Wojtek Garwol" wrote:
sql
from a batch mode to script out all of my database definitions (tables,
columns, sp, functions, indexes, constraints, triggers, etc.) so I can create
an automated routine to check database definitions?
Jason
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> Is there any utility, command, or query in SQL Server, that I can execute
> from a batch mode to script out all of my database definitions (tables,
> columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> create
> an automated routine to check database definitions?
I wrote one some time ago, using DMO. I can provide compiled exe or sources
if you want. AFAIR works only with integrated security (kind of bug I never
really needed to fix). Works fine if you need to monitor database (or all
databases) schema.
Cheers,
Wojtek
|||Yes, that would be great. Can I get both? I will need to modify to work
with sql authentication.
Thanks,
Jason
"Wojtek Garwol" wrote:
> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> I wrote one some time ago, using DMO. I can provide compiled exe or sources
> if you want. AFAIR works only with integrated security (kind of bug I never
> really needed to fix). Works fine if you need to monitor database (or all
> databases) schema.
> Cheers,
> Wojtek
>
>
|||Sure:
http://www.garwol.net/DbCompare.src.zip
http://www.garwol.net/DbCompare.exe.zip
Enjoy
Cheers,
Wojtek
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45287186-E6D8-47E8-8194-DB2523FDF881@.microsoft.com...[vbcol=seagreen]
> Yes, that would be great. Can I get both? I will need to modify to work
> with sql authentication.
> Thanks,
> --
> Jason
>
> "Wojtek Garwol" wrote:
sql
Create an automated routine to check database definitions
Is there any utility, command, or query in SQL Server, that I can execute
from a batch mode to script out all of my database definitions (tables,
columns, sp, functions, indexes, constraints, triggers, etc.) so I can create
an automated routine to check database definitions?
--
Jason"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> Is there any utility, command, or query in SQL Server, that I can execute
> from a batch mode to script out all of my database definitions (tables,
> columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> create
> an automated routine to check database definitions?
I wrote one some time ago, using DMO. I can provide compiled exe or sources
if you want. AFAIR works only with integrated security (kind of bug I never
really needed to fix). Works fine if you need to monitor database (or all
databases) schema.
Cheers,
Wojtek|||Yes, that would be great. Can I get both? I will need to modify to work
with sql authentication.
Thanks,
--
Jason
"Wojtek Garwol" wrote:
> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> > Is there any utility, command, or query in SQL Server, that I can execute
> > from a batch mode to script out all of my database definitions (tables,
> > columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> > create
> > an automated routine to check database definitions?
> I wrote one some time ago, using DMO. I can provide compiled exe or sources
> if you want. AFAIR works only with integrated security (kind of bug I never
> really needed to fix). Works fine if you need to monitor database (or all
> databases) schema.
> Cheers,
> Wojtek
>
>|||Sure:
http://www.garwol.net/DbCompare.src.zip
http://www.garwol.net/DbCompare.exe.zip
Enjoy :)
Cheers,
Wojtek
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45287186-E6D8-47E8-8194-DB2523FDF881@.microsoft.com...
> Yes, that would be great. Can I get both? I will need to modify to work
> with sql authentication.
> Thanks,
> --
> Jason
>
> "Wojtek Garwol" wrote:
>> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
>> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
>> > Is there any utility, command, or query in SQL Server, that I can
>> > execute
>> > from a batch mode to script out all of my database definitions (tables,
>> > columns, sp, functions, indexes, constraints, triggers, etc.) so I can
>> > create
>> > an automated routine to check database definitions?
>> I wrote one some time ago, using DMO. I can provide compiled exe or
>> sources
>> if you want. AFAIR works only with integrated security (kind of bug I
>> never
>> really needed to fix). Works fine if you need to monitor database (or all
>> databases) schema.
>> Cheers,
>> Wojtek
>>
from a batch mode to script out all of my database definitions (tables,
columns, sp, functions, indexes, constraints, triggers, etc.) so I can create
an automated routine to check database definitions?
--
Jason"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> Is there any utility, command, or query in SQL Server, that I can execute
> from a batch mode to script out all of my database definitions (tables,
> columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> create
> an automated routine to check database definitions?
I wrote one some time ago, using DMO. I can provide compiled exe or sources
if you want. AFAIR works only with integrated security (kind of bug I never
really needed to fix). Works fine if you need to monitor database (or all
databases) schema.
Cheers,
Wojtek|||Yes, that would be great. Can I get both? I will need to modify to work
with sql authentication.
Thanks,
--
Jason
"Wojtek Garwol" wrote:
> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
> > Is there any utility, command, or query in SQL Server, that I can execute
> > from a batch mode to script out all of my database definitions (tables,
> > columns, sp, functions, indexes, constraints, triggers, etc.) so I can
> > create
> > an automated routine to check database definitions?
> I wrote one some time ago, using DMO. I can provide compiled exe or sources
> if you want. AFAIR works only with integrated security (kind of bug I never
> really needed to fix). Works fine if you need to monitor database (or all
> databases) schema.
> Cheers,
> Wojtek
>
>|||Sure:
http://www.garwol.net/DbCompare.src.zip
http://www.garwol.net/DbCompare.exe.zip
Enjoy :)
Cheers,
Wojtek
"JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
news:45287186-E6D8-47E8-8194-DB2523FDF881@.microsoft.com...
> Yes, that would be great. Can I get both? I will need to modify to work
> with sql authentication.
> Thanks,
> --
> Jason
>
> "Wojtek Garwol" wrote:
>> "JasonDWilson" <JasonDWilson@.discussions.microsoft.com> wrote in message
>> news:7B571D73-00F3-425B-BB96-555A028AC91F@.microsoft.com...
>> > Is there any utility, command, or query in SQL Server, that I can
>> > execute
>> > from a batch mode to script out all of my database definitions (tables,
>> > columns, sp, functions, indexes, constraints, triggers, etc.) so I can
>> > create
>> > an automated routine to check database definitions?
>> I wrote one some time ago, using DMO. I can provide compiled exe or
>> sources
>> if you want. AFAIR works only with integrated security (kind of bug I
>> never
>> really needed to fix). Works fine if you need to monitor database (or all
>> databases) schema.
>> Cheers,
>> Wojtek
>>
Create an array in a result field
I am between the "newbie" and "intermediate" stages of writing SQL code and I am wondering if there is a way to capture multiple results into one field so I can basically create a "set" for a unique identifier. Here is few result samples I receive from this code I am using now.
ReqNo ProcID
7102005 1409
7102005 1796
7139003 1411
7139003 6097
7261030 1409
7261030 1796
7268303 3998
7268303 4000
I would like to create a single row for each "ReqNo" and have a field that will an array of the "ProcID" results I receive. In other words, for the first "ReqNo" 7102005, can I create a field that will combine the 1409, 1796 into one field? I am trying to capture an array of integers used for that "ReqNo" so I can use that as a unique identifier in a join for another table.
So, ideally my result would be:
ReqNo ProcSet
7102005 1409, 1796
7139003 1411, 6097
7261030 1409, 1796
7268303 3998, 4000
Is this possible?
declare
@.startdate smalldatetime,
@.enddate smalldatetime ,
@.month int,
@.year int
select
@.startdate = dateadd (dd, -7, getdate())
SELECT
@.month = datepart (month, @.startdate),
@.year = datepart (year, @.startdate)
SELECT
@.startdate = convert (smalldatetime, convert(varchar(2), @.month) + "/1/" + convert (varchar(4), @.year))
select
@.enddate = dateadd (dd, 1 , @.startdate)
select distinct
pp_req_no as ReqNo,
pp_cproc_id_r as ProcID
from
risdb_rch08_stag..performed_procedure
(index pp_serv_time_r_ndx)
where
pp_service_time_r between @.Startdate and @.Enddate
and pp_status_v = 'CP'
and pp_rep_id > 0
order by
pp_req_no, pp_cproc_id_r
You could create a function that would concatenate the fields together and return them as a string. This is not really a recommended practice, but can be occassionally useful for presentation. One trick is to use a local variable to accumulate the results. Of course, your function would be specific to this table.
I am not up on 2005 features, but it seems like there is a new unpivot operator that might work.
This demonstrates how to do the accumulation into a local variable.
use Northwindgo
Declare @.result varchar(1000)
Select @.result = Case When @.result Is Not Null Then @.result + ', ' Else '' End + Convert(varchar(10),OrderID)
From Orders
Where CustomerID = 'ALFKI'
Select @.result|||
Confused? 
So are you saying that this syntax @.result + ', ' will build the results in the field? This looks like it will concatenate one result followed by the OrderID in your example (123456, 99999).
How will that build multiple results in one field? I understand how to declare and select, but this does not seem to make sense to me. I will be the first to admit if I am missing something here...which could very well be the case. Bear with me. 
|||
Hi,
SELECT t3.ReqNo, MAX(case t3.seq when 1 then t3.ProcID end)
+ MAX(case t3.seq when 2 then ', ' + t3.ProcID else '' end)
+ MAX(case t3.seq when 3 then ', ' + t3.ProcID else '' end) AS ProcID
FROM ( SELECT ReqNo, ProcID, (SELECT COUNT(*) FROM yourTable AS t2 WHERE t2.ReqNo = t1.ReqNo and t2.ProcID <= t1.ProcID) AS seq
FROM yourTable AS t1
) as t3
GROUP BY t3.ReqNo
Or you can use this for SQL Server 2005:
SELECT t3.ReqNo, t3.[1] + coalesce(', ' + t3.[2], '') + coalesce(', ' + t3.[3], '') AS ProcID
FROM (SELECT ProcID, ReqNo, ROW_NUMBER() OVER(PARTITION BY ReqNo ORDER BY ProcID) AS seq FROM yourTable) as t1
PIVOT (MAX(ProcID) for seq in ([1], [2], [3])) AS t3
Or using CTE in SQL Server 2005:
With MyCTE(ReqNo, ProcID, ProcIDs, myNum) AS
(SELECT a.ReqNo, CONVERT(varchar(50), MIN(a.ProcID)) as col1, CONVERT(varchar(50),(a.ProcID)) as ProcIDs, 1 as myNum
FROM yourTable a GROUP BY a.ReqNo, CONVERT(varchar(50),(a.ProcID))
UNION ALL
SELECT b.ReqNo, CONVERT(varchar(50), b.ProcID), CONVERT(varchar(50), (c.ProcIDs + ',' + b.ProcID)), c.myNum+1 as myNum
FROM yourTable b INNER JOIN MyCTE c ON b.ReqNo=c.ReqNo
WHERE b.ProcID>c.ProcID
)
SELECT a.ReqNo, ProcIDs FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.ReqNo FROM MyCTE a1
group by a1.ReqNo) b on a.ReqNo=b.ReqNo AND a.myNum= b.myNumMax ORDER BY a.ReqNo
|||
Sorry that I wasn't very clear.
You would build a User Defined Function that would be specific to the table you are working with and would accept a parameter that would identify the records -- like the customerID in the Northwind case. The function would concatenate the values into the local variable and return that variable. You could then call this function as part of your Select clause.
Performance would likely be a challenge.
Here is a NorthWind example
Drop Function OrderListgo
Create Function OrderList( @.cust varchar(10) )
Returns varchar(1000)
As
Begin
Declare @.result varchar(1000)
Select@.result =
Case When @.result Is Not Null
Then @.result + ', '
Else ''
End + Convert(varchar(10), OrderID )
FromOrders
WhereCustomerId = @.cust
return @.result
End
go
SelectCustomerID,
CompanyName,
dbo.OrderList( CustomerID )
FromCustomers
Create alert for table activity
Is it possible to create an alert to notify a user when a record is added to
a specific table? This is a error log table so the activity would/should be
very minimal.
"doug" <doug@.discussions.microsoft.com> wrote in message
news:44FA91DE-1435-40E3-B5B4-B6C62088678B@.microsoft.com...
> Is it possible to create an alert to notify a user when a record is added
> to
> a specific table? This is a error log table so the activity would/should
> be
> very minimal.
Triger for Insert.
|||doug wrote:
> Is it possible to create an alert to notify a user when a record is
> added to a specific table? This is a error log table so the activity
> would/should be very minimal.
On SQL 2005 you can use an insert trigger with Notification Services. On SQL
2000, you can still use an insert trigger that updates a ntification table.
Then you can create an Agent job that watches that table and sends an email
or page to the proper user.
David Gugick
Quest Software
a specific table? This is a error log table so the activity would/should be
very minimal.
"doug" <doug@.discussions.microsoft.com> wrote in message
news:44FA91DE-1435-40E3-B5B4-B6C62088678B@.microsoft.com...
> Is it possible to create an alert to notify a user when a record is added
> to
> a specific table? This is a error log table so the activity would/should
> be
> very minimal.
Triger for Insert.
|||doug wrote:
> Is it possible to create an alert to notify a user when a record is
> added to a specific table? This is a error log table so the activity
> would/should be very minimal.
On SQL 2005 you can use an insert trigger with Notification Services. On SQL
2000, you can still use an insert trigger that updates a ntification table.
Then you can create an Agent job that watches that table and sends an email
or page to the proper user.
David Gugick
Quest Software
Create alert for table activity
Is it possible to create an alert to notify a user when a record is added to
a specific table? This is a error log table so the activity would/should be
very minimal."doug" <doug@.discussions.microsoft.com> wrote in message
news:44FA91DE-1435-40E3-B5B4-B6C62088678B@.microsoft.com...
> Is it possible to create an alert to notify a user when a record is added
> to
> a specific table? This is a error log table so the activity would/should
> be
> very minimal.
Triger for Insert.|||doug wrote:
> Is it possible to create an alert to notify a user when a record is
> added to a specific table? This is a error log table so the activity
> would/should be very minimal.
On SQL 2005 you can use an insert trigger with Notification Services. On SQL
2000, you can still use an insert trigger that updates a ntification table.
Then you can create an Agent job that watches that table and sends an email
or page to the proper user.
David Gugick
Quest Software
a specific table? This is a error log table so the activity would/should be
very minimal."doug" <doug@.discussions.microsoft.com> wrote in message
news:44FA91DE-1435-40E3-B5B4-B6C62088678B@.microsoft.com...
> Is it possible to create an alert to notify a user when a record is added
> to
> a specific table? This is a error log table so the activity would/should
> be
> very minimal.
Triger for Insert.|||doug wrote:
> Is it possible to create an alert to notify a user when a record is
> added to a specific table? This is a error log table so the activity
> would/should be very minimal.
On SQL 2005 you can use an insert trigger with Notification Services. On SQL
2000, you can still use an insert trigger that updates a ntification table.
Then you can create an Agent job that watches that table and sends an email
or page to the proper user.
David Gugick
Quest Software
Create alert for table activity
Is it possible to create an alert to notify a user when a record is added to
a specific table? This is a error log table so the activity would/should be
very minimal."doug" <doug@.discussions.microsoft.com> wrote in message
news:44FA91DE-1435-40E3-B5B4-B6C62088678B@.microsoft.com...
> Is it possible to create an alert to notify a user when a record is added
> to
> a specific table? This is a error log table so the activity would/should
> be
> very minimal.
Triger for Insert.|||doug wrote:
> Is it possible to create an alert to notify a user when a record is
> added to a specific table? This is a error log table so the activity
> would/should be very minimal.
On SQL 2005 you can use an insert trigger with Notification Services. On SQL
2000, you can still use an insert trigger that updates a ntification table.
Then you can create an Agent job that watches that table and sends an email
or page to the proper user.
--
David Gugick
Quest Softwaresql
a specific table? This is a error log table so the activity would/should be
very minimal."doug" <doug@.discussions.microsoft.com> wrote in message
news:44FA91DE-1435-40E3-B5B4-B6C62088678B@.microsoft.com...
> Is it possible to create an alert to notify a user when a record is added
> to
> a specific table? This is a error log table so the activity would/should
> be
> very minimal.
Triger for Insert.|||doug wrote:
> Is it possible to create an alert to notify a user when a record is
> added to a specific table? This is a error log table so the activity
> would/should be very minimal.
On SQL 2005 you can use an insert trigger with Notification Services. On SQL
2000, you can still use an insert trigger that updates a ntification table.
Then you can create an Agent job that watches that table and sends an email
or page to the proper user.
--
David Gugick
Quest Softwaresql
Create A/R distribution using IF...THEN or CASE
I am trying to write a query in Transact-SQL to create a user view in my SQL
database. I am trying to populate columns for each "aging" category (30, 60,
90, etc), so the correct age receives the amount due, but other columns are
zero. However, I can't find the correct CASE or IF...THEN syntax. It needs t
o
do something equal to the following:
If ServiceDateAge BETWEEN 0 AND 29 Then CurrentDue = PatientDue Else
CurrentDue = 0.
If ServiceDateAge BETWEEN 30 and 59 Then 30DayDue = PatientDue Else 30DayDue
= 0...
etc
I would be grateful for help...Thank you.Try using CASE expressions like the snippet below. See the Books Online for
more info.
SELECT
CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END AS
"CurrentDue",
CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END AS
"30DayDue"
etc...
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>I am trying to write a query in Transact-SQL to create a user view in my
>SQL
> database. I am trying to populate columns for each "aging" category (30,
> 60,
> 90, etc), so the correct age receives the amount due, but other columns
> are
> zero. However, I can't find the correct CASE or IF...THEN syntax. It needs
> to
> do something equal to the following:
> If ServiceDateAge BETWEEN 0 AND 29 Then CurrentDue = PatientDue Else
> CurrentDue = 0.
> If ServiceDateAge BETWEEN 30 and 59 Then 30DayDue = PatientDue Else
> 30DayDue
> = 0...
> etc
> I would be grateful for help...Thank you.
>|||Thank you. That should do it. I did not see an example in the on-line books
of using CASE to populate a quantity in a column (thought it may have been
there).
"Dan Guzman" wrote:
> Try using CASE expressions like the snippet below. See the Books Online f
or
> more info.
> SELECT
> CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END A
S
> "CurrentDue",
> CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END
AS
> "30DayDue"
> etc...
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>
>|||Dan one more question: Let's say that when ServiceDateAge BETWEEN 0 AND 29 I
want to update CurrentPatientDue to PatientDue but at the same time also
update CurrentInsuranceDue to InsuranceDue. Can I achieve this without
writing another full CASE statement?
"Dan Guzman" wrote:
> Try using CASE expressions like the snippet below. See the Books Online f
or
> more info.
> SELECT
> CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END A
S
> "CurrentDue",
> CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END
AS
> "30DayDue"
> etc...
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>
>|||The important concept here is that SQL CASE is an expression and not a
statement. It simply returns a single value conditionally so you need to
replicate the CASE expression. If you are using SQL 2000, you can
encapsulate CASE in a user-defined scalar function like the example below.
CREATE FUNCTION dbo.AgeAmount
(
LowAge int,
HighAge int,
Age int,
Value int
)
RETURNS int
AS
BEGIN
RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END
END
GO
SELECT
dbo.AgeAmount(0, 29, ServiceDateAge, PatientDue) AS "CurrentDue",
dbo.AgeAmount(30, 59, ServiceDateAge, PatientDue) AS "30DayDue",
dbo.AgeAmount(0, 29, ServiceDateAge, CurrentInsuranceDue) AS
"CurrentInsuranceDue"
etc..
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:79FB0AFA-77E7-46CA-93CA-1B815E514B8F@.microsoft.com...
> Dan one more question: Let's say that when ServiceDateAge BETWEEN 0 AND 29
> I
> want to update CurrentPatientDue to PatientDue but at the same time also
> update CurrentInsuranceDue to InsuranceDue. Can I achieve this without
> writing another full CASE statement?
> "Dan Guzman" wrote:
>|||This opens up a whole new area for me to learn and use. However, my first
attempt is producing an error I don't understand. Instead of declaring the
function every time, I thought I would attempt to create a "User Defined
Function". In the properties dialog I believe I am using your text as follow
s:
CREATE FUNCTION dbo.AgeAmount (LowAge int, HighAge int, Age int, Value int)
RETURNS int AS
BEGIN
(RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END)
END
GO
When I try to save this or check the syntax, the error is: "Must declare the
variable @.age". Can you help me with this as well, please?
"Dan Guzman" wrote:
> The important concept here is that SQL CASE is an expression and not a
> statement. It simply returns a single value conditionally so you need to
> replicate the CASE expression. If you are using SQL 2000, you can
> encapsulate CASE in a user-defined scalar function like the example below.
>
> CREATE FUNCTION dbo.AgeAmount
> (
> LowAge int,
> HighAge int,
> Age int,
> Value int
> )
> RETURNS int
> AS
> BEGIN
> RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END
> END
> GO
> SELECT
> dbo.AgeAmount(0, 29, ServiceDateAge, PatientDue) AS "CurrentDue",
> dbo.AgeAmount(30, 59, ServiceDateAge, PatientDue) AS "30DayDue",
> dbo.AgeAmount(0, 29, ServiceDateAge, CurrentInsuranceDue) AS
> "CurrentInsuranceDue"
> etc..
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:79FB0AFA-77E7-46CA-93CA-1B815E514B8F@.microsoft.com...
>
>|||Try running the following using Query Analyzer:
CREATE FUNCTION dbo.AgeAmount (@.LowAge int, @.HighAge int, @.Age int, @.Value
int)
RETURNS int AS
BEGIN
RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END
END
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:6FB45184-BC27-4FC4-8D7F-81208CA173BF@.microsoft.com...
> This opens up a whole new area for me to learn and use. However, my first
> attempt is producing an error I don't understand. Instead of declaring the
> function every time, I thought I would attempt to create a "User Defined
> Function". In the properties dialog I believe I am using your text as
> follows:
> CREATE FUNCTION dbo.AgeAmount (LowAge int, HighAge int, Age int, Value
> int)
> RETURNS int AS
> BEGIN
> (RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END)
> END
> GO
> When I try to save this or check the syntax, the error is: "Must declare
> the
> variable @.age". Can you help me with this as well, please?
> "Dan Guzman" wrote:
>|||Hello Dan,
OK I'm in business. However, I ran the script in Query Analyzer. It ran
successfully, but I could not find my function, even though I'm sure I was
accessing the correct database. So, I opened a new user defined function,
deleted the standard text and inserted your text. This saved with no errors
and I am using the function now. Thank you for introducing me to this
interesting area of SQL 2000.
"Dan Guzman" wrote:
> Try running the following using Query Analyzer:
> CREATE FUNCTION dbo.AgeAmount (@.LowAge int, @.HighAge int, @.Age int, @.Value
> int)
> RETURNS int AS
> BEGIN
> RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END
> END
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:6FB45184-BC27-4FC4-8D7F-81208CA173BF@.microsoft.com...
>
>
database. I am trying to populate columns for each "aging" category (30, 60,
90, etc), so the correct age receives the amount due, but other columns are
zero. However, I can't find the correct CASE or IF...THEN syntax. It needs t
o
do something equal to the following:
If ServiceDateAge BETWEEN 0 AND 29 Then CurrentDue = PatientDue Else
CurrentDue = 0.
If ServiceDateAge BETWEEN 30 and 59 Then 30DayDue = PatientDue Else 30DayDue
= 0...
etc
I would be grateful for help...Thank you.Try using CASE expressions like the snippet below. See the Books Online for
more info.
SELECT
CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END AS
"CurrentDue",
CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END AS
"30DayDue"
etc...
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>I am trying to write a query in Transact-SQL to create a user view in my
>SQL
> database. I am trying to populate columns for each "aging" category (30,
> 60,
> 90, etc), so the correct age receives the amount due, but other columns
> are
> zero. However, I can't find the correct CASE or IF...THEN syntax. It needs
> to
> do something equal to the following:
> If ServiceDateAge BETWEEN 0 AND 29 Then CurrentDue = PatientDue Else
> CurrentDue = 0.
> If ServiceDateAge BETWEEN 30 and 59 Then 30DayDue = PatientDue Else
> 30DayDue
> = 0...
> etc
> I would be grateful for help...Thank you.
>|||Thank you. That should do it. I did not see an example in the on-line books
of using CASE to populate a quantity in a column (thought it may have been
there).
"Dan Guzman" wrote:
> Try using CASE expressions like the snippet below. See the Books Online f
or
> more info.
> SELECT
> CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END A
S
> "CurrentDue",
> CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END
AS
> "30DayDue"
> etc...
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>
>|||Dan one more question: Let's say that when ServiceDateAge BETWEEN 0 AND 29 I
want to update CurrentPatientDue to PatientDue but at the same time also
update CurrentInsuranceDue to InsuranceDue. Can I achieve this without
writing another full CASE statement?
"Dan Guzman" wrote:
> Try using CASE expressions like the snippet below. See the Books Online f
or
> more info.
> SELECT
> CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END A
S
> "CurrentDue",
> CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END
AS
> "30DayDue"
> etc...
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>
>|||The important concept here is that SQL CASE is an expression and not a
statement. It simply returns a single value conditionally so you need to
replicate the CASE expression. If you are using SQL 2000, you can
encapsulate CASE in a user-defined scalar function like the example below.
CREATE FUNCTION dbo.AgeAmount
(
LowAge int,
HighAge int,
Age int,
Value int
)
RETURNS int
AS
BEGIN
RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END
END
GO
SELECT
dbo.AgeAmount(0, 29, ServiceDateAge, PatientDue) AS "CurrentDue",
dbo.AgeAmount(30, 59, ServiceDateAge, PatientDue) AS "30DayDue",
dbo.AgeAmount(0, 29, ServiceDateAge, CurrentInsuranceDue) AS
"CurrentInsuranceDue"
etc..
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:79FB0AFA-77E7-46CA-93CA-1B815E514B8F@.microsoft.com...
> Dan one more question: Let's say that when ServiceDateAge BETWEEN 0 AND 29
> I
> want to update CurrentPatientDue to PatientDue but at the same time also
> update CurrentInsuranceDue to InsuranceDue. Can I achieve this without
> writing another full CASE statement?
> "Dan Guzman" wrote:
>|||This opens up a whole new area for me to learn and use. However, my first
attempt is producing an error I don't understand. Instead of declaring the
function every time, I thought I would attempt to create a "User Defined
Function". In the properties dialog I believe I am using your text as follow
s:
CREATE FUNCTION dbo.AgeAmount (LowAge int, HighAge int, Age int, Value int)
RETURNS int AS
BEGIN
(RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END)
END
GO
When I try to save this or check the syntax, the error is: "Must declare the
variable @.age". Can you help me with this as well, please?
"Dan Guzman" wrote:
> The important concept here is that SQL CASE is an expression and not a
> statement. It simply returns a single value conditionally so you need to
> replicate the CASE expression. If you are using SQL 2000, you can
> encapsulate CASE in a user-defined scalar function like the example below.
>
> CREATE FUNCTION dbo.AgeAmount
> (
> LowAge int,
> HighAge int,
> Age int,
> Value int
> )
> RETURNS int
> AS
> BEGIN
> RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END
> END
> GO
> SELECT
> dbo.AgeAmount(0, 29, ServiceDateAge, PatientDue) AS "CurrentDue",
> dbo.AgeAmount(30, 59, ServiceDateAge, PatientDue) AS "30DayDue",
> dbo.AgeAmount(0, 29, ServiceDateAge, CurrentInsuranceDue) AS
> "CurrentInsuranceDue"
> etc..
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:79FB0AFA-77E7-46CA-93CA-1B815E514B8F@.microsoft.com...
>
>|||Try running the following using Query Analyzer:
CREATE FUNCTION dbo.AgeAmount (@.LowAge int, @.HighAge int, @.Age int, @.Value
int)
RETURNS int AS
BEGIN
RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END
END
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:6FB45184-BC27-4FC4-8D7F-81208CA173BF@.microsoft.com...
> This opens up a whole new area for me to learn and use. However, my first
> attempt is producing an error I don't understand. Instead of declaring the
> function every time, I thought I would attempt to create a "User Defined
> Function". In the properties dialog I believe I am using your text as
> follows:
> CREATE FUNCTION dbo.AgeAmount (LowAge int, HighAge int, Age int, Value
> int)
> RETURNS int AS
> BEGIN
> (RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END)
> END
> GO
> When I try to save this or check the syntax, the error is: "Must declare
> the
> variable @.age". Can you help me with this as well, please?
> "Dan Guzman" wrote:
>|||Hello Dan,
OK I'm in business. However, I ran the script in Query Analyzer. It ran
successfully, but I could not find my function, even though I'm sure I was
accessing the correct database. So, I opened a new user defined function,
deleted the standard text and inserted your text. This saved with no errors
and I am using the function now. Thank you for introducing me to this
interesting area of SQL 2000.
"Dan Guzman" wrote:
> Try running the following using Query Analyzer:
> CREATE FUNCTION dbo.AgeAmount (@.LowAge int, @.HighAge int, @.Age int, @.Value
> int)
> RETURNS int AS
> BEGIN
> RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END
> END
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:6FB45184-BC27-4FC4-8D7F-81208CA173BF@.microsoft.com...
>
>
Create a view, failed
I tried to create a view with the next select, but the Enterprise Manager
send a message, "El Dise?ador de consultas no admite la interpretación SQL
de
CASE."
Not accept a CASE sql. I need your help to try other way. Thanks.
SELECT
'Raz_01'=
CASE
WHEN TOTAL_ATUAL_MES_01 >= 0 and Bud_Atual_MES_01 > 0 THEN
(TOTAL_ATUAL_MES_01/Bud_Atual_MES_01)*100
WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 > 0 THEN
((ABS(TOTAL_ATUAL_MES_01)+ Bud_Atual_MES_01)/Bud_Atual_MES_01*100*(-1))-100
WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 = 0 THEN
TOTAL_ATUAL_MES_01*100
WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 = 0 THEN
TOTAL_ATUAL_MES_01*100
WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 < 0 THEN
((TOTAL_ATUAL_MES_01+ABS(Bud_Atual_MES_0
1))/ABS(Bud_Atual_MES_01))*100
WHEN TOTAL_ATUAL_MES_01 = 0 and Bud_Atual_MES_01 < 0 THEN
(200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 < 0 THEN
(200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
ELSE 100
--Repeat this with other month 2,3,4-12--
END, CD_PAIS,COD_IND_LOC
From dbo.Datos_reales_2005
ORDER BY CD_PAIS, COD_IND_LOCUse SQL Query Analyzer instead.
AMB
"espinfire" wrote:
> I tried to create a view with the next select, but the Enterprise Manager
> send a message, "El Dise?ador de consultas no admite la interpretación S
QL de
> CASE."
> Not accept a CASE sql. I need your help to try other way. Thanks.
> SELECT
> 'Raz_01'=
> CASE
> WHEN TOTAL_ATUAL_MES_01 >= 0 and Bud_Atual_MES_01 > 0 THEN
> (TOTAL_ATUAL_MES_01/Bud_Atual_MES_01)*100
> WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 > 0 THEN
> ((ABS(TOTAL_ATUAL_MES_01)+ Bud_Atual_MES_01)/Bud_Atual_MES_01*100*(-1))-10
0
> WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 = 0 THEN
> TOTAL_ATUAL_MES_01*100
> WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 = 0 THEN
> TOTAL_ATUAL_MES_01*100
> WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 < 0 THEN
> ((TOTAL_ATUAL_MES_01+ABS(Bud_Atual_MES_0
1))/ABS(Bud_Atual_MES_01))*100
> WHEN TOTAL_ATUAL_MES_01 = 0 and Bud_Atual_MES_01 < 0 THEN
> (200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
> WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 < 0 THEN
> (200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
> ELSE 100
> --Repeat this with other month 2,3,4-12--
> END, CD_PAIS,COD_IND_LOC
> From dbo.Datos_reales_2005
> ORDER BY CD_PAIS, COD_IND_LOCsql
send a message, "El Dise?ador de consultas no admite la interpretación SQL
de
CASE."
Not accept a CASE sql. I need your help to try other way. Thanks.
SELECT
'Raz_01'=
CASE
WHEN TOTAL_ATUAL_MES_01 >= 0 and Bud_Atual_MES_01 > 0 THEN
(TOTAL_ATUAL_MES_01/Bud_Atual_MES_01)*100
WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 > 0 THEN
((ABS(TOTAL_ATUAL_MES_01)+ Bud_Atual_MES_01)/Bud_Atual_MES_01*100*(-1))-100
WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 = 0 THEN
TOTAL_ATUAL_MES_01*100
WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 = 0 THEN
TOTAL_ATUAL_MES_01*100
WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 < 0 THEN
((TOTAL_ATUAL_MES_01+ABS(Bud_Atual_MES_0
1))/ABS(Bud_Atual_MES_01))*100
WHEN TOTAL_ATUAL_MES_01 = 0 and Bud_Atual_MES_01 < 0 THEN
(200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 < 0 THEN
(200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
ELSE 100
--Repeat this with other month 2,3,4-12--
END, CD_PAIS,COD_IND_LOC
From dbo.Datos_reales_2005
ORDER BY CD_PAIS, COD_IND_LOCUse SQL Query Analyzer instead.
AMB
"espinfire" wrote:
> I tried to create a view with the next select, but the Enterprise Manager
> send a message, "El Dise?ador de consultas no admite la interpretación S
QL de
> CASE."
> Not accept a CASE sql. I need your help to try other way. Thanks.
> SELECT
> 'Raz_01'=
> CASE
> WHEN TOTAL_ATUAL_MES_01 >= 0 and Bud_Atual_MES_01 > 0 THEN
> (TOTAL_ATUAL_MES_01/Bud_Atual_MES_01)*100
> WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 > 0 THEN
> ((ABS(TOTAL_ATUAL_MES_01)+ Bud_Atual_MES_01)/Bud_Atual_MES_01*100*(-1))-10
0
> WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 = 0 THEN
> TOTAL_ATUAL_MES_01*100
> WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 = 0 THEN
> TOTAL_ATUAL_MES_01*100
> WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 < 0 THEN
> ((TOTAL_ATUAL_MES_01+ABS(Bud_Atual_MES_0
1))/ABS(Bud_Atual_MES_01))*100
> WHEN TOTAL_ATUAL_MES_01 = 0 and Bud_Atual_MES_01 < 0 THEN
> (200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
> WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 < 0 THEN
> (200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
> ELSE 100
> --Repeat this with other month 2,3,4-12--
> END, CD_PAIS,COD_IND_LOC
> From dbo.Datos_reales_2005
> ORDER BY CD_PAIS, COD_IND_LOCsql
Create a view to get latest status for each application
I would like some help creating a view that will display the latest status for each application. The lastest status should be based on CreateDt.
For example:
Table Structure:
============
Application: ApplicationID, Name, Address, City, State, Zip, etc..
ApplicationAction: ApplicationActionID, ApplicationID, Status (Ex:new, reviewed, approved, closed), CreateDt
View should display:
==============
ApplicantID, ApplicantActionID, Status, CreateDt
Example:
==========
ApplicantID=4, Name=Bob Smith, etc...
ApplicantActionID=1, ApplicantID=4, Status=New, CreatDt=1/3/20071:00
ApplicantActionID=2, ApplicantID=4, Status=Reviewed, CreatDt=1/3/2007 2:00
ApplicantActionID=3, ApplicantID=4, Status=Approved, CreatDt=1/4/2007 1:00
... etc...
View should return:
Applicant=4, ApplicantActionID=3, Status=Approved, CreatDt=1/4/2007 1:00
etc...
Hint: Use MAX(CreatDt) to get the information you need.|||
well I got that far, I need help getting beyond that:( Any ideas?
|||Can you post what you have so far?|||Oh well..
Declare @.ApTable (ApplicationIDint ,Name varchar(50), Addressvarchar(50), Cityvarchar(50), Statevarchar(50), Zipvarchar(50))Declare @.AATable (ApplicationActionIDint, ApplicationIDint, Statusvarchar(50), CreateDtdatetime)Insert into @.ApSELECt 4,'Bob Smith','123 street','SomeCity','SS','12345'UNIONALLSELECT 5,'New Smith','sss','ss','a','4455'Insert into @.AASELECT 1,4,'New','1/3/2007 1:00'UNIONALLSELECT 2,4,'Reviewed','1/3/2007 2:00'UNIONALLSELECT 3,4,'Approved','1/4/2007 1:00'UNIONALLSELECT 1,5,'New','5/24/2006 1:00'Select *from @.ApSelect *from @.AASELECT aa.*FROM (select applicationid,max(Createdt)as MxCreateDtfrom @.AAgroup by applicationid) Xjoin @.AA AAon aa.createdt = X.MXcreatedt
create a view that return data and its count of description
I have a view that has a link to 3 tables.
I need to dispaly only the data in one table and list how
many that data is defined in one table.
here is the code for the first view.
USE Aromatherapy
GO
if exists (select name from sysobjects
where name = 'Oils_Cautions_View' and type
= 'V')
DROP VIEW
Oils_Cautions_View
go
CREATE VIEW Oils_Cautions_View AS
SELECT o.oilID, oilName,
Description FROM Oils AS o, Cautions as c, OilCautions as
oc
Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
All I need is the oilName and its COUNT of cautions.
let say the oil name is "Basil" and it has three counts.
All I need is Basil in one coumn and in the other coulmn
I need "3"
Thank youWithout DDL, I'm taking some guesses, but try:
SELECT o.oilName, count(*) AS CautionCount
FROM Oils AS o
join OilCautions AS oc
on o.OILID = oc.OILID
group by o.oilName
HTH
Vern
>--Original Message--
>I have a view that has a link to 3 tables.
>I need to dispaly only the data in one table and list how
>many that data is defined in one table.
>here is the code for the first view.
>USE Aromatherapy
>GO
>if exists (select name from sysobjects
> where name = 'Oils_Cautions_View' and type
>= 'V')
> DROP VIEW
> Oils_Cautions_View
>go
>CREATE VIEW Oils_Cautions_View AS
>SELECT o.oilID, oilName,
>Description FROM Oils AS o, Cautions as c, OilCautions as
>oc
>Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
>All I need is the oilName and its COUNT of cautions.
>let say the oil name is "Basil" and it has three counts.
>All I need is Basil in one coumn and in the other coulmn
>I need "3"
>Thank you
>.
>|||Hi Vern,
Thank you very much for your time
Cristian
>--Original Message--
>Without DDL, I'm taking some guesses, but try:
>SELECT o.oilName, count(*) AS CautionCount
>FROM Oils AS o
>join OilCautions AS oc
> on o.OILID = oc.OILID
>group by o.oilName
>HTH
>Vern
>>--Original Message--
>>I have a view that has a link to 3 tables.
>>I need to dispaly only the data in one table and list
how
>>many that data is defined in one table.
>>here is the code for the first view.
>>USE Aromatherapy
>>GO
>>if exists (select name from sysobjects
>> where name = 'Oils_Cautions_View' and
type
>>= 'V')
>> DROP VIEW
>> Oils_Cautions_View
>>go
>>CREATE VIEW Oils_Cautions_View AS
>>SELECT o.oilID, oilName,
>>Description FROM Oils AS o, Cautions as c, OilCautions
as
>>oc
>>Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
>>All I need is the oilName and its COUNT of cautions.
>>let say the oil name is "Basil" and it has three counts.
>>All I need is Basil in one coumn and in the other
coulmn
>>I need "3"
>>Thank you
>>.
>.
>
I need to dispaly only the data in one table and list how
many that data is defined in one table.
here is the code for the first view.
USE Aromatherapy
GO
if exists (select name from sysobjects
where name = 'Oils_Cautions_View' and type
= 'V')
DROP VIEW
Oils_Cautions_View
go
CREATE VIEW Oils_Cautions_View AS
SELECT o.oilID, oilName,
Description FROM Oils AS o, Cautions as c, OilCautions as
oc
Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
All I need is the oilName and its COUNT of cautions.
let say the oil name is "Basil" and it has three counts.
All I need is Basil in one coumn and in the other coulmn
I need "3"
Thank youWithout DDL, I'm taking some guesses, but try:
SELECT o.oilName, count(*) AS CautionCount
FROM Oils AS o
join OilCautions AS oc
on o.OILID = oc.OILID
group by o.oilName
HTH
Vern
>--Original Message--
>I have a view that has a link to 3 tables.
>I need to dispaly only the data in one table and list how
>many that data is defined in one table.
>here is the code for the first view.
>USE Aromatherapy
>GO
>if exists (select name from sysobjects
> where name = 'Oils_Cautions_View' and type
>= 'V')
> DROP VIEW
> Oils_Cautions_View
>go
>CREATE VIEW Oils_Cautions_View AS
>SELECT o.oilID, oilName,
>Description FROM Oils AS o, Cautions as c, OilCautions as
>oc
>Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
>All I need is the oilName and its COUNT of cautions.
>let say the oil name is "Basil" and it has three counts.
>All I need is Basil in one coumn and in the other coulmn
>I need "3"
>Thank you
>.
>|||Hi Vern,
Thank you very much for your time
Cristian
>--Original Message--
>Without DDL, I'm taking some guesses, but try:
>SELECT o.oilName, count(*) AS CautionCount
>FROM Oils AS o
>join OilCautions AS oc
> on o.OILID = oc.OILID
>group by o.oilName
>HTH
>Vern
>>--Original Message--
>>I have a view that has a link to 3 tables.
>>I need to dispaly only the data in one table and list
how
>>many that data is defined in one table.
>>here is the code for the first view.
>>USE Aromatherapy
>>GO
>>if exists (select name from sysobjects
>> where name = 'Oils_Cautions_View' and
type
>>= 'V')
>> DROP VIEW
>> Oils_Cautions_View
>>go
>>CREATE VIEW Oils_Cautions_View AS
>>SELECT o.oilID, oilName,
>>Description FROM Oils AS o, Cautions as c, OilCautions
as
>>oc
>>Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
>>All I need is the oilName and its COUNT of cautions.
>>let say the oil name is "Basil" and it has three counts.
>>All I need is Basil in one coumn and in the other
coulmn
>>I need "3"
>>Thank you
>>.
>.
>
Create a view on another DB
Hello !
I have a "SQLServer Express" database andI would like to create a view in it, pointing to data from another database (Progress)
Is this possible ? How ?
Thanks,
Ok I succeed using the linked servers.
However I have read that we could use the syntax
select * from linked_server_name..schema.table
but this gives me an error. It's ok when I use :
select * from OPENQUERY(linked_server_name, 'select * from schema.table')
I cannot use the first syntax ?
create a view of or select from a table on another DB
Does anyone know how you could create a view in one db of a table in another db on the same sql server? or how to perform a cross db join in a query?
thanks!Eg;
create view temp as
select a.* from
master..syslogins a, msdb..sysjobs
Use the books online feature that comes with SQL server. Most useful!
Meera
thanks!Eg;
create view temp as
select a.* from
master..syslogins a, msdb..sysjobs
Use the books online feature that comes with SQL server. Most useful!
Meera
Create a view in VBA
Hi,
Is it possible to create some SQL in VBA, 'run it' then view as a datasheet
the results?
I'm trying to throw together a fairly large search form, and VBA seems the
best way to parse the parameters.
Cheers,
Chris"Not Me" <Not.Me@.faker.fake.fa.kee> wrote in message
news:c6rag7$kt$1@.ucsnew1.ncl.ac.uk...
> Hi,
> Is it possible to create some SQL in VBA, 'run it' then view as a
datasheet
> the results?
> I'm trying to throw together a fairly large search form, and VBA seems the
> best way to parse the parameters.
> Cheers,
> Chris
Yes - you could use the ADO COM objects, then return results to your client
and format/present them there. If you need a more precise answer, then it
would help to have some more detail about what you're trying to do and what
tools you're using (eg. Access, Excel etc.).
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40913ac0$1_1@.news.bluewin.ch...
> "Not Me" <Not.Me@.faker.fake.fa.kee> wrote in message
> news:c6rag7$kt$1@.ucsnew1.ncl.ac.uk...
> > Hi,
> > Is it possible to create some SQL in VBA, 'run it' then view as a
> datasheet
> > the results?
> > I'm trying to throw together a fairly large search form, and VBA seems
the
> > best way to parse the parameters.
> Yes - you could use the ADO COM objects, then return results to your
client
> and format/present them there. If you need a more precise answer, then it
> would help to have some more detail about what you're trying to do and
what
> tools you're using (eg. Access, Excel etc.).
Thanks for the reply.
In the past, I have been able to pre-write a query in Access, (i.e. have it
saved in the queries list) and use docmd.openquery in VBA to have it
displayed on screen.
I have also been able to create queries 'on the fly' in VBA, and be able to
iterate through them/work on them in the same program.
What I'd like to do is sort of combine the above, so create a query in VBA
and have it displayed in a datasheet view. So the user in an ADE would
select from a few comboboxes, press go and up would pop the resulting query.
Cheers,
Chrissql
Create a View in MSSQL 2000
I have created a view in MS SQL2000 as followed:
Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;
my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.
Any HELP would be GREAT.
Thank You!!I have created a view in MS SQL2000 as followed:
Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;
my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.
Any HELP would be GREAT.
Thank You!!
Check out the CASE Statement in Books on Line.
Regards,
hmscott|||Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip, EMAILaddress
FROM orders;
Semi-Colon(";")
Is this for DB2 or Oracle 8i?
SQL Server would be
SELECT order_NO
, shiptoname
, Shiptoaddress
, Shiptocity
, shiptostate
, shiptozip
, EMAILaddress
, CASE WHEN EMAILaddress IS NULL THEN 'N' ELSE 'Y' END AS EMAILflag
FROM orders
GO|||Thank You!
Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;
my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.
Any HELP would be GREAT.
Thank You!!I have created a view in MS SQL2000 as followed:
Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;
my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.
Any HELP would be GREAT.
Thank You!!
Check out the CASE Statement in Books on Line.
Regards,
hmscott|||Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip, EMAILaddress
FROM orders;
Semi-Colon(";")
Is this for DB2 or Oracle 8i?
SQL Server would be
SELECT order_NO
, shiptoname
, Shiptoaddress
, Shiptocity
, shiptostate
, shiptozip
, EMAILaddress
, CASE WHEN EMAILaddress IS NULL THEN 'N' ELSE 'Y' END AS EMAILflag
FROM orders
GO|||Thank You!
Create a view help!
Hi Guys,
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SR
Not sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SR
Not sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>
Create a view help!
Hi Guys,
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SRNot sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SRNot sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>
Create a view help!
Hi Guys,
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SRNot sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SRNot sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>
Create a View Command in Query Analyzer
Hey...a simple question that I forgot...
May I know how to create a view under Query Analyzer...below is what i have
type, but command succesfully, but view not created...wonder why ?
CREATE VIEW dbo.VIEW1
AS
SELECT * FROM Table1If the command was executed successfull the view was created unless you
didnt turn on something like SET Parseonly etc. Did youdirectly Select from
the row after it was created ? "Select * from View1"
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hey...a simple question that I forgot...
> May I know how to create a view under Query Analyzer...below is what i
> have type, but command succesfully, but view not created...wonder why ?
> CREATE VIEW dbo.VIEW1
> AS
> SELECT * FROM Table1
>|||but I tried to go to Enterprise Manager, SQL Server, mydatabase, then under
the VIEWS there...
i click...the newly created View1 is not shown there, but I am able to
Select * from View1...
May I know how to make my View1 to be able to see from the enterprise
manager.
thank you
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
> If the command was executed successfull the view was created unless you
> didnt turn on something like SET Parseonly etc. Did youdirectly Select
> from the row after it was created ? "Select * from View1"
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> " A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
> news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
>|||The gui not always refreshs the right way, did you right click on the View
node and selected Refresh ? That should do it if you placed it in the right
database. Perhaps you issued the command in the wrong database ? Just proof
It via Select @.@.servername,DB_NAME().
If the view was created it should appear in the INFORMATION_SCHEMA:
Select * from INFORMATION_SCHEMA.Tables
Where TABLE_NAME LIKE 'View1'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:OAKfHkPXFHA.2540@.tk2msftngp13.phx.gbl...
> but I tried to go to Enterprise Manager, SQL Server, mydatabase, then
> under the VIEWS there...
> i click...the newly created View1 is not shown there, but I am able to
> Select * from View1...
> May I know how to make my View1 to be able to see from the enterprise
> manager.
> thank you
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
>|||YES, Jens...I am done with that...
Just wonder how could I delete a view then ?
thanks you
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
> If the command was executed successfull the view was created unless you
> didnt turn on something like SET Parseonly etc. Did youdirectly Select
> from the row after it was created ? "Select * from View1"
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> " A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
> news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
>|||Hi,
I have a suggestion here.
Do you have dbo right to view all objects?
Since you have created it using dbo, I am not sure whether you are using dbo
right to view the VIEW1.
another thing is, you can check BOL on how to drop a view.
Syntax
DROP VIEW { view } [ ,...n ]
thanks.
Leo Leong|||Drop View [Viewname]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:%23PT391PXFHA.3572@.TK2MSFTNGP12.phx.gbl...
> YES, Jens...I am done with that...
> Just wonder how could I delete a view then ?
> thanks you
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
>|||I think you were connected to a different database in Query Analyzer than
you think you were (the one you were looking in in Enterprise Manager). You
can use the Object Search (F4) in Query Analyzer to search for a view called
View1 in all your databases.
Jacco Schalkwijk
SQL Server MVP
" A_PK" <pk999@.hotmail.com> wrote in message
news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hey...a simple question that I forgot...
> May I know how to create a view under Query Analyzer...below is what i
> have type, but command succesfully, but view not created...wonder why ?
> CREATE VIEW dbo.VIEW1
> AS
> SELECT * FROM Table1
>sql
May I know how to create a view under Query Analyzer...below is what i have
type, but command succesfully, but view not created...wonder why ?
CREATE VIEW dbo.VIEW1
AS
SELECT * FROM Table1If the command was executed successfull the view was created unless you
didnt turn on something like SET Parseonly etc. Did youdirectly Select from
the row after it was created ? "Select * from View1"
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hey...a simple question that I forgot...
> May I know how to create a view under Query Analyzer...below is what i
> have type, but command succesfully, but view not created...wonder why ?
> CREATE VIEW dbo.VIEW1
> AS
> SELECT * FROM Table1
>|||but I tried to go to Enterprise Manager, SQL Server, mydatabase, then under
the VIEWS there...
i click...the newly created View1 is not shown there, but I am able to
Select * from View1...
May I know how to make my View1 to be able to see from the enterprise
manager.
thank you
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
> If the command was executed successfull the view was created unless you
> didnt turn on something like SET Parseonly etc. Did youdirectly Select
> from the row after it was created ? "Select * from View1"
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> " A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
> news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
>|||The gui not always refreshs the right way, did you right click on the View
node and selected Refresh ? That should do it if you placed it in the right
database. Perhaps you issued the command in the wrong database ? Just proof
It via Select @.@.servername,DB_NAME().
If the view was created it should appear in the INFORMATION_SCHEMA:
Select * from INFORMATION_SCHEMA.Tables
Where TABLE_NAME LIKE 'View1'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:OAKfHkPXFHA.2540@.tk2msftngp13.phx.gbl...
> but I tried to go to Enterprise Manager, SQL Server, mydatabase, then
> under the VIEWS there...
> i click...the newly created View1 is not shown there, but I am able to
> Select * from View1...
> May I know how to make my View1 to be able to see from the enterprise
> manager.
> thank you
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
>|||YES, Jens...I am done with that...
Just wonder how could I delete a view then ?
thanks you
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
> If the command was executed successfull the view was created unless you
> didnt turn on something like SET Parseonly etc. Did youdirectly Select
> from the row after it was created ? "Select * from View1"
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> " A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
> news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
>|||Hi,
I have a suggestion here.
Do you have dbo right to view all objects?
Since you have created it using dbo, I am not sure whether you are using dbo
right to view the VIEW1.
another thing is, you can check BOL on how to drop a view.
Syntax
DROP VIEW { view } [ ,...n ]
thanks.
Leo Leong|||Drop View [Viewname]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:%23PT391PXFHA.3572@.TK2MSFTNGP12.phx.gbl...
> YES, Jens...I am done with that...
> Just wonder how could I delete a view then ?
> thanks you
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
>|||I think you were connected to a different database in Query Analyzer than
you think you were (the one you were looking in in Enterprise Manager). You
can use the Object Search (F4) in Query Analyzer to search for a view called
View1 in all your databases.
Jacco Schalkwijk
SQL Server MVP
" A_PK" <pk999@.hotmail.com> wrote in message
news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hey...a simple question that I forgot...
> May I know how to create a view under Query Analyzer...below is what i
> have type, but command succesfully, but view not created...wonder why ?
> CREATE VIEW dbo.VIEW1
> AS
> SELECT * FROM Table1
>sql
Tuesday, March 27, 2012
Create a view based on variables?
Hi All,
I would like to create a view based on a variable (a date).
CREATE VIEW testView (@.myDate)
AS
select * from testTable
WHERE testVar = @.myDate
--
This is far from what the final view would look like, but if anyone knows if something similiar to this can be done, I would greatly appreciate it.
Thanks.I would say "No, it can't." Why can't you just create the view without the where clause and select from it where the date = @.myDate when you need it? You could create a function that returns a table and takes the date as an argument.
create a view
How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itself), and a SELECT
statement read data from tables or views, it doesn't execute stored procedures. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...
> How can I create a view which based on a store procedure and then sp will return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>Subject: create a view
>>Date: Tue, 11 Apr 2006 15:54:51 +0800
>>Lines: 4
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 210.177.248.66
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427533
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I create a view which based on a store procedure and then sp will
>>return a select statement only? Thanks.
>>|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itself), and a SELECT
statement read data from tables or views, it doesn't execute stored procedures. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...
> How can I create a view which based on a store procedure and then sp will return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>Subject: create a view
>>Date: Tue, 11 Apr 2006 15:54:51 +0800
>>Lines: 4
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 210.177.248.66
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427533
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I create a view which based on a store procedure and then sp will
>>return a select statement only? Thanks.
>>|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.
Create a View
I have a table in a database that has very old and not very relational and I want to create a quick view to show the information in a better way. Let's say that the table has 4 fields : id , child1, child2, child3. I want to create a view from this table that will show two fields: id and child. So, my table currently looks like this:
id child1 child2 child3
1 sam bob chris
and i would like the view to display the information like this.....
id child
1 sam
1 bob
1 chris
Can anybody help me? Thanks in advance,
BobIf your DBMS permits a union view, try:
create veiw view1
as
Select id, child1 child
from table1
union
Select id, child2 child
from table1
union
Select id, child3 child
from table1;
id child1 child2 child3
1 sam bob chris
and i would like the view to display the information like this.....
id child
1 sam
1 bob
1 chris
Can anybody help me? Thanks in advance,
BobIf your DBMS permits a union view, try:
create veiw view1
as
Select id, child1 child
from table1
union
Select id, child2 child
from table1
union
Select id, child3 child
from table1;
Subscribe to:
Posts (Atom)