Can Synonyms Be Used To Replace Hundreds Of Lines?

Sep 10, 2006

Hello

I'm looking for a way to store a large chunck of text (200 lines) in a variable which can be called in different objects within the database.

For example: I have several stored procedures that create the same temp table which exists of 200 column names over and over again.

It would have to look like this

Import myScript (I keep thinking of Import like used in .NET)

and myScript could exist of:

CREATE #MyTable(
...)

or even some simple text or a part of a sql statement.

I'm familiar with synonyms but you can't use that in this scenario.
Then I thought of functions. The scalar function returns a result, not what I want here. Table valued function return tables, not what I want.

Many thanks in advance!

View 3 Replies


ADVERTISEMENT

Fastest Way To Delete Hundreds Of Table Triggers And Hundreds Of Stored Procedures?

Jul 20, 2005

How can i delete all user stored procedures and all table triggers very fastina single database?Thank you

View 17 Replies View Related

How To Replace Empty Lines When Using SQLDataSource

Mar 1, 2006

I have an app which uses SQLDataSource to update the information to database (SQL Server 2000). Is there a way to trim the input of users somehow (blank lines) ? For example if I have an Comments-textarea on my form and users enter text to it, can I somehow take out the extra blank lines out of that textarea before submitting the data to database ? I know how to do this programmatically, but was just wondering if this could be done when using SQLDataSource-control.
I'm using DataReader to extract the information from database to an CSV-file. Because of the blank lines, occasionally CSV are really messed up because of that. Is there a way to remove the blanklines when iterating the DataReader values ?
 

View 1 Replies View Related

Hundreds Of VIEWS

Feb 2, 2006

What are the downsides to have many multiple views on a SQL Server Database?

It was somebody's design decision to create 10 views for each vendor in our database- that could range from 20 to 500 vendors ... which means we could potentially have 5000+ views in our database.

What is the downside/problems with doing this?

View 17 Replies View Related

Hundreds Of Databases

Feb 2, 2008



Hello All,

We are running a hosted application where we currently have over 700 databases and expect to go to over 2000 within a year. This is all running currently on a 2 node fail-over cluster connected to a SAN.

I'm basically looking for advice on growth startegy from anyone that may have a similar setup.
Should I buy the biggest boxes I can afford and keep piling the databases on the same server? Should I add clusters and split the databases in bunches of, say, 500?

Performance is not bad right now but I'm not sure how long that will continue and I would rather plan than fight fires.

TIA

Andrew

View 1 Replies View Related

How To Import Hundreds Of CSV Files Into SQL Server?

Feb 5, 2004

Hi,
I have about 500 CSV files, mostly they are unser same structure. I need to import them all into SQL server. I create a new table for each one based on the firstline as the column name. I can do them one by one but it is very time consuming and boring. Is there a fast way of doing this?

If someone give me a information, I promise to share all of MY CSV files. They are the zone chart from UPS and I have edit all of them.

Thanks.

View 14 Replies View Related

Avoid Listing Hundreds Of Columns

Jun 3, 2014

i have a table with a few hundred columns. Each SELECT statement, I list each of the columns, this is taking lots and lots of space and it is difficult to review the code due to its length...Below, I have to list out every column when I only want to use case logic on 1 column. In the next step I will have to list out every single column again

SELECT
ACCT1_NO
,ACCT1_DT
,ACCT1_RISK
,ACCT1_RISK_WEIGHT
,ACCT1_CUSTOMER_NAME
,ACCT1_CUSTOMER_ADDRESS
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
,ACCT1_CUSTOMER_CITY
,ACCT1_CUSTOMER_ZIP
--,THIS CONTINUES DOWN FOR ANOTHER 150 OR SO ACCTS.
INTO #A1
FROM STAGE.CUSTOMER_ACCTS

Is there a way I can tell SQL to take all of the columns and then list the column where I want to do my case statement. Something like the code below

