Column Name

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - Sample code to importing data from ODBC Hi, there; I try to import data from ODBC using C# programmatically. Is there any sample code we can have a look. Like how to read schema from ODBC source table and then create source column... Thanks Tag: Column Name
  - 2
    - Add column with fixed number of values (text) to the select statement Your questions not clear properly. But I think this will help you, USE PUBS Go SELECT COUNT(*) row_num, A.au_lname FROM authors A, authors B WHERE A.au_lname > B.au_lname GROUP BY A.au_lname ORDER BY COUNT(*) Tag: Column Name
  - 3
    - SQL 2005 - Migration from SQL2000 / LEFT OUTER JOIN Could you run the code below and post the results back to the forum It would also be good if you could run the same code against a copy of your database attached to a SQL Server 2000 instance and again post the results back for comparison. Chris Code Snippet SET SHOWPLAN_TEXT ON GO INSERT INTO Interessent_abgleich_IAB ( IdINT_IAB , IdFIR_IAB , IdFAA_IAB , DELETE_IAB , IdMAR_IAB , Datum_IAB ) SELECT Interessent_INT . Id_INT , Interessent_INT . IdFIR_INT , Firma_adress_partner . Id_FAA , 1 , 'AM' , CONVERT ( DATETIME , '04.24.2007 09:48:23 ' , 102 ) FROM Interessent_INT LEFT OUTER JOIN Firma_adress_partner ON Interessent_INT . IdFIR_INT = Firma_adress_partner . IdFIR_FAD WHERE Id_INT = 4138 GO Tag: Column Name
  - 4
    - Editing Input Data in Script Component ronemac wrote: I can try that, but it seems to me that I will get the same resulting error Can you be more specific Try the derived column. Tag: Column Name
  - 5
    - SQL Server Management Studio Express won't open but appears in task-bar When I first installed SQL Server Management Studio Express everything worked great The next day, when I try to open SQL Server Management Studio Express I get the Startup Screen then all that happens is SQL Server Management Studio Express appears in the taskbar but no window appears I think my problem has something to do with a SQL related product crashing one time when I shut down my computer Does anyone know a solution to this problem Tag: Column Name
  - 6
    - Pass parameter to SSIS I have a SSIS package that imports (appends) data to a SQL table from an ODBC source. This package is run from within a VS project. I'd like to pass a parameter to this package to limit the records it imports. I have seen some discussion of this but I need a detailed step-by-step or a good working example. Specifically: 1. How to get the parameter to the package to begin with. The VB function that runs the SSIS only passes parameters to the stored procedure that executes the SSIS. 2. How to refer to the parameter value in the SQL statement of the data reader object in the SSIS package. Instead of saying 'Select * from table1' it needs to say 'Select * from table1 WHERE field1 = @passedparam' Tag: Column Name
  - 7
    - SQL Server 2005 Express Edition Is SQL Server 2005 Express Edition, compatable with Visual Basic 2008 Express Edition Tag: Column Name
  - 8
    - Upgrading SQL Express: is it possible to restrict to which version upgrade is possible? Greetings, I am about to let users upgrade their SQL Express installations to Standard Edition. They will all run the upgrade program by a command line argument that I will supply along with a setup.ini that I will supply. Is it possible by simple means "hack" the setup.ini file in order to only allow upgrade to x86 Standard Edition We have DVDs with all kinds of editions for both x86 and 64-bit and I would not want the users to accidentally upgrade to Enterprise Edition or a 64-bit Standard edition. Is this possible to attain by simply means or do I have to upgrade all clients myself Regards, Harald Hedlund Tag: Column Name
  - 9
    - Partitioned Tables and Parameters The parameter value is not part of the plan but rather one of the inputs to the execution itself. So the actual form of the plan is not dependant upon the value of the parameter. There is also an issue to watch for of confusion between the argument to your partition function (the variable in this case) and the identity of the partition for that value. This would be less confusing if the input to the partition function were alphabetic rather than an integer. SQL Server does not short circuit this and I suspect that the RangePartitionNew in the plan is actually a way of embedding the logic for determining the partition ID into the plan (so that it can determine at run time which partition to scan). If I take your example and change the partition key to a char(1) and use 'B', 'C', 'D' as the left limits then the final plan is: | --Table Scan(OBJECT [AJR_TEST].[dbo].[tblPartitionTest]), WHERE [AJR_TEST].[dbo].[tblPartitionTest].[PartitionKey]=[@param_partitionkey]) PARTITION ID RangePartitionNew([@param_partitionkey],(0),'B','C','D'))) The change in the arguments of RangePartitionNew indicates to me that the plan includes the information for it to determine the correct PartitionID from the value of the parameter so it is not scanning all the partitions. It is only placing all the information about the partitions into the plan so that it can determine the correction Partition ID at run time regardless of the value of the variable (allowing for plan reuse). Tag: Column Name
  - 10
    - Granting Permissions using SQL 2005 Schema... All, I have been asked to grant a Windows group Full access to all tables under our Sandbox Schema. This will allow these users to do anything to the tables under this Schema. I created the Windows Group (Sandbox Users), created the login in SQL, created the user in the database that is tied to the Windows group, then ran GRANT CONTROL ON SCHEMA::[Sandbox] TO [Sandbox Users]. I have verified that the users are in the Windows group, but they state that they still can not delete tables under the Sandbox Schema. Anyone have any ideas Thanks, Justin Tag: Column Name
  - 11
    - replication folder's didn't locate local publications,need help! Hi, Recently i has installed the TRIAL version of sql server 2005 where i downloaded it from microsoft's website. i plan to replicate the database for my mobile phone application project.I failed to find a 'local publications folder' which it supposes under the replications folder.I heard that the sql server 2005 express edition did not contain a 'local publications folder' which is under the replications folder, and i wonder whether the TRIAL version of sql server 2005 that i have installed in my pc is similar as sql server 2005 express edition.Do any experts know the different with the TRIAL version, Express Edition and Original Edition Tag: Column Name
  - 12
    - Returning a value that has the smallest number of instances in a table Hi Please help! I'm really stuck. I have a table (AssessorAllocations) that stores the data of students who have submitted assessments for marking by an Assessor. Each time an assessment is submitted by a student my program must determine which Assessor has the smallest workload and then assign the current assessment to that Assessor. My problem is how do I determine which Assessor has the smallest number of assessments assigned to him/her. The table columns are: AssessorRegistrationNo LearnerRegistrationNo QualficationName SubjectName AssessmentName DateAssigned Marked DateMarked Price So I'm looking for something like this: Select AssessorRegistrationNo from AssessorAllocations where {Select the Assessor who has the smallest number of AssessmentNames assigned to him/her where Marked='0' and DateAssigned<>''} Thanks Neil Tag: Column Name
  - 13
    - Error Casting String to Date In the list of installed product check the version of SSIS. It should be Version 9.00.3042.00 if you are on SP2. Tag: Column Name
  - 14
    - Reporting Services Matrix Reports Repeating row labels Is it possible to repeat the row labels for matrix reports where there are multiple groups. So if the matrix report rows are department,division,employee then have all three labels show up on each row not just at the breaks. Cannot seem to find a way to do this. thanks Tag: Column Name
  - 15
    - COUNT and COUNT_BIG are too slowly, what to do?! hello.. I used COUNT_BIG to get the number of rows but it was too slowly. I have 100,000 rows in the table. select COUNT_BIG (*) from topicstbl where partID like '" & PagePartID & "' the first execute for this query taked 5 secondes. and in another times it taked 1 second. this mean when the rows count will be 1 million it will take 10 secondes or more! what I have to do Tag: Column Name
  - 16
    - LDAP vs ADSI parameter problem Part III Dear friends, In LDAP query inside SQL Server, How can I return more than 1000 rows It's a limitation in this type of queries... Someone know to return more than 1000 rows Thanks! Tag: Column Name
