Hi All:
Does anyone know where to find the property that disables the broadcast of the sql server from the point where you do not see the server show up in the list when one goes to
"New SQL Server Registration" -> "..."
beside the "Server" field. The dialog itself is titled "Registered SQL Server Properties". Assume both SQL Servers are both behind the firewall.
Hi All
I have an VB.NET 2005 application that open a connection on a database In SQL server 2005 and I want when any data were inserted by this application in a spacific table; the data base send a notification to the application. I did this scenario
I created a trigger on that table for insert, this trigger raise an informational error using RAISERROR and set the severity value with 10 as following
RAISERROR ('Warnning Message', 10,1)
Then in the application I handled the event InfoMessage of the connection to receive the informational message in the application as following
AddHandler DBConnection.InfoMessage, AddressOf My_InfoMessage
Public Sub My_InfoMessage(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)
MessageBox.Show("info message event: " & e.Message)
End Sub
The problem is that when two instance of the EXE work and every instance open a connection and data inserted in the table from one EXE of them the informational message is raised only to the EXE that inserted in the table and I want the informational message is raised to the two EXE in other words I want to broadcast the informational message to all connections opened on this database.
Could someone help me
Thnx
Hi All
I have an VB.NET 2005 application that open a connection on a database In SQL server 2005 and I want when any data were inserted by this application in a spacific table; the data base send a notification to the application. I did this scenario
I created a trigger on that table for insert, this trigger raise an informational error using RAISERROR and set the severity value with 10 as following
RAISERROR ('Warnning Message', 10,1)
Then in the application I handled the event InfoMessage of the connection to receive the informational message in the application as following
AddHandler DBConnection.InfoMessage, AddressOf My_InfoMessage
Public Sub My_InfoMessage(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlInfoMessageEventArgs)
MessageBox.Show("info message event: " & e.Message)
End Sub
The problem is that when two instance of the EXE work and every instance open a connection and data inserted in the table from one EXE of them the informational message is raised only to the EXE that inserted in the table and I want the informational message is raised to the two EXE in other words I want to broadcast the informational message to all connections opened on this database.
Could someone help me
Thnx
Hello,
I develop a database that notifies clients when data changes by sending an UDP broadcast message using an extended stored procedure. Now I want to use a CLR stored procedure to send the UDP broadcast instead:
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net.Sockets;
public partial class UserDefinedFunctions
{
[SqlProcedure]
public static void UdpSend(SqlString address, SqlInt32 port, SqlString message)
{
System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();
byte[] datagram = message.GetUnicodeBytes();
client.Send(datagram, datagram.Length, (string)address, (int)port);
}
};
I have found that to be allowed to send to 255.255.255.255 I must give the assembly permission set 'Unsafe'. If I change to 'External access' I get:
Msg 6522, Level 16, State 1, Procedure UdpSend, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'UdpSend':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.Sockets.Socket.CheckSetOptionPermissions(SocketOptionLevel optionLevel, SocketOptionName optionName)
at System.Net.Sockets.UdpClient.CheckForBroadcast(IPAddress ipAddress)
at System.Net.Sockets.UdpClient.Send(Byte[] dgram, Int32 bytes, String hostname, Int32 port)
at UserDefinedFunctions.UdpSend(SqlString address, SqlInt32 port, SqlString message)
I cannot use permission set 'Unsafe' in production environment, so what I want is to customize the effective permissions with higher resoloution than the three pre-defined permission sets 'Safe', 'External access' and 'Unsafe'. Except from what is allowed by 'Safe' I only want the permissions necessary to send an UDP broadcast.
Anyone who has something like this ?
Hi,I need to maintain both SQL Server 2000 and 2005 on my PC for supportpurposes. I am using the developer additions. After I installeverything (with no install errors) I can use SQL 2005 Mgt Studio fineuntil I bring up SQL 2000 Query Analyzer or Enterprise Mgr. After that,I cannot bring up SQL 2005 Mgt Studio. It get this message:Package 'Microsoft SQL Management Studio package' failed to loadI click OK, the message comes up again, I click OK again and then MgtStudio disappears.My default instance is under SQL 2000 and I created a named instanceunder SQL 2005 on my PC both with Local System Account.My order of installs was:1) SQL Server 2000 (Server tools)2) SQL Server 2000 SP43) SQL Server 2005 (DB Engine, Integration Services, and Workstationcomponents)I also have .NET 2005 Professional installed.The frustrating thing is I can find no log information anywhere --event log, under my Documents and Settings, or in the SQL Server 2005directories where it was installed.My guess is that when I used Query Analyzer it changed a settingsomewhere or prevented me from using Mgt Studio somehow, but I justcan't figure out what it did. I'd like to avoid a complete re-installwhere it may just happen again.Any help would be appreciated. Thanks much.Tom Vicker
View 2 Replies View Related1. Do SQL server versions 6.5 and 7.0 co-exist with each other on the same physical server[OS : WinNT 4.0 SP6].
2. Can I access the databases of different versions of SQL Server [Version 6.5 and 7.0] at the same time on a single server.
I downloaded Quest Software's freeware version ofComparison Suite. When I attempt to install, it tellsme that it requires .NET framework 1.1.4322. I alreadyhave .NET 2.0 installed (as part of the MS-SQL native client install)and I really don't want to mess that up.Is it *safe* to have the installer download/install .NET 1.1to coexist with 2.0? Is it likely to work?
View 1 Replies View RelatedCan someone show me some C# code for detecting if a SQL row exists or not? This seems like a very typical action and I cannot for the life of me find a tutorial online that explains this step. In my code I'm either going to INSERT or UPDATE a record. I tried sending a SELECT command through a ExecuteNonQuery, but only got -1 as a response. Apparently ExecuteNonQuery does not work with SELECT. I then saw that T-SQL has an EXISTS keyword, but I cannot see anyway to use that from within C#.So...can anyone share the typical code they use to identify if a row exists or not within a database. I guess I was execting there to be some method available to do this sort of thing.
View 1 Replies View Related
Hi,
I have a table with the follwing values
Table Cats
{
CatID, date
-----------------------
Cat1, D1
Cat2, D2
Cat3, D3
}
I just wanted to check whether Cat1 exists in the table. Can anyone post the query
Thanks
~MOhan
Hi Friends,I have one table in the databse,i.e userTable with one field userNameIn my form I have one Label ,textbox for entering the userName and One button for submit,So I am entering the data into the table(userTable) after clicking on the submit buttonBut my problem is before entering the data into the table I want to find wheather the given data exits or notif its not exists the data has to insert into the table otherwise its has to display the message"the user is already existed"for this I wrote the code like this in C# public Boolean isUserExists() { SqlCommand cmduserName = new SqlCommand("select count(*) from userTable where userName= " + txtuserName.Text + ")", conn); SqlDataReader rdr = null; rdr = cmduserName.ExecuteReader(); conn.Open(); int count = 0; while (rdr.NextResult()) { count = rdr.GetInt32(1); } conn.Close(); if (count == 0) { return false; } else { return true; } protected void Button1_Click(object sender, EventArgs e) { if(isUserExists()) { Response.Write("Opps ! User already Exists"); } SqlCommand adapInsert = new SqlCommand("insert into userName values('" + txtuserName.Text + "')",conn); conn.Open(); adapInsert.ExecuteNonQuery(); conn.Close(); Response.Write("data inserted"); } is it write or not because I am not getting the output .please tell me any one where I have to change the code ThanksGeeta
View 3 Replies View RelatedWe are currently running MS-SQL 6.5 and are getting new apps which require 7.0.
The new apps will be on their own server(s). My question is - Can a PC run both the
6.5 client and the 7.0 client (simultaneously) to access both 6.5 and 7.0?
So I want to check a directory for a particular extention of file. ex: *.txt, *.zip etc. (I'm using T-SQL by the way)
I tried this:
Code:
insert #a EXEC master..xp_fileexist 'G:wklyld_SQLRMAPOLLOADUntar*.txt'
this code works as long as I give it a specific file name, but if I try the *.txt or *.zip it wont work.
I've also been trying to run
Code:
EXEC MASTER.dbo.xp_cmdshell 'dir "G:wklyld_SQLRMAPOLLOADUntar*.*"
and then copy the results to a temp table and then run queries against the table. But I havent had much luck with that either.
Can anyone help? Thanks in advance.
I have created a table and want to alter that table.
I want to add identity to my existence column.
How to add identity to my existence column?
How is it possible to test at the beginning of a stored procedure if acursor I want to declare already exists? (So I don't cause an error bydeclaring it).ThanksBruno
View 2 Replies View RelatedI'm looking for an efficient t-sql script to loop through all usertables in a db and determine/print the value of each row having adatetime field (including cases where there are multiple datetimefields per row)Help greatly appreciated..
View 2 Replies View RelatedI have a webpage where users can connect with other users by sending them a request. Sending the request just puts a row in a connect table that contains the users id, the targetusers id, a requesteddate, an accepted date and a disconnectdate (for the original requester to end the connection and a reject bit the the target can reject the request. Hoever I need to check to assure that the connect does nt already exist either as pending (requestdate is not null and accept date is null) or both dates are not null (connection already complete.).
ALTER PROCEDURE [dbo].[requestConnect]
-- Add the parameters for the stored procedure here
@requestor int,
@requested int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
[Code] ....
Dear All,
I wanted to know how do I know or check that whether a column exists in a table. What function or method should I use to find out that a column has already exists in a table.
When I run a T-SQL script which i have written does not work. Here is how I have written:
IF Object_ID('ColumnA') IS NOT NULL
ALTER TABLE [dbo].[Table1] DROP COLUMN [ColumnA]
GO
I badly need some help.
Here is my query:
SELECT dnn_Roles.RoleName, xyzUser.FirstName, xyzUser.LastName, xyzUser.Email, xyzRewardPoint.Points, xyzRewardPoint.RewardID
FROM xyzRewardPoint INNER JOIN
xyzUser ON xyzRewardPoint.UserID = xyzUser.UserId INNER JOIN
dnn_UserRoles INNER JOIN
dnn_Roles ON dnn_UserRoles.RoleID = dnn_Roles.RoleID ON xyzUser.ProviderId = dnn_UserRoles.UserID
WHERE (dnn_UserRoles.RoleID = 3) OR
(dnn_UserRoles.RoleID = 4) OR
(dnn_UserRoles.RoleID = 6)
ORDER BY dnn_UserRoles.RoleID
What I need is to extend this query to detect any users who exist in dnn_UserRoles.RoleID 3, 4 or 6 but do not have a RewardID value of '43' in the xyzRewardPoint table.
Apologies if this has been answered before, but the "Search" function doesn't seem to be working on these forums the last couple of days.
I'd just like to check if a table already exists before dropping it so that I can avoid an error if it doesn't exist. Doing a web search, I've tried along the lines of
"If (object_id(sensor_stream) is not null) drop table sensor_stream"
and
"If exists (select * from sensor_stream) drop table sensor_stream"
In both of these cases I get the error: "There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = if ]"
Sooooo... what is the standard way to check for existence of a table before dropping it? Someone help? This seems like it should be simple, but I can't figure it out.
Thanks in advance!
Dana
Hi,
Does TSQL provide methods to check if a file exists?
For example, a TSQL script will read data in a .dbf file into SQL Server. It will check if the file exists before read. How to do this check?
Thank you.
Hi!
SS2005:
create table #tmp(a int)
create index idxt1 on #tmp(a)
insert into #tmp values (42)
select * from sys.indexes
where name like '%idx%'
order by name
But I can't see any rows about idxt1 :-(
If I say
create index idxt1 on #tmp(a)
againg I got error, because it exists.
How to check for existence using query?
B. D. Jensen
Hello All,
I'm trying to develop a stored procedure that would do one of TWO things:
1. Return a 'status' that a value does not exist, if I were to provide the parameter via an ASP.NET2.0 page
2. If it does exists, to return the row data associated with that value (id number)
The stored procedure would search a SQL Server table within it self first. It that fails it would look at an Oracle table (work order table). And if that fails to return a 'row' to look through another Oracle table (work request table). If that doesn't occur, then it would throw the result as described in #2.
If the result exists in one of the TWO Oracle tables it would then insert that row into the first SQL Server table that the stored procedure searched through AND would return the row set to the ASP.NET page.
While all this is happening, I was hoping to get some insight as to how to create a "Please Wait..." feedback and then moving to the final result.
Looking forward to the wise words of the many on this forum, as I have experienced in the past! :)
Check the field existence of a database table, if exist get the type, size, decimal ..etc attributes
I need SP
SP
(
@Tablename varchar(30),
@Fieldname varchar(30),
@existance char(1) OUTPUT,
@field_type varchar(30) OUTPUT,
@field_size int OUTPUT,
@field_decimal int OUTPUT
)
as
/* Below check the existance of a @Fieldname in given @Tablename */
/* And set the OUTPUT variables */
Thanks
we tried out the following code in query analyser -
create procedure TrialProc
as
select * from sakjdhf
when we executed this piece of TSQL in query analyser, we expected it to give an error or warning that no object by the name of sakjdhf exists ( as actually there is no such table or view in the database ). however to our surprise we got "command completed successfully " !!
does this mean the SQL server does not check for necessary objects when creating a stored procedure ? or is there some setting that we missed out whihch is causing SQL server to overlook the error in the code ?
I'm familiar with how to check for the existence of a table before dropping it using the following command:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[xxx]
How does one check for the existence of a temp table (using # syntax) before dropping it? I've tried various flavors of this command and none work. One flavor is
use tempdb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#xxx]
CREATE TABLE #xxx (
NumID INTEGER IDENTITY(1,1),
Exhibitor_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL
)
Thanks.
Nick
I've been matching some incoming contacts to existing contacts in a database and need to update, insert, or rematch based on the ifs below.
If name, phone, and email all provided for both, then use the highest Source. So if the Contact has a Source value of 5 and SourceContact has a ContactSource of 1, update Contact with SourceContact
If name, phone and email all provided, and source the same then update to new values.
If name and phone on SourceContact and name and Email on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and email on SourceContact and name and phone on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and phone on SourceContact and name and/or Phone is blank in Contact then update
If name and email on SourceContact and name and/or email is blank in Contact then update
If phone numbers can be different, just update record to SourceContact if it has a same or higher ContactSource
If email address do not match then set SourceContacts to -1 Contact_fk it was computed incorrectly. Both have a non blank email
If Contact_fk is 0 then it is a new contact and just insert it.
IF OBJECT_ID('dbo.SourceContact', 'U') IS NOT NULL DROP TABLE [dbo].[SourceContact];
CREATE TABLE [dbo].[SourceContact]
(
[SourceContact_pk]INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_SourceContact PRIMARY KEY CLUSTERED,
[ContactLastName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactLastName DEFAULT (''),
[ContactFirstName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactFirstName DEFAULT (''),
[code]....
I have a situation where our stored procedure inserts records from table_1 to table_2 when they don't already exist (uses the EXIST statement) on that table. If table_1 contains multiple records that are the same, it appears after the 1st record has been inserted, it does not recognize it as being there when it checks the existence when attempting to insert record 2.
Here's an example of the script:
insert into table_2 (col1,col2,col3)
select col1,col2,col3
from table_1 t1
where not exists (select '1'
from table_2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2
and t1.col3 = t2.col3)
Data from Table_1 -- Assume that table_2 does not contain these records
col1 col2 col3
AA 11 A1
AA 11 A1
BB 22 B2
All 3 records would be inserted to table_2 in this example.
This function, F_TEMP_TABLE_EXISTS, checks for the existence of a temp table (## name or # name), and returns a 1 if it exists, and returns a 0 if it doesn't exist.
The script creates the function and tests it. The expected test results are also included.
This was tested with SQL 2000 only.
if objectproperty(object_id('dbo.F_TEMP_TABLE_EXISTS'),'IsScalarFunction') = 1
begin drop function dbo.F_TEMP_TABLE_EXISTS end
go
create function dbo.F_TEMP_TABLE_EXISTS
( @temp_table_name sysname )
returns int
as
/*
Function: F_TEMP_TABLE_EXISTS
Checks for the existence of a temp table
(## name or # name), and returns a 1 if
it exists, and returns a 0 if it doesn't exist.
*/
begin
if exists (
select *
from
tempdb.dbo.sysobjects o
where
o.xtype in ('U')and
o.id = object_id( N'tempdb..'+@temp_table_name )
)
begin return 1 end
return 0
end
go
print 'Create temp tables for testing'
create table #temp (x int)
go
create table ##temp2 (x int)
go
print 'Test if temp tables exist'
select
[Table Exists] = dbo.F_TEMP_TABLE_EXISTS ( NM ),
[Table Name] = NM
from
(
select nm = '#temp' union all
select nm = '##temp2' union all
select nm = '##temp' union all
select nm = '#temp2'
) a
print 'Check if table #temp exists'
if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1
print '#temp exists'
else
print '#temp does not exist'
print 'Check if table ##temp4 exists'
if dbo.F_TEMP_TABLE_EXISTS ( '##temp4' ) = 1
print '##temp4 exists'
else
print '##temp4 does not exist'
go
-- Drop temp tables used for testing,
-- after using function F_TEMP_TABLE_EXISTS
-- to check if they exist.
if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1
begin
print 'drop table #temp'
drop table #temp
end
if dbo.F_TEMP_TABLE_EXISTS ( '##temp2' ) = 1
begin
print 'drop table ##temp2'
drop table ##temp2
end
Test Results:
Create temp tables for testing
Test if temp tables exist
Table Exists Table Name
------------ ----------
1 #temp
1 ##temp2
0 ##temp
0 #temp2
(4 row(s) affected)
Check if table #temp exists
#temp exists
Check if table ##temp4 exists
##temp4 does not exist
drop table #temp
drop table ##temp2
CODO ERGO SUM
I'm trying to figure out the best way to write a script to deploy environment variables to different servers. To create the variable I'm currently using catalog. create_environment_variable. I want to wrap that in an if not exist statement.I had thought about just blowing away all the variables and recreating them but I thought that wouldn't go over well in prod. I wasn't sure if by deleting the variable, references to the variable would be lost.
View 3 Replies View RelatedOK. Here's my situation. I check for the existence of a dummy .txt file using a script. I send an e-mail if it does not exist and exit package. The .txt file only exists if another .xls file is present which I import. However, during the validation phase of the package, the package fails because the .xls file does not exist. Is there a way to bypass the validation step? The only solution I came up with is to have a two-step job. The first runs the file check step and sends the e-mail. The second attemps to run the package and fails. Not a very graceful exit.
View 3 Replies View RelatedI have a data flow task inside a foreach loop container which will take multiple excel files and load into a sql table. Once all the excel files are loaded into the table, then at the end one of the column gets updated using execute sql task. Now I am trying to check for a file existence, if the file is not present in the folder then the data flow task should not be executed. Any help is greatly appreciated, I am thinking of using file system task for this, but not exactly sure. Thanks in advance.
Hi All,
Not played with SQL for a while and am a bit rusty so please excuse me if I sound like a demented idiot! )
It's for a data migration from something wierd to SQL, I just need to be able to advise whether a table is worth migrating or someone should manually enter the data (i.e. if only six random fields are populated in a table then get a secretary to enter it).
I was hoping someone might have already done this and have a query I can edit?!
As far as I can make out the query needs to loop through every table, loop through every row, check each field for an existance of data and output something useful.
I've stared at this for about 3 hours now and tried several different things and none work.....and it's doing my nut! (
Help!!
Regs
We have a critical Production database on which we want to setup Log-Shipping. We have also purchased the Symantec NetBackup utility for taking Backup to Tape Drives.
We Know that there are some inherent problems with using Log-Shipping and a Backup Strategy together, and thus we were finding out various ways in which we can run both in tandem.
One of them was taking the Symantec Log backups with Copy-Only option.
The main problem with this is that the Symantec Backups becomes dependent on the Log-Shipping Backups and also most of their Log-Backups become useless. Also, we are not sure whether the utility allows Copy-Only backups
The second alternative was to disable the Backup job of Log-Shipping on the Primary Server and to use the Log-Backups done by the Symantec utility for performing the Restores on the Secondary.
Thus, if Log-Shipping is scheduled to run say every 4 hrs, and the Symantec Log-backup happens every 1 hour, then at an interval of every 4 hrs, the Restore Job on the secondary will pick up the 4 backups done by the Symantec Backup utility and Restore each one of them in sequential manner.
But, I guess it is not easy to have a manual Restore Policy in place.
I was really banking on this solution until I found that the Restore Job of the Log-Shipping setup is dependent on the Filename of the Transaction log file which the systems generates automatically, and it won€™t be easy to create a customized Restore Job on the secondary server which takes in all the Log Backups generated by the Symantec Backup utility and Restore the Secondary database.
Have any one of you ever face this issue? Would like to know what is the best way to keep both of them running together.