I currently have a website which is using ASP.NET 2.0, C#, and SQL
Server 2005. The website will be used to enter grants for a
university. When a new grant is entered, I need to generate a primary
key. The primary key will need to follow the format: Two digit for
fiscal year, then number of the grant for that year. Example:
Year 08 and 14th grant of the year would be: 0814
How can I implement this. Right now, I have a "New Grant.aspx" page
with a Submit button. I am guessing the date is going to be formatted
in C#. How can I check what the last primary key in the database is?
Also, it seems to me that SQL Server insists that the primary key be
32 bits long, however my primary key will only be 4. How can I
override this? Thanks.
Even though I am not "new to SQL Server", my experience in working with it has ebbed and flowed, and working with SQL Server is not something that happens for me even on a monthly basis.
I work as an in-house programmer for a financial investment company and I have been asked to work on a database that is to hold financial data.
One of the requirements for the tables has been that the Primary Key should be [name_of_the_table] + [Identity].
So, for example, for table "tblEquities", the values of the Primary Key would be:
My questions are: 1) Is it possible to achieve this by using just one column, as in defining a formula for the "Computed Column Specification" , something that would look like "'tblEquities' + Identity()"? 2) If the above is not possible, I assume it's much easier to just define a column as being Identity column and then create a 2nd computed column that would aggregate the name of the table with the value in the Identity column, so that I end up with a column holding the desired values? (I guess it would look something like "'tblEquities' + [Identity]")
Also, I have been asked to find out what SQL 2005 have/offers, in term of guaranteeing referential integrity. Are the "Database Diagram", as well as the "Relationships" dialog box, the tools that would meet that criteria or does SQL 2005 contain other tools that would help in this regard?
I have a varchar column in a table, which is an unique key column. Now when I design this table should I use this column as a primary key or should I add one more Integer column as a primary key column for the performance?
One more point is, when ever I do a search on this table I will search based on that unique varchar column values only, so even then if I add new integer column just for Primary key I will not use this column for searching values.
With these information, can some body help me in deciding this?
Discuss the following sql query with respect to performance in an applicaiton involving more number of concurrent users creating and deleting records. The objective is to create continuous primary key integer values.
Table name: SitePage
Column DataType --------- -----------
PageID BigINT PageName nchar(10)
Query to insert new record
DECLARE @intFlag INT SET @intFlag = 0 WHILE (@intFlag =0) BEGIN BEGIN TRY
[Code] ....
We don't want to use auto increment integer value for primary key because of the following reason
[URL] .....
We also don't want to use SEQUENCE as we have to create 50 sequence for 50 tables
This EXECUTES with no error or warning message.However, if I change this to CREATE the PK in an ALTER TABLE statement, I get the (expected by me) error:
==> Msg 8111, Level 16, State 1, Line 17 Cannot define PRIMARY KEY constraint on nullable column in table '#ABC'.
==> Msg 1750, Level 16, State 0, Line 17 Could not create constraint. See previous errors.
(note: As the #ABC table is an actual copy of a few of the columns in a "permanent" table, I will likely change the definition as follows such that the columns are defined to match the names / datatypes / NULLability:
SELECT TOP 0 CAST('01-01-1980' AS DATETIME) AS [ReportRunTime] ,SourceID ,VisitID ,BaseID ,OccurrenceSeqID
Not sure where to post this as it may be a form operation but here goes...I want to generate a custom unique identifier for a table similar to the Northwind Customers database (ie ALFKI, ANTON, etc...) which can be used as a descriptive identifier as well. For example, I have a Products table and I want to have my keys look like: CHR-0001-05."CHR" is my abbreviation for Chairs"0001" is the auto-incrementing number (in this case the first record of the table)"05" is the last 2 digits of the year recieved (2005)Can somebody please point me in the right direction? I'm having problems finding resources on the web relating to this topic (prolly not using the correct keywords for my searches)Cheers =)
I am not sure if this is a correct forum to discuss on the document posted @ http://www.microsoft.com/downloads/details.aspx?familyid=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&displaylang=en on SQL Server Integration Services (SSIS) Hands on Training - Creating Custom Components.
I am assuming Microsoft Developers are constantly monitoring this forum.
In the document - SSIS Creating a Custom Transformation Component .doc on Page 2 - Exercise 1 - Writing the no-op data flow transformation component - Task 1 - Create a new C# Class Library Project
The textual description talks about creating a new Visual C# Class Library project in VS 2005 but the screenshot accompanying it shows the creation of new "Integration Service Project" in VS 2005.
Please change the screenshot appropriately to avoid confusions.
We are developing a batabase which is meant for financial domain,so it will import data from different source system.. and data from our data base will be further passed to other applications.
In contex of our system integration with other data sources ,whether is it a good idea to have a auto integer primary key a or to implement some logic to generate primary key?
Can some one guide us to some pratical data base design case studies?or some best practices.?
What I want to do is add a column that has 5 values and create a row for each value
I want it too look like this -
024Swanson, Ronrswanson@tv.com a 024Swanson, Ronrswanson@tv.com b 024Swanson, Ronrswanson@tv.com c 024Swanson, Ronrswanson@tv.com d 024Swanson, Ronrswanson@tv.com e
[Code] ....
Currently all my query looks like is this -
SELECT EmpID, LastFirst, Email FROM dbo.EmpList WHERE (Active = 1)
I have a package that is going to have roughly 20 sequence containers in it. Each of these containers is going to have the same start task and the same end task. The data flow task(s) in each sequence container will differ. So I was thinking I would create a custom sequence container that would implement the common steps I need in each of my containers. I started to create a task that would inherit from Sequence, but I found that it is sealed. Bummer.
What is involved in creating a sequence task by inheriting from Task? I see that Sequence implements IDtsSequence. But looking at this interface, it seems like I would have to build the logic to execute the workflow contained in my sequence.
Completely new to SQL so wondered if anyone could help with an issue I'm sure there is a simple answer too.
Need to create a Primary Key for an existing table. Now I have managed to create a new column called _ID, which set the contents to NULL. So populated the column with the number 1 so that I could set it to no null.
Now when I run the query:
ALTER TABLE tablename ADD PRIMARY KEY (_ID);
I get the error:
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo._DATACAPTURE' and the index name 'PK___DATACAPTURE__145C0A3F'. The duplicate key value is (1).
Which is obviously due to the repeated 1 in _ID.
So I am thinking I just need to run a query that fills the _ID with a unique number.
Hello to everyone, I've a question about UDTs and the way I can use them to access tables and columns where they are applied in a SQL Server 2005 DB. I've already spent 2 days googling and MSDN reading but nothing helped me to solve my problem, thats why I'm posting it here (this is the second post, maybe the last one was in the wrong Forum).
The scenario follows:
I've created a UDT called MyUDT that exposes 2 properties MyTable, MyColumn, here its the code:
[Serializable] [SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000, Name = "MyUDT")] public class MyUDT : INullable, IBinarySerialize {
/// <summary> /// Set or Get the Table Name where the UDT is applied. /// </summary> public string MyTable { get { return this._myTable; } set { this._myTable = value; } }
/// <summary> /// Set or Get the Table's Column Name where the UDT is applied. /// </summary> public string MyColumn { get { return this._myColumn; } set { this._myColumn = value; } }
....
}
And here it's my question/s:
How can I expose the defined Properties (MyTable, MyColumn) in order to be directly used from SQL Server Management Studio within the Column Properties Panel?
If it is not possible, is there a way for any UDT to get back from the sql server execution context the table and the column where it is applied/used?
I need to solve that in order to later retrieve via SQL the Extended Table Properties where the UDT is used and make some work on presented MetaData. Thanks in advance, every answer/help will be very much appreciated.
Hi folks, i need ur guidance. I have a few DDL and DML scripts which i want to be automatically applied during MSDE setup; or an .MDF file be attached automatically. Please suggest an easier way!
I'm trying to create a custom data flow destination, and it has a custom property that needs to get value from variable(similar to the FileNameVariable property of Raw File Destination), how can I do that?
The main problem I am currently facing is that I do not have the following directory to place the DLL file: C:Program FilesMicrosoft SQL Server80ToolsReport Designer. It is the "Report Designer" part that is missing. I tried adding it, but that didn't work.
Hello all, I am developing my own custom export type via the IRenderingExtension interface, and I am having a fundamental problem getting it to work.
I followed this guide written by James Yip, http://forums.microsoft.com/TechNet/AddPost.aspx?ForumID=82&SiteID=17, and managed to compile my SampleRenderer class and properly configure SRSS so that my 'Sample' export format is now available in the export dropdown in Report Manager.
However, upon actually exporting a report, the result is a blank file. I have absolutely no idea why the stream I create isn't getting through to the final file.
I'm trying to create a composite Primary Key on a table. This is the SQL I've written:
CREATE TABLE BookingItems ( BookingID INT NOT NULL REFERENCES Bookings(BookingID), EquipmentTypeID INT NOT NULL REFERENCES EquipmentType(EquipmentTypeID), CONSTRAINT PK_BookingItems_id PRIMARY KEY (BookingID, EquipmentTypeID) )
Is this right? I'm trying to define a Primary Key made up of BookingID and EquipmentTypesID, which are both Foreign Keys as defined in the column definition.
Thanks to help from Ray Maio I have been able to create copies of various tables using the Select Into command. Ray also responded that this command does does not transfer Primary Key and Index information.
I have attempted to create an index by using the following statement:
create nonclustered index SchoolIndx on wrestlerstest (School)
I did not get an error, but can't tell ift the index was created. The question is, how do I find out if there is an index for the column School? Is there a command that will tell me. I'm just sending commands to the server via a short .asp program and don't really know how to obtain a message back. Could I find the information by browsing the
I need to create a new table in our database. This table is not linked into the existing schema in anyway, so i'm not sure if I need a primary key or not. either way, coudl anyone tell me how to create a primary key ni the CREATE TABLE statement. I have tried searching but cannot find the answer.
Hi all,I've spent hours trying to find the error in the following SQL 2000command:ALTER TABLE [ClientList] ADD CONSTRAINT [PK_ClientList] PRIMARY KEYCLUSTERED ([PhoneNo]) On [PRIMARY]Every time I try to "Execute" this from my (VB5) ODBC connection I get:Runtime error 3289;Syntax error in CONSTRAINT clauseFor the life of me I can see nothing wrong. I used Enterprise managerto create this statement, and I can create the primary key fine fromthere. The PhoneNo field does not allow NULLs.Everything is service-packed up to date. I have tried using[databasename].[dbo].[ClientList] and suchlike.I've tried to find a relevant manual, but my SQL Server developersguide suggests this should be ok and I can't see anything wrong in thebooks online.Can anybody please help?TIA
I designed dll file to get data from sql server, i am calling the dll from ssrs 2005 in preview , everything is ok but after deploying and put the dll file in C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerin and add this to rssrvpolicy.config
<CodeGroup class="SqlClientPermission" version="1" Name="MySecurityExtensionCodeGroup" Description="Code Group for Don's test security extension" PermissionSetName="FullTrust"> <IMembershipCondition class="UrlMembershipCondition" version="1" Url="C:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportServerinMyAssembly.dll" /> </CodeGroup>
I want to integrate SQL Server 2005 setup into my Testproject Configurator setup. SQL Server 2005 install files should not be included in TestProject setup. If SQL Server 2005 is not installed on the target computer and install files are available then TestProject setup will install SQL Server 2005 with myProjectInstance instance name.
how the setup can install the custom instance name.?
Hello, How are you? I have a problem when I try to create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE. When I€™m going to choose the data source, the SQL Server 2005 Compact Edition provider doesn€™t appear in the list. I installed the SQL Server 2005 Compact Edition from this page http://www.microsoft.com/downloads/details.aspx?FamilyId=%2085E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&displaylang=en. In the additional information says by installing SQLServerCE31-EN.msi installs the provider (System.Data.SqlServerCe.dll) in the GAC (global assembly cache) and registers the OLEDB provider (sqlceoledb30.dll). So, I don€™t understand why couldn€™t I create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE?
I would like my transformation to automatically create an output column for each input column. Any tips? I can't seem to determine which event to listen to or method to override.
I am building a DTS Package that is moving data from our webstore (written in house) to a Warehouse Management System(WMS - Turnkey) and I've encountered a problem. both pieces of software have an orders table and an Ordered_Items table, related by the order_ID (makes sense so far). Here is the problem. The primary key on the webstore's Ordered_Items table is a single column (basically an Identity variable), while the primary key on the WMS's Ordered_Items table is a dual column primary key, between the Order_ID and the Order_LineID, so the data should be stored like:
OrderID Order_LineID 1 1 2 1 2 2 2 3 3 1 3 2 4 1
Get the Idea? So I have to create this new Order_LineID column. How can I accomplish this with a SQL statement?
I have created a very simple table. Here is the script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[IndexTable]
GO
CREATE TABLE [dbo].[IndexTable] ( [Id] [int] NOT NULL , [Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IndexTable] ADD CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED ( [Id] ) ON [PRIMARY] GO
The records that i added are:
Id Code
1 a 2 b 3 aa 4 bb
Now when i query like
Select * from IndexTable
I expect the results as:
Id Code
1 a 3 aa 2 b 4 bb
as i have the clustered index on column Code.
But i m getting the results as:
Id Code
1 a 2 b 3 aa 4 bb
as per the primary key order that is a non clustered index.