- Next
  - 1
    - SERVER MESSAGES to textbox HI! i need to grab the "server message" from sql server when i execute a sql script. i would retrive all messages , 'print' output too in order to put the messages into a textbox. is this possible and how Tag: Column Name
  - 2
    - What is the easy way to do this?. Alter the table to add the new column Code Block CREATE TABLE #Test ( [TestID] INT IDENTITY(1, 1), [enddt] DATETIME, ) GO INSERT INTO #Test(enddt) SELECT '20071210' UNION SELECT '20071028' SELECT * FROM #Test GO ALTER TABLE #Test ADD [status] AS CAST((CASE WHEN enddt <= GETDATE() THEN 'live' ELSE 'expired' END) AS VARCHAR(20)) GO SELECT * FROM #Test GO Tag: Column Name
  - 3
    - SQL Server 2005 Log Shipping Error 14262 I am getting the following error " The specified servername does not exist " when I run the Log Shipping procedure. There is only a Primary server running Win Server 2003 withe Service pack 2 and SQL Server 2005 with service pack 2 and one secondary server runnung Win Server 2003 R2 with service pack 2 and SQL Server 2005 with service pack 2. All permission setting look ok . Can anyone help Tag: Column Name
  - 4
    - Is it possible to know whether report is still executing in report viewer? (Web Forms) Hi All, I have a Report Viewer and a Exit button in Web Form. Untill the report is processed i don't want to enable the button. so Is it possible to know whether report is still executing in report viewer I am using Remote Mode. Any help will be appreciated. Thanks Akash Tag: Column Name
  - 5
    - Help using copy database wizard I am trying to copy a database from one sql server 2005 to another. I am a sysadmin on both servers. I first tried copying the same database on the same server using the wizard and it worked :). However when I tried copying it to a different server, Execute SQL Server Agent Job fails. Unfortunately, I don't know the exact error; the error message ought to have been logged to windows event log, however after I open the event viewer, I can't find any error whatsoever logged to application. I tried having the error logged to a local directory as a text file and after the error occured, I still cannot find the error log :( The difference between copying a database from the same server to copying on different servers is the "Location of Source Database Files", included in the wizard. I am not sure what to place in the field "File share on source server". Do I need to create a file share folder Where can I have information on how and why it is necessary Thanks for taking your time to help me. Candy Tag: Column Name
  - 6
    - SQL2005 [264] An attempt was made to send an email when no email session has been established. Great...it worked and the link was very helpful. Tag: Column Name
  - 7
    - Variable Array like implementation using T-SQL Hi, I want to perform Oracle's VARRAY like implementation in SQL SERVER. How should I go about it I've done an implementation in Oracle where I have a VARRAY variable which stores a number of insert statements where each INSERT statement is a single array element. I then traverse through the VARRAY and executed each INSERT statements inside a loop. Tag: Column Name
  - 8
    - SSAS Client - OWC ? Anything else ? You can create reports in Reporting Services 2005 with SSAS2005 as a data source. If clients have Excel installed locally they can export the SSRS2005 report to Excel. It will save you some development time. Regards Thomas Ivarsson Tag: Column Name
  - 9
    - Existence of a value in table Hi, I have a table with the follwing values Table Cats { CatID, date ----------------------- Cat1, D1 Cat2, D2 Cat3, D3 } I just wanted to check whether Cat1 exists in the table. Can anyone post the query Thanks ~MOhan Tag: Column Name
  - 10
    - OLE DB Destination Component Jeff_LIU wrote: those two components can only use the input dataset as parameters, but can't update the data in input data source. What I want to know is if we can run SQLScript to directly update the columns in input dataset No. As I said in your other thread, you can update the columns in the data flow via a lookup transformation and a derived column transformation. Tag: Column Name
  - 11
    - Proxy web service to call SQL RS web service... issues? We have a security policy that requires us to have our web site call to an application server, which in turn calls to our database server. Firewalls are in place so only the web server has any exposure to the internet, and even that is proxied. The database server has the processing power and memory to run reports, but since we can't have the web server access it direcly, I thought that maybe we could create a proxy web service on the app server that "appears" to look like the SQL RS web service, and calls the SQL RS web service on the database server where the real work will be done. I figured ReportViewer would be able to point to that as the ReportServerURL. Has anyone tried something like this Are there any gotchas I should watch out for Is it a simple matter of using WSDL.EXE to generate a C# proxy class for SQL RS web service, then adding tags to expose the methods in the proxy class as web services Is there a better way of handling this Thanks, --Stan Tag: Column Name
  - 12
    - query designer very slow loading tables sorry i'm using sql 2005 in bis, here is a screen shot Tag: Column Name
  - 13
    - how can I save big file to NVARCHAR(Max) column in the database? Check my post: How to insert or update Blob data for SQLServer based on BUFFER_SIZE We have a better solution for SqlServer 2005 without TextPtr function. JDang Tag: Column Name
  - 14
    - Trigger throws exception error in Great Plains 8.0 Not sure if there's a GP 8.0 forum, so giving this one a go. I've added an AFTER UPDATE trigger to the RM00101 table (customer master) in a Great Plains 8.0 SQL Server 2000 SP4 DB. The trigger assigns field values to variables, constructs an update query, and executes the query on a table in a linked SQL Server 2005 DB. The trigger works fine when fired as a result of a direct field update made through Enterprise Manager. However, when the same update is made through the Great Plains GIU (customer card window), an exception error is thrown: "Unhandled database exception: A Save operation on table ��RM_Customer_MSTR�� failed accessing SQL data EXCEPTION_CLASS_DB DB_ERR_SQL_DATA_ACCESS_ERR�� The odd thing is that if I drop the trigger from the RM00101 table, the exception error still occurs. Not just on the record originally updated, but on all records and all fields within the record. Code for the trigger follows: SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE TRIGGER CTrig_Logic_Update_Customer ON RM00101 AFTER UPDATE AS IF UPDATE(CUSTNMBR) or UPDATE(CUSTCLAS) or UPDATE(CNTCPRSN) or UPDATE(STMTNAME) or UPDATE(SHRTNAME) or UPDATE(TAXSCHID) or UPDATE(ADDRESS1) or UPDATE(ADDRESS2) or UPDATE(ADDRESS3) or UPDATE(CITY) or UPDATE(STATE) or UPDATE(ZIP) or UPDATE(PHONE1) or UPDATE(FAX) or UPDATE(SLPRSNID) or UPDATE(PYMTRMID) or UPDATE(PRCLEVEL) or UPDATE(SALSTERR) or UPDATE(INACTIVE) or UPDATE(HOLD) or UPDATE(CRLMTAMT) BEGIN DECLARE @Server_Name Varchar(25), @Logic_DB_Name Varchar(25), @SQLStr nvarchar(1000), @CustomerN int, @SICCode int, @ARContact Varchar(35), @LongName Varchar(50), @CustomerName Varchar(24), @SalesTaxCode Int, @AddrLine1 Varchar(40), @AddrLine2 Varchar(40), @AddrLine3 Varchar(40), @City Varchar(30), @StateProv Varchar(4), @PostalCode Varchar(15), @TelephoneN Varchar(25), @FaxTelephoneN Varchar(25), @SalespersonN Int, @TermsCode Varchar(60), -- Put the customer terms into the CommentN1 field @CustRateSched Int, @SalesAreaCode Int, @InactivePurge Tinyint, @CreditStatus Tinyint, @CreditLimit Int ------- Get the new Customer data from Inserted table SELECT @CustomerN = CAST(RTRIM(i.CUSTNMBR) as Integer), @SICCode = ISNULL((SELECT Dex_Row_ID FROM RM00201 WHERE RM00201.CLASSID = i.CUSTCLAS),0), @ARContact = RTRIM(i.CNTCPRSN), @LongName = RTRIM(i.STMTNAME), @CustomerName = RTRIM(i.SHRTNAME), @SalesTaxCode = ISNULL((SELECT Dex_Row_ID FROM TX00101 WHERE TX00101.TAXSCHID = i.TAXSCHID),0), @AddrLine1 = RTRIM(i.ADDRESS1), @AddrLine2 = RTRIM(i.ADDRESS2), @AddrLine3 = RTRIM(i.ADDRESS3), @City = RTRIM(i.CITY), @StateProv = RTRIM(LEFT(i.STATE,2)), @PostalCode = RTRIM(i.ZIP), @TelephoneN = RTRIM(LEFT(i.PHONE1,10)), @FaxTelephoneN = RTRIM(LEFT(i.FAX,10)), @SalespersonN = RTRIM(i.SLPRSNID), @TermsCode = RTRIM(i.PYMTRMID), @CustRateSched = RTRIM(i.DEX_ROW_ID), @SalesAreaCode = ISNULL((SELECT Dex_Row_ID FROM RM00303 WHERE RM00303.SALSTERR = i.SALSTERR),0), @InactivePurge = i.INACTIVE, @CreditStatus = i.HOLD, @CreditLimit = i.CRLMTAMT FROM inserted i ------- Get Logic server name and database name SELECT @Server_Name = RTRIM(l.Server_Name), @Logic_DB_Name = RTRIM(l.Logic_DB_Name) FROM tbl_Logic_DB l ------- Insert new Customer record into Logic database SET @SQLStr = 'UPDATE [' + @Server_Name + '].[' + @Logic_DB_Name + '].dbo.[Customer] ' + ' SET SICCode = ' + CAST(@SICCode as varchar(10)) + ', ' + ' ARContact = ''' + @ARContact + ''', ' + ' LongName = ''' + @LongName + ''', ' + ' CustomerName = ''' + @CustomerName + ''', ' + ' SalesTaxCode = ' + CAST(@SalesTaxCode as varchar(10)) + ', ' + ' AddrLine1 = ''' + @AddrLine1 + ''', ' + ' AddrLine2 = ''' + @AddrLine2 + ''', ' + ' AddrLine3 = ''' + @AddrLine3 + ''', ' + ' City = ''' + @City + ''', ' + ' StateProv = ''' + @StateProv + ''', ' + ' PostalCode = ''' + @PostalCode + ''', ' + ' FaxTelephoneN = ''' + @TelephoneN + ''',' + ' SalespersonN = ' + CAST(@SalespersonN as varchar(10)) + ', ' + ' CommentN1 = ''' + @TermsCode + ''', ' + ' CustRateSched = ' + CAST(@CustRateSched as varchar(10)) + ', ' + ' SalesAreaCode = ' + CAST(@SalesAreaCode as varchar(10)) + ', ' + ' InactivePurge = ' + CAST(@InactivePurge as varchar(10)) + ', ' + ' CreditStatus = ' + CAST(@CreditStatus as varchar(10)) + ', ' + ' CreditLimit = ' + CAST(@CreditLimit as varchar(10)) + ' ' + ' WHERE CustomerN = ' + CAST(@CustomerN as varchar(10)) SET ANSI_NULLS ON SET ANSI_WARNINGS ON SET XACT_ABORT ON EXEC sp_executesql @SQLStr END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO Tag: Column Name
  - 15
    - SSIS OLEDB Source Data access mode (Table Name or View name variable) Yes, you are right. It works now. Thanks for your help. Tag: Column Name
  - 16
    - SSIS Logs Vs Custom Logs components Hi, In my case I have to log the errors raised by any task in a package to either windows event log, text file or SQL server. Also I need to send an email notifications to a group of people telling them about the error. Now can I use SSIS package logging for logging the errors into the required destinations. I mean right clicking on the package and selecting Logging, then adding the required log providers and enabling the events for logging into those. I think I have to upfront select the log providers to log the error, I will not have the liberty to log the error to the destination, the name of which is passed as a variable to the package. This is okay with me though. Now what will a custom log provider help me to do in this case. Also can I somehow configure my package to call the send mail task everytime an error is raised. Also, one more option can be developing a package that only does the error handling. It will take in the paramters or the error codes and descriptions, the destination to write to and a flag to send mail or not for that particular type of error. What do you think Kindly advise. Thanks in advance for your help and time. Regards, $wapnil Tag: Column Name

Lovericky

How to get column names and column data types of a particular table using T-SQl

Top

Re: Column Name

aquagal

select column_name,data_type

from information_schema.columns

where table_name='myTable'

Top

Re: Column Name

Madhu K Nair

You can also check sp_columns 'yourtablename' or Sys.Columns though best method is

information_schema.columns

Madhu

Top