Im doing a quickie Access Project for a Fileroom orginization DB. Generally, Records are addressed by a 7 character unique ID, with all sorts of rules, assigned by another system. However we need to track Items that for various reasons do not exist in that system. For this, we enter a code that consists of 2 characters of invalid data, followed by a simple incrementing number. In Oracle, i would do this with a Sequence. How do I go about this in Sql Server?basicly, I want to write a trigger that says something like: pseudo code wrote:before insert: if :new.ItemType is Type3 Then-- File local to our file room :new.FileID = "IV" + getNextLocalNumber().ToString() end ifNote that the fileID is not a Key in the normal DB Sense. It is a neumonicly unique identifier w/ some other properties so that people can easily look things up.
Jamie writes "When Inserting a row on a table where the Primary key is a sequence number. Once inserted is there a key word to find out the Sequence number of the newley Created Row."
i'm creating a sql hoilday database and i need to write a sequence but i've been told its wrong but i dont know why, can anyone help? CREATE SEQUENCE SEQ_HOILDAY_SITES INSERT INTO Details_of_sites_visited(Code_of_the_sites, Sites_name) VALUES(SEQ_HOILDAY_SITES.NEXTVAL,'124','Yosemite National Park');
there is this little dumb thing i don't manage to understand about sequences when i create a table i put the id as the primary key and write something like this
Code Block[Id] [int] IDENTITY(1,1) NOT NULL,
so each time the sequence is incremented by,
but if i delete a line from a table and then insert a new one instead of inserting the id 3 for example it inserts id = 4
What is the escape sequence in a stored procedure?
Here is what I'm trying to achieve:
ALTER PROCEDURE Test ( @Func VarChar(1000) )
AS DECLARE @SQL VarChar(8000) SELECT @SQL = 'SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE ' + @Func
Now, my goal is to add single quote (') before @Func and another one after that. For eg, if @Func is "Test", I want my query to be SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE 'Test'
and NOT SELECT DISTINCT TNAME FROM TABLE WHERE FUNC LIKE Test
I have to identify missing records from the example below.
Category BatchNo TransactionNo
CAT1 1 1
CAT1 1 2
CAT1 2 3
CAT1 2 4
CAT1 2 5
CAT1 3 6
CAT1 3 7
CAT1 3 8
CAT1 5 12
CAT1 5 13
CAT1 5 14
CAT1 5 15
CAT1 7 18
CAT2 1 1
CAT2 1 2
CAT2 3 6
CAT2 3 7
CAT2 3 8
CAT2 3 9
CAT2 4 10
CAT2 4 11
CAT2 4 12
CAT2 6 14
I need a script that will identify missing records as below
Category BatchNo
CAT1 4
CAT1 6
CAT2 2
CAT2 5
I do not need to know that CAT1 8 and CAT2 7 are not there as they potentially have not been inserted yet.
I idealy want a nice clean SQL statement and do not particually want to insert new table's or triggers although views i Can deal with to an extent.
Considerations up to 50,000 records added per day!!! Only need script to run once a day and I have insert dates to help me. Only 12 Categorys Batch numbers always start at 1 for different categorys
I would like to use sequences and triggers to update table identity field with int value from sequence via before insert trigger. I'm searching on google for a few days and there are no same or identical article about this subject.
Is there any sample how to create table with column Id, Name, Comment and sequence (for generate int numbers for Int field in table) and trigger which will fired before insert and check is inserted Id is NULL and update this field from sequence or nothing if id is set up.
I am working on a text mining application wherein I need to detect unusual/anomalous sentences in text. Certain sentences, that I know occur very frequently, are given a likelihood of 0.2 by PredictCaseLikelihood. Other sentences that are just as frequent get a much higher likelihood (>0.9). I am using the NORMALIZED option. The only significant difference between these sentences is their length. The one with the lower likelihood has only 2 words in it, whereas the one with the higher likelihood has more than 10 words. The problem is that the shorter sentences end up being interpreted as anomalous, when in fact they are'nt. Any suggestions?
I've seen several posts about reading and writing files that have different record types with varying column metadata. My particular file has 11 record types plus several header types and looks something like:
<Header1>
<Header2>
<Detail01-#1>
<Subdetail02>
<Subdetail03>
...
<Detail01-#2>
<Subdetail02>
<Subdetail03>
...
...
Since i need to get different detail and subdetail records, i can't really use the technique of 3 dest file connection managers found in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=87269&SiteID=1
I've tried using an exec sql to get the main detail records and then a forech ADO en umerator that would get the subdetails, but it all seems so kludgy. I'm starting to think that I should just write the bulk of the file creation code in a c# app instead of trying to smush this into SSIS. Opinions? Am I missing some trick in SSIS?
My server is a dual AMD x64 2.19 GHz with 8 GB RAM running under Windows Server 2003 Enterprise Edition with service pack 1 installed. We have SQL 2000 32-bit Enterprise installed in the default instance. AWE is enabled using Dynamically configured SQL Server memory with 6215 MB minimum memory and 6656 maximum memory settings.
I have now installed, side-by-side, SQL Server 2005 Enterprise Edition in a separate named instance. Everything is running fine but I believe SQL Server2005 could run faster and need to ensure I am giving it plenty of resources. I realize AWE is not needed with SQL Server 2005 and I have seen suggestions to grant the SQL Server account the 'lock pages in memory' rights. This box only runs the SQL 2000 and SQL 2005 server databases and I would like to ensure, if possible, that each is splitting the available memory equally, at least until we can retire SQL Server 2000 next year. Any suggestions?
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
We have an old machine which holds SQL server 2000 database. We need to migrate a whole database to a new machine which has SQL server 2005.
When we tried to move whole database using Import and Export Wizard, only tables can be selected to import/export. However we want to import/export the whole database, including tables, stored procedure, view, etc. Which tool should we use?
When I proposed start to use SQL Server 2005 for new VS 2005 web sites, one of my co-workers responded that we will update the old SQL Server 2000 databases to SQL Server 2005 when we are ready to use 2005 SQL Server.
Questions: 1. Any expected problems to upgrade old 2000 databases to new 2005 SQL Server? 2. I have installed both 2005/Management Studio Express and 2000/Enterprise Manager in my PC. Any expected problems when running both 2000 and 2005 SQL Server at the same database server? 3. What is the best configuration for running SQL Server 2005 when we have old 2000 databases? Upgade or not upgrade?
I am getteing need help Query analyzer error Unable to connect server local Msg17, level 16,state 1 ODBC SQL server driver [DBNETLIB]SQL server does not exist
Hi, I am having a problem connecting my .net applications from the application server to the database server. When I run the application from my windows xp (sp2) box it works fine. When I try to connect via SQL Management Studio to the database server from the application server I get the same error. Here is the error: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Here is the Environment: App Server: Windows Server 2003 Standard Edition Inside Company's Firewall/ Network Database Server: Windows Server 2000 Advanced Edition SQL Server 2000 SP4 Remote Connections to the Server is checked Enable Protocols: Named Pipes & TCP/IP TCP/IP Port: 1402 (I don't know why it isn't the default of 1433) The db server is sitting out side the Company's firewall (don't ask me why). I can access it fine from inside the firewall on my XP box but not from windows server 2003. There is a web server outside the our network that also connects to the db server with no problem and that is running Windows Server 2003 Web Edition. I can ping the db server from the app server using the IP address. I tried using the IP address and the port 1402 in my connection string but that didn't work from any machine (XP and Server). I imagine the issue is somehow related to the company's firewall but why would it only block Windows Server 2003 and not XP? What do I tell the network admin to change? Any help would be appreciated. Thanks, Oran
if you can restore a database to Server B using Server A as the service. Meaning we would issue the command on Server A but somehow point to Server B as where we want the restore to happen.
The backup file would be in a location independent of both servers.
Dear all,On Win2000 server with SP3, I am trying to access a SQL Server 7.0database, "TestDB", from VB6 via a SQL Server ODBC system DSN using ADO2.7. In SQL Server Enterprise Manager, there is a login named "Tester".In its property window, NO "Server Roles" was assigned but its"Database Access" was set to "TestDB". This login was also made as theuser of "TestDB" with "public", "db_datareader" and "db_datawriter"selected as its "Database role membership". All the tables I am tryingto access in "TestDB" were created under "Tester".My code is like:Set conn = New ADODB.Connectionconn.Open "DSN=TestDSN;UID=Tester;PWD=test"Set cmd = New ADODB.Commandcmd.ActiveConnection = conncmd.CommandText = SQLset rs = cmd.Execute()If I set the SQL to something like "SELECT * FROM tbl_test", I alwaysget an error of "-2147217865" saying "[Microsoft][ODBC SQL ServerDriver][SQL Server] Invalid object name tbl_test". If I set the SQL to"SELECT * FROM Tester.tbl_test", everything runs properly. Could anyoneplease kindly advise why the first SQL is not working? Or in otherwords, why must I prefix the table name with its owner while the DBconnection is already made under that owner name? Thanks in advance.Tracy
When I try to migrate a database on a SQL Server 2000 server to a SQL Server 2005 server with the Copy Database Wizard of the SQL Server Management Studio, I'm confronted with the following problem;
Performing operation...
- Add log for package (Success) - Add task for transferring database objects (Success) - Create package (Success) - Start SQL Server Agent Job (Success) - Execute SQL Server Agent Job (Error) Messages * The job failed. Check the event log on the destination server for details. (Copy Database Wizard)
When I take a look at 'Event viewer' on the SQL 2005 server, the following error is displayed;
InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I already enabled the MSSQLSERVER network configuration protocols (TCP/IP and Named Pipes ).
My site works fine in VWD2008 express, but I get this error when I try to use it on my live website. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. According to this article: http://support.microsoft.com/kb/914277 I am supposed to:
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration. Ok, there is no such program in this folder. The only thing in there is "SQL Server Error and Usage Reporting"... The other thing I am greatly concerned with is this: All is want is for my webpages to be able to access my database for user authentication. I DO NOT want to grant the internet rights to remote connect to my database.
Hi All, Please some one help me... I have to insert a csv into one table in sql server. But the problem is the file is in one server and SQL SERVER 2005 is in other server.. how do i insert the file.... please help me.....
Hi Guys, I have had SQL Server Express and Sql Server Management Studio Express installed on my machine for some time and recently tried to install a trial of SQL Server 2005 as well. (Yes, I'm migrating from Visual Studio Express to Visual Studio Professional, just as in tended!) Everything went fine except that nothing seemed to be installed. I searched in all the obvious places - both on the Start/Programs menu and on the hard-drive: nothing. A check under Add/Remove Programs showed that Sql Server 2005 Express was installed, but called SQL Server 2005. So after a number of retries in which the install program kept saying that there was nothing to install, I selected every option under Advanced in the intall process. This resulted in stuff being installed and I now have SQL Server 2005 running on my machine, but no SQL Server Manager icon. My questions are:
Where can I find the SQL Server Manager executeable? Has anyone installed SQL Server Express, SQL Server Mangement Studio Express, SQL Server 2005 and SQL Server Management Studio on a single machine successfully? If so, what order did you install them in? (I'm planning on uninstalling everything SXQL Server related and starting again.) Thanks very much for your help. Regards Gary
Hi,Is it possible to install on a Windows server 2003,SQL SERVER 2000 in the folowing configuration :SQL server 7.0 as a default InstanceandSQL server 2000 as a named instance.Thanks for your answer.
Hi I have created a linked server from SQL Server 2005 (SP 1) to SQL Service 2000 (SP 4) with a sql server login that is available on both servers but with different passwords and permissions.
I am getting the following error while accessing the linked server in management studio based on the scenario given below ;
------ Error Message Starts OLE DB provider "SQLNCLI" for linked server "(SQL Server 2000 instance name)" returned message "Communication link failure". Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host. Msg 18456, Level 14, State 1, Line 0 Login failed for user 'abc'. ------ Error Message Ends
Consider login name is abc. Now this login abc has sysadmin rights on sql server 2005. The same login abc has only db_datareader rights on sql server 2000 on just one database and is not associated with any fixed server role.
I have configured the linked server using the following options; 1. I have tried impersonating login from SQL Server 2005 to SQL Server 2000 . 2. I have also tried specifying remote login / password option.
Anyone having any idea, would be of great help. Regards, Salman Shehbaz.
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
I have an intranet environment consists of two servers:
- An application server windows 2003 server with 64-bit server Sharepoint 2007 SP1 (Standard version for search) - A database server windows 2003 server with 64-bit SQL Server 2005 SP2 64-bit (Standard - 9.0.3054)
It happens regularly that the database server crash (frozen black screen) while the application server indexes (crawl) the content of the intranet site based on sharepoint. There is no alert / error in the observer of events, nor in the sql logs.
The crash is uncertain: dice once all goes well, soon after that crash. When i set the parameter search service (service management research), I can define a regulatory impact of the robot to change the number of documents at the same time : "Request 2 documents at the same time, the crash is more rare, "ask 64 documents at a time, the crash is more common.
My Intranet under sharepoint is composed of a collection site with a dozen sub-sites. There are 3 large library of PDF document. All done in the 4 go in terms of volume.
My problem for about 3-4 months. (Maybe more. Before, indexing was not yet in place).
Can I connect from a SQL Server 2005 database to a SQL Server 2000 database, without establishing a linked server connection.
I need to fire a SELECT query on a SQL Server 2000 database, but don't want to add it as a linked server. Is there any way I can do this or its not possible??
I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005. The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67. I have already installed the Sybase client software on the server.
I also created a SystemDSN on the SQL Server to connect to the Sybase server. I tested the connection and it was able to connect.
I ran the following code to create the linked server:
I then ran sp_tables_ex to make sure I could view the tables in the Sybase database. Here is the error message I get:
<code>
OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".
