|
El`Cachubrey wrote: In my table i have coulmn which should contain customer e-mail address, can i and how if i can
make for this column constraint based on string pattern (is this achived througth
CONSTRAINT Col LIKE '...'????)
Errm. No.
It is not the place of the database engine to enforce things like this because this is a business rule. If you don't want email addresses to look like this, then you need to have a rule that enforces it.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hey all,
I have a very big table (about 165 million rows) in a Sql Server 2005 database. I also have a number of stored procedures which access this table - mostly 'select' type read only queries.
There is one particular select-based stored proc that gets called repeatedly, and the strange thing is the difference in time it takes to execute. Most of the time its quick, but occasionally its very, very slow. When it gets slow, if I open the proc up in Sql Management studio, and resubmit it by simply hitting F5 - the problem goes away.
I suspect this is because the current execution plan gets trashed and a new more effecient one generated when I resubmit it. By using the query plan viewer it has become apparant that Sql Server does vary its execution plan of this proc even though its specification doesn't change. Sometimes the execution plan is good and sometimes it isn't.
Anyone experienced anything like this or any hints or tips?
Regards,
Rob Philpott.
|
|
|
|
|
The problem might not be with this procedure, but with other resources. For instance, if you have a table or lock occurring in one of the tables that is used in the proc then you will find that your procedure will be affected. The execution plan won't change, but your procedure will perform slowly.
What you may want to do is to run SQL Profiler against the database and leave it running for a while. Once you get the problem, then you should be able to analyse what's going on.
As a thought, have you checked the statistics on the table? Is this table frequently updated?
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
hi
i need to select a data from a sybase table into a sql server 2005 table.i dont know whts is the query to do it, let me show u my code.i get error when i do that query
select * from openrowset('MSDASQL','dsn=bodaily;uid=sa;pwd=welco me;Initial Catalog=phoenix;',
' select rim from rm_acct')
this rm_acct is a sybase table
can anyone guide me on this.
thanks
|
|
|
|
|
I use SQL-Server 2000 at the moment. Our approach was to set up a linked-server then use:
select * from openquery(MySybaseServer, 'select * from Products')
We also found that the Sybase ODBC driver ran faster than the OLEDB driver (although our tests were carried out a few years ago).
Hope this helps.
Andy
|
|
|
|
|
Hi,
I'm using an Access database in my C# win application.
I need to sum values from two different tables and then subtract one of the SUM values from the other.
No problem until I runn into one of the SUM values = null. I need it to return 0 if null is the result of the SUM.
What is the best way to handle this... with an IIF?
IIF(SUM(OrderTotal) IS NULL,0,SUM(OrderTotal)) ???
Thanks for any help you can give.
Ron
|
|
|
|
|
|
|
Thanks andyharman,
I tried but it came up with an error so I researched it.
Looks like NZ can't be used with SQL OLE DB, just within MS Access program.
ron
|
|
|
|
|
If you are using SQL Server then the syntax is:
isnull(sum(YourColumn), 0)
|
|
|
|
|
Hi,
I am using SQL Server 2005.
Can someone please explain to me the differenc between a login, and a database user?? I am working through a SQL book and they created a login, then a database user. Is the login always the same as the database user? If not then when will they differ? I have the following piece of code:
-- Create the login Peter
CREATE LOGIN Peter WITH PASSWORD='Abc123';
-- Change the connection context to the database AdventureWorks
USE AdventureWorks;
Go
-- Create the database user Peter, mapped to the login Peter in the database AdventureWorks
CREATE USER Peter FOR LOGIN Peter;
I hope someone can help me clarify this issue.
Regards
ma se
|
|
|
|
|
The login is used for authentication and the user for authorisation.
In otherwords the login is used to ensure that the person or process connecting is permitted to connect to the server. The user dictates the permissions once someone is logged in.
If you create a login, but only create a user for that login on one database then that is the only database they can access. They can't use any of the other databases.
You can then GRANT, DENY or REVOKE permissions on any objects within a table based on the user.
Another way to look at it are that users are per database, logins are server wide.
|
|
|
|
|
|
A 'login' lets you converse with the server. A 'user' record allows you to gain access to a particular database on that server. This allows you to run numerous databases a single server and control security on login by login basis. Example: You don't want your custodial staff to have any access to your accounting database but, you do want them to access building inspection records.
|
|
|
|
|
Hello,
I want to alter a column in a table. The column is called GroupID and is the primary key in this table.
Currently the groupID is a auto incremented and a int datatype. Now I need to change this just to a int and not have the auto increment.
I am not sure of the syntax of how to do this. I know you can add and drop columns, but can you change from an auto increment to not a auto increment.
Many thanks for any syntax you can offer me.
Steve
|
|
|
|
|
|
The docs say this attribute must be applied to any assembly to allow it to be deployed to SQL Server, and that it is automatically added to AssemblyInfo.cs in SQL Server Projects.
I have a SQL Server project that I have just deployed to my SQL Server, and this attribute isn't applied to the assembly, and it wasn't added to AssemblyInfo.cs.
What is the story with this attribute?
|
|
|
|
|
Hi,
I am trying to import data from an Access database to SQL 2005 Express Edition. Express does not have an Import Export wizard. How can I get the data from a Access DB to SQL?
Thank you!
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
You could create a number of "Linked Tables" within MS-Access that point to you SQL-Server instance - then use "INSERT INTO MyLinkedTable SELECT * FROM MyAccessTable" SQL queries to copy the data.
|
|
|
|
|
This also depend on if the destination database have the schema allready set up? Are they on the same computer? If you need to create the database, schema from the access db in code, you could import it into a datatable and then use SMO and sqlbulkcopy to create the destination schema and tranfser the data.
Click on my alias and take a look at the article and see if that might help. SMO is a little buggy as a coworker of mine has realized.
Let me know
AAron
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
Hi All,
Iam new to ado.net
I want to know merge()method of dataset when we use this,
Wat is AcceptChanges() method of dataset when it is required.
Pls describe with sample example.
Thanks in Advance
|
|
|
|
|
Hello plz refer it.........
DataSet.Merge (DataRow[]) Merges an array of DataRow objects into the current DataSet.
DataSet.Merge (DataSet) Merges a specified DataSet and its schema into the current DataSet.
DataSet.Merge (DataTable) Merges a specified DataTable and its schema into the current DataSet.
DataSet.Merge (DataSet, Boolean) Merges a specified DataSet and its schema into the current DataSet, preserving or discarding any changes in this DataSet according to the given argument.
DataSet.Merge (DataRow[], Boolean, MissingSchemaAction) Merges an array of DataRow objects into the current DataSet, preserving or discarding changes in the DataSet and handling an incompatible schema according to the given arguments.
DataSet.Merge (DataSet, Boolean, MissingSchemaAction) Merges a specified DataSet and its schema with the current DataSet, preserving or discarding changes in the current DataSet and handling an incompatible schema according to the given arguments.
DataSet.Merge (DataTable, Boolean, MissingSchemaAction) Merges a specified DataTable and its schema into the current DataSet, preserving or discarding changes in the DataSet and handling an incompatible schema according to the given arguments
(2) AcceptChanges() Call when u sure u'r all statement exected succesfully, Otherwise use RejectChanges()
Parwej Back...............DON of Developer.......
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
Hi all,
This is the first time I set up an SQL database (I worked with mdb before), so please excuse me if I ask the most basic thing here .
Here is the situation. I am developing a VC++ program that connects to a local SQL Express 2005 database locally, this works fine. This is the connection string that I use:
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Uid=myUsername;Pwd=myPassword;
Now when I set up this SQL Express 2005 on WinServer 2003 and ran the program, it worked as well. But when I ran the program from a client connected to the server, it wouldn't connect (the program crashed). Do I need to install "Microsoft SQL Server Native Client" on it as well? or do I need different connection string?
Could anyone please give me some pointers here. Thanks in advance.
|
|
|
|
|
first check if ur sqlserver2005 is enabled for remote . normally it is disabled. then change ur sonnection string..
Your Connection String
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS;Database=myDatabase;Uid=myUsernamewd=myPassword;
New Connection String
Provider=SQLNCLI;Server=myComputerName\MYSQLEXPRESS,1433;Database=myDatabase;Uid=myUsernamewd=myPassword;
The only change i have made is that, I have added a ,1433 in servername and i think u know what it is default remote port for accessing SQLSERVER 2005,
|
|
|
|
|
Would the connection string with 1433 work for local connection as well?
This way I can test it locally right now and I don't have to differentiate between local and remote connection strings
I am guessing that enabling remote connection is done thru Surface Area Configuration?
Thanks for your help.
|
|
|
|