Hi
I'm new to SQL Server but not to VB or Access, but would like to design a database in SQL Server for a set of users who currently do not have SQL Server loaded on any of their networked machines or their Network server. Can I design a database on my machine which has SQL Server and then load it all onto their server without them having SQL Server, or do I have to load SQL Server onto their server?
Thanks for any help.
hi all, I am considering what should be the best way of implementingthe following requirement.I've got a SQL2K production server. Now I've got another machine as thestandby machine for this serverso I'm thinking what method should I be using for this.Should I be using log shipping or Replication? Or if it's replication,what kind of replicationsshould it be?I am thinking maybe snapshot replication can be just fine, right?
View 4 Replies View RelatedHello!
I developed database driven .NET application and I need to deploy it. I faced a problem, which is "how to protect my database against direct access". I use MS SQL Server 2005 Express Edition as a DBMS and appropriate database.
I want to make possible to manipulate with data in my database only through my client application.
1. How do I define SA password and instance name in silent mode of MS SQL 2005 EE installation with Mixed type of Authentication?
1.1. Can I change SA password after the installation?
2. If my database be attached to my new instance... Is it possible to copy my database, attach it to another instance and get a direct access to its objects?
Is there solution that make impossible to connect to my database on third-partie's side Idirectly, without using the client application?
I appriciate for a help.
We are unable to connect with Client SQL database server using VPN connection, even we are able connect with their intranet site.
We tried to connect using IP address, but no success. After that tried to ping that server with server name and IP address using command prompt, but got request time out error.
On Windows XP systems I get the following issue when trying to browse the MSDB folder in SSIS
Client unable to establish connection
Encryption not supported on SQL Server. (Microsoft SQL Native Client)
I have noticed another post where several others have noticed the same issue. It appears to only occur on Windows XP installations. Is there a workaround or fix for this?
I have three tables, Employer, Customer and CustomerEmployer. The employer table contains all employers and the customeremployer table contains customer records for an employer. For example
Employer Table
EmployerID Name Address
1 ABC Company 123 Main Street
2 CDE Company 1 South Street
3 JJJ Company 3 Timothy Street
4 ZZZ Company 2 Rust Street
Customer Employer Table
ID EmployerID CustomerID
1 3 1
2 3 2
Customer Table
ID Name
1 Joe Smith
2 Jane Thomas
3 Tim James
I would like to run a select statement which lists all the employers and whether the employer has already been assigned to a customer. For example, for Joe Smith I would like to see all the employers listed AND some marker indicating that Joe is already assigned to EmployerID 3, JJJ Company.
Results
EmployerID Name Address CustomerID
1 ABC Company 123 Main Street
2 CDE Company 1 South Street
3 JJJ Company 3 Timothy Street 3
4 ZZZ Company 2 Rust Street
I have tried unions and many types of joins but none seem to work correctly. I was using the following select statement however only employers that are contained in the customeremployer table appear.
SELECT Employer.EmployerID, Employer.Name, Address, Employer.City,
CustomerEmployer.CustomerID
FROM CustomerEmployer RIGHT OUTER JOIN
Employer ON CustomerEmployer.EmployerID = Employer.EmployerID
WHERE (CustomerEmployer.CustomerID = @customerid) OR (CustomerEmployer.CustomerID IS NULL)
ORDER BY Name
Any ideas?
Thanks.
Today a vendor bluntly stated that VMWare provides the same failover and redundancy for SQL that would render "AlwaysOn" high availability unnecessary.
Essentially that VMWare would detect a problem and failover and have .9999 uptime .
hi all,
i am working on a project, where i need to do one to one conversion of reports from crystal report to SSRS.
i am facing a problem in Crystal report there is a group where Sorting order is Orginal Order.
whereas there is no such facility in SSRS where i can provide sorting order as Original Order.
i dont know how to achive this if any body have the solution or faced similar problem then let me know.
i am taking an example
data returned from the data base
A 1
A 2
A 5
B 1
B 2
A 7
B 6
then the grouping is done like
A 1
2
3
B 1
2
A 7
B 6
thanks
Mohit Gupta
Hi try to connect with driver {SQL Sever Client} but i can't
I'm have SQL Server 2005 Express SP2
Error connecting to the ODBC server:[Microsoft][SQL Native Client][SQL Server] Changed database context to..
Please Help me
Thanks
Hi,
My code should copy files from a shared folder.
the share can be accessed by all who can provide a specified username and password.
I use the following code:
But where to specify username name password to access that folder?
DECLARE @cmdstr varchar(1000)
set @cmdstr = 'copy \servernamefoldername$ C:DataExTest'
print @cmdstr
EXEC xp_cmdshell @cmdstr
Any sort of help would be highly appreciated.
Thanks in advance
I apologize if this is not the correct forum for this posting. Looking at the descriptions, it appeared to be the best choice.
I am running Windows XP Pro SP2. I have installed the SQL Native Client for
XP. However, when I try to add a new data source through ODBC Connection
Manager, SQL Native Client is not listed as an option. I have followed this procedure on three other systems with no problems. What would be causing the
SQL Native Client to not show up in the list of available ODBC data sources?
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TESTÂ TO Satish.
Hi!
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
Thanks!
Karen
Dear Experts,
i'm going to create a new database for the client, the initial size might be 10GB. so please guide me what will be datafile size and log file size?
i'm not creating secondary datafiles. is it ok?
Vinod
Even you learn 1%, Learn it with 100% confidence.
Does anyone have a sample of what a database would look like that would support multiple clients in a single ASP.net application?
Thank you,
I have developed a VS2008 .net application using Sql Server express 2005. Running this on my development machine works fine but after i deploy to a client machine i get:
Cannot open user default database. Login Failed for user 'mycomputernameadministrator'
The client machine has sqlserver express 2005 installed using all instllation defaults
My connection string is:
Data Source=.SQLEXPRESS;AttachDbFilename=c:mydatamyfile.mdf;Integrated Security=True;User Instance=False"
I want to be able to deploy my app to any client PC without any database user login (i use my own built in to app) or errors obvoiusly
Could someone give me a step by step to resolve, much appreciated
Chris Anderson
Hi all...
I am writing a Windows Application which will be used by about 100 clients. (NT Authenticated)
The application is going to be used in a LAN enviroment. User who have access will download a VB.NET application from a web site.
Of course, I want to prevent any of the 100 users from using a tool such as Query Analysis or ODBC to connect directly to the database and modify data.
So, it appears the way to go is to the "Application Roles". For this to work, the application roll password has to be saved in the application to execute the sp_setapprole procedure.
Is there a way to secure this password? I am worried about a user somehow extracting the password from the application and connecting directly.
Am I on the right track here? Any ideas appreciated!
Thanks,
Forch
Hi All,
My apologies if this question seems abit basic, but I'm a DBA by trade and programming .Net isn't my strong point ;)
I've enabled database mirroring on 3 SQL 2005 servers, a principal, a mirror and a witness.
Principal - SQL 2005 Enterprise Edition, SP1
Mirror - SQL 2005 Enterprise Edition, SP1
Witness - SQL 2005 Express, SP1
I've written some test code to test the mirroring but as soon as the connection is pulled from the principal, the client re-direct doesn't work and the program bombs. I'd be grateful fi someone could have a look at my code below and tell me if there's any schoolboy errors??
(NB, the user running the code is a sys admin on all 3 servers)
Thanks in advance.
Imports System.Data.SqlClient
Public Class Form1
Dim DCTADS As New SqlClient.SqlConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DCTADS.ConnectionString = "Network=dbmssocn; Server=STUD; Failover Partner=DBDRTEST; Database=mirrortest; Integrated Security=True"
DCTADS.Open()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New SqlClient.SqlCommand
cmd.CommandText = "Select fulldesc from unclproduct where internal = '20000110'"
cmd.Connection = DCTADS
Dim dr As SqlClient.SqlDataReader
dr = cmd.ExecuteReader
MsgBox(DCTADS.DataSource.ToString)
If dr.Read Then
MsgBox(dr("fulldesc").ToString)
End If
dr.Close()
End Sub
End Class
We have 15 clients running our applicaton
14 of then conected to SQL server using TCP/IP and it runs fine
1 of 15 when connected using TCP/IP produce "..Time out error "
but runs fine when swiched from TCP/IP to Named pipes
1.What area should we look to correct problem with Time out using TCP/IP ?
2. Where to get information about using TCP/IP via Named pipes ?
Product: Microsoft SQL Server 2005 -- Error 29515. SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]Encryption not supported on the client. Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
View 78 Replies View RelatedHi,
I have SQL2000 installed as the default instance, and now I'm trying to install SQL 2005 standard edition as a named instance.
I receive this error :
SQL Server could not connect to database service for server configuration.. [SQL Native client] Encryption not supported on the client. However I'm able to install client tools
The setup works fine on other box with the same config : SQL 2000/Windows XP, is there any work around for this issue ?
In my SQL 2000 client network utilty "Force proctocol encryption " is desabled and did not find the setting for SQL 2005 !
Thank you
I'm trying to work out a database design to make it quicker for my clientprogram to read and display updates to the data set. Currently it reads inthe entire data set again after each change, which was acceptable when thedata set was small but now it's large enough to start causing noticabledelays. I've come up with a possible solution but am looking for others'input on its suitability to the problem.Here is the DDL for one of the tables:create table epl_packages(customer varchar(8) not null, -- package_type char not null, -- primary keypackage_no int not null, -- /dimensions varchar(50) not null default(0),weight_kg int not null,despatch_id int, -- filled in on despatchloaded bit not null default(0),item_count int not null default(0))alter table epl_packagesadd constraint pk_epl_packagesprimary key (customer, package_type, package_no)My first thought was to add a datetime column to each table to record thetime of the last change, but that would only work for inserts and updates.So I figured that a separate table for deletions would make this complete.DDL would be something like:create table epl_packages(customer varchar(8) not null,package_type char not null,package_no int not null,dimensions varchar(50) not null default(0),weight_kg int not null,despatch_id int,loaded bit not null default(0),item_count int not null default(0),last_update_time datetime default(getdate()) -- new column)alter table epl_packagesadd constraint pk_epl_packagesprimary key (customer, package_type, package_no)create table epl_packages_deletions(delete_time datetime,customer varchar(8) not null,package_type char not null,package_no int not null)And then these triggers on update and delete (insert is handled automaticallyby the default constraint on last_update_time):create trigger tr_upd_epl_packageson epl_packagesfor updateas-- check for primary key changeif (columns_updated() & 1792) > 0 -- first three columns: 256+512+1024insert epl_packages_deletionsselectgetdate(),customer,package_type,package_nofrom deletedupdate Aset last_update_time = getdate()from epl_packages Ajoin inserted Bon A.customer = B.customer andA.package_type = B.package_type andA.package_no = B.package_nogocreate trigger tr_del_epl_packageson epl_packagesfor deleteasinsert epl_packages_deletionsselectgetdate(),customer,package_type,package_nofrom deletedgoThe client program would then do the initial read as follows:select getdate()selectcustomer,package_type,package_no,dimensions,weight_kg,despatch_id,loaded,item_countfrom epl_packageswherecustomer = {current customer}order bycustomer,package_type,package_noIt would store the output of getdate() to be used in subsequent updates,which would be read from the server as follows:select getdate()selectcustomer,package_type,package_no,dimensions,weight_kg,despatch_id,loaded,item_countfrom epl_packageswherecustomer = {current customer} andlast_update_time > {output of getdate() from previous read}order bycustomer,package_type,package_noselectcustomer,package_type,package_nofrom epl_packages_deletionswherecustomer = {current customer} anddelete_time > {output of getdate() from previous read}The client program will then apply the deletions and the updated/insertedrows, in that order. This would be done for each table displayed in theclient.Any critical comments on this approach and any improvements that couldbe made would be much appreciated!
View 4 Replies View RelatedHello,The PRINT command works fine on Query Analyzer.However, when I used it with other Database Client,eg: Aqua Data Studio, nothing got printed out.Is there a way to make it work?Thanks in advance.
View 3 Replies View RelatedI want to deploy my database application to my client's computer. My application uses SQL Server 2014. My client has to run that software on a single PC. I was looking to download a lightweight version of SQL Server that can fulfill my requirements, so when I was looking for SQL Server downloads, I saw these files there, which file should I use in my situation
Express 32BIT WoW64SQLEXPR32_x86_ENU.exe
Express 32BITSQLEXPR_x86_ENU.exe
Express 64BITSQLEXPR_x64_ENU.exe
ExpressAdv 32BITSQLEXPRADV_x86_ENU.exe
ExpressAdv 64BITSQLEXPRADV_x64_ENU.exe
ExpressAndTools 32BITSQLEXPRWT_x86_ENU.exe
ExpressAndTools 64BITSQLEXPRWT_x64_ENU.exe
LocalDB 32BITSqlLocalDB.msi
LocalDB 64BITSqlLocalDB.msi
MgmtStudio 32BITSQLManagementStudio_x86_ENU.exe
MgmtStudio 64BITSQLManagementStudio_x64_ENU.exe
Hi All,
My apologies if this question seems abit basic, but I'm a DBA by trade and programming .Net isn't my strong point ;)
I've enabled database mirroring on 3 SQL 2005 servers, a principal, a mirror and a witness.
Principal - SQL 2005 Enterprise Edition, SP1
Mirror - SQL 2005 Enterprise Edition, SP1
Witness - SQL 2005 Express, SP1
I've written some test code to test the mirroring but as soon as the connection is pulled from the principal, the client re-direct doesn't work and the program bombs. I'd be grateful fi someone could have a look at my code below and tell me if there's any schoolboy errors??
(NB, the user running the code is a sys admin on all 3 servers)
Thanks in advance.
Imports System.Data.SqlClient
Public Class Form1
Dim DCTADS As New SqlClient.SqlConnection
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DCTADS.ConnectionString = "Network=dbmssocn; Server=STUD; Failover Partner=DBDRTEST; Database=mirrortest; Integrated Security=True"
DCTADS.Open()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cmd As New SqlClient.SqlCommand
cmd.CommandText = "Select fulldesc from unclproduct where internal = '20000110'"
cmd.Connection = DCTADS
Dim dr As SqlClient.SqlDataReader
dr = cmd.ExecuteReader
MsgBox(DCTADS.DataSource.ToString)
If dr.Read Then
MsgBox(dr("fulldesc").ToString)
End If
dr.Close()
End Sub
End Class
I have created a PPC application that uses ADS to synchronize my sqlce db with a desktop MS Access database. It works fine on my computers, but my client cant get it to work on his. Here is the situation.
His "Computer Name" as it's stated in his 'My Computer' properties is "PAVSGP024FE.NWFSC.NOAA.GOV"
However his Access.MDW File is located in the directory "C:Documents and SettingsJoe NobodyApplication DataMicrosoftAccessSystem.MDW".
There is no directory called "C:Documents and SettingsPAVSGP024FE.NWFSC.NOAA.GOVApplication DataMicrosoftAccessSystem.MDW".
His current config.xml file looks like this.
<?xml version="1.0" encoding="UTF-8"?>
<SystemNode>
<AccessDB>C:Program FilesNOAA Release LoggerNOAA_ReleaseLogger.mdb</AccessDB>
<DeviceDB>Program FilesNOAA Release LoggerNOAA_ReleaseLogger.sdf</DeviceDB>
<Login>Joe Nobody</Login>
<URL>http://Joe Nobody:1024/</URL>
<WinDir>C:</WinDir>
</SystemNode>
He has tried adding port 1024 as an exception for his firewall settings, has installed Access Database Synchronizer on his computer, and I have also tried using the "long" computer name as the url setting in the config file. (i.e. <URL>http://PAVSGP024FE.NWFSC.NOAA.GOV:1024/</URL> but this throws the error
"The HTTP request failed due to incorrect format or content. Try restarting the IIS server.[]"
I dont know what else to try! Anybody have any suggestions?
We have a small accounting application which is currently based using DBASE database. We need to change the DB and considering SQL Express. However, is some one can clarify following, it would be very helpful:
1) Application is used mostly by standalone non-technical users. There are cases where more than one user will need to connect to DB.
2) We need to ensure that user can not modify database outside of our application. This is needed to ensure database does not get currpted or passwords lost and then no one can open the database.
3) Installation needs to be simple without providing any options to users except where to install database or point to already installed DB in case its a network environment where 2-3 users can be working on the same database.
4) Application is usually installed on normal desktop machines. So, DB should not load the PC heavily.
Please advice if SQL Express is the right direction even with these constraints? What are the other alternatives? We are open to have a small consulting project as well with someone who can guide us through these issues. Email to contact is rkabra101@yahoo.com
rick
i am using sql2005 on win2k3(sbs) platform(domain concept).my client is winxp.when i am tring to connect from client pc i am getting following error message.
connection failed
sql state 01000
sql server error 233
microsoft odbc sql server driver(dbnetlib) connection open (connect)
connection failed
sql state 08001
sql server error 17
microsoft odbc sql server driver(dbnetlib)sql server does not exist or access denied
please solve this problem.
I am currently building an application that will host multiple clients in the same database. Each client will have their own schema. Common data will be stored in [dbo] tables commonly accessible. Client data will be stored in matching schema-specific tables.
EX:
[ClientA].[Orders] ...
[ClientB].[Orders] ...
[ClientC].[Orders] ...
So far so good.
Upon issuing select statements under the various logins, the proper table is accessed and improper tables are protected.
The problem comes when working with the various stored procs to access the data.
My original though was that I would have one version of the stored procs and they would be calling-schema aware, much like select statements are, and deal with the proper table data. What seems to be the case is that each client needs its own copy of ALL the stored procs, creating a maintenance nightmare considering each client's procs is identical in EVERY SINGLE WAY except for the owning schema.
It seems once a proc is called the ownership chain changes to the proc's schema and ignores the caller from there on out. Am I missing a step in this or do I have the proper understanding (and problem).
Example:
Attached is a sample database script.
Four (matching) tables. Stored proc for accessing data via a table function. Three logins dealing with various iterations:
login: clienta
setup: Entry point proc set, table function missing (will call dbo version)
result: Permission error on call to table function. Fix that and pulls data from dbo table
login: clientb
setup: Entry point proc using dbo, schema-specific table function set
result: Calls dbo version of function (not schema version) and pulls dbo table data
login: clientc
setup: Entry point proc set, table function set
result: Correct data pulled from schema table
login: db owner login
setup: Both dbo proc and dbo function set
result: DBO table data pulled
======================================================
-- ====================================================================
--
-- Create Database SchemaTest
--
-- ====================================================================
CREATE DATABASE SchemaTest
GO
USE SchemaTest
GO
-- ====================================================================
--
-- Create Users and Schemas
--
-- ====================================================================
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'clienta')
CREATE USER [clienta] FOR LOGIN [clienta] WITH DEFAULT_SCHEMA=[ClientA]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'clientb')
CREATE USER [clientb] FOR LOGIN [clientb] WITH DEFAULT_SCHEMA=[ClientB]
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'clientc')
CREATE USER [clientc] FOR LOGIN [clientc] WITH DEFAULT_SCHEMA=[ClientC]
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ClientA')
EXEC sys.sp_executesql N'CREATE SCHEMA [ClientA] AUTHORIZATION [clienta]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ClientB')
EXEC sys.sp_executesql N'CREATE SCHEMA [ClientB] AUTHORIZATION [dbo]'
GO
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'ClientC')
EXEC sys.sp_executesql N'CREATE SCHEMA [ClientC] AUTHORIZATION [dbo]'
GO
-- ====================================================================
--
-- Create Tables SType for each Schema
--
-- ====================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ClientA].[SType]') AND type in (N'U'))
BEGIN
CREATE TABLE [ClientA].[SType](
[TypeID] [int] IDENTITY(1,1) NOT NULL,
[Text] [varchar](50) NOT NULL,
[Description] [varchar](100) NULL,
CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED
(
[TypeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ClientB].[SType]') AND type in (N'U'))
BEGIN
CREATE TABLE [ClientB].[SType](
[TypeID] [int] IDENTITY(1,1) NOT NULL,
[Text] [varchar](50) NOT NULL,
[Description] [varchar](100) NULL,
CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED
(
[TypeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ClientC].[SType]') AND type in (N'U'))
BEGIN
CREATE TABLE [ClientC].[SType](
[TypeID] [int] IDENTITY(1,1) NOT NULL,
[Text] [varchar](50) NOT NULL,
[Description] [varchar](100) NULL,
CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED
(
[TypeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SType](
[TypeID] [int] IDENTITY(1,1) NOT NULL,
[Text] [varchar](50) NOT NULL,
[Description] [varchar](100) NULL,
CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED
(
[TypeID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
-- ========================================================
-- Insert Sample Data
--
-- ========================================================
INSERT INTO [ClientA].[SType]
([Text])
VALUES ('A Data')
INSERT INTO [ClientB].[SType]
([Text])
VALUES ('B Data')
INSERT INTO [ClientC].[SType]
([Text])
VALUES ('C Data')
INSERT INTO [dbo].[SType]
([Text])
VALUES ('dbo Master Data')
GO
-- ====================================================================
--
-- Create proc GimmeTable for each Schema
--
-- ClientA has version
-- ClientB does not have version (will use dbo version)
-- ClientC has version
--
-- ====================================================================
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GimmeTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GimmeTable]
AS
BEGIN
SELECT * from tableSType()
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ClientA].[GimmeTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [ClientA].[GimmeTable]
AS
BEGIN
SELECT * from tableSType()
END
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ClientC].[GimmeTable]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE [ClientC].[GimmeTable]
AS
BEGIN
SELECT * from tableSType()
END
'
END
GO
-- ====================================================================
--
-- Create table function tableSType() for each Schema
--
-- ClientA does not have version (will use dbo version)
-- ClientB has version
-- ClientC has version
--
-- ====================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ClientB].[tableSType]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [ClientB].[tableSType]
()
RETURNS TABLE
AS
RETURN
(
select * from SType
)
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[ClientC].[tableSType]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE FUNCTION [ClientC].[tableSType]
()
RETURNS TABLE
AS
RETURN
(
select * from SType
)
'
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tableSType]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create FUNCTION [dbo].[tableSType]
()
RETURNS TABLE
AS
RETURN
(
select * from SType
)
'
END
-- ====================================================================
--
-- Set permissions on GimmeTable to public
--
-- ====================================================================
GRANT EXEC ON [dbo].[GimmeTable] TO PUBLIC
GRANT EXEC ON [ClientA].[GimmeTable] TO PUBLIC
GRANT EXEC ON [ClientC].[GimmeTable] TO PUBLIC
GRANT SELECT ON [dbo].[SType] TO PUBLIC
GRANT SELECT ON [ClientA].[SType] TO PUBLIC
GRANT SELECT ON [ClientB].[SType] TO PUBLIC
GRANT SELECT ON [ClientC].[SType] TO PUBLIC
GO
-- ====================================================================
--
-- Use these statements with each user and compare results
--
-- ====================================================================
EXEC GimmeTable
select * from SType
Hello,Is it necessary to upgrade the Client Connectivity Tools on all clientmachines after the SQL Server database server is upgraded from Version7.0 to 2000?Thank you in advance!Eddy
View 1 Replies View RelatedHello All,
I am trying to create a local database.............using a simple tool called BuildDatabase.cmd for example this is what i pass in the command prompt
C:My.databaseBuildDatabase SAMPLE1SQLEXPRESS MyTestDatabase
this is the error i am getting........
[SQL Native Client]SQL Network Interfaces: Error Locating Server/InstanceSpecified [xFFFFFFFF].[SQL Native Client]Login timeout expired[SQL Native Client]An error has occurred while establishing a connection tothe server. When connecting to SQL Server 2005, this failure may be caused bythe fact that under the default settings SQL Server does not allow remoteconnections.
WARNING! Drop/Create Errors
can someone please help me out....how to fix this.
Thanks a lot
Does anyone know how to upload (bulk) data from a client (written in Excel VBA) to a remote SQL2000 database? Of coarse I tried "INSERT INTO" and rst.addnew but I noticed this is much, much slower as downloading from the same remote database.
Thanks.
Hello,
I'm using the SQL Native Client to connect my VB6 application to my SQL Server 2005 database. My SQL Server 2005 database has ANSI_NULLS turned off. I have a query embedded in my VB6 application that uses the syntax "fieldName = NULL" in the WHERE clause. When I execute the query via the SQL Native Client, the query returns zero rows. When I execute the same query via the old OLEDB driver, the query returns many rows. If I change my query to "fieldName IS NULL" syntax, the problem goes away. However, I am more interested in figuring out why ANSI_NULLS are turned on when using the SQL Native Client even though my database has them turned off. Is there a connection string property that I can use with the SQL Native Client to ensure that the query is executed with ANSI_NULLS off?
Thanks