DB Design :: Prevent Server From Automatically Adding Brackets
Aug 17, 2015
I have a problem setting the default value of a column. I am trying to set it to
(CONVERT([float],getdate()+(2),0))
However, SQL Server automatically sets it to
(CONVERT([float],getdate()+(2),(0)))
While it functionally does not change anything, we have a tool which compares the database schema against a pre-existing schema and shows this as an error.I have tried setting the value directly and through scripts but it does not work either way.
i insert the names of the collumns in the design of the sql server and in some of the names it add the brackets. for example [LabsRadiologyBiochemAmylase>125mMolPLitre]. i try to remove them but when i save it, it write them again. can someone explain me how to remove the brackets???
I have a report that gets two parameters of type date. These parameters have default values. When I press preview the report runs automatically. I want to prevent the report from running automatically, and let it run only when I press the button "view report".
automatically replicates new databases to Availability Group partners - if you do a little prep work on your environment first.To make it work:
1) Create linked servers on all group members pointing to all other servers in the group, with names matching the hostnames they represent. 2) Ensure suitable credentials (or 'current context' impersonation) for linked servers. Also: Enable RPC and RPC OUT 3) Run the DDL code below. 4) Schedule hadr_replicate_queue on [master] to run as often as you want initial syncs to occur. Every 5-10 minutes is plenty for most purposes. 5) Connect to an availability group listener and call CREATE DATABASE :)
I use a slightly more extended version of this code at home to do things like permissions synchronization across replicas - I essentially allow applications to install direct to an availability group replica and then have all the relevant objects replicate to other nodes. I don't really like going through manually and doing things, even though there's an AddIn from SQLSkills for management studio - it still requires manual intervention.
The main use I have for this at home is that I'm using the Azure pack, and want to automatically ensure that my newly created 'SQL Server Cloud' databases are highly available, plus it means when I install a non-alwayson aware product it doesn't require any extra work afterwards to allow failover to another machine.
* AlwaysOn Self-Population Script * By: Steve Gray / steve@mostlyharmful.net * Usage: Free, but buy me a beer if you're ever in Brisbane. **/ USE [master] GO IF EXISTS (SELECT * FROM sys.tables WHERE name='hadr_pending_replicate')
I have a question for you. I have a product database with a lookup that pulls by product number. All my product numbers are made up the same way. IE. N59840, N00951, N00951. ect.
I have a stored procedure that looks up by that product number with a "LIKE" statement that looks like this.
WHERE ([Product#] LIKE '%' + @PRODUCTNUM + '%')
Which has this problem if someone types in "852" it returns
N00852 N05852 N98852
Is there anyway that I can have SQL put in zeros to fill up the 5 number spots so "852" brings up "00852" or "5852" brings up "05852"
I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'
Below is Query 1:
Declare @testvar char(20) Set @testvar = 'noname' Select @testvar= pub_name FROM publishers WHERE pub_id= '999' Select @testvar
Out put of this query is 'Noname'
BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement
Declare @testvar char(20) Set @testvar = 'noname' Select @testvar=(Select pub_name FROM publishers WHERE pub_id= '999') Select @testvar
I have a rather complex query (to me at least) that I need to create but I am unsure of where to start. The query requires me to copy existing data into a new row (which will then create a new ID) as well as update all existing records with the newly created id. More specifically, I need to separate the data associated with LocationID 219 from it's parent, CompanyID 992.
Ideally I want to copy the data associated with LocationId 219 and then make a new CompanyId with the copied data (which will also create a new LocationID). Since this new record is no longer going to be associated with CompanyID 992 I will want to remove/delete/drop it from that record.
Finally, and perhaps most difficult of all, I need to update all tables that reference the old ID's together (992 / 219) to reflect the newly created Company ID and Location ID.
Here is the DDL of a table that I am trying to add an identity column and create a clustered index. I am using SQL 2012 SE
CREATE TABLE [dbo].[IPF]( [IPFId] [uniqueidentifier] NOT NULL, [IPId] [uniqueidentifier] NOT NULL, [FId] [uniqueidentifier] NULL, [FName] [nvarchar](50) NULL, [FItemId] [uniqueidentifier] NULL,
[code]....
The table currently has 220million rows. I am trying to add a new identity new column and create a clustered index on it. I ran the script and its been more than 26hrs and its still running. I ensured there is no blocking.
Nice easy one (hopefully) from a newbie on SQL 2000.
I have a table HolidayTakenBooked which is populated from a stored procedure via the following statement;
TRUNCATE TABLE HolidayTakenBooked INSERT INTO HolidayTakenBooked SELECT * FROM #TMP_HolidayTakenBooked ORDER BY ABR_Clock_No
I am finding that for certain values in the HolidayTakenBooked table decimals are not being transferred correctly. ie. 0.5 in the TMP table appears as 1 in the HolidayTakenBooked table.
I'm pretty sure that this is down to the data definition of the table see sample field below; [HOL_DaysTaken1] [decimal](18, 0) NULL ,
So the simple question here is how do I define decimal places when I define a new table. When designing a new table in Enterprise Manager I select decimal and the server does not allow me to change the value of 9 it defaults to.
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
I have come up with one scenarios where I have three table like Product, Services and Subscription. I have to create one table say Bundle where I can have some of the product id , service id and Subscription id , i.e. a bundle may contains sum prduct , services and subscription . How I can design these relations ?
how do i get rid of the brackets without using any function? i was reading performance tuning. it says its better not to use functions as it slows down the SP.
I am using the following statement but seem to be getting a Syntax Error. I think this is something to do witt the fact that I don't have brackets around the values. However, no matter where I try to put the brackets I seem to get an error!
Any help would be much appreciated!
Thanks,
Jon
INSERT INTO organisation_links (organisation_number_1, organisation_number_2, relationship, amended_on, amended_by) VALUES 2786, Select organisation_number, 'HEAD', '01/12/2007', 'Jon' from organisations where organisation_number IN (143, 177)
I am using Enterprise Manager to create database tables. In the table design view I am trying to create a column called, section. After typing, section, Enterprise Manager automatically puts brackets around the name, ex. [section]. When I view the table by returning all rows the brackets are gone. However, when I go back to desing view the brackets are there. Why is this happening and what affect does it have on my database?
Hi All,I was hoping someone has experienced this before, I'm having troublegoogling this. I'm working with a poorly writtend database that has somefields named as such: MTIC_PROD_VEND[ 1]I do not have the option to rename this field but I do need to retreive datafrom it with SQL. I've tried a suggestion of [MTIC_PROD_VEND[ 1]]] and thatdoesn't seem to work, it may be because the 1 has a space leading it. Doesanyone happen to have any suggestion to try to work around this? Anyassistance would be greatly appreciated. Thanks.Regards,Stephan
I am looking for Articles or Examples on implementing a Single Elimination Tournament architecture in SQL Server. Bracketology, Playoff Bracket, Single Elimination Tournament, whatever you want to call it.
I need the solution to be able to support all sizes of brackets (from 8 - 64 teams and everything in between).
Any "starts in the right direction" would be much appreciated.
Pretty new to Mysql, and have a query that if anyone can help me with id be very grateful!!
I am using the UPDATE command and wish to copy a columns data to another column, but the first column contains text then (text in a bracket) i wish to copy everything from the first column except whats in the bracket to column 2???
example...
UPDATE table_name SET `field1` = `field2`...............???
Hi, On x64 versions of Windows server 2003, there is a folder called "Program Files (x86)". The use of parenthesis in this name has caused a few issues so far, particularly with connecting SQL Server 2005 to Oracle using SSIS. After several hours of searching I realized that the parenthesis was causing it, and eventually I found that the best way is to avoid creating such a name while installing the OS itself, by using winnt.sif. This would be better than renaming it and changing registry entries after the installation is done.
I realize this may not be the best question to ask of Microsoft, but why not just call it "Program Filesx86" by default so no special procedure has to be done to get around these issues? The use of parenthesis is likely to cause even more compatibility issues with other software later?
BTW I use Microsoft products regularly and love most of them
Suppose I have a database storing some confidential information, such as legal information, medical or financial records, etc., and a Web site with a membership system so that only authorized users can view the information.
I understand I can encrypt the information, and the user's passwords, so that if the database is compromised it still shouldn't be possible for an outsider to view the confidential information.
However, what about people who have legitimate access to the database, such as the DBA, Web developer, etc., but who should not be able to view the confidential information? For example, even though the user's password was encrypted, what would stop the DBA from replacing the user's password with his own (encrypted) password, then logging in and viewing the user's info, then copying back the original encrypted password? Or, adding a new user for himself with whatever permissions he chooses?
Need some information regarding SQL Server locking.Requirement is to run SQL Statements for reports (read-only) that willnot lock-out other people trying to update (write lock). For this, wetried the following to make the db connection not deny write locks(our report code will not have a read-only lock).ADODB.ConnectionObj.Mode =adModeShareDenyNoneBut this made our connection bumped whenever someone tried to update.Tried enabling dirty reads by doing:ADODB.ConnectionObj.IsolationLevel =adXactBrowseEven this made our connection bumped whenever someone tried to update.Is there a way to achieve what is needed without having a seperatedatabase for reports?
Does anyone have a good strategy or technique for preventing database drops in SQL Server 2000? I know in 2005 DDL triggers rock, but in 2000 what can you do to audit who drops a database why keeping the same permissions intact.
What I want is a trigger that is specific to my DB called "JunkStuff". I only want to block a servername from connect to my super dooper DB "JunkStuff".
I created account with permissions in order my program (I use C#.net) can connect to SQL Server 2008 by this account But I don't want that user can use this account to login to DB by SQL Management Studio for security purpose So how to prevent user login to SQL Management Studio ?
Can I setup "db_denydatareader" to MASTER db to prevent the access ?
I tried to use dbo.tables of Northwind database in SQL Server Express to do SqlCommand to populate a DataTable in DataSet. The SqlCommand failed in the project execution and I found that Northwind database in SQL Server Express is busted and gone (just the name "Northwind" remained in the SQL Server Management Studio Express). How can I prevent it from happening in my "SqlCommand-SqlConnection" project? Please help and advise.
I tried to repair my "Northwind" database by using the SQL2000SampleDb.msi of Northwind and pubs Sample Databases for SQL Server 2000 downloaded from the http://www.microsoft.com/downloads. My "pubs" database is still in my SQL Server Management Studio Express. How can I just repair my "Northwind" database by using the Microsoft SQL2000SampleDb.msi program? Please help and advise this matter too.
@RemoteQuery consists of a SELECT four-table join, all tables are on the same linked server.
The Linked server has been set up on MyLocalServer using the "Microsoft OLE DB for SQL Server" provider. In the "Provider Options" for the linked server properties I checked "Non transacted updates" and "dynamic parameters". In the "Server Options" tab I have checked "RPC", "RPC Out", "Data Access".
The EXECUTE part of the query runs great (and returns the data very fast) by itself. But with the INSERT part, the query fails and returns the error:
"Server: Msg 7391, Level 16, State 1, Line 17 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."
The two servers are seperated by firewalls, so I believe the reason the query is failing is that I haven't followed the procedures for setting up the ports etc described in one of the microsoft support articles: e.g.: 250367.
Configuring the ports involves too much company politics, and besides, for what this query does, it does not need the benefits of a distributed transaction.
How can I execute my query without SQL Server automatically trying to upgrade it to a distributed transaction?
More Info: I can execute the query as a straight INSERT/SELECT linked-server query and it does the INSERT on the local SQL Server just like I want it to, so I assume it is not trying to use distributed transactions; but it takes around 7 seconds to run even though the entire SELECT is executed on the linked server, whereas executing with sp_executesql takes only 1 second.
I thought selected "non-transacted updates" in the provider would solve this problem, but it did not.
I'm looking for a way to get the name of the server on which the DTS package lives.
I copy packages between servers. The problem is that everytime a package is copied to different server, I have to change the reference in the connection strings to point to the new server name. I'd like to find an automatic way to interrogate the server name where the package currently lives and dynamically change connection strings from within an ActiveX task. That would cut maintenance way down.
