ServerName/Database Name Parameterization
May 24, 2006I would like to parameterize the server name and database name in a query inside a stored procedure. I would like to avoid using the exec(@SqlCmd) technique if possible. HELP?
View 4 RepliesI would like to parameterize the server name and database name in a query inside a stored procedure. I would like to avoid using the exec(@SqlCmd) technique if possible. HELP?
View 4 RepliesI have a application that runs non-parameterized queries against SQL, that are filling my proc cache. The most common one is like the below:
Code Snippet
DELETE FROM Item_Temp WHERE SessionID = '1232345'
The numeric value of the SessionID changes for every time it runs. How do I force parameterization of this without changing the application code?
Dear Members,
Can I parameterize SSIS packages?
As a example, I wanna know that where the perticular SSIS package is located
Please Can anyone help me ?
I belive I am cornered by a BUG with parameterization parameter in the Sql Server 2005.
Steps to Recreate
==============
1) Set the database parameterization to 'Forced'
2) CREATE TABLE TRY
(
ID1 BIGINT,
ID2 BIGINT,
DESCRIPTION NVARCHAR(256),
CONSTRAINT PK PRIMARY KEY (ID1)
)
3) INSERT INTO TRY VALUES(1,1,'ABC')
INSERT INTO TRY VALUES(2,1,N'ЫФСРШТ')
4) SELECT * FROM TRY WHERE DESCRIPTION LIKE N'%Ф%' ;
This will return you the result which is absolutely fine.
SELECT * FROM TRY WHERE DESCRIPTION =N'ЫФСРШТ' AND ID1=2;
This will also return the result which is perfect !!!
SELECT * FROM TRY WHERE DESCRIPTION LIKE N'%Ф%' and ID1=2;
Results are gone !!!!!!! wooosh ....
any combination of like statement with any other condition does NOT work.
5) Change the parameterization back to simple , and everything works fine.
I am using Sql server 2005 standard edition with SP2.
Any help will be appreciated as I am stuck wih my application developement and at the moment I am working with parametrization a simple.
Thanks
Harneet
Hi
I have explicitly used
Exec SP_dropserver and
Exec SP_Addsserver SP's to drop and add a SQL server.
After that i restarted the sql server service. After that, when i used the query
select @@servername, it is returning NULL.
But, when I used Select Serverproprty('servername'), it is returning me the correct servername.
Can i know as why this behavior is exhibited for the same property of finding servername?
Thanks!
Is it possible to view the Connection String information of a remote login/session? I want to know if the login is looking-up the database server via IP address, servername (NetBIOS name) or fully-qualified domain name (FQDN).
Using these DMVs I can get a lot of relevant information:
sys.dm_exec_sessions
Program Name (eg. Microsoft SQL Server Management Studio),
Client Interface Name (eg. .Net SqlClient Data Provider)
sys.dm_exec_connections
Net Transport (eg. TCP),
Client Net Address and TCP Port
but not how the server's IP address was resolved. Is the connection string ever sent by the client to the server, or just used for DNS lookup?
Folks !!
Select @@Servername results in 'NULL' !
For some reason, i can't get the name of the server !!1
Any Thoughts
Girish
I am getting a null value when i am query the Global variable @@servername.
Please help !!
Thanks
vIVEk.
hi!i can't find out the name of the sql2000-server witch is running not local.i know the username,password and i hear that "System-stored Procedure
" are the right one's. but i don't know how i do this. anyone else? thx robert
If anybody can explain why for one of my SQL Server2000 servers the statement select @@servername returns NULL instead of name?
Thanks
I recall a method to get the old servername of a SQL server but old age has gotten to me.
I suspect a particular server has been renamed at one point. Obviously it wasn't me and the person before me is not around for me to ask.
So, can someone help an old guy out?
The results of print @@servername is NULL.
Thanks
Hey folks,
Today's been way too much fun...
Someone changed the name of a machine in our department and now Win2k thinks the machine's name is (for example) "Fred", but if I do a select @@servername, SQL returns (again, for example) "Barney".
The machine's name started out as "Barney".
The last time this happened, it was on an NT4 box, and we just reinstalled sql and away we went.
This time, they reinstalled sql and released the box to the team, only to find out later that reinstalling sql didn't do squat this time.
Which brings me to the question: How do I convince SQL that it's really, truly supposed to be named "Fred"?
Besides that, what do I tell Wilma, Betty, Pebbles and Bamm Bamm?
Any assistance would be greatly appreciated.
: )
Thanks,
Tom
I want create replication through Create Publication Wizard . But I have
got error , that variable @@SERVERNAME is NULL!!!!!But I have my server
with NF5500 name! I query SELECT @@SERVERNAME. But I get NULL. I tried to use
SP_ADDSERVERNAME procedure to add server name, but I have got message that server with this name already exist. Please help me!!
Thanx in advance!!
Hello sir,
I have a probleme with a replication.
I have two servers (serv1 and serv2); when i want to install a replication from serv1 to serv2, i receive this message:
1->2:Replication cannot be installed as @@SERVERNAME is NULL.
from serv2 to serv1; I receive this message:
2->1:If Replication is uninstalled, all publications will be
eliminated and the distribution database[if it exist]will be
dropped. After the process has completed, this session will be
disconnected from the SQL Server. Do you want to unistall
replication?
Please advice me..
On one of my SQL Servers I have noticed the @@servername to be NULL.
Any Idea how to give it back the original value. Simple
>> SELECT @@servername = `MY_SERVER_NAME` will not work since I can not update the global variable
This prevents from instaling publishing on that server, among other concerns for the overall stability of the server.
Help appreciated,
Bojan
Hi :
Can anyone tell me if it is possible to get information like : servername/databasename inside an extended stored procedure ?
I checked the "srv_pfield" function but it only returns user/password information.
Thanks,
Rui
Someone renamed a server from 2A to A.When I do @@servername I get 2A.The server/machine name is 2.Should my triggers say 2A or 2? Does it matter?Thanks
View 3 Replies View Related
Please help:
when I want to create new data source from Data > Add New Data Source > Database > New Connection > Choose data sourse: MS Sql Server > Continue > Server name ----------> here in server name field i can see a lot of server names but not mine... so I'd like you to help me to find my server name...
thanks in advance!
I am trying to script out the creation of database scripts. I am tryingto use @@servername in the statement. I found out the select@@servername returns NULL. I used sp_dropserver to drop any servernamesfirst, restarted SQL, ran sp_addserver 'servername' to add theservername, restarted SQL. select @@servername still returns NULL...Any ideas why this may be happening?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies View RelatedI'm using SQL Manager from my Enterprise server to connect to my SQL Express server that will act as a witness to a db mirroring system. If I try to connect to the SQL Express server using its IP address, I get a "named pipes error 53." If I connect by browsing the network and connect to the Name of the Server "MachinenameSQLExpress", I am successful. I would like to be able to connect to the IP directly as it's not dependant upon certain services to be started or configured a certain way.
Thank you,
Chad
Hi all,
I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).
For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.
Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.
My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.
I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.
How can I achieve this the easiest way ?
Thanks in advance !
Geert
I have a SQL 2005 clustered server which is returning a Null value for @@servername. I find the server entry in sysservers. I have replication configured on this so i am not able to do a Sp_dropserver & sp_addserver as this acts as a publisher. The configured merge repication stopped working because of this issue and I am not able to delete replication as the the delete option uses @@servername which returns a null value. So I am struck in a loop.
Any advice is appreciated.
thanks
I have a SQL 2005 clustered server which is returning a Null value for @@servername. I find the server entry in sysservers. I have replication configured on this so i am not able to do a Sp_dropserver & sp_addserver as this acts as a publisher. The configured merge repication stopped working because of this issue and I am not able to delete replication as the the delete option uses @@servername which returns a null value. So I am struck in a loop.
Any advice is appreciated.
thanks
hi,
I am very new to asp.net..I am creating a webform which connects to sqlserver and display table contents to a grid control.When i run this form,i get error message as follows
Login failed for user 'SERVERNAME/ASPNET'.
I have read in one page of this forum that we should create aspnet user in sqlserver..i tried to create user,i can see 2 user Guest and public...I don't know what to do ?
If someone replies my question,i would appreciate..I have struggling with this for the past 1 week..i couldn't find a solution.
My code is as follows:
Dim MyConnection As SqlClient.SqlConnection
Dim MyCommand As SqlClient.SqlDataAdapter
Response.Write("hello world")
MyConnection = New SqlClient.SqlConnection("server=Localhost;Integrated Security=SSPI;uid=sa;password=;database=sample")
MyCommand = New SqlClient.SqlDataAdapter("select * from emp", MyConnection)
Dim ds = New DataSet
MyCommand.Fill(ds, "emp")
Response.Write("data set has been created")
DataGrid1.DataSource = ds.Tables("emp").DefaultView
DataGrid1.DataBind()
thanks
kar
can anyone help me with this error message, i follow the instruction of someone in other forum, but i cant find where the SQL Server Enterprise manager is, i am using MSDE.
View 2 Replies View RelatedAttempting Upgrade Wizard on PDC, SERVER1 NT4.0 sp5 SQLS6.5 sp5a
Master.db Syservers has 2 entries
1 SERVER1 SERVER1 ###s ###s - the PDC
2 SERVER2 SERVER2 null null - the SDC
when use sp_addserver, local get duplicate warning
when use sp_addserver, local, duplicates_ok completes (but no changes)
Is problem index associated with SERVER1 should be 0 not 1 ?
If so, would simply UPDATEing index from 1 to 0 fix problem ?
Thanks in advance
@@servername sometimes returns NULL depending on how you have been messing around with replication and various other settings.
The simple solution offered to fix this is to delete the entries in the system tables and sp_addserver 'servername','local' and then restart the SQL service.
This is fine but what do you do in the situation where the SQL server is a 24/7 production box ? Does anyone know of a method / hack to fix this without stopping the service ?
Thanks
s
Need to loop through a Cursor to linked server:
-----------------------------------------------
Declare Cursor_Loop_serverName Cursor for
select cast(name as varchar(30)) name, cast(dbID as varchar(5)) dbID,
cast(crdate as varchar(25)) crdate
from ServerName_A.master.dbo.sysdatabases
***How could I pass @serverName to change the from to
from @RemoteServer.master.dbo.sysdatabases?
I have tried dynamic sql, it did not work after the Declare Cursor for...
thanks for the help
David
Hi,
I am trying to connect to SQL Server through Query Analyzer but getting following error: (SQL Server is at remote web server where i have my website hosted.)
Unable to connect to server [server IP]:
Server: Msg 6, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][dbnetlib] Specified SQL server not found.
I have searched this forum for this error and performed following workaround as suggested in some threads-
1. checked and set "TcpPort" registry setting.
2. TCP/IP is enabled in Network Utility.
3. "start SQL server if it is stoped" checkbox is unchecked while connecting.
But, I am still getting the above error. Should i re-installed SQL Server 2000? Any help?
Thanks,
My system config is VISTA Ultimate, Visual Studio 2005 SP1, SQL SEVER 2005 SP2. Using 2005 SQL Server Management Studio I am unable to create a servername/aspnet login account.
I right click logins and get the new login form; key ASPNET Login Name; select Windows Authentication; click search; I get the Select User or Group form; under object type I select all three; location I select my server name; if I enter SERVERNAME/ASPNET and click Check Names I get an error; if I click Advanced and Find Now; ASPNET is not listed in Name(RDN) list.
I tried aspnet_sql.exe -E -A all; that did not work.
Where can I find information on creating an ASPNET Login after experiencing the above problems?
I thought I would post how I got remote t-sql debugging working in my environment. Hope it might help someone.
I have two domains, DomainA and DomainB. DomainB has a one way external trust to DomainA. Dev Vista machine running VS 2005 is in DomainA logged in as user DomainAauser. SQL 2005 running on test Windows Server 2003 DC machine in DomainB under user DomainBuser. When trying to 'step into stored procedure' in VS, whether my data connection used windows authentication or sql server authentication, I would get the 'Unable to start T-SQL degbuggin...'. I could debug t-sql when I logged into the domain controller (console or rdp) and running VS on the server. I changed the SQL Server service to run under the DomainAauser account and I can now remote t-sql debug using both window authentication and sql server authentication. Hope this may help anyone that might run into this.
Regards,
RMcc
Hi There
View 4 Replies View RelatedI am launching a package the following way:
DTEXEC.EXE /SQL "ProcessReportingDatabase" /SERVER RTG23SQLDB01 /REPORTING V /SET "Package.Variables[User::RunID].Value";35 /SET "Package.Connections[RSAnalytics].Properties[InitialCatalog]";"Fnd Prj 1 Dec29" /SET "Package.Connections[RSAnalytics].Properties[ServerName]";"RTG23SQLDB01UAT1PROD"
The problem does not happen with the [User::RunID] variable or the [Initial Catalog] property of my [RSAnalytics] connection object. But I am not successful in setting the [ServerName] property.
What happens when executed is that the package retains the ServerName property of the deployed package (Value="RTG23SQLDB01UAT1QA"), instead of what I pass in via DTEXEC???
Is ServerName read-only or something? I've tried configuring the package connection to RTG23SQLDB01UAT1PROD and creating a package configuration that I specify in place of the /SET - but to no avail as well!
This package has to work against 10 instances. I really don't want to have to create a separate package for each instance, and then a hack to figure out which one to call.
TIA - Dave