SQL Sever 2005 - Creating Alias Names Programatically
Aug 14, 2006Is there a way we can programatically create a SQL server Alias name (SQL 2005 - SQL Native client configuration) using either T-SQL, C# etc. - Manmeet
View 4 Replies
ADVERTISEMENT
Change Output Alias On Synchronous Component Programatically
Nov 21, 2006
Wanted to enquire how this is done. Tried doing it in the setUsageType method I have overwritten but only allows description to be changed. Basically need to change "Name".
Best option would be to change it instantly when a user selects a column from the inputs in the custom component, ie. it changes the Output Alias to a desired value. (Input tab in advanced editor)
All this is being done in a custom component which I would like to be synchronous, can achieve a similar result asynchronously.
Thanks
View SQL Sever 2005 Express Database On SQL Sever Enterprise Manager
Apr 25, 2006Is it possible to connect SQL Sever 2005 Express from SQL Sever 2000 Enterprise Manager? Tried to connect but got the error message "must use SQL Server management tools to connect this sever"
Thanks
How To Change Column Names In The Grid View Programatically?
Aug 31, 2007I have consistent column names that load into a grid view. I now need to change the names in the grid view programatically. I was originally trying to get cute with SQL to do this, but I've been told my below solution will not work and I'm better off to do this in the presentation layer. If this is true, remember I'm still wet behind the ears here...how do I do this in vs2005?
Here's my post to the SQL devs to give you an idea what I'm trying to do.
I have a query that grabs fields from a denormalized table. The result is column names Week1, Week2, Week3....Week26. Users want to see the actual date instead of Week#. So I have a table (lkpdatecaptions ) that contains the fields "fldfieldno" and "Fldcaption". fldfieldno Fldcaption----------- --------------11 9/07/200712 9/14/200713 9/21/2007So fieldno 11 represent week1 and so on. So my hope is to update the alias with a query like: Select fldcaption from forecast.tlkpdatecaptionswhere fldfieldno = 11That quey returns the value of 9/7/2007 and is the value I need to represent the coumn alias name.My current query looks like this:SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS WEEK1,So can I do something like the following?SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...
Display Column Names And Its Values Programatically From The Dataset
Apr 18, 2008Hi.
In my report, I need to display column names (and its values) from my dataset, which uses the stored procedure and within it use the PIVOT statement. I cannot use the matrix control in report since I need to add another static column to the right of the matrix, but since that cannot be done ni SQL SSRS 2005, I need to PIVOT it through stored procedure. The parameter passed to stored procedure is the year and it is used for pivot. The statement looks like this:
@sql = 'SELECT * FROM (SELECT YearId, SchoolId, ReportText, OutputValue_Text from AggregateTable) AS AGAM
PIVOT (MIN(OutputValue_Text) FOR YearId IN ([' + @YearId + '])) PVT'
EXEC(@sql)
So, since the years passed as parameters can change, I cannot hard-code it as column in my report, so I need to programatically display column names and values from my dataset, something like dataSet.Tables[0].Columns[].ColumnName; I have tried with writing the code, but I do not have much experience with it and I have not been very successful. Can someone help me with the code?
Thanks
Trying To Display Alias Field Names
Nov 20, 2006Hello All,
I have the following code:
USE MLS
select sc.name,f.field#,fdesc,flong
from sysobjects so join syscolumns sc
on so.id = sc.id
join fld f
on f.field# = replace(sc.name,'_','')
where so.name = 'dbo.tbl_MLS_Leads_Trans'
I am trying to get the description which is flong and I get the following error message:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'fld'.
What am I doing wrong?
TIA
Kurt
Using Alias Column Names For Calculations
Oct 3, 2007Hi,
I want to have a query where in i can use alias column names in the same query.
like eg
select 1 as 'a', 2 as 'b', a+b as 'c'
note that this query is getting big and is using sub queries.
Kindly help.
Thanks
Creating A Table Programatically!
Nov 25, 2003Hi SQ Gurus,
Please help me with this issue that I am facing. I want to create a table programatically but want to pass its name as a variable. I would like to do something as follows:
CREATE PROCEDURE BS_Create
(@Name nvarchar(25))
AS
CREATE TABLE [dbo].[@Name](
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Prod_Name] [nvarchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
The table name is passed in as a parameter. No matter what I pass as a parameter, the table name is '@Name', which is not what I want. Instead, if the variable contains the value 'Product', I would like to see a table with the name 'Product' to be created.
How can I solve this problem.
Any help is appreciated!
Krish Chandra
Creating An MS SQL Data Table Programatically
Aug 27, 2005I use Visual Studio and VB.
If I want to create an SQL data table on the server, I go to Server
Explorer and make a small table with the corerct columns. Then I add
rows programatically as needed from the Application I'm working with.
It would suit me, for a current job, to be able to create the table itself programatically.
I don't know how to do this. Can it be done ? If so, could someone give
me a starter, please ? Four cols with a key in the first.
David Morley
Problem In Creating A Database Programatically....
Jun 27, 2001I am crateing the database on the fly using db directory in VC++. I am able to create database. I am also able to create login. These can be easily done by logging as master using the username and password sa and "" resp. Now I want to add a user to the database that i created. but it is being added to the master database. I know that I have to change my login from sa ,"" to the username and password that i gave during login. but i dont know how to change the login. Please help. If you have any code for that then that will be of great help.
Hoping to get a positive response as soon as possible..
Thanx
Nitin Gulati
Creating Calculated Members Programatically
Sep 27, 2004Hi
I've read the MSDN documentation about creating calculated members and states that we can only create session scope or query scope members.
I need to create a static calculated member, one that stays there until it's deletion... That is possibel using Analysis Services, but what about with MDX or DSO?
Thanks in advance
Creating Script Task Programatically.
Nov 5, 2007Hi All,
I am trying to create Script task programmatically. i am struck in one place where we will add the script code. I have given my code.
My Code:
TaskHost taskHost = package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") as TaskHost;
taskHost.Name = "Create SO";
taskHost.Properties["ReadWriteVariables"].SetValue(taskHost, "User:alesId");
Equvivalent XML code generated by the above code.
Code Block
<DTS:Executable DTS:ExecutableType="Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" DTS:ThreadHint="0">
<DTS:Property DTS:Name="ExecutionLocation">0</< FONT>DTS:Property>
<DTS:Property DTS:Name="ObjectName">Create SO</< FONT>DTS:Property>
<DTS:Property DTS:Name="DTSID">{4498C2BE-58B6-433C-8F3E-0268BD267BAE}</< FONT>DTS:Property>
<DTS:Property DTS:Name="Description"></< FONT>DTS:Property>
<DTS:Property DTS:Name="CreationName">Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask, Microsoft.SqlServer.ScriptTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91</< FONT>DTS:Property>
<DTS:Property DTS:Name="DisableEventHandlers">0</< FONT>DTS:Property>
<DTS:ObjectData>
<ScriptProject Name="ScriptTask_681442c33ab344b7bedb4492b01245a8" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="True" ReadOnlyVariables="" ReadWriteVariables="User::SalesId"/>
</< STRONG>DTS:ObjectData>
Generated XML when done thru SSIS Editor. i have given only the DTSObject..thats where i am struck.
Code Block
<DTS:ObjectData>
<ScriptProject Name="ScriptTask_d17b2b95a3984e37a536f2a6e017e2c9" Language="Microsoft Visual Basic .NET" EntryPoint="ScriptMain" SaveBinaries="True" ReadOnlyVariables="" ReadWriteVariables="User::SalesId">
<ProjectItem Name="dts://Scripts/ScriptTask_d17b2b95a3984e37a536f2a6e017e2c9/ScriptMain.vsaitem">
<![CDATA[' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
In the DTSObject, i dont know how to add details thru C# code. Please help me.
Thanks.
Creating Server Alias
Apr 3, 2002I have a SQL Server 6.5 db on a server, we need to migrate SQL Server to another server.
We have about 400 users connected to an OBCD driver pointing to the original server and
would like to connect them to the new server.
How can I do this without changing all the users OBCD settings?
Is there a function to alias the new server?
SQL Server CE Encountered Problems In Creating The SQL Sever CE Database.
Apr 24, 2007My exploit environment:
VS.net 2003, SQL Server 2000 SP4, SQL Server CE 2.0 SP4
I write next codes:
Private cn As New SqlCeConnection("data source=my documentsSQLCECF.sdf")
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
If Not System.IO.File.Exists("my documentsSQLCECF.sdf") Then
Dim en As New SqlCeEngine("data source=my documentsSQLCECF.sdf")
en.CreateDatabase() 'create a blank database to subscribe
Synch()
End If
Catch err As SqlCeException
ShowErrors(err)
Catch err As Exception
MsgBox("There was an error:" & err.ToString())
End Try
End Sub
Sub Synch()
Try
Dim rep As SqlCeReplication = New SqlCeReplication
With rep
.Publisher = "192.168.0.222"
.PublisherDatabase = "CFLab"
.PublisherLogin = "sa"
.PublisherPassword = "sa"
.Publication = "CFLab"
.Subscriber = "SQLCENETCF"
.SubscriberConnectionString = "Provider=Microsoft.SQLServer.OLEDB.CE.2.0;Data Source=My DocumentsSQLCECF.sdf"
.InternetUrl = "http://192.168.0.222/SQLCE/sscesa20.dll"
' Create the Local SSCE Database subscription
rep.AddSubscription(AddOption.CreateDatabase)
rep.Synchronize() 'subscribe
End With
Catch err As SqlCeException
ShowErrors(err)
Finally
End Try
End Sub
Public Sub ShowErrors(ByRef e As SqlCeException)
Dim errorCollection As SqlCeErrorCollection = e.Errors
Dim bld As System.Text.StringBuilder = New System.Text.StringBuilder
Dim inner As Exception = e.InnerException
If Not inner Is Nothing Then
MessageBox.Show(("Inner Exception:" & inner.ToString()))
End If
For Each err As SqlCeError In errorCollection
bld.Append("/n Error Code: " + err.HResult.ToString("X", System.Globalization.CultureInfo.CurrentCulture))
bld.Append("/n Message : " + err.Message)
bld.Append("/n Minor Err.: " + err.NativeError.ToString())
bld.Append("/n Source : " + err.Source)
For Each numPar As Int32 In err.NumericErrorParameters
If 0 <> numPar Then
bld.Append("/n Num. Par. : " + numPar.ToString())
End If
Next
For Each errPar As String In err.ErrorParameters
If String.Empty <> errPar Then
bld.Append("/n Err. Par. : " + errPar)
End If
Next errPar
MessageBox.Show(bld.ToString())
bld.Remove(0, bld.Length)
Next err
End Sub
Private Sub btnShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShow.Click
connect()
Dim query As String = "select * from FlightData"
Dim dap As New SqlCeDataAdapter(query, cn)
Dim dataset1 As New DataSet
dap.Fill(dataset1, "FlightData")
Disconnect()
End Sub
Sub connect()
Synch()
Try
cn.Open()
Catch err As SqlCeException
ShowErrors(err)
End Try
End Sub
Sub Disconnect()
Try
cn.Close()
Catch err As SqlCeException
ShowErrors(err)
End Try
End Sub
Private Sub dgdResults_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgdResults.Click
End Sub
End Class
I want to synchronize the data from a SQLCE SERVER 2000
When i run the code above, the errors show:
Error Code:80040E17
MessageQL Server CE encountered problems in creating the SQL Sever CE database.
Error Code:80040E17
A data source with the specified name already exists.
I do not know what's going wrong..=(
Who can help me??!!! HELP~~~~~~~~~
thanks..
Programatically Creating Dataflow Tasks Require Assembly Reference
Jan 17, 2007Hi,
I am creating dataflow tasj using following
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
These refer to Microsoft.SqlServer.DTSPipelineWrap.dll and Microsoft.SQLServer.DTSRuntimeWrap.dll. While these assemblies were already there in my dev machine I don't find these files in production enviornment for SQL server 2005. I am refering these assemblies from following path in my local machine : C:Program FilesMicrosoft SQL Server90SDKAssemblies.
How to install these assemblies in prod env, offcource one option is to copy it and then put it in GAC thru script . Why does not it gets installed while installation of SQL server 2005. Are these assemly dependent on SP1 ?
Thanks
Mohit
Script For Creating Alias Of Database
Oct 16, 2007
can any body help me on creating alias of database using script.
thanks in advance
Regards
Ganesh Gorla
Can't Map Other Sql Servers Without Creating Alias With Port Number On Client Network
Aug 24, 2005I can't map other sql servers without creating alias with proper port number on client network utility. Other users using same version of the client tools, MMC, SQL DMF etc. I need to map 70 sql servers on using my client tools.
Any help is appreciated.
thanks,
Can Create Alias Server1DBInstance1 On Server3DBInstance3 And Assign Objects To That Alias
Nov 24, 2015We will be moving 2 different databases (SS2005 & SS2008) to a new SS2014 SQL Server. Currently our codes looks something like Server1DBInstance1... & Server2DBInstance2... Is it possible to move the objects from these 2 instances to Server3DBInstance3 and then use an alias to reference the objects? Or does Server3 need to have DBInstance1 & DBIstance2? Basically, is the alias just for the database or for the instance too? Can I create an alias "Server1DBInstance1' on Server3DBInstance3 and assign objects to that alias?
View 16 Replies View RelatedCreating /accessing Tables With Dynamic Names
May 17, 2004Hi
I have developed an application in ASP/SQL server 7.
Ths system is single user. One of the tables is updated by usr actions( say Table A).
To make it multi user. I want to create table such as A_Username.
How can query be written for this. Also there are many stored procedures which will access this table. In these stored procedures i will send username as an input. Then the query in the stored procedure shd access the table as A_Username .
Such dynamic table name refrencing , how can it be done.. Is creating a string for the query and then executing it using sp_exec the only option?
pls suggest
Dynamically Creating Temp Table Names
Jul 20, 2005Hello,I am interested in dynamically creating temp tables using avariable in MS SQL Server 2000.For example:DECLARE @l_personsUID intselect @l_personsUID = 9842create table ##Test1table /*then the @l_personsUID */(resultset1 int)The key to the problem is that I want to use the variable@l_personsUID to name then temp table. The name of the temp tableshould be ##Test1table9842 not ##Test1table.Thanks for you help.Billy
View 5 Replies View RelatedSQL Server 2012 :: How To Set Some Restriction On Table Names While Creating
Nov 26, 2013I need to ensure some naming standards to users while creating tables.
All table name should be in same pattern
(ie., ) TeamName_EmpId_tablename
I need a way to restrict user to follow the naming standard. If any user tries to create table without the above naming standard system should not allow.
Creating External Record Of All Subscriptions And Report Names
Jan 9, 2008hi
i have been tasked with creating a record of all the reports ( 200+ ) within each folder ( bout 20 ) and all the subscriptions (including parameters) for our SSRS i was wondering what the best way of doing this was (SQL query?)
i have tried creating a site map with visio but im struggling to get the subscriptions details
anyone done this before (or have any ideas) i would be very grateful
thanks
Jake
SQL Sever 2000 And 2005 On Same Machine
Nov 21, 2006Hi, I am trying to create a connection string that will connect to a SQL Server 2005 Registration on a remote machine. I have a SQL Server 2000 registration (installed) on the same machine and so I think there may be a conflict. The SQL 2005 registration is denoted as HARDYSQL5 and the 2000 registration is merely HARDY (the machine name). I need to connect to a database on the HARDYSQL5 registration but I am not connecting. I have tried various connection strings but none have worked. I get a Login failure. I think this is red herring because I doubt its even finding the database.I can connect to the 2000 db with:"Server=HARDY;Database=Perimeter;UID=use;pwd=example;" But its fails with the following, for example:"Server=HARDYSQL5;Database=Perimeter;UID=use;pwd=example;" "Server=HARDYSQL5;Database=HARDYSQL5Perimeter;UID=use;pwd=example;" "Server=HARDY;Database=SQL5Perimeter;UID=use;pwd=example;" I'm gon go nuts. What am I doing wrong? Thanks.
View 10 Replies View RelatedSQL Sever 2005 Remote Uploads
Jan 12, 2008Hi,I created a small application to upload small video files on my
local machine and tested it to work perfectly on my local sql server
database. I then change the connection string to connect to the remote
database.When I do this and try to upload, the application hangs and
the video file is not uploaded. The file is small file only about 10MB
or less.When I upload the same file locally,it works perfectly fine and
uploads quickly without hanging.Here's a link to sample of my web.config filehttp://www.bleusolutions.com/sampleconfig.txt Here's a link to the pagehttp://www.bleusolutions.com/foto/uploadvideo.aspxI believe that this may be a database issue. I am running my
application locally
(http://localhost:4796/BleuS/foto/uploadvideo.aspx)and all I do is
change the web.config connection string to point to my local database
or to the remote database. When I point to my local database the application works fine as you can see: http://www.bleusolutions.com/images/localupload.jpgWhen
I run the application locally and change the connection string to point
to the remote database this is when I have problems with the same video
file. The application hangs for a minute or two and then it finally
finishes the upload. When I go to the database to view the table the
table takes a minute or two to open and then I get this message:http://www.bleusolutions.com/images/remoteupload.jpgAnd this is what the table looks like after I click Okhttp://www.bleusolutions.com/images/remoteupload2.jpg Any help on why this is happening or how I can fix this issue will be greatly appreciated. Thanks
CONCAT Function + SQL Sever 2005
Nov 29, 2006I cant find a clear answer to the syntax of concat in for SQL 2005. Can anyone help.
View 3 Replies View RelatedSQL Sever 2005 Express Backup
Mar 18, 2008How do you backup tables and stored procedures in SQL sever 2005 express? Also, I'm getting ready to load VS 2008 with SQL sever 2008. How will I move SQL sever 2005 express tables and procedures to SQL sever 2008?
How Do I Look Through The Log To See Updates To Tables In Sql Sever 2005?
Aug 22, 2006hi. changes were made to a dababase and we need to read the log file to see how much exactly was changed and what. how do i read the log file in sql server 2005 with out buying 3rd party software?
View 5 Replies View RelatedSQL Sever 2005 Only Has Client Side?
May 23, 2006Why I couldn't find any datebase engine setup?
SQL Server 2005 Developer
Windows xp
Mail Configuration In SQL Sever 2005
Nov 22, 2007Hi,
I have a requirement sql server 2005 in which i need to send a mail when ever there is an error in the procedure or job.
The mail should fire with the error attached to it.
could you please suggest the ways to handle this.
Please tell me the list of steps to configure and send the mail.
Thanks in advance.
How Can I Speed Up If There Is A Slowdown In SQL Sever 2005?
Jul 4, 2006
How can I speed up if there is a slowdown in SQL Sever 2005?
Please Help How To Add New Instance Of SQL Server 2005 On The Same Sever-new Name
May 14, 2008help
(i have one sql server installed in my server)
how can i add another databese server on the same server
like for example the server name is server-1
need to create
sever-1/sql_data1 ----------------- instance of SQL Server 2005
sever-1/sql_data2 -----------------new instance of SQL Server 2005
so i open my SQL Server Management Studio i see 2 servers !
and how many databese servers i can install in the same server 1,2,3,4......... what is the limit
is thre is examples with screenes picture of the install step by step for add
TNX
SQL Sever 2000 To SQL Server 2005
Jul 27, 2006Hi all,
I have an aplication in VB6 that uses an ODBC to link to a SQL database.
Until today all my clientes had SQL Server2000 and my aplication worked fine.
Some of my cliente have now SQL 2005 and my application still woks fine except in one of them.
In this particular cliente it gives an error when the application tries to read/write a particular table and a particular column.
This column is data type BIT and my code is
...
Myrs("Servida")= true
...
Any idea why the error appens? And why only in a particular machine and not all of them with SQL2005?
What can I do? I changed the code for
myrs("servida")=abs(cint(true)) and solved the problem, but is not the way that I want to go. I do not want to change thousands of line of code where I read/write a column in the database data type BIT.
Is there a way to configure SQL Server 2005 the way that it controls data type BIT?
Regards
Luis
How To Set SQL Server 2005 Transaction Timeout Programatically
Nov 26, 2007
Hello,
I need to execute a stored procedure from the data layer, that uses a transaction. And I have to override the default transaction timeout for this traansaction
I know how to change the transaction timeout form Management Studio, but I need to be able to change it only for that specific transaction (from the stored procedure's code or from the data layer)
Thanks in advance