Multi-database Multi-server
Mar 27, 2007
I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)
I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:
Dataserver A
Database A1
Database A2
Database A3
Dataserver B
Database B1
Database B2
Dataserver C
Database C1
Database C2
Database C3
I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3
Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.
Is this something that Reporting Services is able to handle or do I need to look at some other solution?
Thanks,
Michael
View 5 Replies
ADVERTISEMENT
Sep 23, 2014
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition
--We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
--Recovery needs to happen within 1 hour (Not sure that this is realistic
-- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
View 1 Replies
View Related
Aug 17, 2015
More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.
1) Do you really need DTC per instance or one for all?
2) Should DTC be in its own resource group or within the instance's group?
2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
View 9 Replies
View Related
Feb 4, 2008
the stored procedure don't delete all the records
need help
Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
View 2 Replies
View Related
Jul 20, 2005
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
View 5 Replies
View Related
Jul 4, 2007
We have a setup with a web server and multiple databases, and a live, stage, and dev environment. We use SQL Server standard 2005 and use the ASP.NET ReportView control. I have spent countless hours now trying to get this to work and am about to give this up and go back to Crystal.
First I wanted a report that would work from dev to stage to live without modification, so we set up shared data sources on each environment to point to the appropriate database. No problem, I can publish it to each environment and it works, though sometimes I have to go into Report Manager and fix the data source.
Next I wanted to be able to work with multiple databases, identical in structure. For this we did a hidden parameter with the database name and used a formula for the query string. This works pretty well.
Next I wanted to be able to run against multiple database servers from a single web server. This has been nearly impossible. I've read a million posts about this, and nothing seems to work well. I've tried a dynamic connection string, and passing the server in as a parameter, but this doesn't work, because I can't get the credentials set on the ReportViewer.ServerReport, so it doesn't work from dev to stage. You can't programatically change the shared data source - that would make it too easy. Linked servers are not an option.
I guess I need to either publish a copy of the report for each database server, or set up an instance of SQL on the web server for each database server.
Any other reasonable options out there. I just can't imagine my setup is all that unique.
View 2 Replies
View Related
May 22, 2008
I would appreciate help on how to setup database on server B when SQL Server is located on server A of a multi-server network. I am a novice.
View 3 Replies
View Related
Jul 20, 2005
Greetings,We are trying to set up a set of "Leading Practices" for ourdevelopers, as well as ourselves, and hope some gentle reader canrecommend some documentation in favor of what appears to be the rightposition to take.We do not allow third party applications to run on our SQL Servers. Wewant to include DTS Packages under the definition of third partyapplications, insisting instead that the developers save theirpackages as COM Formatted files into their source code control systemsand run them from their app servers. The devlopers would like to hearthis from someone besides ourselves.While strong recomendations to remove guest access to MSDB altogetherabound, I have been unable to find a straight forward discussion ofthe advantages of structured file storage and app server off load ofDTS packages.Can anyone suggest any articles, white papers, rants, etc attemptingto formulate a solution to the benefits of taking msdb away fromguest, with the advantages of running DTS from an App server orworkstation platform, with the packages protected in source codecontrol?Thank youJohn Pollinsjpollins @ eqt . com
View 2 Replies
View Related
Apr 29, 2008
HI,
I am using Multi languages web application with sql database...I am using Arabaic and English languge is there is any thing to change sql database data in Arabic and English on run time.I am using only one database client add some record in Arabic in sql database but the another side some one wana check those records in English...plz let me know how can i do on run time asp.net
Thanks with best regards,
View 4 Replies
View Related
Apr 8, 1999
Is it possible to create a join between two SQL Server DB's on the same server? What is the syntax?
View 1 Replies
View Related
Jan 22, 2008
I am using this function for my datasource; "="data source=" & Parameters!Server.Value & ";initial catalog=" & Parameters!Database.Value"
When I set my database parameter to be a multivalued, it does not work, it will only let me select one db at a time. I would like to grab a table named pm00200 from many databases.
Does anybody have a solution for this?
Thanks!!
Ryan
View 1 Replies
View Related
Sep 11, 2007
Please help....
I have designed an application that uses sql server 2005 express database. This application was intended to be installed and used on a local desktop machine. However, now this application needs to be also installed on several computers and still needs to use only 1 database. Part of the database saves local user settings, so what I have done thus far is use two connection strings: one for local database (because the application may be used as origionally intended) and one for remote database that everyone should be able to connect to.
When I remotely connect to the database, it works fine. However, it seems to be locked so when I chose the same database for another user I get the error message "This File Is In Use." I need to know how to unlock it and make it usable for several users at the same time.
Thank you for your help! Please let me know if you need more information or if something is unclear in my description.
View 6 Replies
View Related
Jul 10, 2007
Okay, I've read massive topics on ClickOnce, and embedded database applications, etc, etc.
I need a handful of good suggestions how I can create a deployment package, to get my sql express database onto a SQL Express server.
The database will need to be multi-user, because I am also developing a WinForms application to connect to the SQL Express database.
I'm thinking I need to use some automated scripting features, to generate scripts for the database once I am done (unless you have a better suggestion).
After that, I need to know what to do with those scripts, and how can I create a setup / deployment packages to run those scripts against a SQL Express server.
The other alterntative obviously is to copy my sql express database, and run an attach command. This will work the first time, only because this will have been the first deployment of this database.
Please keep in mind when answering this question, that I will not be embedding the database, and as far as I understand ClickOnce is a feature for use with embedded databases or user instance databases (not sure).
Daniel Crane
View 6 Replies
View Related
Aug 18, 2006
hi
i have over 200 tables with all same column and data type locate all over the server(20)different database. so i have table call Tname to stored all the link like (databasename.dbo.tablename) so my job will call the Tname table to use cursor to insert records. but the problem is there's one or more table's column name K datatype has been modify by someone else. so is it cause the job fail.
if there a way or SQL statment that i can use Table Tname to see which one is missing column name K? and is there a way i can print out all the datatype and len for all the table column k? please help thanks
View 2 Replies
View Related
Sep 16, 2015
I have a several questions with regards to setting up a server to cater for multi tenant database.
I have a requirement to host 100 isolated databases for the purpose of multi user environment where each user has it's own database. This is due to security restrictions that why it was requested to be such.
1. What I'm recommending is to setup servers with 256GB of RAM to host multiple databases. Are there servers which can accomodate 256GB? Is this optimal with regards to performance? Or should I break it down to smaller servers with less RAM?
2. Is there a way to determine the CPU requirements for the DB server, taking for example we have 20 databases per DB instance? Are there any best practices / calculators available?
3. Additionally, data from the 100 DB's needs to be consolidated into 1 central repository for Analytics purposes. What I'm looking at is to utilize SQL Server transaction replication. Is there any drawback with this approach? Are there any other approach we could use to easily replicate data across 100 database back to 1 central repository?
View 0 Replies
View Related
Aug 16, 2007
Hi there -
First of all I think of myself as a software developer and by no means a database expert. So I am posting a question here hoping that someone will lead me in the right direction.
I am somewhat following the database diagram from AdventureWorks with my own "normalized" database and have a question on how to update the table(s).
Here is my situation:
Table: Client
ClientID (PK)
Name
...
Table: Address
AddressID (PK)
AddressLine1
AddressLine2
City
...
Table: AddressType
AddressTypeID (PK)
Name
...
Table: ClientAddress
ClientID (PK1)
AddressID(PK2)
...
The tables should be referenced properly, etc... So my question is, when I add a new Client record, how and when do I update the Client table, Address table and ClientAddress table? If my PK on the Client and Address tables is an autoincriment, do I need it before I can update the ClientAddress table? If so, what do I have to look for, or how do I get the newly created ID to update the ClientAddress?
Is this making any sense? - I'm a little lost/confused. So if anyone can provide me links to where I can learn more about stuff like this and/or post some ideas/solutions, I would greatly appreciate it. Also, if I am not clear on my issue, please ask and I will see if I can clarify it more.
Your help is appreciated in advance!
Bob
View 4 Replies
View Related
Jan 4, 2008
I have ran into a problem making a database where i have two versions of a book , one in russian and the other in English. The english part worked but when i input the data for Russian it gets replaced each time with question marks. Is there something i have to enable for the SQL database for it to be able to store Russian Text, or is it a DataType i have to set???
THANKS IN ADVANCE
View 2 Replies
View Related
Mar 22, 2012
I am trying to place a database offline.
When I right in Microsoft SQL Server Management Studio on the database > Take Offline
Set offline failed for Database 'OperationsManagerAC'.
Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. ALTER DATABASE statement failed. (Microsoft SQL Server, Error: 1205)
So I am trying with some queries to pass the database from Single user to multi user but it fails
USE [master]
GO
ALTER DATABASE OperationsManagerAC SET MULTI_USER GO
Msg 1205, Level 13, State 68, Line 1</p><p>Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I tried also
alter DATABASE OperationsManagerAC SET OFFLINE WITH ROLLBACK IMMEDIATE
IT FAILS AS WELL... with the same error... how to run the SQL Statements without using
How to proceed next?
View 12 Replies
View Related
Jan 4, 2007
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
View 1 Replies
View Related
Aug 28, 2015
I have Sql server 2008 database with 200 GB in production.But It will not support multilanguage Since all the tables and procs is having varchar datatype.
I need to change the Entire Tables and procs to support Multilanguage(nvarchar,ntext) .
I have Many Huge data tables with numerous index .ALso I could not afford too much down Time. How can I perform.
View 8 Replies
View Related
May 12, 2008
Using
- SQL Server 2005
- Management Studio Express.
Here's the SQL statement
Code Snippet
SELECT TOP 1000
[test].[CatalogStudioId],
[test].[CollectionId],
[test].[unique],
[test].[att1]
FROM CatalogStudioEntity.dbo.[test]
WHERE [test].[att1] LIKE '%1%'
Now, before you respond, allow me to say that I know using the table name to qualify the columns is redundant, and I know there are other options (such as aliasing the table). Don't respond to tell me this. However -- as far as I know -- this is a valid T-SQL statement and I should be able to get this query to run.
The interesting part is that if management studio is connected to the CatalogStudioEntity database, this query runs fine. If connected to the master database, I get the "multi-part identifier [x] could not be bound" error. The error is listed for every column.
My issue with this is that the table in the FROM clause is qualified with the database name, so whether I'm connected to that database or to "master" should not matter.
I have an application that generates select statements like the one above, and all of the machines so far that we've developed, beta tested and deployed this application on all don't have this problem. It's this one specific installation of SQL Server 2005 on this new machine where this problem arises.
Before I go back and retool the application to output whatever arbitrary syntax this specific machine seems to want, I want to try and troubleshoot and understand why it's acting like this.
Any ideas or thoughts would be greatly appreciated.
View 3 Replies
View Related
Nov 29, 2007
Hi, this is my first week really trying to learn ASP.net (i'm currently working with 2.0).I have a page, that I am trying to set up that will allow the user to input data, now i'm sure that passing data from 1 webpage to 1 table in a SQL database is pretty streightforward, but how do you plan it out when you know that your web page is going to insert multiplte inserts into multiple tables, with the main table PK referencing all?Any ideas? Even just a starting point so I can get going with this? If I can help anymore, please let me know.
View 4 Replies
View Related
Sep 27, 2015
Would it be possible to get a multi-database functionality on the same instance option available in SSMS anytime soon?
View 6 Replies
View Related
Oct 15, 2015
I have one database which is multi tenant, every table has tenant id  and every tenant is mapped with separate file group. Now can I achieve below  :?
1. Can I take the backup of only one tenant and restored in separate database?
2. Can I takeout the data of one tenant quickly?
3. Can I undo the data of a particular tenant  with 2 days older data without disturbing the other tenant ?
View 3 Replies
View Related
Oct 9, 2006
hi,consider this:i have american users in a db called "user" in a server called AMERICAand rest of the world users in a db called "user" in a server called OTHERhow can i do a join betwhen American's users and Rest of the word's User db?
View 3 Replies
View Related
Jun 17, 2007
Hello,I am working on an Access VBA application, working in client/servermode, with a shared SQL Server base.I have to INSERT some data in the base, and the know what is the IDthat SQL Server associated with my data in the table. So I need to dothe followingINSERT data INTO TABLESELECT MAX ID FROM TABLE (to get the ID)but since I am working in client/server, if some DATA has been insertedby another client after my INSERT, then MAX ID is not the convenientID of my data ...Is there a way for me to get the ID when I do the INSERT, or tolock/unlock the table before the INSERT and after the SELECT ?Thank you for any hint about that.--L'ordinateur peut faire plus de calculs que le cerveau de l'homme caril n'a que ça à faire
View 1 Replies
View Related
Aug 22, 2007
What is the effect of deploying Multi-Server instance on performance?
View 3 Replies
View Related
Oct 1, 2007
We are currently managing about 75 SQL Server instances. Each instance contains jobs (backups, index work, admin, etc) which report back to a central instance for monitoring. This is working well, but each time I need to change one of the jobs, I am having to log into each instance to do so.
I have recently played with Multi-server Environment, using a master and target, to see if this might help. There is relatively little written about Multi-server in books-online, and I am left with a question.
All jobs appear to be exact duplicates. But I don't want exact duplicates. For example, on the various target servers, I would like for the backup jobs to run at different times, and write to different directories. How are the rest of you working around this situation, if you are using Multi-server Environment?
View 1 Replies
View Related
Nov 8, 2007
We're moving in a 4-socket (Xeon Dual Core CPU's) server. What version and product of SQL Server should I be looking for to fully take advantage of the new server's 4 processors. I'm not sure I understand the licensing per CPU. Is there any advantage in waiting for SQL Server 2008 as it concerns multi-threading and what should I be looking to spend for a SQL server solution that utilizes all four processors. Someone has told me that the SQL Server standard edition comes with 4 cpu licenses. Is this correct?
Thanks, Rob Huylo
Rob21379@aol.com
View 11 Replies
View Related
Oct 12, 2000
Can anyone help me how to extract data from two different SQL servers? Both the servers are alredy registered on my local machine.I need to select data from one database on Server1 and insert the same in a table in another database of Server2.Names of the servers are abc-efg-hij and 143.89.55.67.
Thanks for any help!
View 2 Replies
View Related
Jan 6, 2004
My company uses a quad processor server connected to a SAN to load and summarize detail sales information from 2000 stores on a nightly basis. We poll and load around 5,000,000 rows of data each night. THis information is summarized up to various levels, then replicated to one or more secondary datamart servers for end user access via web reporting, BI tools like Proclarity/Analysis Services etc...
The initial data polling server is only touched by the development staff supporting the process (1-5 programmers) and is licensed for SQL server Enterprise using a CAL model. Each datamart server is licensed with MS SQL server processor licenses.
The question: We were told that the quad processor polling machine, which has no end user access allowed, must be licensed with processor licenses since it touchs the data ultimately consumed by end users. This makes no sense to me.
The Microsoft white papers discussing multi_tier environments don't seem to address this type of issue. They focus on applications that ultimately pass thru a data request to the SQL server machine. In this situation, user requests are handled by the datamart servers, which are licensed with processor SQL licenses.
Can anyone clarify?
Thanks, Mike
View 1 Replies
View Related
Jan 17, 2008
Hi,
This is chirag patel.
we are going to develop web application which can disply data (Search Results) in different language.
if user selected country Japan, then search results should be in Japanese.
if user selected country Chian, then search results should be in Chinese.
now i want to store all these read only data in Database. how and where i can store it?
do i need to have different Databases? or with single database also i can achive?
I means what is advisable approach to achieve this?
if Single Database for all countries then how i can store same data in different languages in single table?
please guide me if anyone know how to achive such kind of scenario in SQL Server?
View 1 Replies
View Related
Jun 8, 2007
I get this error in a web farm environment:
Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that <machineKey> configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster.
I googled and found a bunch of references to .NET apps, but I wasn't sure how to fix it in SSRS.
Has anyone else experienced this?
Thanks!
BobP
View 2 Replies
View Related