(which will fail as ACCT1_CUSTOMER_ST will be listed twice.
SELECT *
,ACCT1_CUSTOMER_ST = CASE WHEN ACCT1_CUSTOMER_ST = ' ' THEN 'DC' END
INTO #A1
FROM STAGE.CUSTOMER_ACCTS

View 1 Replies View Related

SQL 2012 :: SSIS Variables - Change Value In Hundreds Place

May 21, 2015

in SSIS

( Left(@[User::YourFileName],3) == "AP_" ? 101 : ( Left(@[User::YourFileName],3) == "VD_" ? 102 : ( Left(@[User::YourFileName],3) == "BK_ " ? 103 : 000)))

In the above Variable i want to change the value in Hundreds place i.e from 101,102,103 to 201 ,202, 203 depending upon some Project param

lets say if project param is 1, the values assigned should be as 101,102,103

if project param is 2, the values assigned should be as 201,202,203

if project param is 3, the values assigned should be as 301,302,303 etc...

View 0 Replies View Related

Using SYNONYMs

Apr 21, 2006

From what I'm seeing in MS documentation I would like to use SYNONYM to obtain information from a remote servers tables.  I've tried to find some decient documentation on using it, but can't see to find a clear example for remote servers and in my testing I can make the SYNONYM but the conneciton is failing.
Any helpful hints or references would be welcomed.
Thanks,Ken..

View 8 Replies View Related

API For Synonyms

Mar 28, 2007



Hi Experts:

I am writing a general API which would fetch synonyms from any database
providers (and would filter for a given schema).

I am using GetSchema method on DBConnection object and it works fine for
oracle.

SQL Server 2005 has a support for synonyms, but the above piece of code does
not work. Is there any API by which i can get the list?.

Thanks

AK

View 1 Replies View Related

Synonyms

Nov 20, 2006

Hi all,

Just as a curiosity (and thoroughness) thing, why are there synonyms in SQL Server? When I say synonyms, i mean INT and INTEGER, OUT and OUTPUT: These word pairs mean the same thing!

And in some examples they chop and change between the use of these words... It adds to the confusion.

Anyhows I'm curious as to the "why" behind this.

Thanks

Chris

View 3 Replies View Related

Synonyms Database

Nov 26, 2004

Hello ,

Where can I download synonyms database ?

Thank you !

View 5 Replies View Related

Recovery :: Using Synonyms On AG

Oct 28, 2015

I currently have a 2 node Availability Group that houses some big warehouse type of DB’s. It’s not uncommon to see DB1 populate DB2:

Use db1
Go
Insert into db2.dbo.table_1 values ‘ssfsfsfsf’

This all works as expected. However, we want to split these DB’s away from each other to reduce CPU impact on any one instance. Of course though we don’t want to have to go and change all the connection strings in our SSIS Packages.  By default this would now require going to a 4 part naming convention like so: Insert into newServerName.db2.dbo.table_1 values ‘ssfsfsfsf’Again though, we are trying to avoid this. That said I was thinking to use synonyms. This works as expected, until a failover occurs, and the synonym is redirected to the local Read Only version of DB2.So in other words, SQL Server is behaving exactly as it should, but I need a way to work around it.

Example below:

USE [master]
GO
/****** Object:  LinkedServer [DB2]    Script Date: 10/28/2015 11:43:46 AM ******/
--note this is pointed to the AG Listener name, for failover purposes.
EXEC
master.dbo.sp_addlinkedserver
@server = N'DB2', @srvproduct=N'sqlserver', @provider=N'SQLNCLI',
@datasrc=N'myAGListenerName', @catalog=N'DB2'

[code]....

Again, SQL Server is behaving exactly as it should. However, this synonym is now fairly worthless, as it can't handle failing from one node to the other in the AG

View 2 Replies View Related

Getting JDBC Metadata For Synonyms

Sep 18, 2007

Using the Latest JDBC Driver from SQLExpress I'm attempting to get use the getTable() method to get information about database objects that the user can access/alter. Works fine for for tables and views, but can't seem to get any information returned for synonyms.

Is it possible to get information for synonyms, e.g. column definitions? or am I'm missing some setting in the connection.

Any Help would be appreciated!

Dave.

View 5 Replies View Related

Is There A SQL Server Equivalent For Oracle Synonyms?

Jul 9, 2004

I have a SQL Server database which has one user (UserA) which owns some tables. I've added an additional user (UserB) to the database such that it has access to the tables owned by UserA. What is happening is that when I log on as UserB I have to fully qualify table names and fields in my SQL statements when I deal with tables owned by UserA. Is there a way make the tables accessible without specifying the owner? In Oracle you could create a public synonym for the table eg. <table_name>. Wherever that synonym is referenced the DBMS would know thats its refering to UserA.<table_name>. Is such functionality available in SQL Server? Thanks.

View 2 Replies View Related

Severe Error Encountered With Synonyms

Oct 10, 2006

we now used synonyms on the version 6 of our peoject, at first it turns out good. Then we migrate a database to the new database, everythings fine, its just a data migration anyway. The target database' structure and DML's wasn't change during migration. then intermittendly, we're encountering "A severe error occurred on the current command. The results, if any, should be discarded". I said its intermittent because sometime its ok, mostly its not functioning.

This is very painfull because all our scripts has this synonym. All our scripts uses an sp execution logging stored procedured of another database so we created a synonym for those sp's instead of fully qualifying the procedure name.

This is a major issue for us, please advise. You may response here, send me email or email me. Many Thanks.

Rodel E. Dagumampan
Email: dehranph@gmail.com
Yahoo: dehranph
Blog: http://community.devpinoy.org/blogs/dehranph

View 7 Replies View Related

Synonyms Containing Tables From Another Database And Permissions

Jul 17, 2007

When you create a stored procedure and give the user execute permission, you don't need to give the user select permission on the table used in the stored procedure.



If one of the tables in the stored procedure is a synonym referencing a table in another database, and the user is already in the other database, you get a select permission denied on that table and I could only get it to work if I gave the user select permission on that table.



Is there a way around that, since I hate giving select permissions on tables?




Thank you,
Wissam

View 3 Replies View Related

Problems With Synonyms As Data Sources

Apr 14, 2008



I am using a sql 2005 database that contains a combination of local objects plus synonyms for objects from a second database. I have had no problems using synoyms for tables and views in reporting services but can't get synonyms for stored procedures to work as a report data source. All databases are on the same server and we have installed service pack 2.

Any suggestions?

View 2 Replies View Related

Implementation Of A Backend/frontend Architecture Using SYNONYMS

Mar 28, 2008

Hi!
I am evaluating an architecture for one of our project... a SQL database containing the data (backend) and a second database containing the development code (frontend) linked to the backend with synonyms.

It enables to upgrade the code without touching the data. Or to change the backend / use a different set of data at will.

Everything was going fine, the behavior was expected to be EXACTLY the same with synonyms as with real tables. But I came accross a problem:
Let's say we have a synonym (frontend) dbo.TABLE1 that points to a table (backend) with a IDENTITY column.

I have a sp (frontend) with the following code:
INSERT INTO dbo.TABLE1...
SELECT @SCOPE_IDENTITY = SCOPE_IDENTITY()

Well in that case, @SCOPE_IDENTITY is NULL!

Anyone has ever faced that problem? Should I use another function to return the last ID inserted? Or is it the backend/frontend architecture that is completely flawed? I also heard there's a way, by creating the tables and the code on different filegroups, to restore only the tables or the code...

Thanks for your input!
Frantz


View 13 Replies View Related

SQLCMD Error - Working With Variables And Synonyms

Jan 3, 2008

Hi All,

Been doing some testing with SQLCMD and variables for installation scripts, enclosed below is some source code.
The one problem, I've been getting is the following error:

Incorrect syntax near 'certification'

This has been tested only in SSMS utilising the run in SQLCMD mode.

This error only occurs on the SQLCMD variable replacement, where it replaces $(Cert_Schema) for the word Certification.
Running the CREATE Synonym with the word Certification hard-coded into the script works.

Any ideas?
Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

Best Regards,

D


USE [Testing]

GO

/****** Object: Table [certification].[Table_1] Script Date: 01/03/2008 11:22:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [certification].[Table_1](

[PKId] [smallint] NOT NULL,

[Name] [nchar](10) NULL,

CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED

(

[PKId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

--- SQLCMD Script below:
-- line below should read, colon setvar cert_schema 'certification'

etvar Cert_Schema 'certification'
-- The line below fails to work for some reason

CREATE SYNONYM crt_asa FOR $(Cert_Schema).Table_1

DROP SYNONYM crt_asa

-- Hard-coded equivilant line works

CREATE SYNONYM crt_asa FOR certification.Table_1

DROP SYNONYM crt_asa

View 3 Replies View Related

Starting Out With : Views, Stored Procedures, Functions, Synonyms

Mar 22, 2006

Hi,Right, i have a fairly good understanding of SQL. However, i have a fairly basic understanding of SQL Server.I know Storedprocedures are really good and i'm starting to use them. I understand they are good for making inserting, updating very easy.However when you look at a SQL Server database you get various folder, this leaves me a little confused with what they are all used for? whats the difference between these?Thanks in advance!sorry for the basic question, i'll try to challange you next time

View 1 Replies View Related

SQL Server 2014 :: Script To Search For A String In All DB Objects (Including Synonyms)

Jun 29, 2015

I'm looking for a "God-script" to search for a given string (case invariant) in all DB objects (esp synonyms) in all DBs on a server?

View 9 Replies View Related

SUM OF THE LINES

May 24, 2006

Hey,
take a look at the below. I need to sum the real balance of a line with the credit with the line above, or, n + 1. I don't wanna to have to walk across the lines to do the sum. Any idea of how to do that in a easier and better way? I tried using T-SQL, but I couldn't figure out how to do a reference to the next line on the SQL without moving the cursor.



Date
Reference
Description
Debit
Credit
Real Balance
Bank Balance
 
Grp
Action




$0.00
$6,000.00
$86,814.27

*
 
  




$0.00
$4,000.00
$90,814.27



  




$0.00
$666.00
$91,480.27

*

Thank you!

View 2 Replies View Related

SQL Mail And New Lines

Mar 7, 2008

I'm using SQL mail to send an email from a stored procedure which works fine. However I need a line break in the middle of this email which I am struggling to achieve. I've tried adding char(13)+char(10) to the middle of the email body string but this does not seem to do anything.
 Any ideas?

View 2 Replies View Related

Lines Per Page

Jan 14, 2008

I am using SQL Server 2005 Reporting Services and all my reports preview with many more pages, as the preview will show Page 1 of 20, but if I print the report I get say 12 pages. The reports are always correct, but the preview show less data per page? Is there a way to get the preview to be what is going to print out?

This occurs in MS Visual Studio 2005 also as well as from the web. On a drill down report I get on some reports only 10 line when the print out will have 30 lines perpage

View 12 Replies View Related

Break SQL Statement Into More Lines?

Jul 16, 2007

 I have long SQL statement  String sqlQuery = "INSERT INTO Objekt VALUES(@RegijaID, @StatusID, @VrstaID, @PozicijaID, @Adresa, @Otok, @Ime, @Prezime, @JMBG, @Telefon, @Telefon2, @Mobitel, @Mobitel2, @email, @Cijena, @Cijenam2, @BrojNaloga, @Agent, @BrojSoba, @BrojKatova, @BrojKupaonica, @NaKatuBr, @PovrsinaStambena, @PovrsinaZemljista, @UdaljOdMora, @PrviRed, @PogledNaMore, @Bazen, @Garaza, @Prilozi1, @Prilozi2, @Prilozi3, @Napomena, @smallImage, @KratkiOpisEN, @DugiOpisEN, @KratkiOpisHR, @DugiOpisHR, @PonudaTjednaCroestate, @PonudaTjednaNaslovCroestate, @PonudaTjednaBroker, @PonudaTjednaNaslovBroker, @PonudaTjednaKorcula, @PonudaTjednaNaslovKorcula, @PonudaTjednaCroProperty, @PonudaTjednaNaslovCroProperty, @PonudaTjednaDalmVillas, @PonudaTjednaNaslovDalmVillas, @LinkZaViseoMjestu, @DisplayOnWWW, @TS)";  How broken it into more lines? 

View 6 Replies View Related

Executing Multiple Lines Of Sql Via C#

Jan 27, 2008

I am generating hundreds of lines of sql with a tool and wish to execute is in runtime. The sql consists of table creation, procedure creation, aswell as inserts and updates. When i try with SqlDataAdapter I get an error. Does anyone have a solution on what I can do?         

View 2 Replies View Related

Pulling Single Lines From A DB

Apr 26, 2004

hi im making a page where i want a single line at a time to be pulled from my MS Database, basically at the moment i have a list of questions, but the page is displaying all the questions from my database, i only want it to pull out 1 and then if the user clicks the true button then it goes to another page and displays another question?

Any ideas

Regards,

Joe

View 1 Replies View Related

HTMLto SQL To HTML..... New Lines?

Jun 16, 2005

I take some data from a textbox and then dump it in to SQL.. and then try to show it on the page but the new lines in code dont show in HTML.I grab the data via a <%#Eval(xxx>%>so doing a replace isnt really an option there...any other ideas?

View 1 Replies View Related

Exporting Data/1st Three Lines NOT CSV..

Oct 3, 2005

I have a specific format that I need to export data to. The first three lines of the document MUST be in the form of:

ascii
,
klg, Eastern Daylight Time,1,1
PineGrove,0,2005/10/01,00:00,1,1.75,192
PineGrove,0,2005/10/01,00:05,1,1.75,192
Pinegrove,0,2005/10/01,00:10,1,1.75,192

If I set this up in DTS and do an export, it puts commas after ascii - which I cannot have.

I've also tried using two data sources and exporting twice (hoping to append), however, one just overwrites the other.

Anyone have any ideas?? :o

Thanks in advance,
Krista

View 6 Replies View Related

Geometry - Parallel Lines

Mar 19, 2014

Assuming I have a line, is there a function I can call to create a parallel line at a given distance away.i.e - with the below I would want to draw a parallel line to the one output.

DECLARE @line geometry = 'LINESTRING(1 1, 2 2, 3 3, 4 4)'
SELECT @line

View 9 Replies View Related

Double Lines In The Tables

Nov 29, 2007

Hi all we are student of university
and we trying to make application

We are programming in Tsql.we must make a list of backup sets and backup jobs.

Every backupjob can have more then one backupsets. The problem is when we want to put this in table then. We see the costumors name double in it .

For excample

Mr Jurgen has more backupjobs

We see then in our table then

Loginname Wich Status
MRJURGen SET 1 GOOD
Mrjurgen Set 2 Good
Mrjurgen Set 3 NOT ok


What we want that comes like this in a table

Loginname Wich Status
MrJurgen Set 1 Good
Set 2 Good
Set 3 Not ok

But we keep getting MRjurgen in also in the other lines !

The solution that we think that could help is an while loop

But sadly it doesnt work

So we desperate asking here how to do this :(

Thanks

Students

View 5 Replies View Related

Taking An Average From A Few Lines

Mar 4, 2008

I am trying to get an average result from similar records in a view:

Order No: Product: Price:
1 1 5
1 2 3
1 3 7

2 1 5
2 2 3
2 3 7
2 4 2

I need the sql statment to get the average price of order 1 and order 2 seperatly. Giving a result like:

Order No: Av Price:
1 x
2 y


Thanks in advance for you help

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved