I get an issue, that is: Once i expand databases from SQL Enterprise Manager, it takes a long time to wait displaying all names of databases. How should i do to prevent displaying all names of databases?
View 7 RepliesI get an issue, that is: Once i expand databases from SQL Enterprise Manager, it takes a long time to wait displaying all names of databases. How should i do to prevent displaying all names of databases?
View 1 Replies View RelatedHi,
We have a point of sale application (C# .NET 2.0) and a Sql Server 2005 database back end.
Our customers are concerned that employees could create a backup of the SQL Server database (or even of the MDF file) and use it to steel customer data.
Very often, the application is running on a single PC in a shop using Sql Server Express Edition 2005 under Windows XP. The users usually log on as local administrator. It's hard for us to force our customers to change their local security policies.
Ideally, I would like some form of security mechanism that prevents a backup from being restored on to another PC without either a password or some other form of authentication.
Is this possible?
Regards,
Sigol.
This is strange.
We use Merge (pull) replication to get data from the "mother ship" updated to the laptops of 15 sales reps before they head out into the field, where they work disconnected. At end of day, they all reconnect and all the data is once again shared.
We've been having problems with the replication, but that's another topic.
This morning, I logged onto the server to check the replication monitor, and found that all of the subscriber names for that particular subscription are blank! They show the open/close bracket ( [] ) followed by the database name. This is under the My Publishers >> [server name] >> [publication name] node in the explorer tree. If I try to double-click on one of the subscribers to get their detail, I get tne message, "Replication Monitor could not open the Detail Window." and "Specified cast is not valid".
If I look at the publication in SQL Server Management Studio, all of the subscriber names show up just fine. But they do NOT show up under Replication Monitor.
No one touched the database over the weekend, and this was working just fine on Friday afternoon.
Has anyone seen this before? What to do?
Based on our database infrastructure, we need to secure our SQL databases. The security issue concerns on allowing a limited number of Domain Admin users to access the SQL databases.
We tried certain ways, based on the documents in the Microsoft web site, but we couldn€™t reach to the point of preventing the Domain Admin users accessing the SQL databases.
Thanks in advance.
How can i get names of all databases using sql statements or system stored procedures? Thanks in advance!
View 2 Replies View RelatedI have databases a1 , a2 , a3 , a4 ... an.
Each has a table t1 .
I am writing a stored proc which compares the t1 table between any given two databases.
I want to pass the database name as a variable ..
so the SPROC looks like
exec sp_comparetable 'a3' , 'a7'
My question is , inside the SPROC ,how do I refer to the tables in the a3 and a7 databases.
Thanks,
Alok.
Hi, I am trying to get all the databases from MS SQL into a dropdownlist. However, I am not sure how to do that in query ... is there a way to retrieve all databases name using a query in MS SQL? Thanks.
View 1 Replies View RelatedI want to get the names of the databases of the server using OleDbConnection in c# which i have to add in to a combobox.....the same task i achieved using SqlConnection where in there is a Class SqlClientCollectionNames...the code is as followsusing (SqlConnection connection =new SqlConnection("Data Source=server;User ID=username;Password=passwd")) { connection.Open(); DataTable myData = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases); foreach (DataRow row in myData.Rows) comboBox1.Items.Add(row[0]); connection.Close();} but the same i did for OleDbConnection it is not working using (OleDbConnection connection =new OleDbConnection("Provider=SQLOLEDB;Data Source=server;User ID=username;Password=passwd"))
{
connection.Open();
DataTable myData = connection.GetSchema(SqlClientMetaDataCollectionNames.Databases);//no such method Databases present in OleDb namespace
foreach (DataRow row in myData.Rows)
comboBox1.Items.Add(row[0]);
connection.Close();
} is there any other way to do this using OleDbConnection........??Please help
I want to get all databases with it's owner Name ,I think I need to join following script result with sys.databases or sys.sysdatabases.I want a result set  like this:
DatabaseName   db_Owner
master          sa
pubs           valia
pubs           eli
pubs           maryam
AdentureWork    eli
this script get all owners of the database that is in use:
Use DatabaseName
GO
SELECT  members.name MemberName, roles.name RoleName,roles.type_desc,members.type_desc
Is there a query or sp that I can pull all databases on the server along with their logical file names and physical file names?
View 5 Replies View RelatedI am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
As part if a recent requirement I have to export Chinese/Singaporean names in a CSV file. The data in the tables is a NVARCHAR(256).
I am using a FlatFile Connection manager where all the present columns from the table are exported as NVARCHARs. My understanding was that the Chinese/Singaporean names would blend seamlessly with NVARCHARs in place. But, they get garbled when pushed to the CSV.
Here is the connection manager setup
There are a lot of suggestions of fixing this by copying/pasting to a notepad file and changing the formatting... But I cant do that since the file is generated using a schedules SSIS package. How can I tweak the process to fix the issue?
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
I'm going to be setting up DB mirroring between two SQL Server 2005 boxes. Since these are on two different servers, can the instance names be the same? Is there any reason NOT to do so if the mirror server is going to be used exclusively for DB mirroring?
For example: if the my primary DB is located on SERVER1INSTANCE1, can the mirror be SERVER2INSTANCE1 or do the instance names have to be different even though they're on different boxes.
Thanks!
I'm trying to do an update query that looks like this:
UPDATE
PAEMPLOYEE
SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL
FROM
PAEMPLOYEE A
JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE
It's erroring out on the Employee prefix B.EMPLOYEE saying:
..."does not match with a table name or alias name used in the query"
Is it wrong or will it cause problems to have a field name the same as the table name?
Hello there. I'm trying to populate a drop down box with a list of all available instances of SQL server (express or not) available on a network. To list all instances I'm using EnumAvailableServers() in SmoApplication.
The issue is that the Instance column in the returned data table is always empty, and both the Name and the Server columns contain the same string -- the name of the computer the SQL server is installed. Locally I have SSE installed, the instance name is <computer_name>SQLEXPRESS, however, using Smo I can't seem to get to the either full instance name or just the instance name. I can't assume SQLEXPRESS, since there may be more than one installed.
Alternately I used EnumRegisteredServers() in SmoApplication.SqlServerRegistrations and that, while returning instance information (can be retrieved from the RegisteredServer in the returned collection), doesn't always return the remote SQL servers in the list.
Any idea how can this be done? Maybe I'm going at it the wrong way, maybe there's another class / method I need to use to retrieve all instances?
Thank you
I used to do this with classic asp but I'm not sure how to do it with .net.Basically I would take a table of Categories, Then I would loop through those. Within each loop I would call another stored procedure to get each item in that Category. I'll try to explain, Lets say category 2 has a player Reggie Bush and a player Drew Brees, and category 5 has Michael Vick, but the other categories have no items.Just for an example.. Category Table: ID Category1 Saints2 Falcons3 Bucaneers4 Chargers5 FalconsPlayer Table:ID CategoryID Player News Player Last Updated1 1 Reggie Bush Poetry in motion 9/21/20062 1 Drew Brees What shoulder injury? 9/18/20063 5 Michael Vick Break a leg, seriously. 9/20/2006 Basically I would need to display on a page:SaintsReggie BushPoetry in MotionFalconsMichael VickBreak a leg, seriously.So that the Drew Brees update doesnt display, only the Reggie Bush one, which is the latest.I have my stored procedures put together to do this. I just don't know how to loop through and display it on a page. Right now I have two datareaders in the code behind but ideally something like this, I would think the code would go on the page itself, around the html.
View 1 Replies View RelatedI am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add
db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID);
Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie
My C# update method in the DAL (still working on the code)
private static bool Update(AvatarImageInfo avatarImage)
{
//Invoke a SQL command and return true if the update was successful.
db.ExecuteNonQuery("syl_AvatarImageUpdate",
avatarImage.AvatarImageID,
avatarImage.DateAdded,
avatarImage.ImageName,
avatarImage.ImagePath,
avatarImage.IsApproved);
return true;
}
I am using stored procedures to access the data in the database.
My update stored proc
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate]
@AvatarImageID int,
@DateAdded datetime,
@ImageName nvarchar(64),
@ImagePath nvarchar(64),
@IsApproved bit
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY
UPDATE [syl_AvatarImages]
SET
[DateAdded] = @DateAdded,
[ImageName] = @ImageName,
[ImagePath] = @ImagePath,
[IsApproved] = @IsApproved
WHERE [AvatarImageID] = @AvatarImageID
RETURN
END TRY
BEGIN CATCH
--Execute LogError SP
EXECUTE [dbo].[syl_LogError];
--Being in a Catch Block indicates failure.
--Force RETURN to -1 for consistency (other return values are generated, such as -6).
RETURN -1
END CATCH
END
Have a job that calls a DTS package, DTS is an Export & Import wizard to copy tables. Someone deleted a table from source and my job failed last night. Inputs appreaciated.
View 12 Replies View RelatedI'm going thru my application log, and just seeing what errors are popping up. I have a relatively intense search feature, thats causing alot of deadlocks.
Exception type: SqlException
Exception message: Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
In general, what's the best way to resolve this ?
Should I see if I can apply "WITH (NOLOCK)" to my data ?
Any suggestions are greatly appreciated !
thanks again!
mike123
Hi
I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.
Any help/direction here would be appreciated.
Hi,I'm using SQL Server 2000 MSDE on a laptop running Windows XP.I have a couple of SP's that that quite some time to compile. So I waswondering: is there any way to have the database *not* recompile them everytime after a reboot?BOL says: "As a database is changed by such actions as adding indexes orchanging data in indexed columns, the original query plans used to accessits tables should be optimized again by recompiling them. This optimizationhappens automatically the first time a stored procedure is run afterMicrosoft® SQL ServerT 2000 is restarted."Now the SQL Server is restarted a lot, because laptops don't have endlessbatteries <g>Cheers,Bas
View 3 Replies View RelatedDear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
Hello
I noticed a spelling mistake in the data in a column of several tables, I used the following syntax to alter the spelling:
UPDATE [dbo].[Prod_Cat] SET [ProdName]=N'merseyside' WHERE ProdName = 'mmserseyside'
The above code correctly updated the spelling error, but it also inserted a new row with the corrected data.
So I found myself with two Identical rows containing the corrected information. I had to manually delete the extra row. Because if I had put in a DELETE statement, I would have then lost both rows.
What do I need to do to prevent this happening next time. As I find that I need to update the names of some products, but I don't want to duplicate them.
Thanks
Lynn
How can I prevent duplicate inserts or entries to a table?
Thank you.
Note: I am using SQL Server and coding ASP.net pages in VB.
this is a question I put in the sql community in microsoft, but havent be answered in full
------------
I am using dynamic sql to do a query with differents 'order' sentences and/or 'where' sentences depending on a variable I pass to the sp
ex:
create proc ex
@orden varchar(100)
@criterio varchar(100)
as
declare consulta varchar(4000)
set consulta=N'select pais from paises where '+@criterio' order by '+@orden
------------
I'd like to know it it uses 2 sp in the cache, as I read, the main sp and the query inside the variable of the dynamic sql. if so, as I imagine, then I suppose I have to do the main sp without any 'if' sentence to be the same sp, and so taking it from the cache and not recompile the sp
now, I have various 'if' sentences in the main sp (the caller of the dynamic sql) but I plan to remove them and do the 'if' by program -it is in asp.net-, so I suppose it is better because in this way the main sp is took from the cache, supposing this uses the cache different that the dynamic sql in the variable
what do u think? does the dynamic sql use 2 caches? if so, u think it is better to try to do the main sp same in all uses (no 'if' statements)?
-----
They told me this coding is not good (dynamic sql) because it can give control to the user?
I ask, how does it give control to use? what ar sql injection attack and how to prevent them?
I use dynamis sql because I have 150 queries to do, and thought dynamic sql is good
is it true that dynamic sql have to be recompiled in each execution? I suppose so only if the sql variable is different, right?
can u help me?
Hi,
On my site I have a simple textbox which is a keyword search, people type a keyword and then that looks in 3 colums of an SQL database and returns any matches
The code is basic i.e. SELECT * FROM Table WHERE Column1 LIKE %searcg%
There is no validation of what goes into the text box and I am worried about SQL injection, what can I do to minimize the risk
I have just tried the site and put in two single quotes as the search term, this crashed the script so I know I am vunerable.
Can anyone help, perhaps point me in the direction of furthur resources on the subject?
Thanks
Ben
I have a web form that I use to insert data into a sql database. I want to know how to prevent inserting duplicate records into the database. Thanks.
View 6 Replies View RelatedI want to be able to read and update a value in the database without entering a race condition.
For example:
User #1 reads a row from the database, changes a value then writes the value back.
User #2 reads the same row AFTER user #1 has read it, but BEFORE user #1 writes it back. User #2 then changes the value and writes it back, overwriting the value that user #1 wrote.
I thought I could do this with transactions, but it just makes user #2 wait until user #1 is done writing before user #2 can write. It doesn't stop user #2 from reading while user #1 has it out.
Does that make sense?
hi I have a table I need to have a process which prevent a user from entering a name value( company Name ) in a field. how can I do that .
Ahmed
Is there a way to monitor all ODBC connections to a MSSQL server and prevent a username/ODBC combination. My problem is that we have many frontends for viewing reports, but we managed them all and users are not allow to make their own connections. Some users now uses MSAccess over ODBC to draw their own reports - they have all the permissions as neededd by the other apps.
thanx
Hi all,
I'm writing a trigger to prevent duplicates. I know that this can be done through primary key or unique constraints but in the real world my uniqueness is defined by 8 columns which is too a big an index to maintain on the primary / unique key.
If I create a table with 2 columns
CREATE TABLE Table1
(CentreCHAR(10),
Month CHAR(3)
)
Then create a trigger to prevent duplicates
CREATE TRIGGER trigger_Check_Duplicates
ON Table1
FOR INSERT, UPDATE
AS
-- This trigger has been created to check that duplicate rows are not inserted into AudioVisual table.
DECLARE @IsDuplicate INTEGER
-- Check if row exists
SELECT @IsDuplicate = 1
FROM Inserted i, Table1 t
WHERE t.Centre = i.Centre
AND t.Month = i.Month
IF (@IsDuplicate = 1)
-- Display Error and then Rollback transaction
BEGIN
RAISERROR ('This row already exists in the table', 16, 1)
ROLLBACK TRANSACTION
END
Then insert a row into the new table (no other data is in there)
INSERT Table1
VALUES('0691040176','AUG')
I get the Trigger error message that the row already exists. Why is this the case? I though that Table 1 (target table) would show no entries as it has no data - it should be a before image of the table and the inserted table should be an after image.
Please help!!!
Thanks
Neill